Oracle Scratchpad

February 25, 2008

IOTs and 10053

Filed under: CBO,Execution plans,Infrastructure,IOT,Oracle,trace files,Troubleshooting — Jonathan Lewis @ 9:03 pm GMT Feb 25,2008

I’ve probably got a couple of comments about 10053 trace files lurking somewhere on this blog and on my website – and when I mention the 10053 I usually remember to say that it’s a last resort that I only use when I think there may be a bug that needs to be nailed.

So here’s a bit of a 10053 – which I only looked at because I thought it was going to show me a bug.

The problem appeared in an update of an index organized table (IOT). The table had about 12,000,000 rows and the update was supposed to update about 2,200 of those rows. To make the update more efficient there was a secondary index on the IOT – but the optimizer was not using it, despite the fact that most of the rows in the table were null for that column leaving only about 2,400 entries in the (tiny) secondary index.

Here’s the critical part of the 10053 trace – showing why the optimizer prefered to do a fast full scan (with a cost of 42,000) of the IOT, rather than doing an indexed access path with a cost that would have been about 4,400.

BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: CF_LOCKS  Alias: CF_LOCKS
    #Rows: 11819932  #Blks:  150126  AvgRowLen:  10.00
Index Stats::
  Index: CF_LOCKS_OWNER_IDX  Col#: 2
    LVLS: 1  #LB: 8  #DK: 23  LB/K: 1.00  DB/K: 43.00  CLUF: 994.00
  Index: CF_LOCKS_PK  Col#: 1
    LVLS: 2  #LB: 150126  #DK: 11819932  LB/K: 1.00  DB/K: 1.00  CLUF: 0.00
***************************************
SINGLE TABLE ACCESS PATH
  Column (#2): OWNER_ID(NUMBER)
    AvgLen: 2.00 NDV: 23 Nulls: 11817695 Density: 2.2351e-04 Min: 2 Max: 927154736
    Histogram: Freq  #Bkts: 23  UncompBkts: 2237  EndPtVals: 23
  Table: CF_LOCKS  Alias: CF_LOCKS
    Card: Original: 11819932  Rounded: 2202  Computed: 2202.00  Non Adjusted: 2202.00
  Access Path: index (index (FFS))
    Index: CF_LOCKS_PK
    resc_io: 40661.00  resc_cpu: 3078501741
    ix_sel: 0.0000e+00  ix_sel_with_filters: 1
  Access Path: index (FFS)
    Cost:  42385.26  Resp: 42385.26  Degree: 1
      Cost_io: 40661.00  Cost_cpu: 3078501741
      Resp_io: 40661.00  Resp_cpu: 3078501741
OPTIMIZER PERCENT INDEX CACHING = 0
  Access Path: index (AllEqRange)
    Index: CF_LOCKS_OWNER_IDX
    resc_io: 23270982.21  resc_cpu: 168050348534
    ix_sel: 0.98435  ix_sel_with_filters: 0.98435
    Cost: 23365106.80  Resp: 23365106.80  Degree: 1
  Access Path: index (FullScan)
    Index: CF_LOCKS_PK
    resc_io: 150128.00  resc_cpu: 3433113944
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 152050.88  Resp: 152050.88  Degree: 1
  ****** trying bitmap/domain indexes ******
  Best:: AccessPath: IndexFFS  Index: CF_LOCKS_PK
         Cost: 42385.26  Degree: 1  Resp: 42385.26  Card: 2202.00  Bytes: 0        

You may want to open a second window so that you can leave the trace visible while reading my comments.

The update statement reads:

 update cf_locks set owner_id = null where owner_id = {constant}; 

The owner_id column is the one with the secondary index.

Starting from the top – you can see that the table has 11,819,932 rows, and the index on owner_id (cf_lock_owner_idx) has a blevel (LVLS) of 1 and only 8 leaf blocks (#LB). By comparison the primary key for the table has a blevel of 2 and 150,126 leaf blocks.

The column stats under the heading Single Table Access Path tells us that the owner_id column has a frequency histogram in place, with 23 distinct values. We can infer that there are 2,237 non-null entries in the index, and trust that the optimizer’s calculation of 2,202 rows (Card: Computed:) is based accurately on the histogram.

When we examine the cost of the access path using the cf_locks_owner_idx, we should expect it to be in the order of 4,404 (roughly speaking that’s 2,202 probes into the PK which has a blevel of 2). In fact we see that the I/O cost (resc_io) is reported as 23,270,982.21 – which seems pretty extreme. Clearly, this is a bug – either in the model, or in the code.

So can we guess where that number comes from? This is my best guess.

We are expecting 2,202 rows from a possible 2,237 in the index. As you can see, that’s quoted as a selectivity(ix_sel) of 0.98435. What would you get if you applied that selectivity to the number of rows in the table, rather than applying it to the number of entries in the cf_locks_owner_idx index – and then multiplied by the blevel ?

11,819,932 * 0.98435 * 2 = 23,269,900

That’s remarkably close to the cost of 23,270,982 produced by the optimizer. Sufficiently close that I’m not going to get out a calculator and cross the t’s and dot the i’s – I think we can probably make a fairly safe bet that we’ve found a bug in the code for calculating the cost of a secondary index on an IOT – possibly relevant to the cases where the indexed column is mostly null. (I recall that there was a recent change in the way the optimizer dealt with columns showing more than 5% nulls – maybe that change wasn’t implemented completely in this special case.)

I usually try to avoid reading through 10053 traces – the output from dbms_xplan (from an explain plan, or from v$sql_plan) is usually enough to supply all the information needed to solve an optimizer problem – but sometimes the numbers in the 10053 can give you a very clear clue about the problem.

3 Comments »

  1. Nice analysis Jonathan..Your blog is very useful for budding Oracle DBA’s like me :-)

    Comment by Amit — February 26, 2008 @ 4:25 am GMT Feb 26,2008 | Reply

  2. I am still puzzled at the multiplication of (rows * selectivity) by the Blevel. We cannot predict how much of index blocks will be in the cache at the SQL start however we can predict usually that it is more that the stated OPTIMIZER PERCENT INDEX CACHING = 0. Even if there are no blocks at start, after some access to the first leafs blocks we should have loaded some branch blocks so that subsquent leaf reads will not have to hard read all branch blocks. But Oracle seems to consider that every access to a leaf blocks comes with its branch block IO and that’s truely not the case.

    Comment by Polarski Bernard — February 27, 2008 @ 7:33 am GMT Feb 27,2008 | Reply

  3. Bernard,

    Your interpretation of the numbers and the way the model probably doesn’t match reality is something that I agree with. The fraction of an index that goes into the branch blocks is usually tiny – often less than 1%, especially when it’s an IOT carrying a few extra table columns – so you have a good chance of finding the branch blocks of a popular in memory.

    That’s why it’s still reasonable to consider changing optimizer_index_caching from its default value of zero, even in 9i. However,you have to remember that its affect only applies to nested loop joins and in-list iterators. (And, in some ways, accessing an IOT through a secondary index like a nested loop join – and the parameter change can have an impact).

    There are all sorts of odd details that apply to the IOT calculations, by the way, and I think some of them relate to refining ‘generic’ cost code for indexes to the special case for IOTs and some of them are bugs (I have some very odd restults from setting optimizer_index_caching to anything other than zero in 10.2.0.1, for example). My comment about ‘two block visits per key’ was only indicative of the sort of thing you might expect – what actually happens in the arithmetic varies with version, environment, data content, and specific query.

    Comment by Jonathan Lewis — March 1, 2008 @ 2:19 pm GMT Mar 1,2008 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,521 other followers