Oracle Scratchpad

August 26, 2013

Index Sorting

Filed under: latches,Oracle,Troubleshooting — Jonathan Lewis @ 7:48 am GMT Aug 26,2013

From time to time I’ve mentioned the fact that the optimizer will sort indexes alphabetically by name as the last tie-breaker when two plans have the same cost. Thanks to an email that arrived a couple of days ago I’ve been alerted to event 10089 (which has been around since at least 8.1.7.4) with the description: “CBO Disable index sorting”.

Setting this event will disable the index sorting (which may result in some tied execution plans changing, of course) and may reduce contention on the row cache objects latches if you’re running 11.2.0.3 or later and doing a lot of hard parsing.

For further references, you need to read MOS note 1485410.1, or check out a couple of articles by William Tang: a pdf file of a short Powerpoint presentation, and a pdf file of some experiments and results.

Update – 3 hours later

When I first read through the various notes they looked like a perfectly reasonable explanation for the problem appearing on an upgrade – 11g tries far more query transformations than 10g, so it’s going to do more optimisation of query blocks.

Having published this note, though, and after receiving a couple of tweeter notifications I suddenly realised that I hadn’t thought things through properly: just because a single statement can go through many different transformations (which could result in the appearance of numerous different query blocks being reported in the 10053 trace) why should the optimizer search the dictionary cache for object definitions on every query block ? Surely it need only ransack the dictionary cache once in a “pre-transformation’ phase to acquire all the information about all the objects in the statement and copy that information into private memory.

I came up with three reasons why the note was extremely likely to be correct:

  • first – the people writing the optimizer code are not stupid and if (after all these years) my idea were feasible then it would already have been done;
  • second – some optimisations introduce new objects (such as materialized views – and this makes the problem more subtle than it might seem at first sight;
  • third – the function call referenced in the MOS note is KKOIQB – the KKO is about optimisation, maybe IQB is “initialise query block”.

So here’s another option that, if you know what you are doing, may help to reduce the contention: put some hints into the SQL to limit the transformations that the optimizer might otherwise consider – in particular the (no_)merge and  (no_)unnest hints might be appropriate, and there’s always the master hint: no_query_transformation.

Footnotes:

It occurs to me that adaptive cursor sharing and cardinality feedback could also cause the optimizer to increase the volume of optimisation – so another hint (to deal with adaptive cursor sharing) would be no_bind_aware.

The referenced bug number shows that problem is very specific to 11.2.0.3 and not earlier versions of 11.2 – so it doesn’t seem to be the number of transformations that are considered, per se – nevertheless, reducing the number of transformations considered may still help.

1 Comment »

  1. Just in on the OTN database forum, a reference to an old article by Alex Fatkulin that conveniently includes a query to connect v$row_cache with the x$ underneath v$latch_children. So when you see latch contention on the “row cache objects” latch you can work out which dc_thing it comes from.

    Comment by Jonathan Lewis — August 27, 2013 @ 3:12 pm GMT Aug 27,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

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,306 other followers