If you run a query that is supposed to return one row from a large table, and there’s a suitable index in place you would probably expect the optimizer to identify and use the index. If you change the query to return all the data (without sorting) from the table you would probably expect the optimizer to choose a full tablescan.
This leads to a very simple idea that is often overlooked:
Sometimes it takes just one extra row (that the optimizer knows about) to switch a plan from an indexed access to a full tablescan.
There has to be a point in our thought experiment where the optimizer changes from the “one row” indexed access to the “all the rows” tablescan.
If you’re lucky and the optimizer’s model is perfect there won’t be any significant difference in performance, of course. But we aren’t often that lucky, which is why people end up asking the question: “How come the plan suddenly went bad, nothing changed … except for a little bit of extra data?” All is takes is one row (that the optimizer knows about) to change from one plan to another – and sometimes the optimizer works out the wrong moment for making the change.
Oracle Scratchpad 
Jonathan,
you’re right of course, the transition from ‘index’ to ‘full table scan’ mode is of course a big CBO issue. It’s an even bigger issue when bind peeking is involved and Adaptive Cursor Sharing is also in play. When does the CBO decide to use the ‘other’ plan when new bind variables come along? Hmmm. Time for some research methinks.
Stelios
Comment by Stelios — July 15, 2011 @ 5:31 pm BST Jul 15,2011 |
Stelios,
I’m sure everyone will be interested in the results – as a quick starting point, there is a parameter _plan_verify_improvement_margin which suggests that a 50% improvement is needed for evolving plans under SQL Plan management, perhaps the same applies for ACS.
Comment by Jonathan Lewis — July 25, 2011 @ 8:42 am BST Jul 25,2011 |
You’re the preacher and I’m the choir on this one. One of the most important points I was trying to make in the physical ordering paper is the section on the proper correspondence of the number of rows being returned boundary between using the index and a full table scan with the cluster factor of the relevant index. I’ve been trying to make this point in one way or another for a long time going back to when we sometimes prevented the use of an index intentionally when it had what is calculated today as a bad cluster factor.
Thank you for stating it so succinctly in such clearly bounded logic!
Comment by Mark W. Farnham — July 15, 2011 @ 8:15 pm BST Jul 15,2011 |
It would be useful if Oracle could log where the optimizer had come out with two plans with very similar costs (eg less than 1% difference) so you had some advance warning of when a query might have its execution plan flipped from one to the other. I suppose it should be possible to extract that from a 10053 trace
Comment by Gary — July 16, 2011 @ 2:53 am BST Jul 16,2011 |
Gary’s idea is really interesting. Except in special cases you don’t want an entire 10053 trace to look through. But an option to record something like the sql_id and the non-winner plan outlines within some percentage of the estimated winner smells like an implementable idea to me. Quite a few possibilities errupt such as a way to easily transplant the non-winner plans to a test database for a run with –+ gather_plan_statistics leap to mind. This might not only help operational DBAs in the field pre-empt brewing problems but also provide an interesting set of feedback tests to the optimizer development group.
I don’t know whether that would be a different instance wide level of 10053 event setting or a different event or parameter entirely, but I think the idea has real promise.
Comment by Mark W. Farnham — July 16, 2011 @ 2:15 pm BST Jul 16,2011 |
Mark / Gary,
I think the idea is interesting in theory – but one practical difficulty is that there are likely to be so many plans with the same cost once the queries start to involve more than a couple of tables. Take a 10 table join: the cost of the whole join might be the same if you switch the order of the ninth and tenth tables – or the cost might vary depending by a tiny amount.
Mark,
There is a feature already that allows you to tell Oracle to use the “Nth join order” as the execution plan. It’s a hidden parameter that you can set at the session level. It probably wouldn’t take much more work to produce a tool that allowed you to scan the 10053 trace file for join orders that were evaluated to completion, generate the stored plans from the join order information, and test. The problem here is that lots of join orders would be discarded prematurely because a partial cost exceeded the current best cost.
Comment by Jonathan Lewis — July 25, 2011 @ 8:50 am BST Jul 25,2011 |
Following Gary’s idea. When a plan has a cost that is just a little better than another one, then choosing the lower cost may not be the best choice. I would prefer that the more stable plan is choosen in those cases, one that do not depend too much on the number of rows.
For example, the response time of a full table scan will not increase when there is less rows than expected. But for an index access, having more rows than estimated can make a big difference. Then I prefer the full table scan over an index access when the costs are in the same ballpark, even if the cost is a bit higher.
Comment by Franck Pachot — July 16, 2011 @ 4:58 pm BST Jul 16,2011 |
Franck,
That’s another very interesting line of attack. In particular it would be nice to have a feature that allowed Oracle to “vary it’s guesses”. Take the case of existence subqueries which introduce a 5% guess, it would be very useful if Oracle had a feature for evaluating (say) three different guesses – 5%, 50% 95% – and reporting the different plans that would appear.
The difficulty here, of course, is that the number of guesses could a massive explosion of work and outputs – think about the effects of having just 3 subqueries in a statement !
It would be nice to have a type of opt_estimate hint that allowed specification of the guess percentage on a subquery by subquery basis, though.
Comment by Jonathan Lewis — July 25, 2011 @ 8:54 am BST Jul 25,2011 |
The comparison between old and new plans must be possible.
Since we know when the plan is updated (after a stats run) and it’s possible to save our current plan as an outline plan (http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:470638000346821096) then I guess it’s possible to construct something to use the DBA_OUTLINES views to do a cost comparison between old and new and notify the DBA through OEM.
Comment by Darryl Griffiths — July 16, 2011 @ 6:31 pm BST Jul 16,2011 |
Darryl,
I think there’s already at least one tool in the market that does this sort of thing (possibly from Quest, or maybe Embarcadero). I’ve seen something that will take a query as its input and interrogate the optimizer with a huge number of hinted versions of the query and produce a list of plans with costs.
Comment by Jonathan Lewis — July 25, 2011 @ 8:56 am BST Jul 25,2011 |
Recently, a question came up on an Oracle French forum about when an execution plan changes from an index access path to a full table scan path. I couldn’t find a better explanation than what you’ve put in philosophy 15. This is why I’ve sent him back to your blog article.
But in that forum, they are encouraging French language; this is why I’ve translated this philosophy into the Moliere language
Sorry If I didn’t asked permission before that.
http://www.developpez.net/forums/d1164564-2/bases-donnees/oracle/sql/probleme-performance-select-jointures/#post6403427
Mohamed
Comment by hourim — February 11, 2012 @ 4:36 pm GMT Feb 11,2012 |
Mohamed,
No problem.
I thought I had a list of other items you had translated into French – but I’ve just realised that it was Franck Pachot. If you’ve got any others that you’ve translated let me know and I’ll collate a little catalogue for them.
Comment by Jonathan Lewis — February 15, 2012 @ 2:34 am GMT Feb 15,2012 |
Jonathan,
It’s always a pleasure for me to translate your articles.
I have the intention to translate all your “Philosophy” articles. When this is done, I will send them to you by e-mail
Comment by hourim — February 15, 2012 @ 7:29 am GMT Feb 15,2012 |