Oracle Scratchpad

January 18, 2007

Using 10053

Filed under: CBO,Execution plans,Hints,Ignoring Hints,trace files,Troubleshooting — Jonathan Lewis @ 8:51 pm BST Jan 18,2007

A little while ago I wrote a note about an anomaly that someone was seeing with the index_ss() hint. Since then, they forwarded me a 10053 trace file of the question.

I’ve posted brief analysis of it on my website, just to give you an idea of the sorts of things you can look out for when you’re trying to work out why Oracle seems to be ignoring a hint.

[Further reading on "ignoring hints"]

4 Comments »

  1. need to understand event 10053

    Comment by Abhay — February 8, 2007 @ 7:05 am BST Feb 8,2007 | Reply

  2. Abhay: Metalink has some notes, search for 10053 under the “Knowledge Base” area. The first item will (probably) be: this one which leads on to the case study

    For other assistance from the internet check Wolfgang Breitling’s paper (pdf)

    You could also get my book, which has a sample walkthrough of a 4-table join in chapter 14.

    Be aware of this comment from the Oracle pages though: “The 10053 is typically not the best place to start looking for tuning opportunities – the execution plan and TKProf have better information in this regard”

    Comment by Jonathan Lewis — February 8, 2007 @ 10:36 am BST Feb 8,2007 | Reply

  3. Hi Jonathan,

    May be you will be interested and also have a minute…
    Why does not CBO choose index with definitely least cost?

      Access Path: index (AllEqGuess)
        Index: DR$CTX__ADDRESSES$R                        --this index must be chosen and it really best for the query because it contains one column which is used in predicate.
        resc_io: 4.00  resc_cpu: 44231686
        ix_sel: 0.004000  ix_sel_with_filters: 0.004000 
        Cost: 1.04  Resp: 1.04  Degree: 1
      Access Path: index (FullScan)
        Index: DR$CTX__ADDRESSES$X
        resc_io: 164364.00  resc_cpu: 12221266764
        ix_sel: 1.000000  ix_sel_with_filters: 1.000000 
     ***** Logdef predicate Adjustment ****** 
     Final IO cst 0.00 , CPU cst 300.00
     ***** End Logdef Adjustment ****** 
        Cost: 8241.67  Resp: 8241.67  Degree: 1
      Access Path: index (FullScan)
        Index: DR$CTX__ADDRESSES01
        resc_io: 258336.00  resc_cpu: 12890482724
        ix_sel: 1.000000  ix_sel_with_filters: 1.000000 
     ***** Logdef predicate Adjustment ****** 
     Final IO cst 0.00 , CPU cst 300.00
     ***** End Logdef Adjustment ****** 
        Cost: 12940.83  Resp: 12940.83  Degree: 1
      ****** trying bitmap/domain indexes ******
      ****** finished trying bitmap/domain indexes ******
      Best:: AccessPath: IndexRange
      Index: DR$CTX__ADDRESSES$X                        --but this index was chosen, it is compound index and column from predicate is last
             Cost: 8241.67  Degree: 1  Resp: 8241.67  Card: 531604.58  Bytes: 0
    

    It is about a recursive query which is run Oracle Text index is being updated.
    By the way some unusual setting is in place
    optimizer_index_caching=90
    optimizer_index_cost_adj=5
    With default values for this two parameters CBO choose right index.

    Full 10053 trace https://docs.google.com/file/d/0Bx66iTw1xgqGc3R5UVNtSzYwRDQ/edit?usp=sharing

    Thank you

    Comment by Yuri — September 19, 2013 @ 5:23 pm BST Sep 19,2013 | Reply

    • Your query is:

      delete from DR$CTX__ADDRESSES$I
      where
       DR$ROWID = :rid
      

      The plan is reported as:

      --------------------------------------------------+-----------------------------------+
      | Id  | Operation         | Name                  | Rows  | Bytes | Cost  | Time      |
      --------------------------------------------------+-----------------------------------+
      | 0   | DELETE STATEMENT  |                       |       |       |  8242 |           |
      | 1   |  DELETE           | DR$CTX__ADDRESSES$I   |       |       |       |           |
      | 2   |   INDEX FULL SCAN | DR$CTX__ADDRESSES$X   |  519K |   37M |  8242 |  00:02:39 |
      --------------------------------------------------+-----------------------------------+
      Predicate Information:
      ----------------------
      2 - access("DR$ROWID"=CHARTOROWID(:RID))
      2 - filter("DR$ROWID"=CHARTOROWID(:RID))
      

      Note the function call in the predicate section. Your problem is an example of the “unknown range scan” dictating Oracle’s options for choosing a path.

      There’s actually a little clue in the fragment of the trace file you sent me – the (AllEqGuess) on the first access path: “All Equality with Guessing”. It’s a little puzzling why the optimizer should choose the path you want when you get rid of the silly parameter settings – but there may be a scaling factor that limits the rule of ignoring the guess, there may simply be an odd code path that gets followed because of some side effect of the parameter settings (I found a couple of odd cases in the calculations which I think I mentioned in CBO – Fundamentals)

      Comment by Jonathan Lewis — September 20, 2013 @ 9:52 am BST Sep 20,2013 | 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. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,514 other followers