When creating the database session, the Siebel database connector makes the following session level changes
It is important to always remember these session hints when analyzing or tuning the performance of Siebel queries. For example, if you are executing a problematic SQL from SQLPlus, or reviewing an execution plan, and you did not set the above session hints, the plan that Oracle will use will most likely differ from the plan the optimizer uses for Siebel.
When analyzing Siebel-generated SQL outside of Siebel, it is therefore recommended to set the following session identifiers when opening the connection:
—it sets the optimization mode to First_ROWS_10, disables hash join, disables sort-merge, and, using two columns, enables a sanity check for the join operation.
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