Siebel using Oracle Cost Based Optimizer

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.