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 184.108.40.206) 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 220.127.116.11 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.
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 18.104.22.168 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.