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
不是有效的提示。