Martins Blog

Trying to explain complex things in simple terms

Why does the Optimiser not respect my qb_name() hint?

Posted by Martin Bach on June 30, 2014

I recently was involved in an investigation on a slow-running report on an Exadata system. This was rather interesting, the raw text file with the query was 33kb in size, and SQL Developer formatted the query to > 1000 lines. There were lots of interesting constructs in the query and the optimiser did its best to make sense of the inline views and various joins.

This almost lead to a different post about the importance of understanding query transformation. You will see in a bit what QT has to do with this post. Although access paths and join methods are still very relevant when trying to understand execution plans you will be amazed by the extent of the work happening during query transformation. I have said so on twitter already, but in case you missed it: Oracle has published a SQL Tuning Guide with the 12c documentation set. This is a really, really good, well-written piece of documentation. And it contains a lot of information about query transformation in chapter 5.

Query Blocks

One of the lessons I learned when looking at complex SQL statements is to think about Query Blocks. Let’s start with an example. I created some test tables, T1 and T2, both with rather random data. T1 is bigger, about 33 million rows, T2 has about 1 million rows. All of them are based on Jonathan Lewis’s presentation about “creating tests“. This is Oracle 12.1.0.1.3 on Exadata.

Here is the statement used for this blog post, unhinted. To keep things simple there are no indexes on T1 or T2.

select /*+ gather_plan_statistics */
  /* test0013 */
 t1.id,t1.N1
from t1
where exists ( select 1 from t2 where t1.id = t2.id and t2.id between 10 and 20)
and t1.n1 = (select n1 from t1 where id = 1 and rownum = 1)   -- the subquery would return 32 rows without the count stopkey
union all
select  t2.id, t2.n1 from t2
where t2.id between 10 and 12
union all
select t2.id, t2.n1 from t2
where t2.id between 13 and 15;

The statement does not make sense for humans, but it should be logically correct (this is my first post on the optimiser, I am hoping that the more knowledgeable readers point out any inaccuracies should there be any). The intent is to introduce a few query blocks.

This results in the following execution plan:

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last +alias'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5zrwsqnwjzzry, child number 0
-------------------------------------
select /*+ gather_plan_statistics */   /* test0013 */  t1.id,t1.N1 from
t1 where exists ( select 1 from t2 where t1.id = t2.id and t2.id
between 10 and 20) and t1.n1 = (select n1 from t1 where id = 1 and
rownum = 1)  union all select  t2.id, t2.n1 from t2 where t2.id between 10
and 12 union all select t2.id, t2.n1 from t2 where t2.id between 13 and
15

Plan hash value: 319705157

-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |      |      1 |        |      6 |00:00:00.07 |     516K|    516K|       |       |          |
|   1 |  UNION-ALL                               |      |      1 |        |      6 |00:00:00.07 |     516K|    516K|       |       |          |
|*  2 |   HASH JOIN SEMI                         |      |      1 |      1 |      0 |00:00:00.07 |     485K|    485K|  1421K|  1421K|  540K (0)|
|*  3 |    TABLE ACCESS STORAGE FULL             | T1   |      1 |      1 |      0 |00:00:00.07 |     485K|    485K|  1025K|  1025K| 3085K (0)|
|*  4 |     COUNT STOPKEY                        |      |      1 |        |      1 |00:00:00.01 |       4 |      2 |       |       |          |
|*  5 |      TABLE ACCESS STORAGE FULL FIRST ROWS| T1   |      1 |      2 |      1 |00:00:00.01 |       4 |      2 |  1025K|  1025K| 1029K (0)|
|*  6 |    TABLE ACCESS STORAGE FULL             | T2   |      0 |     12 |      0 |00:00:00.01 |       0 |      0 |  1025K|  1025K|          |
|*  7 |   TABLE ACCESS STORAGE FULL              | T2   |      1 |      4 |      3 |00:00:00.01 |   15204 |  15200 |  1025K|  1025K| 3085K (0)|
|*  8 |   TABLE ACCESS STORAGE FULL              | T2   |      1 |      4 |      3 |00:00:00.01 |   15204 |  15200 |  1025K|  1025K| 3085K (0)|
-----------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SET$1
   2 - SEL$5DA710D3
   3 - SEL$5DA710D3 / T1@SEL$1
   4 - SEL$3
   5 - SEL$3        / T1@SEL$3
   6 - SEL$5DA710D3 / T2@SEL$2
   7 - SEL$4        / T2@SEL$4
   8 - SEL$5        / T2@SEL$5

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."ID"="T2"."ID")
   3 - storage(("T1"."ID"<=20 AND "T1"."ID">=10 AND "T1"."N1"=))
       filter(("T1"."ID"<=20 AND "T1"."ID">=10 AND "T1"."N1"=))
   4 - filter(ROWNUM=1)
   5 - storage("ID"=1)
       filter("ID"=1)
   6 - storage(("T2"."ID"<=20 AND "T2"."ID">=10))
       filter(("T2"."ID"<=20 AND "T2"."ID">=10))
   7 - storage(("T2"."ID"<=12 AND "T2"."ID">=10))
       filter(("T2"."ID"<=12 AND "T2"."ID">=10))
   8 - storage(("T2"."ID"<=15 AND "T2"."ID">=13))
       filter(("T2"."ID"<=15 AND "T2"."ID">=13))

