Thursday, February 19, 2015

Are you using the QB_NAME hint? You should be!




In Oracle land each time the optimizer sees the work SELECT that is a new query block within the query.  Each query block gets a name even when you don’t name them.  But they are typical system type generated names; they aren’t very informative by themselves.  They get names like SEL$1 and SEL$2 and so on.    But we can them is such a way that they do make sense to us humans.

It’s really a good idea to do this.  These names for the query blocks are also used in the 10053 trace files.  And again it can just be easier to read things if you name them in more human understandable ways.  Here’s a quick couple of examples to show this hint in action.

select /*+ QB_NAME(mainblock) */ a.username,
  (select /*+ QB_NAME(cntblock) */  count(*)
   from scalar_allobjects b where b.owner = a.username) cnt
 from scalar_allusers a;

This is a simple enough query to demonstrate the point.  Note there are two query blocks.  The main query and then a sub query to get a count.  So I named them MAINBLOCK and CNTBLOCK.  Pretty tricky eh?

Now when I run the query and use DBMS_XPLAN.DISPLAY_CURSOR to see the plan I can use the ALIAS format option to see where in the plan the query blocks are getting evaluated.

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR
  2  ('g7pt8bj8jjy8r',0,'ALLSTATS LAST ALIAS'))
  3  /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
SQL_ID  g7pt8bj8jjy8r, child number 0
-------------------------------------
select /*+ QB_NAME(mainblock) */ a.username,   (select /*+
QB_NAME(cntblock) */  count(*)    from scalar_allobjects b where
b.owner = a.username) cnt  from scalar_allusers a

Plan hash value: 2801865168

--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |      1 |        |     43 |00:00:00.01 |       6 |
|   1 |  SORT AGGREGATE    |                   |     43 |      1 |     43 |00:00:00.26 |   65059 |
|*  2 |   TABLE ACCESS FULL| SCALAR_ALLOBJECTS |     43 |   2805 |  52617 |00:00:00.25 |   65059 |
|   3 |  TABLE ACCESS FULL | SCALAR_ALLUSERS   |      1 |     43 |     43 |00:00:00.01 |       6 |
--------------------------------------------------------------------------------------------------

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

   1 - CNTBLOCK
   2 - CNTBLOCK  / B@CNTBLOCK
   3 - MAINBLOCK / A@MAINBLOCK

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

   2 - filter("B"."OWNER"=:B1)


29 rows selected.

Of course nothing is perfect.  If you query block gets merged into another one, then some names get lost.  Rats!

In this query there are several query blocks, but because the query block ORDERED_STDS gets merged into MAIN we lose the MAIN name and it gets a new name SEL$DDAF0A40. Even with this, it’s still easier to see which lines of the plan go to which subqueries.  Also this little example uses the good old explain plan to show they are used with explain plan as well.

SQL> explain plan set statement_id = 'EX2_2_1' for
  2        select /*+ QB_NAME(MAIN) */ student_id, last_name
  3         from (select /*+ QB_NAME (ORDERED_STDS) */  * from student order by student_id) s
  4         where (select /*+ QB_NAME(ENROLL_CNT) */ count(*)
  5               from enrollment e
  6               where s.student_id = e.student_id) >
  7                   (select /*+ QB_NAME(ENROLL_AVG) */ avg(count(*))
  8                      from enrollment
  9                      group by student_id);

Explained.

SQL>
SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','EX2_2_1','TYPICAL ALIAS'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
Plan hash value: 3191484131

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |   268 |  3216 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| STUDENT |   268 |  3216 |     2   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN           | STU_PK  |    13 |       |     1   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE           |         |     1 |     4 |            |          |
|*  4 |     INDEX RANGE SCAN        | ENR_PK  |     1 |     4 |     1   (0)| 00:00:01 |
|   5 |    SORT AGGREGATE           |         |     1 |     4 |     1   (0)| 00:00:01 |
|   6 |     SORT GROUP BY           |         |     1 |     4 |     1   (0)| 00:00:01 |
|   7 |      INDEX FULL SCAN        | ENR_PK  |   226 |   904 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   1 - SEL$DDAF0A40 / STUDENT@ORDERED_STDS
   2 - SEL$DDAF0A40 / STUDENT@ORDERED_STDS
   3 - ENROLL_CNT
   4 - ENROLL_CNT   / E@ENROLL_CNT
   5 - ENROLL_AVG
   7 - ENROLL_AVG   / ENROLLMENT@ENROLL_AVG

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

   2 - filter( (SELECT /*+ QB_NAME ("ENROLL_CNT") */ COUNT(*) FROM
              "ENROLLMENT" "E" WHERE "E"."STUDENT_ID"=:B1)> (SELECT /*+ QB_NAME
              ("ENROLL_AVG") */ AVG(COUNT(*)) FROM "ENROLLMENT" "ENROLLMENT" GROUP BY
              "STUDENT_ID"))
   4 - access("E"."STUDENT_ID"=:B1)

33 rows selected.

SQL>