Oracle Scratchpad

June 25, 2007

qb_name

Filed under: Execution plans,Hints,Parallel Execution,Tuning — Jonathan Lewis @ 8:15 pm BST Jun 25,2007

When Oracle tries to optimize a complex SQL statement one of the first steps it takes is to transform it into something simpler. The ideal target (from the optimizer’s perspective) is to eliminate any subqueries and in-line views so that your SQL statement ends up as a list of tables in a single from clause. Every in-line view and subquery is a separate query block, and the optimizer really wants to turn the whole thing into a single query block. Unfortunately this isn’t always possible, and sometimes it isn’t even a good idea - which is why 10g introduced ‘cost base query transformation’.

In 10g the optimizer works harder at optimising complex queries, calculating the varying costs of using different transformations on a query, before deciding on a final strategy for the optimization. This means you are more likely to see the individual query blocks from your query, as well as several partially transformed query blocks being optimized separately in the 10053 trace file.

Because of this, and because of the need to debug problems with transformations, the trace has been enhanced to make the handling of query blocks highly visible. The following extract from a 10053 trace file shows an example of how the optimizer introduces a query block that it is about to investigate:

*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
  fro(0): flg=0 objn=56629 hint_alias="T1"@"SEL$1"

In this extract you can see Oracle starting work on a query block that it has named sel$1, and the query block holds a single table with the alias of t1 – which has been qualified by the query block name to become t1@sel$1.

By default Oracle will generate query block names like sel$1, ins$2upd$3, del$4, misc$5 if you don’t use explicit hints for the select, insert, update, delete, and merge blocks that you write. Given the importance of query blocks and transformations, though, Oracle has also introduced the qb_name hint to allow you to supply explicit labels for each query block in your code. The syntax is simply: /*+ qb_name(my_name_for_ block) */.

Apart from the query block names that you give explicitly and any query block names that Oracle generates for the base blocks in your query, you will also see further query block names like SEL$8FA4BC11 appearing in the trace file for blocks that Oracle generates by query transformation. These query block names include 8-digit hex values that are a deterministic hash-value for the generated query block, so once you have found them in a trace file, you will be able to take advatnage of them if you really need to (as we shall see later).

The are several benefits in naming query blocks. First, query block names can be included in hints. Many (if not all) of the hints you currently use can take an extra “parameter” - thethe query block name that the hint should be applied to. This means you can now list all the hints at the top of a complex query, rather than scattering them through the query in the individual query blocks.

The second significant benefit of query block names is that they can be reported through the dbms_xplan.display() function, where they appear as an optional extra to the ‘typical’ output. Here’s an example of a call  you might make to the procedure to see the query block and associated alias information.

select
	*
from
	table(
		dbms_xplan.display(
			null,
			null,
			'typical alias -rows -bytes -cost'
		)
	)
;            

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT       |          | 00:00:02 |        |      |            |
|   1 |  LOAD AS SELECT              | T3       |          |        |      |            |
|   2 |   PX COORDINATOR             |          |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)       | :TQ10002 | 00:00:01 |  Q1,02 | P->S | QC (RAND)  |
|   4 |     HASH GROUP BY            |          | 00:00:01 |  Q1,02 | PCWP |            |
|   5 |      PX RECEIVE              |          | 00:00:01 |  Q1,02 | PCWP |            |
|   6 |       PX SEND HASH           | :TQ10001 | 00:00:01 |  Q1,01 | P->P | HASH       |
|   7 |        HASH GROUP BY         |          | 00:00:01 |  Q1,01 | PCWP |            |
|*  8 |         HASH JOIN            |          | 00:00:01 |  Q1,01 | PCWP |            |
|   9 |          PX RECEIVE          |          | 00:00:01 |  Q1,01 | PCWP |            |
|  10 |           PX SEND BROADCAST  | :TQ10000 | 00:00:01 |  Q1,00 | P->P | BROADCAST  |
|  11 |            PX BLOCK ITERATOR |          | 00:00:01 |  Q1,00 | PCWC |            |
|  12 |             TABLE ACCESS FULL| T2       | 00:00:01 |  Q1,00 | PCWP |            |
|  13 |          PX BLOCK ITERATOR   |          | 00:00:01 |  Q1,01 | PCWC |            |
|  14 |           TABLE ACCESS FULL  | T1       | 00:00:01 |  Q1,01 | PCWP |            |
-----------------------------------------------------------------------------------------         

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$8FA4BC11
  12 - SEL$8FA4BC11 / T2@MAIN
  14 - SEL$8FA4BC11 / T1@INLINE         

