오라클 19c xplan(dbms_xplan.display_cursor) 실행계획 확인 방법

2025. 1. 20. 19:28 내맘대로긍정

프린트 하기

OS 환경 : Oracle Linux 7.6 (64bit)

DB 환경 : Oracle Database 19.3.0.0

방법 : 오라클 19c xplan(dbms_xplan.display_cursor) 실행계획 확인 방법

오라클 환경에서 xplan 을 이용해 실제 실행계획을 확인하는 방법을 설명함
이전에 아래 게시글에서 실행계획 확인하는 방법을 작성하였지만 너무 길어 보기 힘들어 간소화된 버전을 작성함
참고 : 오라클 실행계획 확인 및 트레이스 방법 ( https://positivemh.tistory.com/364 )

테스트
0. dbms_xplan.display_cursor 란?
1. 예제 테이블 생성
2. 방법1 statistics_level all 설정
3. 방법2 쿼리에 gather_plan_statistics 힌트 사용
4. xplan 결과가 쿼리가 나오지 않을때1
5. xplan 결과가 쿼리가 나오지 않을때2

0. dbms_xplan.display_cursor 란?
단일 sql문에 대해 실제 수행된 실행계획을 보여주는 오라클 기능임
dbms_xplan.display의 결과는 예측치이지만 dbms_xplan.display_cursor는 실측치임

옵션 등 상세 설명은 아래 게시글을 참고하길 바람

참고 : 오라클 실행계획 확인 및 트레이스 방법 ( https://positivemh.tistory.com/364 )

1. 예제 테이블 생성
아래 게시글의 스크립트로 emp, dept 테이블 생성
오라클 EMP, DEPT table 생성 스크립트 ( https://positivemh.tistory.com/99 )

2. 방법1 statistics_level 파라미터를 all 설정
gather_plan_statistics 힌트를 넣지 않고 statistics_level 파라미터를 all로 설정한뒤 샘플 쿼리 수행를 수행하면 dbms_xplan.display_cursor 확인이 가능함

세션 레벨에서 statistics_level을 all로 설정

1
2
3

SQL> alter session set statistics_level = all;
 
Session altered.

설정됨

샘플 쿼리 수행

1
2
3
4
5
6
7
8
9
10
11

SQL> 
set lines 200 pages 1000
select e.empno, e.ename, e.job, e.sal, d.deptno, d.dname, d.loc
from emp e, dept d
where e.deptno = d.deptno
and d.dname = 'SALES'
and e.sal > 2000;
 
     EMPNO ENAME      JOB              SAL     DEPTNO DNAME          LOC
---------- ---------- --------- ---------- ---------- -------------- -------------
      7698 BLAKE      MANAGER         2850         30 SALES          CHICAGO

dbms_xplan.display_cursor 결과 확인

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
68
69
70
71
72
73
74

SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last');
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  f56wbukqrgsxg, child number 0
-------------------------------------
select e.empno, e.ename, e.job, e.sal, d.deptno, d.dname, d.loc from
emp e, dept d where e.deptno = d.deptno and d.dname = 'SALES' and e.sal
> 2000
 
Plan hash value: 844388907
 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |       |     6 (100)|          |      1 |00:00:00.01 |       8 |      8 |       |       |          |
|   1 |  MERGE JOIN                  |         |      1 |      2 |    90 |     6  (17)| 00:00:01 |      1 |00:00:00.01 |       8 |      8 |       |       |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |    20 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      2 |       |       |          |
|   3 |    INDEX FULL SCAN           | PK_DEPT |      1 |      4 |       |     1   (0)| 00:00:01 |      4 |00:00:00.01 |       1 |      1 |       |       |          |
|*  4 |   SORT JOIN                  |         |      1 |      6 |   150 |     4  (25)| 00:00:01 |      1 |00:00:00.01 |       6 |      6 |  2048 |  2048 | 2048  (0)|
|*  5 |    TABLE ACCESS FULL         | EMP     |      1 |      6 |   150 |     3   (0)| 00:00:01 |      6 |00:00:00.01 |       6 |      6 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   2 - SEL$1 / D@SEL$1
   3 - SEL$1 / D@SEL$1
   5 - SEL$1 / E@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")
      INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
      FULL(@"SEL$1" "E"@"SEL$1")
      LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
      USE_MERGE(@"SEL$1" "E"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("D"."DNAME"='SALES')
   4 - access("E"."DEPTNO"="D"."DEPTNO")
       filter("E"."DEPTNO"="D"."DEPTNO")
   5 - filter("E"."SAL">2000)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "D"."DEPTNO"[NUMBER,22], "D"."LOC"[VARCHAR2,13], "D"."DNAME"[VARCHAR2,14], "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
       "E"."SAL"[NUMBER,22]
   2 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
   3 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
   4 - (#keys=1) "E"."DEPTNO"[NUMBER,22], "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."SAL"[NUMBER,22]
   5 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."SAL"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
 
Query Block Registry:
---------------------
 
  <q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[D]]></t><s><![CDATA[SEL$1]]></s></h><h><t><![CDATA[E]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
 
 
 
68 rows selected.

xplan 결과가 정상적으로 표시됨

3. 방법2 쿼리에 gather_plan_statistics 힌트 사용
statistics_level 파라미터를 all 설정하지 않은 상태에서 sql에 gather_plan_statistics 힌트를 사용해도 dbms_xplan.display_cursor 확인이 가능함

gather_plan_statistics 힌트를 넣어 샘플 쿼리 수행

1
2
3
4
5
6
7
8
9
10
11
12

SQL> 
set lines 200 pages 1000
select /*+ gather_plan_statistics */  
e.empno, e.ename, e.job, e.sal, d.deptno, d.dname, d.loc
from emp e, dept d
where e.deptno = d.deptno
and d.dname = 'SALES'
and e.sal > 2000;
 
     EMPNO ENAME      JOB              SAL     DEPTNO DNAME          LOC
---------- ---------- --------- ---------- ---------- -------------- -------------
      7698 BLAKE      MANAGER         2850         30 SALES          CHICAGO

dbms_xplan.display_cursor 결과 확인

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
68
69
70
71
72
73
74
75

SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last');
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  brbfu1xk1ugvy, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ e.empno, e.ename, e.job, e.sal,
d.deptno, d.dname, d.loc from emp e, dept d where e.deptno = d.deptno
and d.dname = 'SALES' and e.sal > 2000
 
Plan hash value: 844388907
 
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |       |     6 (100)|          |      1 |00:00:00.01 |       8 |       |       |          |
|   1 |  MERGE JOIN                  |         |      1 |      2 |    90 |     6  (17)| 00:00:01 |      1 |00:00:00.01 |       8 |       |       |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |    20 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |       |       |          |
|   3 |    INDEX FULL SCAN           | PK_DEPT |      1 |      4 |       |     1   (0)| 00:00:01 |      4 |00:00:00.01 |       1 |       |       |          |
|*  4 |   SORT JOIN                  |         |      1 |      6 |   150 |     4  (25)| 00:00:01 |      1 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)|
|*  5 |    TABLE ACCESS FULL         | EMP     |      1 |      6 |   150 |     3   (0)| 00:00:01 |      6 |00:00:00.01 |       6 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   2 - SEL$1 / D@SEL$1
   3 - SEL$1 / D@SEL$1
   5 - SEL$1 / E@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")
      INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
      FULL(@"SEL$1" "E"@"SEL$1")
      LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
      USE_MERGE(@"SEL$1" "E"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("D"."DNAME"='SALES')
   4 - access("E"."DEPTNO"="D"."DEPTNO")
       filter("E"."DEPTNO"="D"."DEPTNO")
   5 - filter("E"."SAL">2000)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "D"."DEPTNO"[NUMBER,22], "D"."LOC"[VARCHAR2,13], "D"."DNAME"[VARCHAR2,14], "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
       "E"."JOB"[VARCHAR2,9], "E"."SAL"[NUMBER,22]
   2 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
   3 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
   4 - (#keys=1) "E"."DEPTNO"[NUMBER,22], "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."SAL"[NUMBER,22]
   5 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."SAL"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
 
Query Block Registry:
---------------------
 
  <q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[D]]></t><s><![CDATA[SEL$1]]></s></h><h><t><![CDATA[E]]></t><s><![CDATA[SEL$1]]></s></h></f><
        /q>
 
 
 
69 rows selected.

xplan 결과가 정상적으로 표시됨

4. xplan 결과가 쿼리가 나오지 않을때1
sqlplus 에서 dbms_xplan.display_cursor 실행시 아래와 같이 나오는 경우 조치방안

1
2
3
4
5
6
7
8
9
10
11
12
13
14

SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last');
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9babjv8yq8ru3, child number 0
 
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
 
NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)
 
 
8 rows selected.

본인 쿼리와 xplan 쿼리 사이에 내부적으로 다른 쿼리가(DBMS_OUTPUT 프로시저) 실행되어 xplan결과가 안나옴

sqlplus 에서 set serveroutput off 수행

1

SQL> set serveroutput off

이후 샘플 쿼리 재수행

1
2
3
4
5
6
7
8

SQL> 
set lines 200 pages 1000
select e.empno, e.ename, e.job, e.sal, d.deptno, d.dname, d.loc
from emp e, dept d
where e.deptno = d.deptno
and d.dname = 'SALES'
and e.sal > 2000;
(결과 생략)

dbms_xplan.display_cursor 결과 확인

1
2

SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last');
(결과 생략)

xplan 결과가 정상적으로 표시됨

5. xplan 결과가 쿼리가 나오지 않을때2
sqlid를 찾을수 없다고 표시될 때
오렌지나 dbeaver 등의 클라이언트 툴로 쿼리 수행 후 xplan 실행시 본인 쿼리와 xplan 쿼리 사이에 내부적으로 다른 쿼리가 실행되어 xplan결과가 잘 안나올수 있음
이때는 쿼리에 별칭을 넣어 수행한 뒤 sql_id를 찾아 해당 sql_id를 xplan에 넣어 수행하면 정상적으로 표시됨

샘플 쿼리 수행 시 alias를 하나 넣어서 수행
아래 쿼리에는 test20250201 라는 쿼리 별칭을 넣어서 수행함

1
2
3
4
5
6
7
8
9

SQL> 
set lines 200 pages 1000
select /*+ gather_plan_statistics test20250201 */ 
e.empno, e.ename, e.job, e.sal, d.deptno, d.dname, d.loc
from emp e, dept d
where e.deptno = d.deptno
and d.dname = 'SALES'
and e.sal > 2000;
(결과 생략)

v$sql 에서 sql_id 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14

SQL> 
select sql_id, plan_hash_value, last_load_time, sql_text
from v$sql
where sql_text like '%select /*+ gather_plan_statistics test20250201 */%';
 
SQL_ID        PLAN_HASH_VALUE LAST_LOAD_TIME
------------- --------------- --------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
85xzj72079jdf       844388907 2025-01-20/09:58:06
select /*+ gather_plan_statistics test20250201 */ e.empno, e.ename, e.job, e.sal, d.deptno, d.dname, d.loc from emp e, dept d where e.deptno = d.deptno and d.dname = 'SALES' and e.sal > 2000
 
1mx9gdm4g9yv3       903671040 2025-01-20/09:58:15
select sql_id, plan_hash_value, last_load_time, sql_text from v$sql where sql_text like '%select /*+ gather_plan_statistics test20250201 */%'

85xzj72079jdf 라는 sql_id로 dbms_xplan.display_cursor 결과 확인

1
2

SQL> select * from dbms_xplan.display_cursor('85xzj72079jdf', null, 'advanced allstats last');
(결과 생략)

xplan 결과가 정상적으로 표시됨

참조 :

https://positivemh.tistory.com/364

저작자표시

'ORACLE > Performance Tuning ' 카테고리의 다른 글

오라클 데이터베이스 무료 분석 툴 SQLdb360 사용법 (0)	2024.12.17
오라클 19c 테이블 ctas 생성 및 append 삽입시, 인덱스 생성시 자동 통계정보 수집 (0)	2024.10.24
오라클 23ai append_values 힌트를 사용한 벌크 인서트 성능 비교 (0)	2024.07.09
오라클 19c hash 조인 과다 실행시 부하 테스트 (0)	2024.05.28
오라클 19c ordered_predicates 힌트 사용 확인(xplan, 10053 비교) (0)	2024.05.24

내맘대로긍정

박영민 ympark.pro@gmail.com

Category

Menu

Comment

Link