Knowing what you want to achieve before thinking of how to achieve it – a query optimization example

Today I received a question which was a good example of systematic problem solving approach. It was about getting a long-running query to run faster. It took a long time as the correlated subquery in the query was not unnested, was re-visited many times, causing the whole subquery subtree in the plan to be executed again and again). The main part of the question was this:

Is there a way to avoid “NOT IN” conversion to “NOT EXISTS” by optimizer … My sub query, I would like it to be fully instantiated as view and then execute it as a Hash Anti join.

The first part of the above question sounds like the type of question I hear quite frequently (e.g. how to force Oracle use an index or adjust some feature of the execution plan – without any extra context information). These kind of questions are often the result of the developer not being familiar with the data and hoping to optimize the query by adding some “silver bullet” hint.

But the second part of the above question (in bold) clearly shows that the asker had done his homework and knew exactly what he was trying to achieve. In other words, the asker knew the data model, the data (amount and distribution), understood what the query was trying to achieve and finally knew roughly how many rows would be returned from different tables for the given query predicates. And based on that knowledge and understanding of how Oracle SQL plan execution works, he knew that it’s not a good idea to use the nested FILTER operator for correlated subquery lookups, as if the parent query returns millions of rows, you’d potentially have to re-visit the subquery branch of execution plan tree millions of times too, revisiting the same blocks again and again, driving up (logical) IOs, CPU usage etc.

The asker also knew that Oracle can use a special variation of hash join for performing anti-joins (NOT IN, NOT EXISTS style subqueries get unnested and are executed using a join mechanism instead of correlated lookups using FILTER loop). The asker also knew that it’s much more efficient to join / compare millions of rows with a single hash join run (visit source tables only once) as opposed to millions of correlated lookups revisiting “random” index & table blocks again and again.

As a result, the asker formulated what he was trying to change in the execution plan and then thought how to achieve it. This is the systematic way for tuning a SQL statement and it requires you to understand the query, database capabilities and definitely the data this query is using. The problem was that regardless of the hints he tried, he couldn’t achieve the desired plan. (Note the “desired plan” – you know in advance what you want and use tools like hints for achieving that, not the other way around).

I came up with a small test case (as I often don’t remember things off the top of my head) to illustrate the correct hints for changing the plan the way he wanted:


Table created.


Table created.


Table created.

Now the minimal test query (its quite simplistic and note that it’s not even a correlated subquery as the subquery predicates do not reference parent query’s tables):

