Debugging SQL with Oracle Cost Based Optimizer

October 16, 2007 at 9:38 am 4 comments

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!

Advertisements

Entry filed under: Siebel, SQL.

Discussion Forums for Siebel Why there may be a place for Direct SQL

4 Comments

  • 1. Andy C  |  October 16, 2007 at 6:15 pm

    Hi Stuart

    Great post. The issue of stats on empty tables is well documented (Alert 1162) but not widely known in the Siebel community.

    Another potential gotcha when debugging Siebel queries in TOAD is that people often substitute literals where Siebel Object Manager uses bind variables.

    The presence of literals forces Oracle to re-parse the query and potentially can generate a different plan (and hence different performance).

    Andy

  • 2. stuandgravy  |  October 17, 2007 at 9:39 am

    Good point about using bind variables – another thing that can catch you trying to debug.

  • 3. Mike Lin  |  November 27, 2007 at 10:54 am

    There was an interesting post on ITToolbox from Robert Ponder about the Oracle CBO parameter optimizer_index_cost_adj, which is a setting that tells the optimizer whether to favor index scans or full-table scans. Oracle’s default value of 100 (on a 1 to 1000 range) tells the optimizer that the cost of each is the same, so the optimizer weights them equally when determining the execution plan. Siebel recommends a value of 1, which tells the optimizer that index scans are much prefered over full-table scans.

    The new thinking is that this value might be too low, and a setting of 1 may result in a less-than-optimal plan to be chosen. I don’t fully understand it, so I can’t tell you what the sweet spot is. Robert is recommending a setting of 10, although an Oracle RAC expert recommended setting this parameter back to the default setting of 100.

    The way to fine-tune this may just be to start lowering the value from 100 until you get a peak in performance.

    For those of you thinking, “but isn’t it always better to do a index scan versus a full-table scan?” The answer is not always. It depends on the make-up of your data (especially table size), and your hardware. The faster your system is able to handle multi-block reads, or the fewer blocks that need to be read, the cheaper full-table scans become.

  • 4. stuandgravy  |  November 27, 2007 at 9:25 pm

    Great info, thanks Mike.

    Robert’s original post, for anyone interested.


Feeds


%d bloggers like this: