QB NAME - 日本エクセム株式会社 Oracle 技術情報

/*+ QB_NAME（queryblock_name）*/

- Statistics LevelをAllに変更します。
alter session set statistics_level = all;

- QB_NAMEヒント付与
explain plan for select /*+ qb_name(main) */ t1.id1, t2.name2, x.id4, x.name5,
    (select /*+ qb_name(scalar) */ count(*) from t1 s where s.id1 = t1.id1) as id1_1
from t1, t2, t3, t5,
    (select /*+ qb_name(inline) */ t4.id4, t5.name5
        from t4, t5
        where t4.id4 = t5.id5 and t5.name5 like '%c%') x
where t1.id1 = t2.id2
    and t2.id2 in (select /*+ qb_name(subquery) */ id3 from t3 where name3 like '%b%')
    and t2.id2 = x.id4
    and t3.id3 = t1.id1
    and t5.name5 = t1.name1;

- CursorのALL statsを照会します。 
select * from table(dbms_xplan.display(null,null, 'ALL'));

----------------------------------------------------------------------------------
| Id  | Operation               | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |        |     1 |   113 |    14  (15)| 00:00:01 |
|   1 |  SORT AGGREGATE         |        |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN      | T1_IDX |     1 |    13 |     1   (0)| 00:00:01 |
|*  3 |  HASH JOIN              |        |     1 |   113 |    14  (15)| 00:00:01 |
|*  4 |   HASH JOIN SEMI        |        |     1 |   106 |    12  (17)| 00:00:01 |
|   5 |    NESTED LOOPS         |        |     1 |    86 |     9  (12)| 00:00:01 |
|*  6 |     HASH JOIN           |        |     1 |    73 |     8  (13)| 00:00:01 |
|*  7 |      HASH JOIN          |        |     1 |   53 |     6  (17)| 00:00:01 |
|   8 |       NESTED LOOPS      |        |     1 |    33 |     3   (0)| 00:00:01 |
|*  9 |        TABLE ACCESS FULL| T5     |     1 |    20 |     2   (0)| 00:00:01 |
|* 10 |        INDEX RANGE SCAN | T4_IDX |     1 |    13 |     1   (0)| 00:00:01 |
|  11 |       TABLE ACCESS FULL | T2     |     1 |    20 |     2   (0)| 00:00:01 |
|  12 |      TABLE ACCESS FULL  | T1     |     1 |    20 |     2   (0)| 00:00:01 |
|* 13 |     INDEX RANGE SCAN    | T3_IDX |     1 |    13 |     1   (0)| 00:00:01 |
|* 14 |    TABLE ACCESS FULL    | T3     |     1 |    20 |     2   (0)| 00:00:01 |
|  15 |   TABLE ACCESS FULL     | T5     |     1 |     7 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SCALAR
   2 - SCALAR       / S@SCALAR
   3 - SEL$EA1A1EE6
   9 - SEL$EA1A1EE6 / T5@INLINE
  10 - SEL$EA1A1EE6 / T4@INLINE
  11 - SEL$EA1A1EE6 / T2@MAIN
  12 - SEL$EA1A1EE6 / T1@MAIN
  13 - SEL$EA1A1EE6 / T3@MAIN
  14 - SEL$EA1A1EE6 / T3@SUBQUERY
  15 - SEL$EA1A1EE6 / T5@MAIN

select /*+ qb_name(main) no_unnest(@subquery) */ t1.id1, t2.name2, x.id4, x.name5,
    (select /*+ qb_name(scalar) */ count(*) from t1 s where s.id1 = t1.id1) as id1_1
from t1, t2, t3, t5,
    (select /*+ qb_name(inline) */ t4.id4, t5.name5
        from t4, t5
        where t4.id4 = t5.id5 and t5.name5 like '%c%') x
where t1.id1 = t2.id2
    and t2.id2 in (select /*+ qb_name(subquery) */ id3 from t3 where name3 like '%b%')
    and t2.id2 = x.id4
    and t3.id3 = t1.id1
    and t5.name5 = t1.name1;