Predicate Information (identified by operation id):
---------------------------------------------------
   8 - access("T2"."ID"=DECODE("T1"."N500",0,(-1),"N500"))         

The query in question was a parallel CTAS (create table as select) and, as you can see, I’ve used a few ‘negative’ options  to trim the width of my execution plan. The option I want to highlight, though, is the ‘alias’ option that has resulted in the ‘Query block name / Object alias’ section of the report.

The query (which I will be showing a little further down the page) is a join between a simple table called t2, and an in-line aggregate view of a table called t1. I included a (redundant) /*+ merge */ hint in the query and used the /*+ qb_name() */ hint to name the main select statement and the inline view.

As you can see from the plan structure, the statement ran as a parallel query and used complex view merging to join t1 to t2 before aggregating. But Oracle has also told me three things about query blocks and aliases in the section just below the plan structure.

Because the in-line aggregate view was merged into the main query the optimizer generated a new query block – which has the generated name SEL$8FA4BC11 that appears in line 1 of the Query Block Name section. This query block joins two tables in lines 12 and 14 respectively, but because these tables have arrived from other query blocks, we see their fully qualified aliases – which include the source query block names, hence the t2@main and t1@inline.

Now this information can be very helpful. Take a look back at the structure of the plan. As you can see, we got a parallel hash join (line 8 ) using the broadcast distribution method (line 10) for the first table. Unfortunately, while the hash join is a good idea, the broadcast is not – the first table is rather larger than the optimizer thinks it is. I would like to tell Oracle to use the hash distribution method – which I can do with the pq_distribute() hint.

But how do you tell Oracle what to do with a hash join, when the join isn’t immediately visible in the original query and the two tables are in different query blocks. The answer is as follows:

create table t3
as
select
	/*+
		qb_name(main)
		merge(@inline)
		leading(@SEL$8FA4BC11 t2@main t1@inline)
		full(t2@main)
		full(t1@inline)
		use_hash(@SEL$8FA4BC11 t1@inline)
		no_swap_join_inputs(@SEL$8FA4BC11 t1@inline)
		pq_distribute(@SEL$8FA4BC11 t1@inline hash hash)
	*/
	v1.n1,
	v1.sum_id,
	t2.small_vc
from
	(
		select	/*+ qb_name(inline) */
			decode(t1.n500,0,-1,n500)	n1,
			decode(t1.n11,0,-1,n11)		n11,
			decode(t1.n23,0,-1,n23)		n23,
			decode(t1.n37,0,-1,n37)		n37,
			sum(t1.id)			sum_id,
			max(small_vc)			max_vc
		from
			t1
		group by
			decode(t1.n500,0,-1,n500),
			decode(t1.n11,0,-1,n11),
			decode(t1.n23,0,-1,n23),
			decode(t1.n37,0,-1,n37)
	)	v1,
	t2
where
	t2.id = v1.n1
;

I’ve been generous with the hints (if you’re going to use one you really need a full set, which means something like an average of two per table, to make sure that you eliminate all other possible execution paths).

Notice, in particular, that I’ve picked up the generated query block name from my first execution plan – which has the correct transformation, even though the distribution was undesirable – and used that name to give hints to Oracle to handle transformed query block differently.

I’ve used the generated name as the first parameter to most of the hints I’ve used – and one particularly special case of this is the /*+ merge() */. Historically you could use this hint in one of two ways – include it in the in-line view you wanted to merge, or include it in the block that contained the inline view. If you put the hint in the view you used it with no parameter, if you used the hint in the containing block you used the view alias as the parameter to the hint. But, as you can see, I]in 10g you can also use the query block name as the parameter to the hint. (The same variation applies to other hints – for example the /*+ push_pred() */ hint and the /*+ push_subq */ hint).

