The title is a bit of a joke, really. It’s mirroring a title I used a little over a year ago “Logical Tuning” and reflects my surprise that a silly little trick that I tried actually worked.
If you don’t want to read the original article, here’s a quick précis – I started with the first query, which the optimizer executed as a filter subquery, and rewrote it as the second query, which the optimizer executed as two anti-joins (reducing the execution time from 95 seconds to 27 seconds):
When doing updates with statements that use correlated subqueries, it’s important to make sure that your brackets (parentheses) are in the right place. Here are two statements that look fairly similar but have remarkably different results – and sometimes people don’t realise how different the statements are:
The previous post reminded me of another (fairly special) case where the order of operations in an execution plan seems to be wrong according to the traditional “first child first / recursive descent” strategy for reading execution plans. Here’s a simple select statement with its execution plan to demonstrate the point:
I’ve copied the following question up from a recent comment because it’s a useful generic type of question, and I think I can answer it in the few minutes I have to spare.
Hi Jonathan. I have a query plan where I cannot explain how time adds up. I did the ALTER SESSION trick but it changed nothing. I ran this, and got the following plan. I have two questions (I appoligize for not being able to format this code and plan but I saw not formatting buttons on the insert box).
1) @ step #8 in the plan, the query jumps to 3 and 1/2 minutes. This step says VIEW but gives no indication of what it did that actually took 3 and 1/2 minutes. Can you explain or give me some idea how to find out what is being done on this line that takes that long. Especially with so few rows.
2) the total elapsed time of the query was 3 minutes 45 seconds. This jives with step #1 that says 3:44.54. But if A-TIME is supposed to be among other things, the sum of child steps, then how to we explain step #2. Step #2 says 3 minutes 3 minutes 44 seconds. But the sum of its child steps (#3 and #8) is7 minutes and 25 seconds. Did this query do these steps “in parallel” so to speak? Or is there something really off in the numbers and if so how to I prove it?
This is not something that is super pressing but I sure would like to be able to explain the differences to people. It is making it somewhat harder to get people to believe I know what I am doing when I cannot such obvious issues as these two things.
Thanks, Kevin Meade
Fortunately Kevin included enough information to allow me to understand what was going on. In this case that means: “Here’s the query and the execution plan pulled from memory – showing the execution statistics”. As follows:
One of my recent assignments involved a company that had run into some performance problems after upgrading from 10.2.0.3 to 11.2.0.2. We had spent half an hour on the phone discussing the system before I had arrived, and I’d made a couple of suggestions that had solved most of their problems before I got on site – but they still wanted me to come in and give them some specific ideas about why the critical part of the solution had helped.
The most critical piece of advice I had given them (after listening very carefully to their description of the system) was to get rid of ALL the histograms they had on their system, then watch very carefully for any signs that they might need to re-introduce a handful of histograms over the next few weeks.
One of their critical queries completed in less that 2 seconds when histograms were removed but took 33 seconds to complete when histograms were in place. With their permission, the following notes record my investigation of this puzzle, the underlying Oracle bug (possibly not yet documented) that caused it, and the optimum workaround that was available to them.
(more…)
I see that Christian Antognini posted a note about an interesting little defect in Enterprise Manager a little while ago – it doesn’t always know how to interpret execution plans. The problem appears in Christian’s example when a filter subquery predicate is applied during an index range scan. It’s a topic I wrote about a few months ago with the title “filter bug” because the plan shows (or, rather, fails to show) a “missing” filter operation, which has been subsumed into the predicate section of the operation that in other circumstances would have been the first child of the filter operation: the rule of recursive descent through the plan breaks and the ordering that OEM gives for the operations goes wrong.
My note on “NOT IN” subqueries is one of the most popular on my blog, staying in the top 5 hits for the last five years, but it’s getting a bit old so it’s about time I said something new about “NOT IN” – especially since the Null Aware Anti Join has been around such a long time. The example I want to talk about is, as so often, something that came up as a problem on a customer site. Here’s a bit of SQL to model the situation, which is currently running under Oracle 11.1.0.7:
Here’s a quick tutorial in hinting, promped by a question (that’s no longer available) on the OTN database forum. The OP was trying to change a plan that was using a hash semi-join and Oracle appeared to be ignoring a hint to use a nested loop:
After publishing my note about emulating star transformations when there was something blocking the optimizer’s ability to do them automatically I ended up having an interesting email conversation with Mark Farnham about variations on the theme, concluding with a completely different (and possibly more intuitive) SQL statement to achieve the same aim.
(more…)
Well, it surprised me!
I’ve said for a very long time that in principle, ignoring bugs and restrictions, the optimizer will always choose the lowest cost option during its search for an execution path. It turns out that this isn’t true. I learned from comment 5 of this note about a possible bug relating to function-based indexes that there are cases where the optimizer follows a rule that allows it to ignore the lowest cost path if it is derived from a range-based predicate involving unpeekable bind variables. In short this means that:
there are cases where the optimizer can choose to discard an execution plan with a lower cost in favour of an execution plan with a higher cost.
Here’s an example to remind you how important it is to look at the “Predicate Information” supplied with an execution plan. Here are two execution plans that look very similar in shape – a continuous set of steps inwards and downwards from parent to child, with no “multi-child” parent rows:
(more…)
A little while ago someone sent me a brief email about an odd section of execution plan that they had seen. To make things a little more challenging they didn’t send the SQL statement, and they only sent me a few lines from the middle of the plan to see if I could explain what was going on. This is what is looked like:
(more…)
Oracle Scratchpad 