近期，在优化一个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")