오라클 19c dbms_xplan.display_cursor Hint Report 포맷

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.11.0.0

 

방법 : 오라클 19c dbms_xplan.display_cursor hint report 포맷

쿼리에 문제가 생겼을 때 실행계획을 보기위해 dbms_xplan.display_cursor 을 사용함

19c에서는 hint_report라는 포맷이 생겨서 어떤 힌트가 사용되었고 어떤 힌트가 사용이 되지않았는지(사용실패), 왜 사용실패되었는지를 알려줌

힌트 리포트는 세가지로 구분됨

U(Unused, 구문은 맞지만 미사용됨),

N(Unresolved, 구문은 맞지만 쿼리에 없는 별칭사용),

E(Syntax error, 구문에러)

아래는 테스트를 통해 어떤식으로 표시되는지 보여줌

 

 

샘플 테이블 생성

SQL> 
drop table monjip purge;
create table monjip (code varchar2(10), mon varchar2(10));
insert into monjip values ('A','202001');
commit;
 
drop index monjip_idx2 ;
create index monjip_idx2 on monjip(mon, code);

 

 

쿼리 실행 및 실행계획 확인1

select 절에 use_concat full(monjip) index(test) testtest(dual)를 사용(유효한 힌트는 full(monjip))

SQL> select /*+ gather_plan_statistics use_concat full(monjip) index(test) testtest(dual)  */ 
count(*) from monjip
where code like 'A%'
and mon between '202001' and '202012';
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID    3g9dq46qvczwa, child number 0
-------------------------------------
select /*+ gather_plan_statistics use_concat full(monjip) index(test)
testtest(dual)    */ count(*) from monjip where code like 'A%' and mon
between '202001' and '202012'
 
Plan hash value: 2046792910
 
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation       | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |       1 |          |       |   874 (100)|          |      1 |00:00:00.10 |     3091 |
|   1 |  SORT AGGREGATE    |        |       1 |        1 |    14 |        |          |      1 |00:00:00.10 |     3091 |
|*  2 |   TABLE ACCESS FULL| MONJIP |       1 |      200 |  2800 |   874    (3)| 00:00:01 |     12 |00:00:00.10 |     3091 |
-----------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   2 - SEL$1 / MONJIP@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "MONJIP"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(("MON">='202001' AND "MON"<='202012' AND "CODE" LIKE 'A%'))
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=0) COUNT(*)[22]
   2 - (rowset=60)
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 4 (U - Unused (1), N - Unresolved (1), E - Syntax error (1))
---------------------------------------------------------------------------------------
 
   1 -    SEL$1
     N -  index(test)
     U -  use_concat
     E -  testtest
 
   2 -    SEL$1 / MONJIP@SEL$1
       -  full(monjip)
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
 
Query Block Registry:
---------------------
 
  <q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[MONJIP]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
 
 
 
69 rows selected.

32번째 줄에 이전 버전에도 존재하던 Outline Data(Oracle이 사용한 Hidden Hint 목록) 이 나오고

57번째 줄에 Hint Report가 나옴

select 절에 use_concat full(monjip) index(test) testtest(dual)를 사용하였는데

use_concat 힌트는 힌트구문은 맞지만 쿼리에 적용될수 없는 힌트라 U로 표시되었고

index(test) 힌트는 힌트구문은 맞지만 존재하지 않는 인덱스를 넣어서 N로 표시되었고

testtest(dual) 힌트는 힌트구문이 틀려서 E로 표시됨

full(monjip) 힌트는 힌트구문이 맞고 쿼리에 적용 가능하기 때문에 E,U,N 표시가 생기지 않음

 

 

DBMS_XPLAN.DISPLAY_CURSOR 포맷에 'ADVANCED ALLSTATS LAST'을 넣지않고 HINT_REPORT만 넣으면 Hint Report와 Predicate Information 정보만 나옴

 

 

쿼리 실행 및 실행계획 확인2

