오라클 SQL 분석정보 정리

1. 실행계획 (Execution Plan) - "예상"

옵티마이저가 SQL을 어떻게 처리할지 세운 계획. 실제 실행 전 예측지.

• 주요컬럼
  • Operation: 처리 방식 (TABLE ACCESS FULL, INDEX RANGE SCAN, HASH JOIN 등)
  • Rows (E-Rows) : 옵티마이저 예상 행 수
  • Cost : 옵티마이저 비용
  • Bytes / Time : 예상 데이터량 / 예상 시간

---

2. 수행통계 (Runtime Statistics) - "실제"

SQL이 실제로 돌고 난 뒤의 결과 수치. 예상(E) vs 실제(A)

2-1. GATHER_PLAN_STATISTICS + DBMS_XPLAN.DISPLAY_CURSOR

실행계획 각 줄(오퍼레이션 단계)마다 통계가 붙어서 나온다

--예시쿼리
SELECT /*+ GATHER_PLAN_STATISTICS */ * 
FROM emp e, dept t
WHERE e.deptno = t.deptno
AND e.sal > 3000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

-- 결과
-------------------------------------------------------------------------------------------------------------------
| Id | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads | Writes |
-------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |        |      1 |        |      5 |00:00:00.01 |      14 |     2 |      0 |
|  1 |  NESTED LOOPS                |        |      1 |      4 |      5 |00:00:00.01 |      14 |     2 |      0 |
|  2 |   TABLE ACCESS FULL          | EMP    |      1 |      4 |      5 |00:00:00.01 |       7 |     2 |      0 |
|  3 |   TABLE ACCESS BY INDEX ROWID| DEPT   |      5 |      1 |      5 |00:00:00.01 |       7 |     0 |      0 |
|* 4 |    INDEX UNIQUE SCAN         | PK_DEPT|      5 |      1 |      5 |00:00:00.01 |       0 |     0 |      0 |
-------------------------------------------------------------------------------------------------------------------

 

• 컬럼의미
  • Id : 오퍼레이션 번호 (들여쓰기로 부모-자식 관계 표현)
  • Operation : 처리방식(FULL SCAN, INDEX SCAN, NESTED LOOPS 등)
  • Name : 대상객체 (테이블/인덱스 이름)
  • Starts : 해당 오퍼레이션 실행 횟수
  • E-Rows : 옵티마이저 예상 행 수
  • A-Rows : 실제 처리 된 행 수
  • A-time : 실제 누적 수행 시간
  • Buffers : 논리적 읽기 블록 수 (=cr)
  • Reades : 물리적 읽기 블록 수 (=pr)
  • writes : 물리적 쓰기 (=pw)

 

2-2. SET AUTOTRACE ON

간단한 전체 통계 및 쿼리전체에 대한 합계통계

--예시쿼리
SET AUTOTRACE ON
SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno AND e.sal > 3000;

... (실행계획 트리) ...

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets         ← 전체 합계만!
          2  physical reads
          0  redo size
       1234  bytes sent via SQL*Net
          5  rows processed

 

• 주요
  
  • recursive calls: 내부 재귀 호출 수
  • db block gets: Current 모드 블록 읽기
  • consistent gets: CR 모드 블록 읽기 (= 논리적 읽기)
    / 총 14개 블록을 읽었지만 어느 단계에서 읽었는지는 알 수 없음
  • physical reads: 디스크에서 읽은 블록 수
  • redo size: 생성된 redo 양
  • sorts (memory/disk): 메모리/디스크 정렬 횟수
  • rows processed: 처리된 행 수

---

3. SQL Trace + TKPROF — "가장 상세한 분석"

세션의 모든 SQL 활동을 파일로 기록. 대기 이벤트, 바인드 변수까지 다 잡힘

-- 예시쿼리
SELECT * FROM big_table WHERE c4 = 'xxx';

call     count       cpu    elapsed       disk      query    current        rows
------- ------  --------  ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.04       0.05       1466       7471          0          10
------- ------  --------  ---------- ---------- ---------- ----------  ----------
total        4      0.04       0.05       1466       7471          0          10

• 해석
  • disk=1466: 디스크에서 1466블록 물리적 읽기 (캐시에 없었던 부분)
  • query=7471: 총 7471블록을 CR 모드로 읽음 → 10건 뽑으려고 7471블록을 뒤짐 = 비효율
  • rows=10: 10건 반환
  • elapsed (0.05초): 처음부터 끝까지 실제로 흐른 시간
  • cpu (0.04초): 그중 CPU가 실제로 일한 시간
  • elapsed - cpu = I/O 등 대기 시간 -> 0.05 - 0.04 = 0.01초
    / 1466블록을 디스크에서 물리적으로 읽어오기 위해 0.01초 대기 함