With this stack of hints in place, the execution plan changes as follows:

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT       |          | 00:00:02 |        |      |            |
|   1 |  LOAD AS SELECT              | T3       |          |        |      |            |
|   2 |   PX COORDINATOR             |          |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)       | :TQ10003 | 00:00:01 |  Q1,03 | P->S | QC (RAND)  |
|   4 |     HASH GROUP BY            |          | 00:00:01 |  Q1,03 | PCWP |            |
|   5 |      PX RECEIVE              |          | 00:00:01 |  Q1,03 | PCWP |            |
|   6 |       PX SEND HASH           | :TQ10002 | 00:00:01 |  Q1,02 | P->P | HASH       |
|   7 |        HASH GROUP BY         |          | 00:00:01 |  Q1,02 | PCWP |            |
|*  8 |         HASH JOIN            |          | 00:00:01 |  Q1,02 | PCWP |            |
|   9 |          PX RECEIVE          |          | 00:00:01 |  Q1,02 | PCWP |            |
|  10 |           PX SEND HASH       | :TQ10000 | 00:00:01 |  Q1,00 | P->P | HASH       |
|  11 |            PX BLOCK ITERATOR |          | 00:00:01 |  Q1,00 | PCWC |            |
|  12 |             TABLE ACCESS FULL| T2       | 00:00:01 |  Q1,00 | PCWP |            |
|  13 |          PX RECEIVE          |          | 00:00:01 |  Q1,02 | PCWP |            |
|  14 |           PX SEND HASH       | :TQ10001 | 00:00:01 |  Q1,01 | P->P | HASH       |
|  15 |            PX BLOCK ITERATOR |          | 00:00:01 |  Q1,01 | PCWC |            |
|  16 |             TABLE ACCESS FULL| T1       | 00:00:01 |  Q1,01 | PCWP |            |
-----------------------------------------------------------------------------------------    

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$8FA4BC11
  12 - SEL$8FA4BC11 / T2@MAIN
  16 - SEL$8FA4BC11 / T1@INLINE    

Predicate Information (identified by operation id):
---------------------------------------------------
   8 - access("T2"."ID"=DECODE("T1"."N500",0,(-1),"N500"))

So, through a deliberate ‘trial and error’ process, I’ve managed to use the name of a query block that doesn’t exist to apply a hint to a join that isn’t visible. It’s a valid technique for dealing with difficult queries; but one that will require some careful thought.