WHERE t1.user_id NOT IN (
    SELECT t2.user_id
    FROM t2, t3
    WHERE t2.username = t3.username

The CBO trace showed the final form of the query after transformations – and the uncorrelated NOT IN subquery was converted to a correlated NOT EXISTS subquery:

******* UNPARSED QUERY IS *******

SELECT /*+ */
        SELECT /*+ */ 0 FROM "SYSTEM"."T2" "T2","SYSTEM"."T3" "T3"

The execution plan uses a correlated FILTER loop for probing the subquery, even though the nature of this query doesn’t really require correlated access. As a result, the HASH JOIN in line #3 below and the table access rowsources under it were visited 26 times, in this case once per row returned from the parent query’s row sources (table T1in line #2):

| Id  | Operation           | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
|*  1 |  FILTER             |      |      1 |        |            |      0 |00:00:00.01 |     159 |       |       |          |
|   2 |   TABLE ACCESS FULL | T1   |      1 |     26 |     2   (0)|     26 |00:00:00.01 |       3 |       |       |          |
|*  3 |   HASH JOIN         |      |     26 |      1 |     5  (20)|     26 |00:00:00.01 |     156 |  1517K|  1517K|  301K (0)|
|*  4 |    TABLE ACCESS FULL| T2   |     26 |      1 |     2   (0)|     26 |00:00:00.01 |      78 |       |       |          |
|   5 |    TABLE ACCESS FULL| T3   |     26 |     26 |     2   (0)|    351 |00:00:00.01 |      78 |       |       |          |

Predicate Information (identified by operation id):

   1 - filter( IS NULL)
   3 - access("T2"."USERNAME"="T3"."USERNAME")
   4 - filter("T2"."USER_ID"=:B1)

The pseudo-bind variable :B1 reported in predicate section gives a further clue that a correlated lookup loop is used, the FILTER operation takes the next parent query row’s USER_ID value and passes it in to the filter predicate on line #4 above with every loop iteration. The execution plan unparser (which generates human readable execution plan texts) uses a naming convention so that such FILTER loop variables are reported as bind variables – but the query doesn’t actually use any real (user supplied binds). It’s just how these query plan explainers report things…

Anyway, as I knew what kind of change we wanted to achieve, I recommended to use the following hints to see whether they give the desired result.

WHERE t1.user_id NOT IN (
    SELECT /*+ UNNEST HASH_AJ(t2) */
    FROM t2, t3
    WHERE t2.username = t3.username

And now the important part – this wasn’t a wild guess (like “let’s see, maybe it helps”). I deliberately recommended these hints as they control the features we wanted to change.

The resulting unparsed query text after transformations was following – the subquery was gone (no NOT IN or NOT EXISTS anymore) and the subquery was unnested and converted into an inline view (in bold), joined to the parent query (using the where condition in the bottom of the below output):

******* UNPARSED QUERY IS *******

    (SELECT /*+ UNNEST */
        "T2"."USER_ID" "$nso_col_1"
        FROM "SYSTEM"."T2" "T2","SYSTEM"."T3" "T3"
    ) "VW_NSO_1"
   ,"SYSTEM"."T1" "T1"

A little detour:

Note that the “unparsed” query text in CBO tracefile is FYI only. It is not the real query text actually executed! It doesn’t contain all the little details required for executing the query right and returning correct data. Unparsing means extracting information from the binary execution plan and converting it into human (or DBA) readable form, but some of the information gets lost in the process. If you ran the above unparsed query, you would get resultset of a regular join, not the NOT IN antijoin originally requested. This is why you should never assume that the unparsed query is the correct text to use in your application. It’s just FYI, for debugging.

One of the pieces present in binary execution plan, but not in the unparsed query, is highlighted below. See the HASH JOIN ANTI – this ANTI in the binary plan says that don’t use a regular join (returning matching rows) but do exactly the opposite (return non-matching rows).

| Id  | Operation            | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
|*  1 |  HASH JOIN ANTI      |          |      1 |      1 |     7  (15)|      0 |00:00:00.02 |       9 |  1023K|  1023K| 1176K (0)|
|   2 |   TABLE ACCESS FULL  | T1       |      1 |     26 |     2   (0)|     26 |00:00:00.01 |       3 |       |       |          |
|   3 |   VIEW               | VW_NSO_1 |      1 |     26 |     5  (20)|     26 |00:00:00.01 |       6 |       |       |          |
|*  4 |    HASH JOIN         |          |      1 |     26 |     5  (20)|     26 |00:00:00.01 |       6 |  1179K|  1179K| 1180K (0)|
|   5 |     TABLE ACCESS FULL| T2       |      1 |     26 |     2   (0)|     26 |00:00:00.01 |       3 |       |       |          |
|   6 |     TABLE ACCESS FULL| T3       |      1 |     26 |     2   (0)|     26 |00:00:00.01 |       3 |       |       |          |

Predicate Information (identified by operation id):

   1 - access("T1"."USER_ID"="$nso_col_1")
   4 - access("T2"."USERNAME"="T3"."USERNAME")

When you look into the Starts column in the above plan, you see that now all the tables were scanned only once (that’s what hash joins do). So, no more revisiting the same blocks again and again for each row returned from table(s) in the parent query. Also, note the new VIEW row source which Oracle has injected in between the parent query and subquery tables – this is to make sure that the subquery tables don’t get merged with parent query and joined in a wrong order / possibly with the ANTI-join done in the wrong place. In the stored outline hints, such injection will show up as a NO_ACCESS hint (telling optimizer that the parent query block does not have access into a query block under it for merging its contents).

In any case, now my little test query visited much less buffers, did less work, did not repeatedly scan through the same tables again. Of course, this was just a synthetic test query – but nevertheless, applying the same hints on the real problem query made it run in 2 minutes instead of multiple hours.

Now, there are some outstanding questions here, like whether it’s a good idea to tune a query with hints and whether the optimizer statistics were representative of the reality and so on. Why didn’t optimizer come up with the best plan itself? Was this due to a bug, etc.

But all this isn’t the point of my blog post – my point is that whenever you want to systematically fix or improve something, you will have to know what you are trying to achieve first, also understand why should this actually work and then you’ll pick the best tools for achieving that goal (how). As the guy who asked the question had already done most of the work – figuring out how the plan should be executed for best efficiency – finding the way to achieve that was easy.

When manually optimizing SQL, this does require that you actually care about your work enough to take the time to undestand the capabilities of your database engine, the query and the data. Of course you have to be smart about where you optimize manually and where you should try to get Oracle to do the right thing by itself. Phew, I’ve got to write about that stuff some other day :-)

Update: Thanks to Jonathan Lewis’es comment I drilled down to this example further and it turns out that in this case just using UNNEST hint would be enough (and HASH_AJ is not needed) as after the unnesting is forced, the CBO figures the best (anti)join method out from there. Note that this post wasn’t about hints, it was about emphasizing that if you want to systematically tune a SQL statement execution plan, you should first figure out what it should be doing (which join order, -methods, access paths etc) and then find a way to achieve that – in my case it was done with hints.

P.S. I will schedule my Advanced Oracle SQL Tuning online course in september or october – stay tuned ;-)

