[SQL튜닝/SQL 분석] SQL 분석 도구(1) (AutoTrace, dbms_xplan.display & dbms_xplan.display

🖥️ 들어가며

📌 Trace 결과 분석: AutoTrace, dbms_xplan.display & dbms_xplan.display_cursor, 10046 Trace & SQL Trace
📌 전체 DB 성능 분석: AWR Report

✏️ 1. AutoTrace

SQL*Plus에서 제공하는 실제실행계획 및 실행통계 확인 도구
SQL 수행 시 실제 일량 측정 및 튜닝하는데 유용한 정보들을 많이 포함하는 도구입니다.
10046 Trace와는 달리(.trc 파일 생성) 트레이스 파일 없이 화면에 결과를 출력합니다.
출력 정보
- 조건절 정보(Predicate Information): access / filter
- Hint Report
- Note
- Event에 대한 통계 정보: sorts

SQL> set autot on
SQL> select * from scott.emp where empno=7900;
     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 81/12/03        950                    30

Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    37 |     1   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)|
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)|
---------------------------------------------------------------------------

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
        750  bytes sent via SQL*Net to client
        405  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> set autot off

수행 옵션
- (1)~(3): 수행결과를 출력해서 보여주어야 함 → 쿼리를 실제 수행
- (4), (6): 실행통계를 보여주어야 함 → 쿼리를 실제 수행
- (5): 실행계획만 출력하면 됨 → 쿼리를 실제 수행 X

옵션	SQL수행여부	SQL수행결과	실행계획(예상)	실행통계
(0) set autot off	O	O
(1) set autotrace on	O	O	O	O
(2) set autotrace on explain	O	O	O
(3) set autotrace on statistics	O	O		O
(4) set autotrace traceonly	O		O	O
(5) set autotrace trace explain			O
(6) set autotrace trace statistics	O			O

SQL*Plus - Auto Trace 사용법

-- SQL*Plus 접속
-- line과 페이지 설정
SQL> set linesize 200 pagesize 200;
-- trace on 설정
SQL> set autotrace traceonly;

-- 쿼리
SQL>
SELECT /*+ INDEX(EMP PK_EMP) */ ENAME, EMPNO
FROM EMP
WHERE EMPNO = 7900;

-- traceonly에 대한 실행결과
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

✏️ 2. dbms_xplan.display & dbms_xplan.display_cursor

dbms_xplan.display
- 단일 SQL문에 대해 예측 실행계획을 보여주는 Function, 실측 정보가 아닌 예측 정보를 제공합니다.
- display(): 예상 계획
  display_cursor(): 실제 실행 계획 확인

dbms_xplan.display 사용법

FUNCTION DISPLAY(TABLE_NAME VARCHAR2 DEFAULT 'PLAN_TABLE',
    STATEMENT_ID VARCHAR2 DEFAULT NULL,
    FORMAT VARCHAR2 DEFAULT 'TYPICAL',
    FILTER_PREDS VARCHAR2 DEFAULT NULL)

SQL> 
EXPLAIN PLAN FOR -- 분석하고자 하는 SQL을 EXPLAIN PLAN FOR 다음에 넣습니다. (+SQL)
SELECT /*+ INDEX(EMP PK_EMP) */ ENAME, EMPNO
FROM EMP
WHERE EMPNO = 7900;

SQL> 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 기본적으로 PLAN_TABLE과 TYPICAL Format으로 출력하겠다는 의미

dbms_xplan.display 옵션 파라미터		설명
table_name		- Execution Plan이 저장되는 테이블을 지정. - 기본값: 'PLAN_TABLE'
statement_id		- Execution Plan시 SET STATEMENT_ID를 지정한 경우 이를 불러올 수 있음. - 값이 Null일 경우 마지막에 실행된 문장을 불러옴.
format	basic	- 가장 기본적인 정보만 보여줌
	typical	- 기값: TYPICAL - SQL 튜닝에 필요한 Normal한 정보를 보여줌. (Id, Operation, Name, Rows, Bytes, Cost (%CPU), Time + Predicate Information) - SQL 튜닝에 가장 유용하게 사용되는 Predicate Information이 제공됨.
	all	- Typical Format에 Query Block Name과 Column Projection Information이 추가로 제공됨. (Id, Operation, Name, Rows, Bytes, Cost (%CPU), Time + Predicate Information + Query Block Name + Column Projection Information)
	outline	- Typical Format에 추가적으로 Hidden Hint인 Outline Global Hint를 제공함. (Id, Operation, Name, Rows, Bytes, Cost (%CPU), Time + Predicate Information + Outline data)
	advanced	- All format에 Outline Format을 합친 정보를 제공함. (Id, Operation, Name, Rows, Bytes, Cost (%CPU), Time + Predicate Information + Query Block Name + Column Projection Information + Outline data)
filter_preds		- 저장된 Plan에서 일부 Row 또는 Row Set을 제한하여 출력할 수 있음.

dbms_xplan.display_cursor
- display(): 예상 계획
  display_cursor(): 실제 실행 계획 확인
- 단일 SQL문에 대해 실제 수행된 실행계획을 보여주는 Fuction (실측치)
- 10046 Trace와 같은 TKPROF 변환 불필요 (Raw Trace → Readable Format 변환)
- AutoTrace와 같이 SQL*Plus에서 수행하는 제약 없음
- 실행계획의 Row Source별 수행시간, 블록 접근 정보, 사용 Memory 용량 등 확인 가능
- SQL 툴에서 OS 접근없이 간단하게 활용 가능
- 튜닝 정보를 활용할 때 가장 편안하게 사용하는 툴
- 실행계획과 조건절 정보 확인 가능하나, Wait Event는 미출력

dbms_xplan.display_cursor를 위한 사전 준비
- Dynamic Performance View에 SELECT 권한 필요
  : V$SQL, V$SESSION, V$SQL_PLAN, V$SQL_PLAN_STATISTICS_ALL
- 히든 파라미터인 _rowsource_execution_statistics = true 설정이 필요 (기본값: true)

dbms_xplan.display_cursor 사용법

FUNCTION DISPLAY_CURSOR(SQL_ID VARCHAR2 DEFAULT NULL,
                        CURSOR_CHILD_NO INTEGER DEFAULT 0,
                        FORMAT VARCHAR2 DEFAULT 'TYPICAL')

-- 세션 레벨의 파라미터 변경 (운영 DB에서는 삼가해야 함)
SQL> ALTER SESSION SET STATISTICS_LEVEL = ALL;
-- 또는 select문에서 /*+ gather_plan_statistics */ 힌트 사용 -> alter session 수행 시 불필요

-- 분석하고자 하는 SQL 실행
SQL> 
SELECT /*+ INDEX(EMP PK_EMP) Hello */ ENAME, EMPNO
FROM EMP
WHERE EMPNO = 7900;

-- dbms_xplan.display_cursor로 조회
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

출력 이해
- Id: 실행계획 구분자
- Operation: 각 단계에서 어떤 작업이 일어났는지 표시
- Name: 테이블명이나 Index명 표시
- Cost: CBO가 쿼리 계획의 각 단계에 할당한 비용.
  (CBO: 동일한 쿼리에 대해 다양한 실행 경로/계획을 생성함으로써 동가하며 모든 쿼리에 대해 비용을 할당함)
- Starts: 수행 횟수
- E-Rows: 예측 행 수
- A-Rows: 실제 수행하영 얻은 행 수
- A-Time: 수행시간
- Buffers: Logical Read
- Reads: Physical Read (Logical Read 수를 포함하고 있음)

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(null, null, 'iostats last -rows')

select * from table(dbms_xplan.display_cursor(null, null, 'iostats last -rows'));

파라미터 1: SQL ID (첫 번째 null)
파라미터 2: Child ID (두 번째 null)
파라미터 3: iostats, memstats, allstats 중 택 1 (마지막 iostats)
- 파라미터 추가 옵션
  - all: 동일 SQL의 누적 통계 대신 마지막 수행 SQL에 대한 통계 값
  - Advanced: allstats와 함께 사용. 힌트 등 다양한 정보를 추가로 제공함
  - -rows: 예상 E-Rows 미출력
  - parallel: 병렬 쿼리 실행시 병렬에 대한 정보 제공
  - partition: 파티션 프러닝 정보 출력

✏️ 3. 10046 Trace & SQL Trace

핵심 차이

구분	레벨	수집 정보	활용도
SQL Trace	Level 1	기본 실행 통계	일반 성능 분석
10046 Trace	Level 8/12	Wait 이벤트 + Bind 변수	심층 튜닝

10046 Trace
- 주요 정보
  - DBMS Call 정보: Parse, Execute, Fetch Call
  - 실행계획 및 실행계획별 블록의 Logical 및 Physical Read 수
  - SQL 수행 과정에서 발생하는 각종 Event 정보
- Trace Level
  - Level 1: 기본 정보
  - Level 4: 기본 정보 + Binding 정보
  - Level 8: 기본 정보 + Waiting 정보
  - Level 12: 기본 정보 + Binding 정보 + Waiting 정보
- 수행 절차

-- Trace File Size 지정
alter session set max_dump_file_size = unlimited;

-- Trace File 이름 끝에 sik를 붙임 (관리하기 쉽게)
alter session set tracefile_identifier = 'sik'; -- 이 과정은 skip 가능

-- Trace 시작
alter session set events '10046 trace name context forever, level 12';

