Oracle Scratchpad

December 8, 2013

12c Subqueries

Filed under: 12c,CBO,Execution plans,Oracle,subqueries — Jonathan Lewis @ 11:32 am BST Dec 8,2013

When you upgrade you often find that some little detail (of the optimizer) that didn’t receive a lot of attention in the “New Features” manuals introduces a few dramatic changes in execution plans. Here’s one example of a detail that is likely to catch a few unlucky people. We start with a very simple table which is just and id column with some padding, and then show the effect of a change in the handling of “constant subqueries”. Here’s my data set:


November 29, 2013

Interesting Plan

Filed under: Execution plans,Oracle,subqueries — Jonathan Lewis @ 8:56 am BST Nov 29,2013

A recent question on the OTN database forum included an execution plan that prompted one reader to ask: “but where has the existence subquery gone?” Here’s the original question showing the query, and here’s the later response showing the plan that prompted the question.

There were three possible reasons why that question may have been posed:


November 3, 2013

Not Pushing

Filed under: Bugs,Execution plans,Oracle,subqueries — Jonathan Lewis @ 5:24 pm BST Nov 3,2013

Here’s an odd little optimizer glitch – probably irrelevant to most people, but an indication of the apparent randomness that appears as you combine features. I’ve created an example which is so tiny that the only explanation I can come up with the for optimizer not “behaving properly” is that I’ve found an undocumented restriction relating to a particular feature.


June 28, 2013

Illogical Tuning

Filed under: CBO,Execution plans,Oracle,subqueries — Jonathan Lewis @ 6:55 pm BST Jun 28,2013

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):


December 7, 2012

Update Error

Filed under: Execution plans,Oracle,subqueries,Troubleshooting — Jonathan Lewis @ 5:10 pm BST Dec 7,2012

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:


November 21, 2012

Plan Order

Filed under: Execution plans,Oracle,subqueries — Jonathan Lewis @ 6:53 pm BST Nov 21,2012

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:


November 19, 2012

Plan timing

Filed under: Execution plans,Oracle,subqueries — Jonathan Lewis @ 9:59 pm BST Nov 19,2012

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:


August 9, 2012


Filed under: Bugs,Execution plans,Oracle,subqueries — Jonathan Lewis @ 7:04 pm BST Aug 9,2012

One of my recent assignments involved a company that had run into some performance problems after upgrading from to 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, and 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.

February 29, 2012

Missing Filter

Filed under: Bugs,Execution plans,Oracle,subqueries — Jonathan Lewis @ 9:30 pm BST Feb 29,2012

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 thing that would otherwise 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.


February 21, 2012

Not In – 2

Filed under: CBO,Execution plans,Oracle,Performance,subqueries,Tuning — Jonathan Lewis @ 9:24 pm BST Feb 21,2012

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


June 8, 2011

How to hint – 1

Filed under: dbms_xplan,Execution plans,Hints,Oracle,subqueries,Troubleshooting — Jonathan Lewis @ 3:00 pm BST Jun 8,2011

Here’s a quick tutorial in hinting, promped by a question on the OTN database forum.
The OP has a hash semi-join and Oracle appears to be ignoring a hint to use a nested loop:


April 27, 2011

Star Transformation – 2

Filed under: CBO,Oracle,subqueries,Tuning — Jonathan Lewis @ 6:13 pm BST Apr 27,2011

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.

September 7, 2010

CBO Surprise

Filed under: CBO,Execution plans,Oracle,subqueries — Jonathan Lewis @ 7:00 pm BST Sep 7,2010

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. In a comment attached to a note I had written about a possible bug relating to function-based indexes I was told 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.

August 31, 2010

Filter “Bug”

Filed under: Execution plans,Oracle,subqueries — Jonathan Lewis @ 7:01 pm BST Aug 31,2010

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:

May 19, 2009

Odd Filter

Filed under: Execution plans,subqueries — Jonathan Lewis @ 6:39 pm BST May 19,2009

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:

« Previous Page

Powered by