19.3.3.4提示使用情况报告：示例
这些示例显示了各种类型的提示使用情况报告。
以下示例均显示hr架构中的表查询。
示例19-2语句级未使用的提示
下面的示例为索引指定索引范围提示emp_manager_ix：
EXPLAIN PLAN FOR
SELECT /*+ INDEX_RS(e emp_manager_ix) */ COUNT(*)
FROM employees e
WHERE e.job_id < 5;计划表的以下查询指定的format值TYPICAL，该值仅显示未使用的提示：
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'TYPICAL'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 2731009358
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | INDEX FULL SCAN| EMP_JOB_IX | 5 | 45 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
2 - filter(TO_NUMBER("E"."JOB_ID")<5)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
2 - SEL$1 / E@SEL$1
U - INDEX_RS(e emp_manager_ix)
将U在前面的提示使用报告表明，INDEX_RS不使用提示。该报告显示未使用的提示总数：U – Unused (1)。
示例19-3提示冲突
以下示例指定了两个提示，一个提示用于跳过扫描，一个提示用于快速全扫描：
EXPLAIN PLAN FOR
SELECT /*+ INDEX_SS(e emp_manager_ix) INDEX_FFS(e) */ COUNT(*)
FROM employees e
WHERE e.manager_id < 5;计划表的以下查询指定的format值TYPICAL，该值仅显示未使用的提示：
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'TYPICAL'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 2262146496
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| EMP_MANAGER_IX | 1 | 4 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
2 - access("E"."MANAGER_ID"<5)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------
2 - SEL$1 / E@SEL$1
U - INDEX_FFS(e) / hint conflicts with another in sibling query block
U - INDEX_SS(e emp_manager_ix) / hint conflicts with another in sibling query block先前的报告显示INDEX_FFS(e)和INDEX_SS(e emp_manager_ix)提示相互冲突。索引跳过扫描和索引快速完整扫描是互斥的。优化程序忽略了这两个提示，如文本所示U — Unused (2)。即使优化程序忽略了指定emp_manager_ix索引的提示，但优化程序仍然会基于其基于成本的分析来使用该索引。
示例19-4多表提示
以下示例指定了四个提示，其中之一指定了两个表：
EXPLAIN PLAN FOR
SELECT /*+ ORDERED USE_NL(t1, t2) INDEX(t2) NLJ_PREFETCH(t2) */ COUNT(*)
FROM jobs t1, employees t2
WHERE t1.job_id = t2.employee_id;计划表的以下查询指定了的format值ALL：
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 2668549896
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
| 2 | NESTED LOOPS | | 19 | 228 | 1 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | JOB_ID_PK | 19 | 152 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN| EMP_EMP_ID_PK | 1 | 4 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / T1@SEL$1
4 - SEL$1 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T2"."EMPLOYEE_ID"=TO_NUMBER("T1"."JOB_ID"))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
2 - (#keys=0)
3 - "T1"."JOB_ID"[VARCHAR2,10]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 5 (U - Unused (2))
---------------------------------------------------------------------------
1 - SEL$1
- ORDERED
3 - SEL$1 / T1@SEL$1
U - USE_NL(t1, t2)
4 - SEL$1 / T2@SEL$1
U - NLJ_PREFETCH(t2)
- INDEX(t2)
- USE_NL(t1, t2)
先前的报告显示未使用两个提示：USE_NL(t1, t2)和NLJ_PREFETCH(t2)。计划的第3步是对jobs表进行索引完全扫描，该表使用别名t1。该报告显示，优化器未USE_NL(t1, t2)对访问的提示应用提示jobs。步骤4是表的索引唯一扫描，该扫描employees使用别名t2。没有U前缀USE_NL(t1, t2)，这意味着优化程序确实使用了的提示employees。
示例19-5未使用的查询块的提示
以下示例在子查询上指定了两个提示：UNNEST和SEMIJOIN。
EXPLAIN PLAN FOR
SELECT COUNT(*), manager_id
FROM departments
WHERE manager_id IN (SELECT /*+ UNNEST SEMIJOIN */ manager_id FROM employees)
AND ROWNUM <= 2
GROUP BY manager_id;计划表的以下查询指定了的format值ALL：
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 173733304
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 3 (34)| 00:00:01 |
| 1 | HASH GROUP BY | | 2 | 14 | 3 (34)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | NESTED LOOPS SEMI | | 2 | 14 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| DEPARTMENTS | 2 | 6 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_MANAGER_IX | 107 | 428 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
4 - SEL$5DA710D3 / DEPARTMENTS@SEL$1
5 - SEL$5DA710D3 / EMPLOYEES@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=2)
4 - filter("MANAGER_ID" IS NOT NULL)
5 - access("MANAGER_ID"="MANAGER_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) "MANAGER_ID"[NUMBER,22], COUNT(*)[22]
2 - "MANAGER_ID"[NUMBER,22]
3 - (#keys=0) "MANAGER_ID"[NUMBER,22]
4 - "MANAGER_ID"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
0 - SEL$2
- SEMIJOIN
- UNNEST
在此示例中，提示在查询块中指定SEL$2，但未SEL$2出现在最终计划中。该报告显示SEL$2带有相关行号的提示0。
示例19-6覆盖的提示
下面的示例FULL在同一查询块中的同一表上指定两个提示：
EXPLAIN PLAN FOR
SELECT /*+ INDEX(t1) FULL(@sel$2 t1) */ COUNT(*)
FROM jobs t1
WHERE t1.job_id IN (SELECT /*+ FULL(t1) NO_MERGE */ job_id FROM employees t1);计划表的以下查询指定了的format值ALL：
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 3101158531
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 3 (34)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
| 2 | NESTED LOOPS | | 19 | 323 | 3 (34)| 00:00:01 |
| 3 | SORT UNIQUE | | 107 | 963 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMPLOYEES | 107 | 963 | 2 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | 8 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
4 - SEL$5DA710D3 / T1@SEL$2
5 - SEL$5DA710D3 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."JOB_ID"="JOB_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
2 - (#keys=0)
3 - (#keys=1) "JOB_ID"[VARCHAR2,10]
4 - (rowset=256) "JOB_ID"[VARCHAR2,10]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 4 (U - Unused (1))
---------------------------------------------------------------------------
0 - SEL$2
- NO_MERGE
4 - SEL$5DA710D3 / T1@SEL$2
U - FULL(t1) / hint overridden by another in parent query block
- FULL(@sel$2 t1)
5 - SEL$5DA710D3 / T1@SEL$1
- INDEX(t1)在指定的三个提示中，只有一个未使用。提示FULL(t1)在查询块中指定SEL$2由提示覆盖FULL(@sel$2 T1)在查询块中指定SEL$1。使用NO_MERGE了查询块中的提示SEL$2。
以下使用格式设置查询计划表TYPICAL仅显示未使用的提示：
SQL> select * from table(dbms_xplan.display(format => 'TYPICAL'));
Plan hash value: 3101158531
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 3 (34)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
| 2 | NESTED LOOPS | | 19 | 323 | 3 (34)| 00:00:01 |
| 3 | SORT UNIQUE | | 107 | 963 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMPLOYEES | 107 | 963 | 2 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | 8 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."JOB_ID"="JOB_ID")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
4 - SEL$5DA710D3 / T1@SEL$2
U - FULL(t1) / hint overridden by another in parent query block示例19-7多种提示
以下UNION ALL查询指定十个不同的提示：
SELECT /*+ FULL(t3) INDEX(t2) INDEX(t1) MERGE(@SEL$5) PARALLEL(2) */ t1.first_name
FROM employees t1, jobs t2, job_history t3
WHERE t1.job_id = t2.job_id
AND t2.min_salary = 100000
AND t1.department_id = t3.department_id
UNION ALL
SELECT /*+ INDEX(t3) USE_MERGE(t2) INDEX(t2) FULL(t1) NO_ORDER_SUBQ */ t1.first_name
FROM departments t3, jobs t2, employees t1
WHERE t1.job_id = t2.job_id
AND t2.min_salary = 100000
AND t1.department_id = t3.department_id;以下对共享SQL区域的查询指定的format值ALL（请注意，出于可读性考虑，计划行已被截断）：
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALL'))
...
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)|
| 1 | UNION-ALL | | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | 5 | 175 | 5 (0)|
|* 4 | HASH JOIN | | 5 | 175 | 5 (0)|
| 5 | PX RECEIVE | | 3 | 93 | 3 (0)|
| 6 | PX SEND BROADCAST | :TQ10001 | 3 | 93 | 3 (0)|
| 7 | NESTED LOOPS | | 3 | 93 | 3 (0)|
| 8 | NESTED LOOPS | | 6 | 93 | 3 (0)|
|* 9 | TABLE ACCESS BY INDEX ROWID BATCHED| JOBS | 1 | 12 | 2 (0)|
| 10 | BUFFER SORT | | | | |
| 11 | PX RECEIVE | | 19 | | 1 (0)|
| 12 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 19 | | 1 (0)|
| 13 | PX SELECTOR | | | | |
| 14 | INDEX FULL SCAN | JOB_ID_PK | 19 | | 1 (0)|
|* 15 | INDEX RANGE SCAN | EMP_JOB_IX | 6 | | 0 (0)|
| 16 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 6 | 114 | 1 (0)|
| 17 | PX BLOCK ITERATOR | | 10 | 40 | 2 (0)|
|* 18 | TABLE ACCESS FULL | JOB_HISTORY | 10 | 40 | 2 (0)|
| 19 | PX COORDINATOR | | | | |
| 20 | PX SEND QC (RANDOM) | :TQ20002 | 3 | 93 | 4 (0)|
|* 21 | HASH JOIN | | 3 | 93 | 4 (0)|
| 22 | JOIN FILTER CREATE | :BF0000 | 1 | 12 | 2 (0)|
| 23 | PX RECEIVE | | 1 | 12 | 2 (0)|
| 24 | PX SEND BROADCAST | :TQ20001 | 1 | 12 | 2 (0)|
|* 25 | TABLE ACCESS BY INDEX ROWID BATCHED | JOBS | 1 | 12 | 2 (0)|
| 26 | BUFFER SORT | | | | |
| 27 | PX RECEIVE | | 19 | | 1 (0)|
| 28 | PX SEND HASH (BLOCK ADDRESS) | :TQ20000 | 19 | | 1 (0)|
| 29 | PX SELECTOR | | | | |
| 30 | INDEX FULL SCAN | JOB_ID_PK | 19 | | 1 (0)|
| 31 | JOIN FILTER USE | :BF0000 | 106 | 2014 | 2 (0)|
| 32 | PX BLOCK ITERATOR | | 106 | 2014 | 2 (0)|
|* 33 | TABLE ACCESS FULL | EMPLOYEES | 106 | 2014 | 2 (0)|
------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$1
4 - SEL$1
9 - SEL$1 / T2@SEL$1
14 - SEL$1 / T2@SEL$1
15 - SEL$1 / T1@SEL$1
16 - SEL$1 / T1@SEL$1
18 - SEL$1 / T3@SEL$1
21 - SEL$E0F432AE
25 - SEL$E0F432AE / T2@SEL$2
30 - SEL$E0F432AE / T2@SEL$2
33 - SEL$E0F432AE / T1@SEL$2
Predicate Information (identified by operation id):
-----------------------------------------------------------
4 - access("T1"."DEPARTMENT_ID"="T3"."DEPARTMENT_ID")
9 - filter("T2"."MIN_SALARY"=100000)
15 - access("T1"."JOB_ID"="T2"."JOB_ID")
18 - access(:Z>=:Z AND :Z<=:Z)
21 - access("T1"."JOB_ID"="T2"."JOB_ID")
25 - filter("T2"."MIN_SALARY"=100000)
33 - access(:Z>=:Z AND :Z<=:Z)
filter(("T1"."DEPARTMENT_ID" IS NOT NULL AND SYS_OP_BLOOM_FILTER(:BF0000,"T1"."JOB_ID")))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - STRDEF[20]
2 - "T1"."FIRST_NAME"[VARCHAR2,20]
3 - (#keys=0) "T1"."FIRST_NAME"[VARCHAR2,20]
4 - (#keys=1; rowset=256) "T1"."FIRST_NAME"[VARCHAR2,20]
5 - (rowset=256) "T1"."DEPARTMENT_ID"[NUMBER,22], "T1"."FIRST_NAME"[VARCHAR2,20]
6 - (#keys=0) "T1"."DEPARTMENT_ID"[NUMBER,22], "T1"."FIRST_NAME"[VARCHAR2,20]
7 - "T1"."FIRST_NAME"[VARCHAR2,20], "T1"."DEPARTMENT_ID"[NUMBER,22]
8 - "T1".ROWID[ROWID,10]
9 - "T2"."JOB_ID"[VARCHAR2,10]
10 - (#keys=0) "T2".ROWID[ROWID,10], "T2"."JOB_ID"[VARCHAR2,10]
11 - (rowset=256) "T2".ROWID[ROWID,10], "T2"."JOB_ID"[VARCHAR2,10]
12 - (#keys=1) "T2".ROWID[ROWID,10], "T2"."JOB_ID"[VARCHAR2,10]
13 - "T2".ROWID[ROWID,10], "T2"."JOB_ID"[VARCHAR2,10]
14 - "T2".ROWID[ROWID,10], "T2"."JOB_ID"[VARCHAR2,10]
15 - "T1".ROWID[ROWID,10]
16 - "T1"."FIRST_NAME"[VARCHAR2,20], "T1"."DEPARTMENT_ID"[NUMBER,22]
17 - (rowset=256) "T3"."DEPARTMENT_ID"[NUMBER,22]
18 - (rowset=256) "T3"."DEPARTMENT_ID"[NUMBER,22]
19 - "T1"."FIRST_NAME"[VARCHAR2,20]
20 - (#keys=0) "T1"."FIRST_NAME"[VARCHAR2,20]
21 - (#keys=1; rowset=256) "T1"."FIRST_NAME"[VARCHAR2,20]
22 - (rowset=256) "T2"."JOB_ID"[VARCHAR2,10]
23 - (rowset=256) "T2"."JOB_ID"[VARCHAR2,10]
24 - (#keys=0) "T2"."JOB_ID"[VARCHAR2,10]
25 - "T2"."JOB_ID"[VARCHAR2,10]
26 - (#keys=0) "T2".ROWID[ROWID,10], "T2"."JOB_ID"[VARCHAR2,10]
27 - (rowset=256) "T2".ROWID[ROWID,10], "T2"."JOB_ID"[VARCHAR2,10]
28 - (#keys=1) "T2".ROWID[ROWID,10], "T2"."JOB_ID"[VARCHAR2,10]
29 - "T2".ROWID[ROWID,10], "T2"."JOB_ID"[VARCHAR2,10]
30 - "T2".ROWID[ROWID,10], "T2"."JOB_ID"[VARCHAR2,10]
31 - (rowset=256) "T1"."FIRST_NAME"[VARCHAR2,20], "T1"."JOB_ID"[VARCHAR2,10]
32 - (rowset=256) "T1"."FIRST_NAME"[VARCHAR2,20], "T1"."JOB_ID"[VARCHAR2,10]
33 - (rowset=256) "T1"."FIRST_NAME"[VARCHAR2,20], "T1"."JOB_ID"[VARCHAR2,10]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 10 (U - Unused (2), N - Unresolved (1), E - Syntax error (1))
----------------------------------------------------------------------------------------
0 - STATEMENT
- PARALLEL(2)
0 - SEL$5
N - MERGE(@SEL$5)
0 - SEL$2
E - NO_ORDER_SUBQ
9 - SEL$1 / T2@SEL$1
- INDEX(t2)
15 - SEL$1 / T1@SEL$1
- INDEX(t1)
18 - SEL$1 / T3@SEL$1
- FULL(t3)
21 - SEL$E0F432AE / T3@SEL$2
U - INDEX(t3)
25 - SEL$E0F432AE / T2@SEL$2
U - USE_MERGE(t2)
- INDEX(t2)
33 - SEL$E0F432AE / T1@SEL$2
- FULL(t1)
Note
-----
- Degree of Parallelism is 2 because of hint
该报告指出了以下未使用的提示：
- 两个未使用的提示（
U）该报告指示
INDEX(t3)和USER_MERGE(t2)未在查询块中使用SEL$E0F432AE。 - 一个未解决的提示（
N）MERGE由于查询块SEL$5不存在，因此无法解析该提示。 - 一种语法错误（
E）NO_ORDER_SUBQ中指定的提示SEL$2不是有效的提示。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