select 절에 full(monjip) index_ss(monjip monjip_idx2) 를 같이 사용

SQL> select /*+ gather_plan_statistics full(monjip) index_ss(monjip monjip_idx2) */ count(*) from monjip
where code like 'A%'
and mon between '202001' and '202012';
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'HINT_REPORT'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID    46n7uq59ccjva, child number 2
-------------------------------------
select /*+ gather_plan_statistics full(monjip) index_ss(monjip
monjip_idx2) */ count(*) from monjip where code like 'A%' and mon
between '202001' and '202012'
 
Plan hash value: 577717968
 
---------------------------------------------------------------------------------
| Id  | Operation      | Name    | Rows    | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |    |    |     1 (100)|        |
|   1 |  SORT AGGREGATE   |        |     1 |    14 |         |        |
|*  2 |   INDEX RANGE SCAN| MONJIP_IDX2 |     1 |    14 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("MON">='202001' AND "CODE" LIKE 'A%' AND "MON"<='202012')
       filter("CODE" LIKE 'A%')
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------
 
   2 -    SEL$1 / MONJIP@SEL$1
     U -  full(monjip) / hint conflicts with another in sibling query block
     U -  index_ss(monjip monjip_idx2) / hint conflicts with another in sibling query block
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
 
 
34 rows selected.

select 절에 full(monjip) index_ss(monjip monjip_idx2)를 사용하였는데

full(monjip) 힌트는 힌트구문이 맞고 쿼리에 적용가능함

index_ss(monjip monjip_idx2) 힌트도 힌트구문이 맞고 쿼리에 적용 가능하지만

두가지 힌트를 동시에 사용했기때문에(충돌) U로 표시되었고(미사용)

Hint Report 에 'hint conflicts with another in sibling query block'(sibling 쿼리 블록에서 힌트가 다른 것과 충돌합니다) 라고 표시됨

두가지 힌트가 미사용 되었기 때문에 옵티마이저가 판단해서 실행계획이 index range scan으로 풀림

 

 

쿼리 실행 및 실행계획 확인3

select 절에 존재하지 않는 인덱스를 사용한 경우

SQL> select /*+ gather_plan_statistics index(monjip testidx1)  */ 
count(*) from monjip
where code like 'A%'
and mon between '202001' and '202012';
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'HINT_REPORT'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID    7b88369gc66nx, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(monjip testidx1)  */ count(*)
from monjip where code like 'A%' and mon between '202001' and '202012'
 
Plan hash value: 577717968
 
---------------------------------------------------------------------------------
| Id  | Operation      | Name    | Rows    | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |    |    |     1 (100)|        |
|   1 |  SORT AGGREGATE   |        |     1 |    14 |         |        |
|*  2 |   INDEX RANGE SCAN| MONJIP_IDX2 |     1 |    14 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("MON">='202001' AND "CODE" LIKE 'A%' AND "MON"<='202012')
       filter("CODE" LIKE 'A%')
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
 
   2 -    SEL$1 / MONJIP@SEL$1
     U -  index(monjip testidx1) / index specified in the hint doesn't exist
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
 
 
32 rows selected.

select 절에 존재하지 않는 인덱스를 넣은 힌트(monjip testidx1)를 사용한 경우 U로 표시되었고(미사용)

'index specified in the hint doesn't exist'(힌트에 지정된 인덱스가 존재하지 않습니다)로 표시됨

 

 

19c의 DBMS_XPLAN.DISPLAY_CURSOR Hint Report을 활용해서 어떤 쿼리블록에 어떤 힌트가 제대로 적용이 안되고 있는지 조금더 쉽게 파악할 수 있음

 

 

참조 : https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_XPLAN.html#GUID-0EE333AF-E9AC-40A4-87D5-F6CF59D6C47B

https://franckpachot.medium.com/oracle-19c-hint-usage-reporting-345563a461f0

2735444.1

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/influencing-the-optimizer.html#GUID-F9F20FDC-8AC9-429C-A4F9-3FF747077182