Hint Usage Reporting

19c 버전에는 Hint Usage Reporting 이란 새로운 기능이 추가되었다.

SQL 플랜 정보에서 사용한 힌트에 대해 어떻게 반영이 되었는지에 대한 정보를 보여준다.

힌트 사용 후 반영되는 단계를 확인하는데 유용할 듯하다.

추후 19c 릴리즈 후 더 사용해 보고 사용성에 대해 확인해 봐야 할 듯하다.

아래는 오라클 LiveSQL에서 사용해 본 예이다.

drop table test_hint_usage_reporting;

create table test_hint_usage_reporting
as
select * from all_objects where rownum <= 1000;

alter table test_hint_usage_reporting add constraint t1_pk primary key(object_id);

explain plan for
select * 
from test_hint_usage_reporting 
where object_id = :b1;

select * from table(dbms_xplan.display(null,null, 'hint_report'));

explain plan for
select /*+ full(a) */ * 
from test_hint_usage_reporting a where object_id = :b1;

select * from table(dbms_xplan.display(null,null, 'hint_report'));

--output------------------------------------------------------------
Table dropped.
Table created.
Table altered.
Statement processed.
Result Set 2
PLAN_TABLE_OUTPUT
Plan hash value: 3783310192
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                           |     1 |   114 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HINT_USAGE_REPORTING |     1 |   114 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | T1_PK                     |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("OBJECT_ID"=TO_NUMBER(:B1))

14 rows selected.
Statement processed.
Result Set 3
PLAN_TABLE_OUTPUT
Plan hash value: 2741854708
 
-------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                           |     1 |   114 |     9   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| TEST_HINT_USAGE_REPORTING |     1 |   114 |     9   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - storage("OBJECT_ID"=TO_NUMBER(:B1))
       filter("OBJECT_ID"=TO_NUMBER(:B1))
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
 
   1 -  SEL$1 / A@SEL$1
           -  full(a)

21 rows selected.