Alter Session Parameter for Siebel Query

I got surprised by the result of the execution time of one of the query, on which I was working on, as we were facing a performance issue in Production env while navigating one view.

As per the general process, I checked the Spool and Siebel Object Manager log to check for the query, which one is the culprit. I found the query which was taking around 70 seconds (as per the spool file). So that means I have won half of the battle, rest is to analyse the query and I will be thru. But now when I checked that query how it is behaving at the database, I ran the query on our Siebel Database (Oracle 9i) and got surprised when query returned the result in 200 seconds. This is something strange, how could this happened that if database is returning the result in 200 seconds then getting this result on UI is taking just 70 seconds.

In later investigation, I found that this is due to fact that whenever Siebel run any query on Oracle (Cost Based Optimizer), it sets few session variables for better executions of SQLs. So while verifying the SQL performance of a Siebel Client that is running on Oracle Cost-based optimizer mode, it is important to run the following alter session statements on the database :

For Oracle 9i

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

For Oracle 10g

alter session set optimizer_mode = first_rows_10
alter session set "_optimizer_sortmerge_join_enabled" = false
alter session set "_optimizer_join_sel_sanity_check" = true

After setting these parameters, query started behaving the same way as it was behaving on the UI. So, now the only thing we need to check is what is the execution plan is being generated by Oracle, and I found that there was a "Full Scan" for one of the where clause, despite of the fact an index already exists on that column. The Explain Plan query being used was :

Explain Plan for

"Query"

Select * from Plan_table

Now, our investigation was narrowed down to the point that since index was not being used by the query, that's why it is resulting in performance issue. We asked our DBA to regenerate the statistics for that particular index and finally performance issue was resolved.