Debugging SQL with Oracle Cost Based Optimizer
I recently had a performance issue in Siebel 7.8 that on brief inspection looked like a SQL issue: the application was taking forever to build a list applet, re-query was just as slow, there weren’t any obvious calculated fields, no dodgy MVGs etc etc… it all pointed at the database.
So I did the obvious thing and spooled the SQL. Sure enough, the applet query was taking nearly 30 seconds to complete. Next step, fire up Toad to get an explain plan and start picking apart the SQL. Except when I pasted my query into Toad, it completed in half-a-second. The explain plan showed perfect use of the expected unique index scans, with no full table scans or other anomalies. Why the discrepancy?
After a bit of digging around, the answer came from our often-invaluable TAM: SupportWeb 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 /
In Toad, you add these statements and choose to ‘Execute as script’. 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.
In my case, the session variables allowed me to replicate the slow performance of the query in Toad, the explain plan of which showed Oracle using the wrong index, which identified the incorrect evaluation of costs, which (eventually) pointed to a root cause of stats being generated on empty tables. Obviously!
4 comments October 16th, 2007