23 Comments »

  1. Hi Jonathan,

    Just out of interest, were you able to determine on what exactly the generated query block names (e.g. SEL$8FA4BC11) are based on? e.g. a hash on the internal representation of the generated query?

    If the original query is modified in some trivial fashion, but the generated query remains the same, does the generated query block name remain the same?

    Jeff

    Comment by Jeffrey Kemp — June 26, 2007 @ 6:09 am BST Jun 26,2007 | Reply

  2. Jeffrey, I haven’t investigated this, but as a first guess I’d look at the list of tables in the block plus the list of join columns – possibly all the columns in the where clause. It might also include the columns in the group by, having, and order by clauses.

    Comment by Jonathan Lewis — June 28, 2007 @ 12:34 pm BST Jun 28,2007 | Reply

  3. Very very interesting posting – I hope to see more in your next book ;)

    As per my own quick experiments, the generated query block name is simply a function (hash?) of the transformed blocks’ qb_names, it doesn’t depend on their actual sql. For example (in 10.2.0.3), the following statement, that uses the same qb_names you used (main, inline) :

    explain plan for
    select /*+ qb_name(main) */ *
    from (select /*+ qb_name(inline) */ mgr from scott.emp)
    where mgr = 0;

    is transformed into a block whose name is SEL$8FA4BC11 – exactly the same name found in your posting.

    Changing the statements even dramatically (adding a “group by”, a “where”, etcetera) doesn’t change the generated name, but as soon as I change the qb_names (eg from “inline” to “inline2″) the generated name changes.

    Very convenient!

    Comment by Alberto Dell'Era — June 28, 2007 @ 7:31 pm BST Jun 28,2007 | Reply

  4. Alberto, brilliant ! The simplest option is always the best. Yes, the same thing happens in my test case when I change the names of the query blocks.

    Rhetorical question: what happens in the case of a double transformation (for example, subquery unnest followed by complex view merge) – does Oracle hash the name of a generated block, or of the blocks that went into the generated block. Logic dictates that it should be the generated name that gets hashed.

    This also leads to another little detail – what does Oracle do if it uses the same query block in two different ways after transforming. (Answer: in some cases, at least, it appends a counter to the original name).

    Comment by Jonathan Lewis — June 29, 2007 @ 6:59 am BST Jun 29,2007 | Reply

  5. > what happens in the case of a double transformation (snip)
    > Logic dictates that it should be the generated name that gets hashed.

    At least for merge, it works exactly as you said (10.2.0.3):

    select /*+ qb_name (q1) */ count(*)
    from (
    select /*+ qb_name (q2) */ *
    from (
    select /*+ qb_name (q3) */ *
    from scott.emp
    )
    );
    gives the generated name SEL$1F9AA06C (two merge operations)

    The inner merge:
    select /*+ qb_name (q2) */ *
    from (
    select /*+ qb_name (q3) */ *
    from scott.emp
    );
    gives the generated name SEL$59E833E1

    Hard-coding the inner qb_name:
    select /*+ qb_name (q1) */ count(*)
    from (
    select /*+ qb_name (SEL$59E833E1) */ *
    from scott.emp
    );
    gives SEL$1F9AA06C “as required”.

    Logic does seem to hold here – the CBO simply tries a transformation at a time, and each transformation has as input the previous transformed query or the original SQL, and doesn’t make any distinction between them (at least for the generation of the query block names).

    Which makes for an interesting question – I know from you book that the piece of code that makes the transformations is separate (or used to be in 9i) from the one that optimizes and calculates the best cost and plan; the former produces a set of candidate transformations and then submits the transformed SQLs to the latter. Does the latter starts all over again for each candidate transformation, or is it able to save some cost/plan computation e.g. for common, inner blocks ?

    Comment by Alberto Dell'Era — July 2, 2007 @ 7:31 pm BST Jul 2,2007 | Reply

  6. Very interesting paper, informative and well written. Thanks for showing me.

    Comment by Alberto Dell'Era — July 8, 2007 @ 5:08 pm BST Jul 8,2007 | Reply

  7. Hi Johnatan,

    I am not sure that query_block hint will be taken by optimizer all the time. In your case you put a hint “full(t1@inline)” and the CBO did a full scan on T1. If the condition on the T1 table is different (making vary obvious use of index), then optimizer will ‘ignore’ hint “full(t1@inline)” in outer query – at least it was my experience. When system generated outer query block name is specified the hint ‘will be taken’.

    Here is one example (oracle version 10.2.0.2):

    drop table t_1;
    create table t_1 as
    select
    rownum id,
    rpad(rownum,10) col_1,
    rpad(‘x’,100) col_2
    from
    all_objects
    where
    rownum ‘CTLM_DBA’
    ,TabName => ‘T_1′
    ,Method_Opt => ‘FOR ALL COLUMNS SIZE AUTO ‘
    ,Degree => NULL
    ,Cascade => TRUE
    ,No_Invalidate => FALSE);
    END;
    /

    drop table t_2;
    create table t_2 as
    select
    rownum id,
    rpad(rownum,10) col_1,
    rpad(‘x’,100) col_2
    from
    all_objects
    where
    rownum ‘CTLM_DBA’
    ,TabName => ‘T_2′
    ,Method_Opt => ‘FOR ALL COLUMNS SIZE AUTO ‘
    ,Degree => NULL
    ,Cascade => TRUE
    ,No_Invalidate => FALSE);
    END;
    /

    PLAN_TABLE_OUTPUT
    —————————————————————————————————-
    SQL_ID 12sgtdhau3yd1, child number 0
    ————————————-
    select /*+ full(t_1@opala1) */ * from (select /*+ qb_name(opala1) */ * from
    t_1 where id = 100) t1, (select /*+ qb_name(opala2) */ * from t_2 where
    id = 100) t2 where t1.id = t2.id

    Plan hash value: 71837195

    —————————————————————————————
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    —————————————————————————————
    | 0 | SELECT STATEMENT | | | | 2 (100)| |
    | 1 | NESTED LOOPS | | 1 | 230 | 2 (0)| 00:00:01 |
    | 2 | TABLE ACCESS BY INDEX ROWID| T_2 | 1 | 115 | 1 (0)| 00:00:01 |
    |* 3 | INDEX UNIQUE SCAN | T_2_PK | 1 | | 1 (0)| 00:00:01 |
    | 4 | TABLE ACCESS BY INDEX ROWID| T_1 | 1 | 115 | 1 (0)| 00:00:01 |
    |* 5 | INDEX UNIQUE SCAN | T_1_PK | 1 | | 1 (0)| 00:00:01 |
    —————————————————————————————

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

    PLAN_TABLE_OUTPUT
    —————————————————————————————————-

    1 – SEL$4F74B185
    2 – SEL$4F74B185 / T_2@OPALA2
    3 – SEL$4F74B185 / T_2@OPALA2
    4 – SEL$4F74B185 / T_1@OPALA1
    5 – SEL$4F74B185 / T_1@OPALA1

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

    3 – access(“ID”=100)
    5 – access(“ID”=100)
    filter(“T_1″.”ID”=”T_2″.”ID”)

    35 rows selected.

    PLAN_TABLE_OUTPUT
    —————————————————————————————————-
    SQL_ID 2m8x72ff31v7b, child number 0
    ————————————-
    select /*+ full(@SEL$4F74B185 t_1@opala1) */ * from (select /*+
    qb_name(opala1) */ * from t_1 where id = 100) t1, (select /*+
    qb_name(opala2) */ * from t_2 where id = 100) t2 where t1.id = t2.id

    Plan hash value: 242399927

    —————————————————————————————
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    —————————————————————————————
    | 0 | SELECT STATEMENT | | | | 14 (100)| |
    | 1 | NESTED LOOPS | | 1 | 230 | 14 (58)| 00:00:01 |
    | 2 | TABLE ACCESS BY INDEX ROWID| T_2 | 1 | 115 | 1 (0)| 00:00:01 |
    |* 3 | INDEX UNIQUE SCAN | T_2_PK | 1 | | 1 (0)| 00:00:01 |
    |* 4 | TABLE ACCESS FULL | T_1 | 1 | 115 | 13 (62)| 00:00:01 |
    —————————————————————————————

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

    PLAN_TABLE_OUTPUT
    —————————————————————————————————-
    1 – SEL$4F74B185
    2 – SEL$4F74B185 / T_2@OPALA2
    3 – SEL$4F74B185 / T_2@OPALA2
    4 – SEL$4F74B185 / T_1@OPALA1

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

    3 – access(“ID”=100)
    4 – filter((“ID”=100 AND “T_1″.”ID”=”T_2″.”ID”))

    32 rows selected.

    In this case the query_block name in outer query is ignored (unless NO_QUERY_TRANSFORMATION hint is specified), the CBO has generated his own query_block name and the hint ‘full(t_1@opala1)’ is ignored too:

    PLAN_TABLE_OUTPUT
    —————————————————————————————————-
    SQL_ID 58z2pnf8v9nhx, child number 0
    ————————————-
    select /*+ qb_name(main) full(t_1@opala1) */ * from (select /*+
    qb_name(opala1) */ * from t_1 where id = 100) t1, (select /*+
    qb_name(opala2) */ * from t_2 where id = 100) t2 where t1.id = t2.id

    Plan hash value: 71837195

    —————————————————————————————
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    —————————————————————————————
    | 0 | SELECT STATEMENT | | | | 2 (100)| |
    | 1 | NESTED LOOPS | | 1 | 230 | 2 (0)| 00:00:01 |
    | 2 | TABLE ACCESS BY INDEX ROWID| T_2 | 1 | 115 | 1 (0)| 00:00:01 |
    |* 3 | INDEX UNIQUE SCAN | T_2_PK | 1 | | 1 (0)| 00:00:01 |
    | 4 | TABLE ACCESS BY INDEX ROWID| T_1 | 1 | 115 | 1 (0)| 00:00:01 |
    |* 5 | INDEX UNIQUE SCAN | T_1_PK | 1 | | 1 (0)| 00:00:01 |
    —————————————————————————————

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

    PLAN_TABLE_OUTPUT
    —————————————————————————————————-

    1 – SEL$10B56D35
    2 – SEL$10B56D35 / T_2@OPALA2
    3 – SEL$10B56D35 / T_2@OPALA2
    4 – SEL$10B56D35 / T_1@OPALA1
    5 – SEL$10B56D35 / T_1@OPALA1

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

    3 – access(“ID”=100)
    5 – access(“ID”=100)
    filter(“T_1″.”ID”=”T_2″.”ID”)

    35 rows selected.

    Regards,
    goran

    Comment by goran — September 4, 2007 @ 1:01 pm BST Sep 4,2007 | Reply

  8. it looks like some part of text is not displayed correctly…reposting this part:
    drop table t_1;
    create table t_1 as
    select
    rownum id,
    rpad(rownum,10) col_1,
    rpad(‘x’,100) col_2
    from
    all_objects
    where
    rownum ‘CTLM_DBA’
    ,TabName => ‘T_1′
    ,Method_Opt => ‘FOR ALL COLUMNS SIZE AUTO ‘
    ,Degree => NULL
    ,Cascade => TRUE
    ,No_Invalidate => FALSE);
    END;

    drop table t_2;
    create table t_2 as
    select
    rownum id,
    rpad(rownum,10) col_1,
    rpad(‘x’,100) col_2
    from
    all_objects
    where
    rownum ‘CTLM_DBA’
    ,TabName => ‘T_2′
    ,Method_Opt => ‘FOR ALL COLUMNS SIZE AUTO ‘
    ,Degree => NULL
    ,Cascade => TRUE
    ,No_Invalidate => FALSE);
    END;

    Comment by goran — September 4, 2007 @ 1:25 pm BST Sep 4,2007 | Reply

  9. Goran,
    Interesting point.

    If you read the section on ‘how to comment’ in the menu at top-right you will see how to deal with your formatting problem. The most significant thing is the “less than” symbol. If you want to repost the SQL I will tidy up the ones that went wrong.

    It is always important to remember how detailed your hints have to be – in my case it is possible that my full() hint should have included the derived query block – but they may have been unnecessary (possibly by accident) because I used the derived query block name in all the other hints.

    In your case it is possible (though it looks unlikely at first sight) that if you specified more hints about the execution path, the use of the alias would have been sufficient.

    Thanks for the observation.

    Comment by Jonathan Lewis — September 4, 2007 @ 7:52 pm BST Sep 4,2007 | Reply

  10. Jonathan,

    I have discovered that ANSI join syntax affects query block naming! It seems that Oracle treats ANSI joins as query blocks internally and then transforms them. I would not be at all surprised if this is related to the problems experienced with ANSI joins in materialized view definitions. This can cause global hints to be ignored. Here is some code.

    create table t1 as select 1 id from dual;

    create table t2 as select 1 id from dual;

    explain plan for select * from t1,t2 where t1.id=t2.id;

    select * from table(dbms_xplan.display(null,null,’basic +alias’));

    explain plan for select * from t1 join t2 on t1.id=t2.id;

    select * from table(dbms_xplan.display(null,null,’basic +alias’));

    explain plan for select /*+ qb_name(myqbname) use_nl(@myqbname t2) */ * from t1,t2 where t1.id=t2.id;

    select * from table(dbms_xplan.display(null,null,’basic +alias’));

    explain plan for select /*+ qb_name(myqbname) use_nl(@myqbname t2) */ * from t1 join t2 on t1.id=t2.id;

    select * from table(dbms_xplan.display(null,null,’basic +alias’));

    and here is the output:

    Plan hash value: 1838229974

    ———————————–
    | Id | Operation | Name |
    ———————————–
    | 0 | SELECT STATEMENT | |
    | 1 | HASH JOIN | |
    | 2 | TABLE ACCESS FULL| T1 |
    | 3 | TABLE ACCESS FULL| T2 |
    ———————————–

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

    1 – SEL$1
    2 – SEL$1 / T1@SEL$1
    3 – SEL$1 / T2@SEL$1

    Plan hash value: 1838229974

    ———————————–
    | Id | Operation | Name |
    ———————————–
    | 0 | SELECT STATEMENT | |
    | 1 | HASH JOIN | |
    | 2 | TABLE ACCESS FULL| T1 |
    | 3 | TABLE ACCESS FULL| T2 |
    ———————————–

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

    1 – SEL$58A6D7F6
    2 – SEL$58A6D7F6 / T1@SEL$1
    3 – SEL$58A6D7F6 / T2@SEL$1

    Plan hash value: 1967407726

    ———————————–
    | Id | Operation | Name |
    ———————————–
    | 0 | SELECT STATEMENT | |
    | 1 | NESTED LOOPS | |
    | 2 | TABLE ACCESS FULL| T1 |
    | 3 | TABLE ACCESS FULL| T2 |
    ———————————–

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

    1 – MYQBNAME
    2 – MYQBNAME / T1@MYQBNAME
    3 – MYQBNAME / T2@MYQBNAME

    Plan hash value: 1838229974

    ———————————–
    | Id | Operation | Name |
    ———————————–
    | 0 | SELECT STATEMENT | |
    | 1 | HASH JOIN | |
    | 2 | TABLE ACCESS FULL| T1 |
    | 3 | TABLE ACCESS FULL| T2 |
    ———————————–

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

    1 – SEL$FCE1789F
    2 – SEL$FCE1789F / T1@SEL$1
    3 – SEL$FCE1789F / T2@SEL$1

    Comment by Tony Hasler — November 24, 2008 @ 8:08 pm BST Nov 24,2008 | Reply

  11. Tony,

    Thanks for the comment.
    ANSI joins make hinting even harder than usual. As you say, each joined table seems to be treated as a new query block – so when you have something like this:


    select. ...
    from    t1
    join    t2 on t2.colx = t1.coly
    join    t3 on t3.cola = t2.colb

    and want to put in a simple /*+ no_merge */ hint to force the optimizer to create the result set of t1 and t2, the interference from ANSI is just a little irritating.

    There are some notes here from the Optimizer Group about how Oracle handles ANSI join syntax that confirm your observation.

    Comment by Jonathan Lewis — November 25, 2008 @ 11:57 pm BST Nov 25,2008 | Reply

  12. [...] stuck with the hint syntax that will work in 8i, 9i – rather than using qb_name() and fully qualified hints which would be appropriate for 10g and 11g. [...]

    Pingback by Strategic Hints « Oracle Scratchpad — April 28, 2009 @ 7:21 pm BST Apr 28,2009 | Reply

  13. [...] notice that I’ve used the qb_name() hint in these examples – this makes it easier to control the details in more complex [...]

    Pingback by Group By « Oracle Scratchpad — May 9, 2009 @ 11:05 am BST May 9,2009 | Reply

  14. [...] possible to use a “global” hint in the main query with a query block name referencing the inline view, of course – but it can be very hard to make this work correctly [...]

    Pingback by Collection Costs « Oracle Scratchpad — November 30, 2010 @ 7:23 am BST Nov 30,2010 | Reply

  15. [...] qb_name: a really important hint introduced in 10g [...]

    Pingback by Rules for Hinting « Oracle Scratchpad — December 2, 2010 @ 8:57 am BST Dec 2,2010 | Reply

  16. [...] you want pushed by writing it in the subquery (as I have here) or by naming each subquery with the qb_name hint and then putting the push_subq(@{your subquery name}) hint(s) at the top of the query, naming [...]

    Pingback by Subquery Selectivity – 2 « Oracle Scratchpad — January 17, 2011 @ 6:39 pm BST Jan 17,2011 | Reply

  17. [...] for the moment. I’m also going to adopt “sensible practice” and give each of my query blocks a name. Let’s see what we get from dbms_xplan with the [...]

    Pingback by How to hint – 1 « Oracle Scratchpad — June 8, 2011 @ 3:00 pm BST Jun 8,2011 | Reply

  18. [...] For more on queryblock naming see Jonathan Lewis’s article on qb_name including the discussions in the comments. [...]

    Pingback by Hints of Acceptability « OraStory — December 12, 2011 @ 9:48 pm BST Dec 12,2011 | Reply

  19. [...] https://jonathanlewis.wordpress.com/2007/06/25/qb_name/ Like this:LikeBe the first to like this. Categories: Uncategorized Comments (0) Trackbacks (0) Leave a comment Trackback [...]

    Pingback by Hint queries containing views « Yorakb — June 22, 2012 @ 3:50 pm BST Jun 22,2012 | Reply

  20. [...] https://jonathanlewis.wordpress.com/2007/06/25/qb_name/ Partager :FacebookTwitterLinkedInJ'aime ceci:J'aimeSoyez le premier à aimer ceci. de → Infrastructures ← Ajout d’un noeud supplémentaire dans un cluster RAC 11gR2 Pas encore de commentaire [...]

    Pingback by Optimiser des requêtes SQL contenant des vues « EASYTEAM LE BLOG — October 11, 2012 @ 5:19 pm BST Oct 11,2012 | Reply


RSS feed for comments on this post. TrackBack URI

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

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,530 other followers