select /*+ ordered use_nl(e) index(e) */ e.empno, e.ename, d.deptno, d.dname
from   emp e, dept d
where  e.deptno = d.deptno
and    e.sal >= 3000;

-- Trace 종료
alter session set events '10046 trace name context off';

Trace file 위치 확인 → Trace file 확인 → Trace file 변환

-- Trace file 위치 확인
-- 9i, 10g, 11g
select name, value from v$parameter where name = 'user_dump_dest';
NAME             VALUE
---------------  ------------------------
user_dump_dest   C:\ORA21CSRV\RDBMS\TRACE

-- 12c 이상
select value from v$diag_info where name = 'Diag Trace'
VALUE
--------------------------------------
C:\APP\YOON\diag\rdbms\orcl\orcl\trace


-- Trace file 확인
OS> ls -l *sik* -- unix 계열
OS> dir *sik*   -- 윈도우일 경우 command 창에서

2025-02-12 오후 5:58          10,477 orcl_ora_12244_sik.trc -- 암호처럼 되어 있으므로 변환 필요!
2025-02-12 오후 5:58           2,810 orcl_ora_12244_sik.trm


-- Trace file 변환
OS> tkprof orcl_ora_12244_sik.trc sik10046.txt(sys=no)
TKPROF: Release 19.0.0.0.0 - Development on 수 2월 12 18:01:02 2025
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

-- 추가설명
-- tkprof source target (trc 파일을 -> txt로 변환 타겟)
-- sys=no 옵션: SQL 파싱 중 발생하는 내부수행 SQL 제외 (recursive call 無)

변환한 파일 확인 (sik10046.txt)

SELECT *
FROM   EMP E
WHERE  E.EMPNO  = 9999999
AND    E.DEPTNO = 10

-- #보기 1
Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.002          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        1    0.000        0.028          3          4          0          0
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        3    0.000        0.031          3          4          0          0

-- #보기 2
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

-- #보기 3
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      0   TABLE ACCESS BY INDEX ROWID EMP (cr=4 pr=3 pw=0 time=28400 us)
      1    INDEX UNIQUE SCAN EMP_U1 (cr=3 pr=2 pw=0 time=22438 us)(Object ID 6485271)

구분	항목		설명
보기1	Call	Parse	SQL을 Parsing하는 구간
		Execute	SQL을 실제 수행하는 구간. (DML일 경우 이 부분이 표시됨)
		Fetch	SQL을 통해 나온 값을 사용자에게 반환하는 구간
	Cpu		CPU에서 수행한 시간(단위 : 초)
	Elapsed		각 구간에서 시작과 종료까지 총 수행한 시간(단위 : 초)
	Disk		Disk에서 Block을 읽은 양(Physical Read)
	Query		Memory에 Block을 읽은 양(Logical Read)
	Current		현재의 Session에서 Commit하지 않은 Block을 읽은 양
	Rows		각 단계별 액세스한 ROWS
보기2	Misses in library cache during parse		Parse 구간에서 해당 SQL을 Library Cache에서 읽지 못하고 잃어버린 횟수
			값은 1씩 증가함
			값이 1이면 Hard Parse. 0이면 Soft Parse를 의미함
보기3	cr(consistent read)		Logical Block Read
	pr(Physical Read)		Physical Block Read
	pw(Physical Write)		Physical Block Write
	time		수행시간(단위 - 1 / 1,000,000 초)

SQL Trace
- 10046 Trace와 달리 Waiting이나 Binding 정보는 미출력 (Level 1: 기본 정보만)
- 수행 절차

-- Trace File Size 지정
alter session set max_dump_file_size = unlimited;

-- Trace File 이름 끝에 sik를 붙임 (관리하기 쉽게)
alter session set tracefile_identifier = 'sik'; -- 이 과정은 skip 가능

-- Trace 시작
alter session set sql_trace = true;
select * from emp;

-- Trace 종료
alter session set sql_trace = false;

-- 이후는 10046 Trace와 같은 형태로 Trace file 찾아 tkprof 수행 후 분석

📒 정리하면

SQL 분석 도구
1. Trace 결과 분석: AutoTrace, dbms_xplan.display & dbms_xplan.display_cursor, 10046 Trace & SQL Trace
2. 전체 DB 성능 분석: AWR Report
AutoTrace
1. SQL*Plus에서 실행 계획, 조건절 정보, 통계 정보를 즉시 확인 가능하며, SET AUTOTRACE ON 명령어로 활성화한다.
2. (0) set autot off
  (1) set autotrace on
  (2) set autotrace on explain
  (3) set autotrace on statistics
  (4) set autotrace traceonly
  (5) set autotrace trace explain
  (6) set autotrace trace statistics
  (1)~(3): 수행결과를 출력해서 보여주어야 함 → 쿼리를 실제 수행
  (4), (6): 실행통계를 보여주어야 함 → 쿼리를 실제 수행
  (5): 실행계획만 출력하면 됨 → 쿼리를 실제 수행 X