NB! If you want to move to the "New World" - and benefit from the awesomeness of Hadoop, without having to re-engineer your existing applications - check out Gluent, my new startup that will make history! ;-)

This entry was posted in Oracle and tagged , . Bookmark the permalink.

14 Responses to Knowing what you want to achieve before thinking of how to achieve it – a query optimization example

  1. > you’ll pick the best tools for achieving that goal
    Should that be to use a deprecated hint though?

  2. Tanel Poder says:

    @Dominic Brooks

    Dominic, this is of course a very good and valid point.

    You should ideally avoid deprecated stuff, having future in mind. Also, it’s a good idea to try to avoid micromanaging query execution plans (via hints for example) as well. This is not always possible in real life though – sometimes you do have to “take the screwdriver” and fix Oracle yourself :)

    Actually one of the first things what I replied to the person asking the question was that let’s try to adjust the query plan as you wanted (with the hint in this case) to see whether this change would help at all. And once this is validated, THEN see why doesn’t Oracle figure out that better plan by itself (by checking and fixing cardinality misestimates for example).

    So, instead of first FIXING the problem (re-gathering stats, or creating histograms, which will affect all other SQLs too) and seeing whether that fix actually fixed anything – we take the opposite approach, we understand the query, data, understand where the inefficiency comes from, understand what would be a better order and layout for executing the query – then VALIDATE it with whatever tools (hints in this case).

    Once you have validated & found the new best execution plan, you’ll see how to make it stick (keep the hints in, create a stored outline/plan baseline or adjust the cardinality estimates (stored profile) or optimizer stats)

  3. Tanel Poder says:

    Note that the reason for deprecation of the various anti-join/semi-join hints is that these transformations are now cost-based and manual controlling via hints shouldn’t be needed – this all assumes that the CBO does a good job (and the stats are good enough etc).

  4. Tanel,

    I’m not sure that the hash_aj (et. al.) hints are deprecated because of costing. I had assumed that is was simply that you need only say “unnest and hash join” and the optimizer is implicitly aware of the fact that it has to be an anti-join and doesn’t have to be told explicitly.

  5. @Tanel Poder
    “understand the query, data”

    This is key.

    But it’s not necessarily quick or easy.

    Understand the query and data and often there’s probably a better way to express the query that results in a better plan.

  6. Darshan says:

    Unnesting of Nested Subqueries in the Doc mentions hash_AJ. I could not find where it is deprecated.


    This issue was coming in database. The person tried 100% stats on all the objects involved with and without histograms. As per application team they cannot change the sql. Hint can be applied.

  7. Tanel Poder says:

    @Jonathan Lewis
    Ha, I trust that what you’re saying is more correct.

    In fact the HASH_AJ hint is redundant – at least in my test case, it’s enough to have the UNNEST hint in place and CBO figures out to execute the anti-join with hash join itself.

    I remember that back in non-cost-based transformations one had to always explicitly use an _AJ or _SJ hint or use _always_anti_join (or _always_semi_join parameter). That’s why I figured the parameter deprecation was related to cost based query transformation rollout…

    All said in the blog still applies though, only instead of 2 tools (UNNEST and HASH_AJ hint) you would have to use one (UNNEST hint only) – or take a step back and invest some time into figuring out why doesn’t optimizer make this decision for you.

    Thanks Jonathan and Dominic for very good comments!

  8. Tanel Poder says:

    @Dominic Brooks
    “But it’s not necessarily quick or easy.”

    Yes indeed – as a consultant (having never seen the database before) it can be particularly hard if someone asks you to “take a quick look” into this SQL. Luckily the SQL monitoring and the “gather plan statistics” features allow to (pin)point to the problem area faster…

  9. Tanel Poder says:

    Yep – sometimes – especially in the real world ;) – you don’t have other options than controlling some plans using hints. Especially if you know exactly what the plan should be doing and CBO does not :)

    The deprecation notice is in 10.1 docs:

  10. Flado says:

    > … still using a (uncorrelated) subquery

    It looks correlated to me:
    > “T2″.”USER_ID”=”SYS_ALIAS_1″.”USER_ID”

    Or have I missed something?


  11. Tanel Poder says:


    Flado, that’s what happens when finishing the article at 4am. I had messed up that statement – the details were a bit more complex. Basically the original subquery (NOT IN) was a non-correlated query as the subquery did not reference the parent query tables at all. But optimizer / query transformer considered to transform the query into a correlated NOT EXISTS instead. Thanks for noticing the mistake!

  12. Milen Kulev says:

    Hello Dominic,
    ok, in this case the HASH_AJ was necessary, but for the sake of completeness the hint
    HASH_AJ is not deprecared at all:

    SQL> select * from v$version;

    Oracle Database 11g Enterprise Edition Release – 64bit Production
    PL/SQL Release – Production
    CORE Production
    TNS for Linux: Version – Production
    NLSRTL Version – Production

    SQL> select NAME , CLASS from v$sql_hint where name like ‘%HASH%’ order by NAME ;

    —————————— —————————————-
    HASH_AJ ANTIJOIN select NAME , CLASS , VERSION from v$sql_hint where name like ‘%ANTI%’ ;

    —————————— —————————————- ————————-

    Best Regards

  13. @Milen Kulev
    For the sake of completeness…

    Deprecated means just that deprecated not invalid … yet
    Tanel linked to the official 10.1 deprecation notice above.

    Presence in V$SQL_HINT is not an indication of deprecation or otherwise.
    For example, if you look in the 11.2 documentation, you will see that NOPARALLEL is deprecated as of 11.2. It is still present in V$SQL_HINT.

  14. antony says:

    In my test database(,i don’t have to add a hint to force unnesting because of the following default parameter.


    SQL_ID 17d2t9tgp0b3w, child number 0
    select * from t1 where t1.user_id not in(select t2.user_id from t2,t3
    where t2.username=t3.username)

    Plan hash value: 2149164814

    | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
    | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 9 | | | |
    |* 1 | HASH JOIN ANTI | | 1 | 37 | 0 |00:00:00.01 | 9 | 1011K| 1011K| 1258K (0)|
    | 2 | TABLE ACCESS FULL | T1 | 1 | 37 | 37 |00:00:00.01 | 3 | | | |
    | 3 | VIEW | VW_NSO_1 | 1 | 37 | 37 |00:00:00.01 | 6 | | | |
    |* 4 | HASH JOIN | | 1 | 37 | 37 |00:00:00.01 | 6 | 1156K| 1156K| 1262K (0)|
    | 5 | TABLE ACCESS FULL| T2 | 1 | 37 | 37 |00:00:00.01 | 3 | | | |
    | 6 | TABLE ACCESS FULL| T3 | 1 | 37 | 37 |00:00:00.01 | 3 | | | |

    Predicate Information (identified by operation id):

    1 – access(“T1″.”USER_ID”=”USER_ID”)
    4 – access(“T2″.”USERNAME”=”T3″.”USERNAME”)

    – dynamic sampling used for this statement (level=2)
    – automatic DOP: skipped because of IO calibrate statistics are missing


    SQL> alter session set “_optimizer_unnest_all_subqueries”=false;

    Session altered.

    SQL> select * from t1 where t1.user_id not in(select t2.user_id from t2,t3 where t2.username=t3.username);

    no rows selected

    SQL> @xplanc

    SQL_ID 17d2t9tgp0b3w, child number 1
    select * from t1 where t1.user_id not in(select t2.user_id from t2,t3
    where t2.username=t3.username)

    Plan hash value: 1257224752

    | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
    | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 225 | | | |
    |* 1 | FILTER | | 1 | | 0 |00:00:00.01 | 225 | | | |
    | 2 | TABLE ACCESS FULL | T1 | 1 | 37 | 37 |00:00:00.01 | 3 | | | |
    |* 3 | HASH JOIN | | 37 | 1 | 37 |00:00:00.01 | 222 | 1269K| 1269K| 384K (0)|
    |* 4 | TABLE ACCESS FULL| T2 | 37 | 1 | 37 |00:00:00.01 | 111 | | | |
    | 5 | TABLE ACCESS FULL| T3 | 37 | 37 | 703 |00:00:00.01 | 111 | | | |

Leave a Reply

Your email address will not be published. Required fields are marked *