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. 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. 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.

Author: sercanbilgic

Current: Eproseed Position: Bigdata Senior Architect Past: Oracle Position: Engineered Systems Architect(Exadata,Exalogic,SuperCluster,Big Data Appliance) Past: Vodafone Position: Middleware Infrastructure Operations Senior Specialist Past: Accenture Position: Senior Developer/Programmer

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s