Skip to content

Oracle 19c新特性 -HINT_REPORT

版权声明：本文为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

Your email is never published nor shared. Required fields are marked *