내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS환경 : Oracle Linux6.8(64bit)
DB 환경 : Oracle Database 11.2.0.4
방법 : 오라클 SQL 실행계획 확인 및 트레이스 방법
순서
A. Autotrace
B. DBMS_XPLAN.DISPLAY
C. DBMS_XPLAN.DISPLAY_CURSOR
D. SQL 트레이스
A. Autotrace
Autotrace 설명 : AutoTrace 결과에는 SQL을 튜닝하는데 유용한 정보들이 많이 포함하고 있어 가장 즐겨 사용되는 도구 중 하나임
SQL Plus에서 실행계획을 가장 쉽고 빠르게 확인해 볼 수 있는 방법임
Autotrace 사용법 :
1. PLUSTRACE 권한생성
|
1
|
SQL> @?/sqlplus/admin/plustrace
|
2. 해당 사용자에게 PLUSTRACE 권한부여
|
1
2
3
|
SQL> grant plustrace to user_name;
ex)
SQL> grant plustrace to scott;
|
3. 해당유저로 접속 후 Autotrace 기능켜고 sql 실행
|
1
2
3
|
SQL> conn scott/tiger
SQL> set autotrace on
SQL> select * from table;
|
Autotrace 옵션 :
1. set autotrace on
SQL 실제 수행 => SQL 실행결과, 실행계획 및 실행통계 출력
2. set autotrace on explain
SQL 실제 수행 => SQL 실행결과, 실행계획 출력
3. set autotrace on statistics
SQL 실제 수행 => SQL 실행결과, 실행통계 출력
4. set autotrace traceonly
SQL 실제 수행 => 실행계획 및 실행통계 출력
5. set autotrace traceonly explain
SQL 실제 수행 X => 실행계획 출력
6. set autotrace traceonly statistics
SQL 실제 수행 => 실행통계 출력
- 1, 2, 3는 수행결과를 출력해야 하므로 쿼리를 실제 수행함
- 4, 6는 실행통계를 보여줘야 하므로 쿼리를 실제 수행함
- 5는 실행계획만 출력하면 되므로 쿼리를 실제 수행하지 않음
Autotrace 출력 이해
Id : 실행계획에서의 구분자
Operation : 각 단계에서의 어떤 작업이 일어났는지 표시
Name : 테이블명이나 index 명을 표시함
Rows : 해당 쿼리 계획 단계에서 나올 것으로 예상되는 행의 수
Byte : 실행 계획의 각 단계가 반환할 것으로 예상 되는 데이터의 크기를 바이트로 나타낸 수. 이 수는 행의 수와 행의 예상 길이에 따라 결정됨
Cost : CBO가 쿼리 계획의 각 단계에 할당한 비용. CBO는 동일한 쿼리에 대해 다양한 실행 경로/계획을 생성하며 모든 쿼리에 대해 비용을 할당함
위의 실행 계획에선 전체 비용이 13인것을 알수 있음
Time: 각 단계별 수행 시간
Predicate 출력 이해
어떤 컬럼, 값으로 access 되었는지 filter되었는지 표시됨
ACCESS_PREDICATES : 인덱스의 읽는 범위를 줄여주는 조건
FILTER_PREDICATES : 인덱스에서 Filter만 하는 조건
Predicate 정보가 왜 그렇게 중요한가? 실행 계획이 복잡해지면 Where 절의 특정 조건이 실행 계획의 각 단계에서
정확하게 어떻게 상용되었는지가 매우 중요해짐.
Query Transformation 이란는 특별한 과정 때문에 Predicate의 변형이 발생할 때는 이 정보가 특히 중요함
Statistics 출력 이해
recursive calls : 사용자의 SQL문을 실행하기 위하여 수행된 SQL 문의 수
db block gets : 현재 모드(current mode)에서 버퍼 캐시로부터 읽어온 블록의 총 수
consistent gets : 버퍼 캐시의 블록에 대한 일관된 읽기의 요청 횟수. 일관된 읽기는 언두 정보, 즉 롤백 정보에 대한 읽기를 요구할 수도 있으며 이들 언두에 대한 읽기도 계산됨
physical reads : 물리적으로 데이터 파일을 읽어 버퍼 캐시에 넣은 횟수
redo size : 해당 문이 실행되는 동안 생성된 리두의 전체 크기를 바이트 단위로 나타낸 수
byte sent via SQL*Net to client : 서버로부터 클라이언트에 전송된 총 바이트 수
byte recevied via SQL*Net from client : 클라이언트로부터 받은 총 바이트 수
SQL*Net roundtrips to/from client : 클라이언트로(부터) 전송된 SQL*Net 메시지의 총 수. 다중 행 결과 집합으로부터 꺼내오기 위한 왕복을 포함함
sorts(memory) : 사용자의 세션 메모리(정렬 영역)에서 수행된 정렬 sort_area_size 데이터베이스 매개변수에 의해 제어됨
sorts(disk) : 사용자의 정렬 영역의 크기를 초과하여 디스크(임시 테이블 영역)를 사용하는 정렬
rows processed : 수정되거나 select 문으로부터 반환된 행
Autotrace 예제
쿼리 :
|
1
2
3
4
|
SQL>
SELECT /*+ INDEX(EMP PK_EMP) */ ENAME, EMPNO
FROM EMP
WHERE EMPNO = 7900;
|
Autotrace 1번 옵션으로 수행
|
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
|
SQL> set autotrace on
SQL> /
ENAME EMPNO
---------- ----------
JAMES 7900
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
596 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
|
Autotrace 2번 옵션으로 수행
|
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
|
SQL> set autotrace on explain
SQL> /
ENAME EMPNO
---------- ----------
JAMES 7900
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
|
Autotrace 3번 옵션으로 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
SQL> set autotrace on statistics
SQL> /
ENAME EMPNO
---------- ----------
JAMES 7900
1 row selected.
Elapsed: 00:00:00.01
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
596 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
|
Autotrace 4번 옵션으로 수행
|
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
|
SQL> set autotrace traceonly
SQL> /
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
596 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
|
Autotrace 5번 옵션으로 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL> set autotrace traceonly explain
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
|
Autotrace 6번 옵션으로 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL> set autotrace traceonly statistics
SQL> /
1 row selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
596 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
|
*참고
실행계획이 아래와 같을 경우 실행되는 순서
|
1
2
3
4
5
6
7
8
9
|
Id | Operation
------------------------------------
0 | SELECT STATEMENT
1 | SORT UNIQUE
2 | UNION-ALL
3 | TABLE ACCESS BY INDEX ROWID
4 | INDEX UNIQUE SCAN
5 | TABLE ACCESS BY INDEX ROWID
6 | INDEX UNIQUE SCAN
|
순서 : 4-3-6-5-2-1-0
B. DBMS_XPLAN.DISPLAY
DBMS_XPLAN.DISPLAY 설명 : 단일 SQL문에 대해 예측 실행계획을 보여주는 Function 임
실측 정보가 아닌 예측 정보를 제공함
DBMS_XPLAN.DISPLAY 사용법 :
|
1
2
3
4
|
FUNCTION DISPLAY(TABLE_NAME VARCHAR2 DEFAULT 'PLAN_TABLE',
STATEMENT_ID VARCHAR2 DEFAULT NULL,
FORMAT VARCHAR2 DEFAULT 'TYPICAL',
FILTER_PREDS VARCHAR2 DEFAULT NULL)
|
1. 분석하고자 하는 SQL을 'EXPLAIN PLAN FOR' 다음에 넣고 Enter
|
1
2
3
4
5
6
7
|
SQL> EXPLAIN PLAN FOR + SQL;
ex)
SQL>
EXPLAIN PLAN FOR
SELECT /*+ INDEX(EMP PK_EMP) */ ENAME, EMPNO
FROM EMP
WHERE EMPNO = 7900;
|
2. SQL이 EXPLAIN 되었으면 그 다음에 원하는 포멧으로 출력
아래와 같이 작성하면 기본적으로 'PLAN_TABLE'과 'TYPICAL' Format으로 출력하겠다는 의미임
|
1
|
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
|
DBMS_XPLAN.DISPLAY 옵션 :
| 파라미터 | 설명 | |
|---|---|---|
| TABLE_NAME | Execution Plan이 저장되는 테이블을 지정하며, 기본값은 'PLAN_TABLE' 임 | |
| STATEMENT_ID | Execution Plan시 SET STATEMENT_ID를 지정한 경우 이를 불러올 수 있음 값이 NULL일 경우 마지막에 실행된 문장을 불러옴 |
|
| FORMAT | BASIC | 가장 기본적인 정보만 보여줌 |
| TYPICAL | Format의 Default값인 Typical은 SQL 튜닝에 필요한 Normal한 정보를 보여줌 SQL 튜닝에 가장 유용하게 사용되는 Predicate Information이 제공됨 |
|
| ALL | Typical Format에 Query Block Name과 Column Projection Information이 추가로 제공됨 | |
| OUTLINE | Typical Format에 추가적으로 Hidden Hint인 Outline Global Hint를 제공함 | |
| ADVANCED | ALL Format에 OUTLINE Format를 합친 정보를 제공함 | |
| FILTER_PREDS | 저장된 PLAN에서 일부 Row 또는 Row Set을 제한하여 출력할 수 있음 | |
DBMS_XPLAN.DISPLAY 출력 이해
Id : 실행계획에서의 구분자
Operation : 각 단계에서의 어떤 작업이 일어났는지 표시
Name : 테이블명이나 index 명을 표시함
Rows : 해당 쿼리 계획 단계에서 나올 것으로 예상되는 행의 수
Byte : 실행 계획의 각 단계가 반환할 것으로 예상 되는 데이터의 크기를 바이트로 나타낸 수. 이 수는 행의 수와 행의 예상 길이에 따라 결정됨
Cost : CBO가 쿼리 계획의 각 단계에 할당한 비용. CBO는 동일한 쿼리에 대해 다양한 실행 경로/계획을 생성하며 모든 쿼리에 대해 비용을 할당함
위의 실행 계획에선 전체 비용이 13인것을 알수 있음
Time: 각 단계별 수행 시간
Query Block Name 출력 이해
SQL 문장을 Query Block이라는 단위로 나누는데 Transformation 및 Optimization의 기본 단위가 됨
Query Block 명은 Inline View 와 Subquery가 많이 사용되는 복작한 Query를 해석할 때 특히 유용함
Outline Data 출력 이해
실행 계획을 수립 하는데 필요한 내부적으로 적용된 Hint들의 목록을 의미함
Predicate 출력 이해
어떤 컬럼, 값으로 access 되었는지 filter되었는지 표시됨
ACCESS_PREDICATES : 인덱스의 읽는 범위를 줄여주는 조건
FILTER_PREDICATES : 인덱스에서 Filter만 하는 조건
Predicate 정보가 왜 그렇게 중요한가? 실행 계획이 복잡해지면 Where 절의 특정 조건이 실행 계획의 각 단계에서
정확하게 어떻게 상용되었는지가 매우 중요해짐.
Query Transformation 이란는 특별한 과정 때문에 Predicate의 변형이 발생할 때는 이 정보가 특히 중요함
Column Projection 출력 이해
실행 계획의 특정 단계에서 어떤 Column을 추출하는가를 의미함
Query Transformation 을 Troubleshooting 할 때 유용한 정보가 됨
DBMS_XPLAN.DISPLAY 예제
쿼리 :
|
1
2
3
4
5
|
SQL>
EXPLAIN PLAN FOR
SELECT /*+ INDEX(EMP PK_EMP) */ ENAME, EMPNO
FROM EMP
WHERE EMPNO = 7900;
|
DBMS_XPLAN.DISPLAY 'BASIC' FORMAT으로 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'BASIC'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2949544139
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP |
| 2 | INDEX UNIQUE SCAN | PK_EMP |
----------------------------------------------
9 rows selected.
|
DBMS_XPLAN.DISPLAY 'TYPICAL' FORMAT으로 수행(기본값)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'TYPICAL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
14 rows selected.
|
DBMS_XPLAN.DISPLAY 'ALL' FORMAT으로 수행
|
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
|
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
2 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]
26 rows selected.
|
DBMS_XPLAN.DISPLAY 'OUTLINE' FORMAT으로 수행
|
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
|
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'OUTLINE'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
28 rows selected.
|
DBMS_XPLAN.DISPLAY 'ADVANCED' FORMAT으로 수행
|
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
|
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
2 - SEL$1 / EMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]
40 rows selected.
|
C. DBMS_XPLAN.DISPLAY_CURSOR
DBMS_XPLAN.DISPLAY_CURSOR 설명 : 단일 SQL문에 대해 실제 수행된 실행계획을 보여주는 Function
DISPLAY가 예측치를 보여주는 거라면 DISPLAY_CURSOR는 실측치를 보여줌
DBMS_XPLAN.DISPLAY_CURSOR 사용법 :
|
1
2
3
|
FUNCTION DISPLAY_CURSOR(SQL_ID VARCHAR2 DEFAULT NULL,
CURSOR_CHILD_NO INTEGER DEFAULT 0,
FORMAT VARCHAR2 DEFAULT 'TYPICAL')
|
1. 세션 레벨의 파라미터 변경
|
1
|
SQL> ALTER SESSION SET STATISTICS_LEVEL = ALL;
|
2. 분석하고자 하는 SQL을 실행(Hello는 식별용 문구)
|
1
2
3
4
|
SQL>
SELECT /*+ INDEX(EMP PK_EMP) Hello */ ENAME, EMPNO
FROM EMP
WHERE EMPNO = 7900;
|
3. DBMS_XPLAN.DISPLAY_CURSOR로 조회
아래와 같이 작성하면 기본적으로 'PLAN_TABLE'과 'TYPICAL' Format으로 출력하겠다는 의미임
|
1
|
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
|
DBMS_XPLAN.DISPLAY_CURSOR 옵션 :
| 파라미터 | 설명 |
|---|---|
| TABLE_NAME | 실제 수행된 SQL의 SQL_ID 값을 입력하면 해당 SQL의 실행계획을 볼 수 있다. |
| SQL_ID 값은 V$SQL, V$SQLAREA에서 확인할 수 있다. | |
| SQL_ID 값을 명시하지 않으면 해당 세션의 마지막에 실행된 문장을 의미한다. | |
| STATEMENT_ID | 해당 SQL_ID의 CHILD NUMBER 값을 지정한다. |
| CURSOR_CHILD_NO 값을 명시하지 않으면 해당 SQL_ID의 첫번째 CURSOR_CHILD_NO 값을 가져온다. | |
| FORMAT | 저장된 PLAN을 어떤 Format으로 보여줄 지 결정하는 파라미터 |
FORMAT 옵션 :
| 구분 | FORMAT | 내용 |
|---|---|---|
| 예측 내용 |
BASIC | 가장 기본적인 정보만 보여줌 |
| TYPICAL | Format의 Default값인 Typical은 SQL 튜닝에 필요한 Normal한 정보를 보여줌 SQL 튜닝에 가장 유용하게 사용되는 Predicate Information이 제공됨
|
|
| ALL | Typical Format에 Query Block Name과 Column Projection Information이 추가로 제공됨 | |
| OUTLINE | Typical Format에 추가적으로 Hidden Hint인 Outline Global Hint를 제공함 | |
| ADVANCED | ALL Format에 OUTLINE Format를 합친 정보를 제공함 | |
| 실측 내용 |
ALLSTATS | 실제 액세스한 로우수와 수행시간 그리고 CR, PR, PW 정보를 보여줌 |
| 수행횟수에 따라 누적된 값을 보여줌 | ||
| ALLSTATS LAST | 실제 액세스한 로우수와 수행시간 그리고 CR, PR, PW 정보를 보여줌 | |
| 수행횟수에 따라 누적된 값을 보여줌 | ||
| ADVANCED ALLSTATS LAST | DBMS_XPLAN.DISPLAY_CURSOR에서 지원하는 모든 Format의 정보를 보여줌 |
PLAN_TABLE_OUTPUT 출력 이해
수행한 쿼리의 SQL_ID 와 child number, SQL구문이 표시됨
DBMS_XPLAN.DISPLAY_CURSOR 출력 이해
Id : 실행계획에서의 구분자
Operation : 각 단계에서의 어떤 작업이 일어났는지 표시
Name : 테이블명이나 index 명을 표시함
Rows : 해당 쿼리 계획 단계에서 나올 것으로 예상되는 행의 수
Byte : 실행 계획의 각 단계가 반환할 것으로 예상 되는 데이터의 크기를 바이트로 나타낸 수. 이 수는 행의 수와 행의 예상 길이에 따라 결정됨
Cost : CBO가 쿼리 계획의 각 단계에 할당한 비용. CBO는 동일한 쿼리에 대해 다양한 실행 경로/계획을 생성함으로써 동가하며 모든 쿼리에 대해 비용을 할당함
위의 실행 계획에선 전체 비용이 13인것을 알수 있음
Time: 각 단계별 수행 시간
DBMS_XPLAN.DISPLAY_CURSOR 출력 이해('ADVANCED ALLSTATS LAST' FORMAT 조회시)
Id : 실행계획에서의 구분자
Operation : 각 단계에서의 어떤 작업이 일어났는지 표시
Name : 테이블명이나 index 명을 표시함
Starts : 말 그대로 해당 오퍼레이션이 "시작"된 횟수를 의미함
이 개념은 Nested Loops Join을 생각하시면 쉽게 이해할 수 있음
Nested Loops Join은 선행 테이블에서 읽는 로우수만큼 후행 테이블을 탐색하는 구조임
만일 선행 테이블에서 100건이 나온다면 후행 테이블을 100번 액세스하게됨
이럴 경우에 후행 테이블에서 대한 읽기 작업의 Starts 값이 "100"이 되는것
E-Rows : 예측 Row 수(Estimated Row Counts )
(오라클의 옵티마이저는 통계 정보와 SQL 문의 WHERE 조건 값을 참조해서 특정 조건을 만족하는 로우수 계산 )
E-Bytes : 예측 바이트 수(Estimated Bytes) 계획의 각 단계가 반환할 것으로 예상 되는 데이터의 크기를 바이트로 나타낸 수.
Cost : CBO가 쿼리 계획의 각 단계에 할당한 비용. CBO는 동일한 쿼리에 대해 다양한 실행 경로/계획을 생성하며 모든 쿼리에 대해 비용을 할당함
E-Time : 예측 수행 시간(Estimated Time)
A-Rows : 실제 Row 수(Actual Row Counts)
A-Time : 실제 수행 시간(Actual Time)
Buffers : Logical Reads
Logical Reads는 Buffer Cache를 경유해 읽은 블록 수(이것을 Conventional Path Read라고함)와
Buffer Cache를 경유하지 않고 Process가 직접 읽은 블록 수(이것을 Direct Path Read라고 부름)를 합친 값임
즉, 간단한게 말하면 특정 작업을 수행하는 과정에서(가령 쿼리를 실행하기 위해) 읽는 데이터베이스 블록 수라고 보면됨
Query Block Name 출력 이해
SQL 문장을 Query Block이라는 단위로 나누는데 Transformation 및 Optimization의 기본 단위가 됨
Query Block 명은 Inline View 와 Subquery가 많이 사용되는 복작한 Query를 해석할 때 특히 유용함
Outline Data 출력 이해
실행 계획을 수립 하는데 필요한 내부적으로 적용된 Hint들의 목록을 의미함
Predicate 출력 이해
어떤 컬럼, 값으로 access 되었는지 filter되었는지 표시됨
ACCESS_PREDICATES : 인덱스의 읽는 범위를 줄여주는 조건
FILTER_PREDICATES : 인덱스에서 Filter만 하는 조건
Predicate 정보가 왜 그렇게 중요한가? 실행 계획이 복잡해지면 Where 절의 특정 조건이 실행 계획의 각 단계에서
정확하게 어떻게 상용되었는지가 매우 중요해짐.
Query Transformation 이란는 특별한 과정 때문에 Predicate의 변형이 발생할 때는 이 정보가 특히 중요함
Column Projection 출력 이해
실행 계획의 특정 단계에서 어떤 Column을 추출하는가를 의미함
Query Transformation 을 Troubleshooting 할 때 유용한 정보가 됨
DBMS_XPLAN.DISPLAY_CURSOR 예제
쿼리 :
|
1
2
3
4
|
SQL>
SELECT /*+ INDEX(EMP PK_EMP) Hello */ ENAME, EMPNO
FROM EMP
WHERE EMPNO = 7900;
|
DBMS_XPLAN.DISPLAY_CURSOR로 'BASIC' FORMAT으로 수행
SQL 수행 후 XPLAN 수행
|
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
|
SQL>
SELECT /*+ INDEX(EMP PK_EMP) Hello */ ENAME, EMPNO
FROM EMP
WHERE EMPNO = 7900;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'BASIC'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT /*+ INDEX(EMP PK_EMP) Hello */ ENAME, EMPNO FROM EMP WHERE EMPNO
= 7900
Plan hash value: 2949544139
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP |
| 2 | INDEX UNIQUE SCAN | PK_EMP |
----------------------------------------------
15 rows selected.
|
DBMS_XPLAN.DISPLAY_CURSOR로 'TYPICAL' FORMAT으로 수행
SQL 수행 후 XPLAN 수행
|
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
|
SQL>
SELECT /*+ INDEX(EMP PK_EMP) Hello */ ENAME, EMPNO
FROM EMP
WHERE EMPNO = 7900;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'TYPICAL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 66pv12sa182dt, child number 0
-------------------------------------
SELECT /*+ INDEX(EMP PK_EMP) Hello */ ENAME, EMPNO FROM EMP WHERE EMPNO
= 7900
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
20 rows selected.
|
DBMS_XPLAN.DISPLAY_CURSOR로 'ALL' FORMAT으로 수행
SQL 수행 후 XPLAN 수행
|
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
|
SQL>
SELECT /*+ INDEX(EMP PK_EMP) Hello */ ENAME, EMPNO
FROM EMP
WHERE EMPNO = 7900;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 66pv12sa182dt, child number 0
-------------------------------------
SELECT /*+ INDEX(EMP PK_EMP) Hello */ ENAME, EMPNO FROM EMP WHERE EMPNO
= 7900
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
2 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]
32 rows selected.
|
DBMS_XPLAN.DISPLAY_CURSOR로 'OUTLINE' FORMAT으로 수행
SQL 수행 후 XPLAN 수행
|
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
|
SQL>
SELECT /*+ INDEX(EMP PK_EMP) Hello */ ENAME, EMPNO
FROM EMP
WHERE EMPNO = 7900;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'OUTLINE'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 66pv12sa182dt, child number 0
-------------------------------------
SELECT /*+ INDEX(EMP PK_EMP) Hello */ ENAME, EMPNO FROM EMP WHERE EMPNO
= 7900
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
34 rows selected.
|
DBMS_XPLAN.DISPLAY_CURSOR로 'ADVANCED' FORMAT으로 수행
SQL 수행 후 XPLAN 수행
|
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
|
SQL>
SELECT /*+ INDEX(EMP PK_EMP) Hello */ ENAME, EMPNO
FROM EMP
WHERE EMPNO = 7900;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 66pv12sa182dt, child number 0
-------------------------------------
SELECT /*+ INDEX(EMP PK_EMP) Hello */ ENAME, EMPNO FROM EMP WHERE EMPNO
= 7900
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
2 - SEL$1 / EMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]
46 rows selected.
|
DBMS_XPLAN.DISPLAY_CURSOR로 'ALLSTATS' FORMAT으로 수행
SQL 수행 후 XPLAN 수행
|
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
|
SQL>
SELECT /*+ INDEX(EMP PK_EMP) Hello */ ENAME, EMPNO
FROM EMP
WHERE EMPNO = 7900;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 66pv12sa182dt, child number 0
-------------------------------------
SELECT /*+ INDEX(EMP PK_EMP) Hello */ ENAME, EMPNO FROM EMP WHERE EMPNO
= 7900
Plan hash value: 2949544139
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21 | | 21 |00:00:00.07 | 42 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 21 | 1 | 21 |00:00:00.07 | 42 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 21 | 1 | 21 |00:00:00.04 | 21 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
20 rows selected.
|
DBMS_XPLAN.DISPLAY_CURSOR로 'ALLSTATS LAST' FORMAT으로 수행
SQL 수행 후 XPLAN 수행
|
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
|
SQL>
SELECT /*+ INDEX(EMP PK_EMP) Hello */ ENAME, EMPNO
FROM EMP
WHERE EMPNO = 7900;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 66pv12sa182dt, child number 0
-------------------------------------
SELECT /*+ INDEX(EMP PK_EMP) Hello */ ENAME, EMPNO FROM EMP WHERE EMPNO
= 7900
Plan hash value: 2949544139
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | 1 | 1 |00:00:00.01 | 1 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
20 rows selected.
|
DBMS_XPLAN.DISPLAY_CURSOR로 'ADVANCED ALLSTATS LAST' FORMAT으로 수행
SQL 수행 후 XPLAN 수행
|
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
|
SQL>
SELECT /*+ INDEX(EMP PK_EMP) Hello */ ENAME, EMPNO
FROM EMP
WHERE EMPNO = 7900;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 66pv12sa182dt, child number 0
-------------------------------------
SELECT /*+ INDEX(EMP PK_EMP) Hello */ ENAME, EMPNO FROM EMP WHERE EMPNO
= 7900
Plan hash value: 2949544139
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1 (100)| | 1 |00:00:00.01 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 20 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 |
--------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
2 - SEL$1 / EMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]
46 rows selected.
|
DBMS_XPLAN.DISPLAY_CURSOR 'IOSTATS LAST' FORMAT으로 수행
Iostats Format : I/O Statistics 즉, Read/Write 정보를 보여주겠다는 것을 의미
SQL 수행 후 XPLAN 수행
|
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
|
SQL>
SELECT /*+ INDEX(EMP PK_EMP) Hello */ ENAME, EMPNO
FROM EMP
WHERE EMPNO = 7900;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'IOSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 66pv12sa182dt, child number 0
-------------------------------------
SELECT /*+ INDEX(EMP PK_EMP) Hello */ ENAME, EMPNO FROM EMP WHERE EMPNO
= 7900
Plan hash value: 2949544139
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | 1 | 1 |00:00:00.01 | 1 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
20 rows selected.
|
DBMS_XPLAN.DISPLAY 'MEMSTATS LAST' FORMAT으로 수행
PGA 메모리 관리가 활성화 된 경우(즉, pga_aggregate_target매개 변수가 0이 아닌 값으로 설정된 경우)이 형식을 사용하면 메모리 관리 통계를 표시함
(예: 운영자의 실행 모드, 사용된 메모리 양, Disk에 유출된 바이트 수 등) 이러한 통계는 해시 조인, 정렬 또는 비트 맵 연산자와 같은 메모리 집중식 작업에만 적용됨
pga_aggregate_target 파라미터 사용 확인(0이 아니어야함)
|
1
2
3
4
5
|
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 276M
|
SQL 수행 후 XPLAN 수행
|
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
|
SQL>
SELECT /*+ INDEX(EMP PK_EMP) Hello */ ENAME, EMPNO
FROM EMP
WHERE EMPNO = 7900
ORDER BY EMPNO, ENAME;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'MEMSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID 2zzyscw1z4m0j, child number 0
-------------------------------------
SELECT /*+ INDEX(EMP PK_EMP) Hello */ ENAME, EMPNO FROM EMP WHERE EMPNO
= 7900 ORDER BY EMPNO, ENAME
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 1 |00:00:00.01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | 1 | 1 |00:00:00.01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
20 rows selected.
|
아래처럼 나와야 하는데 내 테스트에선 Mem 부분이 나오지 않았음
D. SQL 트레이스
SQL 트레이스 설명 : SQL Trace는 실행되는 SQL문의 실행통계를 세션별로 모아서 Trace 파일을 만듬
SQL Trace는 세션과 인스턴스 레벨에서 SQL문장들을 분석할 수 있음
SQL Trace에 의해 생성된 파일의 확장자는 .trc 임
.trc 파일은 직접 읽기 불편하고, tkprof 유틸리티를 이용하면 쉽게 분석 할 수 있음
인스턴스 레벨로 Trace를 수행시키면 전체적인 수행능력이 20~30%정도 감소하므로, 될 수 있으면 세션 레벨로 trace 파일을 생성해야함
Call 통계 출력 이해
Call : 커서 상태에 따라 Parse, Execute, Fetch 세 개의 Call로 나누어 각각에 대한 통계정보를 보여줌
- Parse : 커서를 파싱하고 실행계획을 생성하는 데 대한 통계
- Execute : 커서의 실행 단계에 대한 통계
- Fetch : 레코드를 실제로 Fetch하는 데 대한 통계
Count : Parse, Execute, Fetch 각 단계가 수행된 횟수
Cpu : 현재 커서가 각 단계에서 사용한 cpu time
Elapsed : 현재 커서가 각 단계를 수행하는 데 소요된 시간, Call 단위로 측정이 이루어 짐
Elapsed Time = CPU Time + Wait Time
= Response시점 - Call 시점
Disk : 디스크로부터 읽은 블록 수
Query : Consistent 모드에서 읽은 버퍼 블록 수
Current : Current모드에서 읽은 버퍼 블록수
Rows : 각 단계에서 읽거나 갱신한 처리건수
SQL 트레이스 요약정보 출력 이해
Misses in library cache during parse : Parse 구간에서 해당 SQL을 Library Cache에서 읽지 못하고 잃어버린 횟수
값이 1이면 Hard Parse, 0이면 Soft Parse를 의미함
Optimizer mode : 옵티마이저 모드
Parsing user id: 파싱 유저의 id
Number of plan statistics captured: 캡처된 실행계획 통계 수
Row Source Operation 통계 출력 이해
Rows : 각 수행단계에 출력된 row수
Cr : consistent 모드 블록 읽기
Pr(r) : 디스크 블록 읽기
Pw(w) : 디스크 블록 쓰기
Time : 소요시간(us= microsecond)
Cost : Cost Based Optimizer(CBO)에서 예측한 비용
Size : 리턴한 데이터 size(bytes)
Card : Cardinality, Cost Based Optimizer(CBO)에서 예측한 row수
SQL 트레이스 예제
trace 파일이 떨어지는 경로 확인
|
1
2
3
4
5
|
SQL> show parameter background_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /oracle/app/oracle/diag/rdbms/orcldb/ORCLDB/trace
|
trace file이 많기 때문에 TEST라는 식별자 부여
|
1
|
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER='TEST';
|
세션 레벨로 SQL TRACE 작동
|
1
|
SQL> ALTER SESSION SET SQL_TRACE = TRUE;
|
event 를 level 12 로 설정
|
1
|
SQL> ALTER SESSION SET events '10046 trace name context forever, level 12';
|
10046 = Query가 수행되는 사건. 즉 Query를 수행하는 "사건"을 의미함
trace name = Trace를 수행하라. 사건이 발생했을 때의 행위(Action)을 의미함
context forever, level 12 = 사건이 발생할 때마다 행위를 수행하되, 행위의 레벨을 12로 하라는뜻
일반적으로 SQL_TRACE와 동일하나 레벨에 따라 추가적인 정보 확인이 가능함
LEVEL 0 : 트레이스 안 함. SQL_TRACE=false 로 설정한 것과 동일한 효과
LEVEL 1 : 일반적인 SQL 트레이스(SQL_TRACE=true) 정보 제공
LEVEL 4: SQL TRACE 정보 + BIND 정보 제공
LEVEL 8: SQL TRACE 정보 + WAIT EVENT 정보 제공
LEVEL 12: SQL TRACE 정보 + BIND 정보 + WAIT EVENT 정보 제공
쿼리 입력 후 SQL 트레이스 중지후 sqlplus 나가기
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
SELECT /*+ INDEX(EMP PK_EMP) Hello */ ENAME, EMPNO
FROM EMP
WHERE EMPNO = 7900
ORDER BY EMPNO, ENAME;
ENAME EMPNO
---------- ----------
JAMES 7900
# trace 종료
# ALTER SESSION SET SQL_TRACE = TRUE; 명령으로 trace 실행한 경우
SQL> ALTER SESSION SET SQL_TRACE = FALSE;
#ALTER SESSION SET events '10046 trace name context forever, level 12'; 명령으로 trace 실행한 경우
SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
SQL> exit
|
트레이스 파일 떨어지는 경로로 이동후 트레이스 파일 확인
|
1
2
3
4
5
|
$ cd /oracle/app/oracle/diag/rdbms/orcldb/ORCLDB/trace
$ ls -ltr *TEST*
-rw-r----- 1 oracle dba 9858 Jan 9 18:00 ORCLDB_ora_11883_TEST.trc
-rw-r----- 1 oracle dba 306 Jan 9 18:00 ORCLDB_ora_11883_TEST.trm
|
트레이스 파일을 그냥 열어보면 읽기가 어려움
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
$ vi ORCLDB_ora_11883_TEST.trc
.
.
*** 2019-01-09 17:59:55.270
CLOSE #139993117140672:c=0,e=25,dep=0,type=0,tim=1547024395270578
=====================
PARSING IN CURSOR #139993117140672 len=69 dep=0 uid=36 oct=42 lid=36 tim=1547024395272600 hv=784711452 ad='0' sqlid='94gh3rcrcbgsw'
ALTER SESSION SET events '10046 trace name context forever, level 12'
END OF STMT
PARSE #139993117140672:c=161,e=1926,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=1547024395272599
EXEC #139993117140672:c=1195,e=9653,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=1547024395282340
WAIT #139993117140672: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1547024395282390
.
.
|
그래서 tkprof 툴로 변환해서 봐야함 sys=no는 시스템 메세지는 출력하지 않겠다는 뜻
|
1
2
3
4
5
|
$ tkprof ORCLDB_ora_11883_TEST.trc TEST.trc sys=no
TKPROF: Release 11.2.0.4.0 - Development on Wed Jan 9 18:08:00 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
|
vi 로 TEST.trc 파일을 열어서 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
$ vi TEST.trc
SELECT /*+ INDEX(EMP PK_EMP) Hello */ ENAME, EMPNO
FROM EMP
WHERE EMPNO = 7900
ORDER BY EMPNO, ENAME
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.06 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.06 0 2 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 36
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=1277 us cost=2 size=20 card=1)
1 1 1 INDEX UNIQUE SCAN PK_EMP (cr=1 pr=0 pw=0 time=601 us cost=1 size=0 card=1)(object id 15166)
|
참조 :
https://docs.oracle.com/database/121/SQPUG/ch_eight.htm#SQPUG543
https://blog.naver.com/sophie_yeom/220891529668
http://wiki.gurubee.net/display/STUDY/AUTOTRACE
http://wiki.gurubee.net/display/DBSTUDY/DBMS_XPLAN
http://www.gurubee.net/lecture/2137
https://docs.oracle.com/cd/E24693_01/appdev.11203/e23448/d_xplan.htm#i998364
http://www.gurubee.net/lecture/1842
http://wiki.gurubee.net/display/DBSTUDY/AUTOTRACE,TKPROF
http://wiki.gurubee.net/pages/viewpage.action?pageId=23429127
http://wiki.gurubee.net/pages/viewpage.action?pageId=30966077
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| ASH를 이용한 wait event 및 session 찾기 (0) | 2019.01.03 |
|---|---|
| 오라클 대기이벤트 ; cursor: pin S wait on X (2) | 2019.01.03 |
| STATSPACK 테이블로 특정 시간대 SQL_ID별 실행횟수 확인 (0) | 2018.12.28 |
| 오라클 무료 모니터링 툴 myora 사용법 (2) | 2018.12.28 |
| SQL 튜닝 팁 (0) | 2018.12.27 |