54 rows selected.

So far so good, nothing really too surprising here but keep in mind this is a simple example. Now if I wanted to pass hints to the optimiser on how to deal with the subqueries I have to refer to the query blocks. Jonathan Lewis has described this in an older but still relevant blog post, and you can find presentations from others on the subject as well. Easy enough, I simply add a /*+ qb_name(name) */ immediately after every select, provide the query block name in the hint (/*+ full(@block_a t1@block_a) */) and I’m done. So let’s try:

select /*+ qb_name(block_a) gather_plan_statistics */
  /* test0013 */
 t1.id,t1.N1
from t1
where exists ( select /*+ qb_name(block_c) */ 1 from t2 where t1.id = t2.id and t2.id between 10 and 20)
and t1.n1 = (select /*+ qb_name(block_d) */ n1 from t1 where id = 1 and rownum = 1)
union all
select /*+ qb_name(block_e) */ t2.id, t2.n1 from t2
where t2.id between 10 and 12
union all
select /*+ qb_name(block_f) */ t2.id, t2.n1 from t2
where t2.id between 13 and 15;

Now surely I should get query blocks block_[ac-f] in the execution plan, right? When going over the post I noticed that block_b was missing in the SQL statement by the way, for some reasons it went missing, never mind.

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last +alias'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  66ytd4g9xmwxb, child number 0
-------------------------------------
select /*+ qb_name(block_a) gather_plan_statistics */   /* test0012 */
t1.id,t1.N1 from t1 where exists ( select /*+ qb_name(block_c) */ 1
from t2 where t1.id = t2.id and t2.id between 10 and 20) and t1.n1 =
(select /*+ qb_name(block_d) */ n1 from t1 where id = 1 and rownum = 1)
union all select /*+ qb_name(block_e) */ t2.id, t2.n1 from t2 where
t2.id between 10 and 12 union all select /*+ qb_name(block_f) */ t2.id,
t2.n1 from t2 where t2.id between 13 and 15

Plan hash value: 319705157

