qb_name

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 based 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$2, upd$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 advantage 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” – the 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 information. 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 at least two, often 3, 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 the 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, in 10g you can also use the query block name as the parameter to the hint. (The same variation applies to some 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 (initially) exist to apply a hint to a join that isn’t (initially) visible. It’s a valid technique for dealing with difficult queries; but one that will require some careful thought.