关于QB_NAME的hint

近期,在优化一个SQL时用到了QB_NAME的HINT,此HINT我经常使用的原因目的是让子查询单独作为一个整体,与其他表关联时可以进行调整驱动顺序,举例:

SELECT b.owner,
       b.object_name,
       b.created,
       b.status
FROM albert.fc b
WHERE 1=1
  AND b.object_id IN
    (SELECT c.object_id
     FROM albert.P c
     WHERE c.owner='NFS_SN')
执行计划如下:
Plan hash value: 3977992979

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		     | Name    | Starts | E-Rows | Cost (%CPU)| E-Time	 | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	       |      1 |	 |   569 (100)| 	 |    119 |00:00:00.05 |    1445 |
|*  1 |  HASH JOIN RIGHT SEMI	     |	       |      1 |  64830 |   569  (11)| 00:00:07 |    119 |00:00:00.05 |    1445 |
|   2 |   TABLE ACCESS BY INDEX ROWID| P       |      1 |   2192 |   116   (0)| 00:00:02 |    119 |00:00:00.01 |      17 |
|*  3 |    INDEX RANGE SCAN	     | P_IDX01 |      1 |   2192 |     6   (0)| 00:00:01 |    119 |00:00:00.01 |       2 |
|   4 |   TABLE ACCESS FULL	     | FC      |      1 |   3000K|   439  (11)| 00:00:06 |    100K|00:00:00.02 |    1428 |
--------------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / C@SEL$2
   3 - SEL$5DA710D3 / C@SEL$2
   4 - SEL$5DA710D3 / B@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$5DA710D3" "B"@"SEL$1")
      INDEX_RS_ASC(@"SEL$5DA710D3" "C"@"SEL$2" ("P"."OWNER"))
      LEADING(@"SEL$5DA710D3" "B"@"SEL$1" "C"@"SEL$2")
      USE_HASH(@"SEL$5DA710D3" "C"@"SEL$2")
      SWAP_JOIN_INPUTS(@"SEL$5DA710D3" "C"@"SEL$2")
      END_OUTLINE_DATA
  */

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

   1 - access("B"."OBJECT_ID"="C"."OBJECT_ID")
   3 - access("C"."OWNER"='NFS_SN')

该执行计划并不是最优,FC表连接列object_id是存在索引的,但是并没有用上,我们可以利用QB_NAME进行调整,利用P表,Nested_Loop FC表:

SELECT /*+ use_nl(b,c@x) leading(c#@x) */b.owner,
                                         b.object_name,
                                         b.created,
                                         b.status
FROM albert.fc b
WHERE 1=1
  AND b.object_id IN
    (SELECT /*+ qb_name(x) */ c.object_id
     FROM albert.P c
     WHERE c.owner='NFS_SN')

Plan hash value: 2117722349

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		       | Name	  | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |	 A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	       |	  |	 1 |	    |  2311 (100)|	    |	 119 |00:00:00.01 |	 89 |
|   1 |  NESTED LOOPS		       |	  |	 1 |  65492 |  2311   (1)| 00:00:28 |	 119 |00:00:00.01 |	 89 |
|   2 |   NESTED LOOPS		       |	  |	 1 |  65492 |  2311   (1)| 00:00:28 |	 119 |00:00:00.01 |	 68 |
|   3 |    SORT UNIQUE		       |	  |	 1 |   2192 |	116   (0)| 00:00:02 |	 119 |00:00:00.01 |	 17 |
|   4 |     TABLE ACCESS BY INDEX ROWID| P	  |	 1 |   2192 |	116   (0)| 00:00:02 |	 119 |00:00:00.01 |	 17 |
|*  5 |      INDEX RANGE SCAN	       | P_IDX01  |	 1 |   2192 |	  6   (0)| 00:00:01 |	 119 |00:00:00.01 |	  2 |
|*  6 |    INDEX RANGE SCAN	       | FC_IDX01 |    119 |	 30 |	  1   (0)| 00:00:01 |	 119 |00:00:00.01 |	 51 |
|   7 |   TABLE ACCESS BY INDEX ROWID  | FC	  |    119 |	 30 |	  2   (0)| 00:00:01 |	 119 |00:00:00.01 |	 21 |
-----------------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$17ACF30C
   4 - SEL$17ACF30C / C@X
   5 - SEL$17ACF30C / C@X
   6 - SEL$17ACF30C / B@SEL$1
   7 - SEL$17ACF30C / B@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$17ACF30C")
      UNNEST(@"X")
      OUTLINE(@"SEL$1")
      OUTLINE(@"X")
      INDEX_RS_ASC(@"SEL$17ACF30C" "C"@"X" ("P"."OWNER"))
      INDEX(@"SEL$17ACF30C" "B"@"SEL$1" ("FC"."OBJECT_ID"))
      LEADING(@"SEL$17ACF30C" "C"@"X" "B"@"SEL$1")
      USE_NL(@"SEL$17ACF30C" "B"@"SEL$1")
      NLJ_BATCHING(@"SEL$17ACF30C" "B"@"SEL$1")
      SEMI_TO_INNER(@"SEL$17ACF30C" "C"@"X")
      END_OUTLINE_DATA
  */

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

   5 - access("C"."OWNER"='NFS_SN')
   6 - access("B"."OBJECT_ID"="C"."OBJECT_ID")

