Have you ever encountered a problem where, when you navigate to an applet in Siebel it takes time to populate the data and when you spool the query and execute it against the database it will work in less than a second
Try Analyzing the table and if it doesnt help try the work around below
Check the TechNote 582. The technote has all the background to the Cost Based Optimizer, and the key is the explanation of Siebel’s Oracle session variables. In summary, to make your SQL client behave like a Siebel session, you need to set the following session variables:
ALTER SESSION SET optimizer_mode = FIRST_ROWS_10
ALTER SESSION SET "_HASH_JOIN_ENABLED" = FALSE
ALTER SESSION SET "_OPTIMIZER_SORTMERGE_JOIN_ENABLED" = FALSE
ALTER SESSION SET "_OPTIMIZER_JOIN_SEL_SANITY_CHECK" = TRUE
The really significant variable here is that first one:
optimizer_mode = FIRST_ROWS_10.
This tells Oracle to optimize for the first page, rather than the whole query, which can dramatically change the way indexes are evaluated.