-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |      |      1 |        |      6 |00:00:00.04 |     516K|    516K|       |       |          |
|   1 |  UNION-ALL                               |      |      1 |        |      6 |00:00:00.04 |     516K|    516K|       |       |          |
|*  2 |   HASH JOIN SEMI                         |      |      1 |      1 |      0 |00:00:00.04 |     485K|    485K|  1421K|  1421K|  385K (0)|
|*  3 |    TABLE ACCESS STORAGE FULL             | T1   |      1 |      1 |      0 |00:00:00.04 |     485K|    485K|  1025K|  1025K| 3085K (0)|
|*  4 |     COUNT STOPKEY                        |      |      1 |        |      1 |00:00:00.01 |       4 |      2 |       |       |          |
|*  5 |      TABLE ACCESS STORAGE FULL FIRST ROWS| T1   |      1 |      2 |      1 |00:00:00.01 |       4 |      2 |  1025K|  1025K| 1029K (0)|
|*  6 |    TABLE ACCESS STORAGE FULL             | T2   |      0 |     12 |      0 |00:00:00.01 |       0 |      0 |  1025K|  1025K|          |
|*  7 |   TABLE ACCESS STORAGE FULL              | T2   |      1 |      4 |      3 |00:00:00.01 |   15204 |  15200 |  1025K|  1025K| 3085K (0)|
|*  8 |   TABLE ACCESS STORAGE FULL              | T2   |      1 |      4 |      3 |00:00:00.01 |   15204 |  15200 |  1025K|  1025K| 3085K (0)|
-----------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SET$1
   2 - SEL$7CA7EA44
   3 - SEL$7CA7EA44 / T1@BLOCK_A
   4 - BLOCK_D
   5 - BLOCK_D      / T1@BLOCK_D
   6 - SEL$7CA7EA44 / T2@BLOCK_C
   7 - BLOCK_E      / T2@BLOCK_E
   8 - BLOCK_F      / T2@BLOCK_F

Surprise, surprise

Umm, where are the query blocks A and C (remember there is no hint for block_b)? I was hoping to get rid of all the system generated names, but no luck. Actually, that’s not quite right, and to understand this you have to look at the 10053 trace, which is mostly harmless. In the trace you find all the query block names, right at the beginning:

Registered qb: BLOCK_A 0x3bc2578 (HINT BLOCK_A)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=BLOCK_A nbfros=1 flg=0
    fro(0): flg=4 objn=61471 hint_alias="T1"@"BLOCK_A"

Registered qb: BLOCK_C 0x3bc5610 (HINT BLOCK_C)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=BLOCK_C nbfros=1 flg=0
    fro(0): flg=4 objn=61485 hint_alias="T2"@"BLOCK_C"

Registered qb: BLOCK_D 0xfedf8560 (HINT BLOCK_D)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=BLOCK_D nbfros=1 flg=0
    fro(0): flg=4 objn=61471 hint_alias="T1"@"BLOCK_D"

Registered qb: BLOCK_E 0xfedf7290 (HINT BLOCK_E)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=BLOCK_E nbfros=1 flg=0
    fro(0): flg=4 objn=61485 hint_alias="T2"@"BLOCK_E"

Registered qb: BLOCK_F 0xfedf5f50 (HINT BLOCK_F)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=BLOCK_F nbfros=1 flg=0
    fro(0): flg=4 objn=61485 hint_alias="T2"@"BLOCK_F"

Registered qb: SET$1 0xfedf4b28 (PARSER)

So why is there no BLOCK_A in the Query Block Name / Object Alias section of the plan? Now it’s time to scroll down (a lot) or to search for the query block from the execution plan (SEL$7CA7EA44 for example).

I found it in the query transformation part of the optimiser trace, more specifically in the cost-based query transformation part. There you can see how the optimiser tries various optimisations to the SQL statement you issued. Not really surprisingly the optimiser works on the EXISTS part trying to unnest it. Here’s the excerpt from the trace:

*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block SET$1 (#1) that are valid to unnest.
Subquery removal for query block BLOCK_D (#5)
RSW: Not valid for subquery removal BLOCK_D (#5)
Subquery unchanged.
Subquery Unnesting on query block BLOCK_A (#4)SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block BLOCK_A (#4).
SU:   Checking validity of unnesting subquery BLOCK_C (#6)
SU:   Passed validity checks.
SU:   Transforming EXISTS subquery to a join.
Registered qb: SEL$7CA7EA44 0xfedfa350 (SUBQUERY UNNEST BLOCK_A; BLOCK_C)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$7CA7EA44 nbfros=2 flg=0
    fro(0): flg=0 objn=61471 hint_alias="T1"@"BLOCK_A"
    fro(1): flg=0 objn=61485 hint_alias="T2"@"BLOCK_C"

Notice the new query block name: Registered qb: SEL$7CA7EA44 0xfedfa350 (SUBQUERY UNNEST BLOCK_A; BLOCK_C). And there you go, the rewritten part of the SQL statement “replaces” the original blocks A and C.

Many thanks go to Karen Morton who helped me along the way and had so many good suggestions!

About these ads

5 Responses to “Why does the Optimiser not respect my qb_name() hint?”

  1. cherif said

    Hi Martin,
    Thanks for this post;
    Two weeks ago, I was faced a performance issue on select from a view containing 4 UNION ALL.
    There is an enhancement done on the second set bloc on this view by adding a hint ordered , after some days the query takes the same execution plan. ( with this plan we have important performance degradation)

    The second and the third blocs contains a join on the same table TTT, I have detect that there is query transformation : factorization behavior ; the hint Ordered is note taken into account on the second set bloc.

    You can try to use the hint /*+ opt_param(‘_optimizer_cost_based_transformation’,’off’) */; to avoid this behavior.

    Thanks,
    Cherif

    • Dom Brooks said

      Cherif – if you have a problem with join factorisation you don’t need to turn off the whole query transformation process, you can just turn off join factorisation:
      http://orastory.wordpress.com/2011/06/07/join-factorization/

      • bijga said

        Hi Dom,

        Thanks for your suggestion. The first time I thought using the /*+ opt_param(‘_optimizer_join_factorization’,’off’) */; instead of _optimizer_cost_based_transformation; the views contains three set blocs. The last time a developer sent to me an ORA-600 on a similar view ( same list of tables , joins …)
        “ORA-00600: code d’erreur interne, arguments : [15160], [], [], [], [], [], [], [], [], [], [], []”

        I checked on MOS, there is a bug and fixed by /*+ opt_param(‘_optimizer_cost_based_transformation’,’off’) */.

        I tried to fix it by /*+ opt_param(‘_optimizer_join_factorization’,’off’) */ but I have the same error . So I thought that is the best way to fix a performance issue and internal error with by one hint.

        Thanks,
        Cherif

  2. Dom Brooks said

    Hi Martin,

    Good post.

    I wondered whether this meant that your hint should really just be /*+ full(t1@block_a) */ because post transformation the query block name had changed, but it doesn’t seem to be the case but I only used a test case.

    What is also really interesting in Jonathan’s blog post is the comment by Alberto Dell’Era (and follow-ups) which points out that your query block name SEL$7CA7EA44 is just a hash of the two input block names and if I come up with a different piece of sql which does something similar, I will get the same generated block name.

    E.g.

    drop table t1;
    drop table t2;
    
    create table t1
    (col1 number);
    
    create table t2
    (col1 number);
    
    explain plan for 
    select /*+ qb_name(block_a)*/
     *
    from t1
    where exists ( select /*+ qb_name(block_c) */ 1 from t2 where t1.col1 = t2.col1);
    
    select * from table(dbms_xplan.display(format =&gt; '+alias'));
    
    Plan hash value: 1713220790
     
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |     1 |    26 |     5  (20)| 00:00:01 |
    |*  1 |  HASH JOIN SEMI    |      |     1 |    26 |     5  (20)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |
    |   3 |   TABLE ACCESS FULL| T2   |     1 |    13 |     2   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
     
    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------
     
       1 - SEL$7CA7EA44
       2 - SEL$7CA7EA44 / T1@BLOCK_A
       3 - SEL$7CA7EA44 / T2@BLOCK_C
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       1 - access(&quot;T1&quot;.&quot;COL1&quot;=&quot;T2&quot;.&quot;COL1&quot;)
    

    Cheers,
    Dominic

  3. coskan said

    Martin

    It Nice to see you out of your comfort zone :)

    Good one

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 2,240 other followers

%d bloggers like this: