오라클 23ai 신기능 xplan 의 Query Block Registry 개선

2024. 7. 1. 21:24 내맘대로긍정

프린트 하기

OS 환경 : Oracle Linux 8.4 (64bit)

DB 환경 : Oracle Database 23.4.0.24.05 ai Free

방법 : 오라클 23ai 신기능 xplan 의 Query Block Registry 개선

오라클 23ai (정확히는 21c) xplan 의 Query Block Registry 개선에 대해 설명함

오라클에서 쿼리를 튜닝할때 나의 경우는 xplan 에서 advanced allstats last 옵션을 많이 사용함
이때 Query Block Registry 부분은 아래와 같이 보기 힘들게 되어있어서
늘 어떤 뜻인지 잘 모르고 그냥 지나쳤었음

1
2
3
4
5
6
7
8
9

Query Block Registry:
---------------------
 
  <q o="2" f="y"><n><![CDATA[INS$1]]></n><f><h><t><![CDATA[T1]]></t><s><![CDATA[INS$1]]></s></h></f></q>
  <q o="2" f="y"><n><![CDATA[SET$1]]></n><f><h><t><![CDATA[NULL_HALIAS]]></t><s><![CDATA[SET$1]]></s></h></f>
        </q>
  <q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[DUAL]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
  <q o="2" f="y"><n><![CDATA[SEL$2]]></n><f><h><t><![CDATA[DUAL]]></t><s><![CDATA[SEL$2]]></s></h></f></q>
  <q o="2" f="y"><n><![CDATA[SEL$3]]></n><f><h><t><![CDATA[DUAL]]></t><s><![CDATA[SEL$3]]></s></h></f></q>

하지만 21c부터 Query Block Registry 의 표현방식이 xml 에서 읽기쉬운 형식으로 변경됨
참고 : https://fatdba.com/2022/10/10/differences-i-have-noticed-in-query-block-registry-section-of-an-execution-plan-between-oracle-19c-and-21c/

본문에서는 19c 와 23ai 버전에서 각각 Query Block Registry 가 어떻게 표시되는지 확인해봄

테스트
샘플 테이블 생성(19c, 23ai 모두)

1
2
3

SQL> 
drop table t1 purge;
create table t1 (col1 number, col2 varchar2(10));

샘플 쿼리 수행(19c, 23ai 모두)

1
2
3
4
5
6
7
8

SQL>
insert /*+ gather_plan_statistics */
into t1
(select 1, 'test1' from dual)
union all
(select 2, 'test2' from dual)
union all
(select 3, 'test3' from dual);

xplan 확인(19c)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3t8ftj9r988y6, child number 0
-------------------------------------
insert /*+ gather_plan_statistics */ into t1 (select 1, 'test1' from
dual) union all (select 2, 'test2' from dual) union all (select 3,
'test3' from dual)
 
Plan hash value: 2623540522
 
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |      1 |        |     6 (100)|          |      0 |00:00:00.01 |      33 |
|   1 |  LOAD TABLE CONVENTIONAL | T1   |      1 |        |            |          |      0 |00:00:00.01 |      33 |
|   2 |   UNION-ALL              |      |      1 |        |            |          |      3 |00:00:00.01 |       0 |
|   3 |    FAST DUAL             |      |      1 |      1 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       0 |
|   4 |    FAST DUAL             |      |      1 |      1 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       0 |
|   5 |    FAST DUAL             |      |      1 |      1 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       0 |
-------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SET$1
   3 - SEL$1 / DUAL@SEL$1
   4 - SEL$2 / DUAL@SEL$2
   5 - SEL$3 / DUAL@SEL$3
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"INS$1")
      FULL(@"INS$1" "T1"@"INS$1")
      END_OUTLINE_DATA
  */
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   2 - STRDEF[2], STRDEF[5]
 
Query Block Registry:
---------------------
 
  <q o="2" f="y"><n><![CDATA[INS$1]]></n><f><h><t><![CDATA[T1]]></t><s><![CDATA[INS$1]]></s></h></f></q>
  <q o="2" f="y"><n><![CDATA[SET$1]]></n><f><h><t><![CDATA[NULL_HALIAS]]></t><s><![CDATA[SET$1]]></s></h></f>
        </q>
  <q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[DUAL]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
  <q o="2" f="y"><n><![CDATA[SEL$2]]></n><f><h><t><![CDATA[DUAL]]></t><s><![CDATA[SEL$2]]></s></h></f></q>
  <q o="2" f="y"><n><![CDATA[SEL$3]]></n><f><h><t><![CDATA[DUAL]]></t><s><![CDATA[SEL$3]]></s></h></f></q>
 
62 rows selected.

xplan 확인(23ai)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3t8ftj9r988y6, child number 0
-------------------------------------
insert /*+ gather_plan_statistics */ into t1 (select 1, 'test1' from
dual) union all (select 2, 'test2' from dual) union all (select 3,
'test3' from dual)
 
Plan hash value: 2623540522
 
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |      1 |        |     6 (100)|          |      0 |00:00:00.01 |      35 |
|   1 |  LOAD TABLE CONVENTIONAL | T1   |      1 |        |            |          |      0 |00:00:00.01 |      35 |
|   2 |   UNION-ALL              |      |      1 |      3 |     6   (0)| 00:00:01 |      3 |00:00:00.01 |       0 |
|   3 |    FAST DUAL             |      |      1 |      1 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       0 |
|   4 |    FAST DUAL             |      |      1 |      1 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       0 |
|   5 |    FAST DUAL             |      |      1 |      1 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       0 |
-------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SET$1
   3 - SEL$1 / "DUAL"@"SEL$1"
   4 - SEL$2 / "DUAL"@"SEL$2"
   5 - SEL$3 / "DUAL"@"SEL$3"
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('23.1.0')
      DB_VERSION('23.1.0')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"INS$1")
      LOAD_METHOD(@"INS$1" "T1"@"INS$1" CONVENTIONAL)
      LOAD_TYPE(@"INS$1" "T1"@"INS$1" SERIAL)
      FULL(@"INS$1" "T1"@"INS$1")
      END_OUTLINE_DATA
  */
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   2 - STRDEF[2], STRDEF[5]
 
Query Block Registry:
---------------------
 
  INS$1 (PARSER) [FINAL]
  SEL$1 (PARSER) [FINAL]
  SEL$2 (PARSER) [FINAL]
  SEL$3 (PARSER) [FINAL]
  SET$1 (PARSER) [FINAL]
 
63 rows selected.

Query Block Registry 부분만 차이 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

19c
Query Block Registry:
---------------------
 
  <q o="2" f="y"><n><![CDATA[INS$1]]></n><f><h><t><![CDATA[T1]]></t><s><![CDATA[INS$1]]></s></h></f></q>
  <q o="2" f="y"><n><![CDATA[SET$1]]></n><f><h><t><![CDATA[NULL_HALIAS]]></t><s><![CDATA[SET$1]]></s></h></f>
        </q>
  <q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[DUAL]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
  <q o="2" f="y"><n><![CDATA[SEL$2]]></n><f><h><t><![CDATA[DUAL]]></t><s><![CDATA[SEL$2]]></s></h></f></q>
  <q o="2" f="y"><n><![CDATA[SEL$3]]></n><f><h><t><![CDATA[DUAL]]></t><s><![CDATA[SEL$3]]></s></h></f></q>
 
23ai
---------------------
 
  INS$1 (PARSER) [FINAL]
  SEL$1 (PARSER) [FINAL]
  SEL$2 (PARSER) [FINAL]
  SEL$3 (PARSER) [FINAL]
  SET$1 (PARSER) [FINAL]

19c에서는 xml 형식으로 표시가 되고 23ai 버전에서는 읽기 편한 형식으로 표시됨
- Query Block Registry 해석
INS$1 (PARSER) : insert 1번 수행됨
SEL$1~3 (PARSER) : select 3번 수행됨
SET$1 (PARSER) : 집합 연산자 사용하여 집합(SET) 만들어짐

결론 :
19c 버전의 Query Block Registry 에서도 한눈에 읽기가 어려워서 그렇지 23ai처럼 INS,SEL,SET은 모두 들어가 있었음
하지만 23ai 버전부터는 더 읽기 쉽게 표현되서 튜닝시 도움이 될듯함

참조 :

https://fatdba.com/2022/10/10/differences-i-have-noticed-in-query-block-registry-section-of-an-execution-plan-between-oracle-19c-and-21c/
https://positivemh.tistory.com/364

저작자표시

'ORACLE > Admin' 카테고리의 다른 글

오라클 23ai 신기능 load_method, load_type 힌트 (0)	2024.07.05
오라클 23ai 신기능 Direct Load(Direct Path Insert) 에 대한 제약 해제 (0)	2024.07.02
오라클 23ai 신기능 SQL Transpiler (0)	2024.06.29
오라클 23ai 신기능 세미나 발표자료(202406) (0)	2024.06.29
오라클 23ai 신기능 sqlplus 추가 명령 (0)	2024.06.26

내맘대로긍정

박영민 OracleDBA@kakao.com

Category

Menu

Comment

Link