可以看到,SQL由原来的1400个buffer降至89个buffer。但是,如下SQL利用此HINT却无法进行调整:

SELECT /*+ use_nl(b,c@x) leading(c@x) */ b.owner,
                                         b.object_name,
                                         b.created,
                                         b.status
FROM albert.fc b
WHERE 1=1
  AND b.object_id IN
    (SELECT /*+ qb_name(x)
*/ c.object_id
     FROM albert.P c
     WHERE c.owner='NFS_SN'
     GROUP BY c.object_id)

Plan hash value: 3083885101

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name     | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |          |      1 |        |  2205 (100)|          |    119 |00:00:00.25 |    1971 |    198 |
|   1 |  MERGE JOIN SEMI                |          |      1 |  64800 |  2205   (1)| 00:00:27 |    119 |00:00:00.25 |    1971 |    198 |
|   2 |   TABLE ACCESS BY INDEX ROWID   | FC       |      1 |   3000K|  2087   (1)| 00:00:26 |  97565 |00:00:00.18 |    1954 |    198 |
|   3 |    INDEX FULL SCAN              | FC_IDX01 |      1 |    100K|   225   (1)| 00:00:03 |  97565 |00:00:00.14 |     225 |    198 |
|*  4 |   SORT UNIQUE                   |          |  97565 |   2169 |   118   (2)| 00:00:02 |    119 |00:00:00.03 |      17 |      0 |
|   5 |    VIEW                         | VW_NSO_1 |      1 |   2169 |   117   (1)| 00:00:02 |    119 |00:00:00.01 |      17 |      0 |
|   6 |     HASH GROUP BY               |          |      1 |   2169 |   117   (1)| 00:00:02 |    119 |00:00:00.01 |      17 |      0 |
|   7 |      TABLE ACCESS BY INDEX ROWID| P        |      1 |   2192 |   116   (0)| 00:00:02 |    119 |00:00:00.01 |      17 |      0 |
|*  8 |       INDEX RANGE SCAN          | P_IDX01  |      1 |   2192 |     6   (0)| 00:00:01 |    119 |00:00:00.01 |       2 |      0 |
---------------------------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$17ACF30C
   2 - SEL$17ACF30C / B@SEL$1
   3 - SEL$17ACF30C / B@SEL$1
   5 - SEL$1FB17987 / VW_NSO_1@SEL$17ACF30C
   6 - SEL$1FB17987
   7 - SEL$1FB17987 / C@X
   8 - SEL$1FB17987 / C@X

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1FB17987")
      OUTLINE_LEAF(@"SEL$17ACF30C")
      UNNEST(@"X" UNNEST_SEMIJ_VIEW)
      OUTLINE(@"X")
      OUTLINE(@"SEL$1")
      INDEX(@"SEL$17ACF30C" "B"@"SEL$1" ("FC"."OBJECT_ID"))
      NO_ACCESS(@"SEL$17ACF30C" "VW_NSO_1"@"SEL$17ACF30C")
      LEADING(@"SEL$17ACF30C" "B"@"SEL$1" "VW_NSO_1"@"SEL$17ACF30C")
      USE_MERGE(@"SEL$17ACF30C" "VW_NSO_1"@"SEL$17ACF30C")
      INDEX_RS_ASC(@"SEL$1FB17987" "C"@"X" ("P"."OWNER"))
      USE_HASH_AGGREGATION(@"SEL$1FB17987")
      END_OUTLINE_DATA
  */

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

   4 - access("B"."OBJECT_ID"="OBJECT_ID")
       filter("B"."OBJECT_ID"="OBJECT_ID")
   8 - access("C"."OWNER"='NFS_SN')