DBMS_XPLAN
1. DISPLAY는 예측 실행 계획, DISPLAY_CURSOR는 실제 실행 계획을 보여주며, iostats last -rows 옵션이 유용하다.
2. 10046 Trace와 같은 TKPROF 변환 불필요 (Raw Trace → Readable Format 변환)
3. AutoTrace와 같이 SQL*Plus에서 수행하는 제약 없음
dbms_xplan.display_cursor(null, null, 'iostats last -rows')
1. 파라미터 1: SQL ID (첫 번째 null)
2. 파라미터 2: Child ID (두 번째 null)
3. 파라미터 3: iostats, memstats, allstats 중 택 1 (마지막 iostats)
4. 파라미터 3 추가 옵션
  all: 동일 SQL의 누적 통계 대신 마지막 수행 SQL에 대한 통계 값
  Advanced: allstats와 함께 사용. 힌트 등 다양한 정보를 추가로 제공함
  -rows: 예상 E-Rows 미출력
  parallel: 병렬 쿼리 실행시 병렬에 대한 정보 제공
  partition: 파티션 프러닝 정보 출력
10046 Trace
1. Level에 따라 수집 정보가 다르며(Wait, Bind), TKPROF를 사용하여 트레이스 파일을 변환 후 분석한다. (Level 8/12)
2. 수행 절차
  : Trace File Size 지정 → Trace 시작 → Trace 종료 → Trace file 위치 확인 → Trace file 확인 → Trace file 변환 → 변환한 파일 확인
3. Trace Level
  - Level 1: 기본 정보
  - Level 4: 기본 정보 + Binding 정보
  - Level 8: 기본 정보 + Waiting 정보
  - Level 12: 기본 정보 + Binding 정보 + Waiting 정보
SQL Trace: 10046 Trace와 유사하지만 Wait, Binding 정보는 미출력한다. (Level 1)

참고자료

'데이터베이스 > SQL 튜닝' 카테고리의 다른 글

[SQL튜닝/아키텍처 기반 튜닝 원리] 데이터베이스 I/O 원리(2) (RAC 캐시 퓨전, 노드간 전송 매커니즘, I/O 효율화 원리 요약) (0)	2025.02.11
[SQL튜닝/아키텍처 기반 튜닝 원리] 데이터베이스 I/O 원리(1) (블록단위 I/O, 메모리 I/O vs. 디스크 I/O, Sequential I/O vs. Random I/O, Single Block I/O vs. Multi Block I/O, Prefetch, Direct Path I/O) (0)	2025.02.11
[SQL튜닝/아키텍처 기반 튜닝 원리] 데이터베이스 Call과 네트워크 부하 (데이터베이스 Call 종류 및 최소화, Array Processing, Fetch Call 최소화, 페이지 처리, 분산 쿼리 최적화, 사용자 정의 함수/프로시저 제한적 사용/성능 저하 요인 관리) (0)	2025.02.10
[SQL튜닝/아키텍처 기반 튜닝 원리] SQL 파싱 부하 (SQL 처리과정, 캐싱된 SQL 공유, Bind 변수 사용, Static/Dynamic SQL, Application Cursor Caching) (0)	2025.02.10
[SQL튜닝/아키텍처 기반 튜닝 원리] 데이터베이스 아키텍처 (Library 캐시 최적화, DBMS Call 최소화, I/O 성능 효율화, Oracle 아키텍처, 파일 구조, 메모리 구조, SGA, PGA) (0)	2025.02.10

꿈꾸는 도토리

[SQL튜닝/SQL 분석] SQL 분석 도구(1) (AutoTrace, dbms_xplan.display & dbms_xplan.display_cursor, 10046 Trace & SQL Trace)

🖥️ 들어가며

✏️ 1. AutoTrace

✏️ 2. dbms_xplan.display & dbms_xplan.display_cursor

✏️ 3. 10046 Trace & SQL Trace

📒 정리하면

'데이터베이스 > SQL 튜닝' 카테고리의 다른 글

티스토리툴바

[SQL튜닝/SQL 분석] SQL 분석 도구(1) (AutoTrace, dbms_xplan.display & dbms_xplan.display_cursor, 10046 Trace & SQL Trace)

🖥️ 들어가며

✏️ 1. AutoTrace

✏️ 2. dbms_xplan.display & dbms_xplan.display_cursor

✏️ 3. 10046 Trace & SQL Trace

📒 정리하면

'데이터베이스 > SQL 튜닝' 카테고리의 다른 글

'데이터베이스/SQL 튜닝' Related Articles

티스토리툴바