版权声明:本文为Buddy Yuan原创文章,转载请注明出处。原文地址:Oracle 19c新特性 -HINT_REPORT
在Oracle 19c之前,我们经常会遇到一个问题,我们使用了错误的hint,它不会告诉我们任何错误。这会导致我们在优化执行计划的时候有点手足无措,明明我设置了index的hint啊,怎么使用不了,怎么仍然是全表扫描。但是在新的19c下面,有一个新功能可以帮助我们查看hint使用的情况报告,我们使用dbms_xplan函数(display,display_cursor,display_workload_repository或者是display_sql_plan_baseline)时,默认情况下启动此功能。
接下来我们来演示一下这个功能,我们先早点数据,然后创建索引。在hint中,我们使用错误的索引看看会出现什么情况。
我们来创建一张表,一个索引,并收集统计信息。
create table test as select * from dba_objects; create index idx_object_type on test(OBJECT_TYPE); exec dbms_stats.gather_table_stats(OWNNAME=>'TEST',TABNAME=>'TEST' , cascade => true);
接下来我们来演示使用错误的hint,hint中索引名字写错
select /*+ INDEX (test,idxobjectype) */ distinct object_type from test where owner=’SYS’;
在执行dbms_xplan.display_cursor的时候,我们选择format的格式为HINT_REPORT,这里我们就可以看到Hint Report的提示,这个地方就提示我们INDEX (test,idxobjectype) / index specified in the hint doesn’t exist。我们就可以立马发现问题,指定的索引不存在。
SQL> select * from table (dbms_xplan.display_cursor (format=>'HINT_REPORT'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9s0p5vxpg01jg, child number 1
-------------------------------------
select /*+ INDEX (test,idxobjectype) */ distinct object_type from test
where owner='SYS'
Plan hash value: 2203132549
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 393 (100)| |
| 1 | HASH UNIQUE | | 45 | 675 | 393 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| TEST | 2896 | 43440 | 392 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SYS')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
2 - SEL$1 / TEST@SEL$1
U - INDEX (test,idxobjectype) / index specified in the hint doesn't exist
27 rows selected.
我们在改回正确的索引,可以看到正确的就是OUTLINE中的内容。但是这里hint提示Unused,这里还是得说下优化器还是很强大的,仍然并没有使用我们的hint,在hint这个地方告诉我们Unused。
SQL> select * from table (dbms_xplan.display_cursor (format=>'HINT_REPORT'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bqg63rfpsad9j, child number 0
-------------------------------------
select /*+ INDEX (test,idx_object_type) */ distinct object_type from
test where owner='SYS'
Plan hash value: 2203132549
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 393 (100)| |
| 1 | HASH UNIQUE | | 45 | 675 | 393 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| TEST | 2896 | 43440 | 392 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SYS')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
2 - SEL$1 / TEST@SEL$1
U - INDEX (test,idx_object_type)
看了演示,可能会觉得这只能检测到名字之类的错误啊,其实还可以帮助我们检测到语法之类的错误,注意看这里提示变成了E – Syntax error。因为USE_NL中,我们没有写相关的驱动表。
select /*+ USE_NL */ distinct object_type from test where owner='SYS';
SQL> select * from table (dbms_xplan.display_cursor (format=>'HINT_REPORT'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8xb3sgt6mkm1w, child number 0
-------------------------------------
select /*+ USE_NL */ distinct object_type from test where owner='SYS'
Plan hash value: 2203132549
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 393 (100)| |
| 1 | HASH UNIQUE | | 45 | 675 | 393 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| TEST | 2896 | 43440 | 392 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SYS')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - USE_NL
26 rows selected.
还有一种情况是我们使用了多个hint,而这两个hint是有冲突的。我们在刚刚的语句中指定FULL(test) INDEX(test,idx_object_type),因为索引扫描和全表扫描是互斥的。在这种情况下,优化器会忽略两个冲突的提示。两个hint这里都会提示hint conflicts。
select /*+ FULL(test) INDEX(test,idx_object_type) */ distinct object_type from test where owner='SYS'; SQL> select * from table (dbms_xplan.display_cursor (format=>'HINT_REPORT')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID f199byqapmvxm, child number 0 ------------------------------------- select /*+ FULL(test) INDEX(test,idx_object_type) */ distinct object_type from test where owner='SYS' Plan hash value: 2203132549 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 393 (100)| | | 1 | HASH UNIQUE | | 45 | 675 | 393 (1)| 00:00:01 | |* 2 | TABLE ACCESS FULL| TEST | 2896 | 43440 | 392 (1)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OWNER"='SYS') Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 2 (U - Unused (2)) --------------------------------------------------------------------------- 2 - SEL$1 / TEST@SEL$1 U - FULL(test) / hint conflicts with another in sibling query block U - INDEX(test,idx_object_type) / hint conflicts with another in sibling query block 28 rows selected.
最后一种情况比较难模拟。这里介绍一下,转换会使某些hint无效。例如,一条语句指定PUSH_PRED(some_view) MERGE(some_view)。当some_view合并到其包含的查询块时,优化器无法应用PUSH_PRED提示,因为该提示some_view不可用。
有了这个报告,可以帮助我们分析为什么我们的hint为什么不起作用,酷!
参考文档:https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/influencing-the-optimizer.html#GUID-98F9788B-9162-4A12-9257-CC855A4136B3
Post a Comment