SQL选择了MERGE JOIN,并且buffer高达了1900+,为何QB_NAME失效了呢?此SQL子查询hint中已命名X,从执行计划中Query Block Name中看到:

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

   1 - SEL$17ACF30C
   2 - SEL$17ACF30C / B@SEL$1
   3 - SEL$17ACF30C / B@SEL$1
   5 - SEL$1FB17987 / VW_NSO_1@SEL$17ACF30C  --子查询最后并命名了一个VIEW
   6 - SEL$1FB17987
   7 - SEL$1FB17987 / C@X
   8 - SEL$1FB17987 / C@X

因为此SQL中存在GROUP BY,针对此SQL,Oracle将子查询生成一个”VIEW”所以C@X的HINT已经被”盖掉“,针对此类情况我们需要利用如下方式进行修正:

SQL_ID	14p81kpzbzq5h, child number 0
-------------------------------------
SELECT /*+ use_nl(b,VW_NSO_1) leading(VW_NSO_1) */ b.owner,
				       b.object_name,
				 b.created,
		       b.status FROM albert.fc b WHERE 1=1   AND
b.object_id IN	   (SELECT c.object_id	    FROM albert.P c	 WHERE
c.owner='NFS_SN'      GROUP BY c.object_id)

Plan hash value: 3937912146

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		       | Name	  | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |	 A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	       |	  |	 1 |	    |  5633 (100)|	    |	 119 |00:00:00.01 |	 74 |
|   1 |  HASH GROUP BY		       |	  |	 1 |  65492 |  5633   (1)| 00:01:08 |	 119 |00:00:00.01 |	 74 |
|   2 |   NESTED LOOPS		       |	  |	 1 |  65492 |  4503   (1)| 00:00:55 |	 119 |00:00:00.01 |	 74 |
|   3 |    NESTED LOOPS 	       |	  |	 1 |  65760 |  4503   (1)| 00:00:55 |	 119 |00:00:00.01 |	 59 |
|   4 |     TABLE ACCESS BY INDEX ROWID| P	  |	 1 |   2192 |	116   (0)| 00:00:02 |	 119 |00:00:00.01 |	 17 |
|*  5 |      INDEX RANGE SCAN	       | P_IDX01  |	 1 |   2192 |	  6   (0)| 00:00:01 |	 119 |00:00:00.01 |	  2 |
|*  6 |     INDEX RANGE SCAN	       | FC_IDX01 |    119 |	 30 |	  1   (0)| 00:00:01 |	 119 |00:00:00.01 |	 42 |
|   7 |    TABLE ACCESS BY INDEX ROWID | FC	  |    119 |	 30 |	  2   (0)| 00:00:01 |	 119 |00:00:00.01 |	 15 |
-----------------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$C9C6826C
   4 - SEL$C9C6826C / C@SEL$2
   5 - SEL$C9C6826C / C@SEL$2
   6 - SEL$C9C6826C / B@SEL$1
   7 - SEL$C9C6826C / B@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$C9C6826C")
      MERGE(@"SEL$683B0107")
      OUTLINE(@"SEL$5DA710D3")
      UNNEST(@"SEL$2" UNNEST_INNERJ_DISTINCT_VIEW)
      OUTLINE(@"SEL$683B0107")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      INDEX_RS_ASC(@"SEL$C9C6826C" "C"@"SEL$2" ("P"."OWNER"))
      INDEX(@"SEL$C9C6826C" "B"@"SEL$1" ("FC"."OBJECT_ID"))
      LEADING(@"SEL$C9C6826C" "C"@"SEL$2" "B"@"SEL$1")
      USE_NL(@"SEL$C9C6826C" "B"@"SEL$1")
      NLJ_BATCHING(@"SEL$C9C6826C" "B"@"SEL$1")
      USE_HASH_AGGREGATION(@"SEL$C9C6826C")
      END_OUTLINE_DATA
  */

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

   5 - access("C"."OWNER"='NFS_SN')
   6 - access("B"."OBJECT_ID"="C"."OBJECT_ID")