Oracle 성능 튜닝 기초 - 실행계획 분석과 인덱스 최적화

Oracle 성능 튜닝 기초 가이드

Oracle 데이터베이스 성능 최적화를 위한 실행계획 분석, 인덱스 활용, 쿼리 튜닝 기법을 다룹니다.

1. 실행계획 확인

-- EXPLAIN PLAN
EXPLAIN PLAN FOR
SELECT * FROM tb_order WHERE customer_id = 100;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- AUTOTRACE
SET AUTOTRACE ON;
SELECT * FROM tb_order WHERE customer_id = 100;
SET AUTOTRACE OFF;

-- 실제 실행 통계
SELECT /*+ GATHER_PLAN_STATISTICS */ * 
FROM tb_order WHERE customer_id = 100;

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

2. 실행계획 읽기

주요 오퍼레이션:
- TABLE ACCESS FULL: 전체 테이블 스캔 (대량 조회 시 비효율)
- INDEX RANGE SCAN: 인덱스 범위 스캔 (효율적)
- INDEX UNIQUE SCAN: 유일값 조회 (가장 효율적)
- NESTED LOOPS: 중첩 루프 조인 (소량 데이터)
- HASH JOIN: 해시 조인 (대량 데이터)
- SORT MERGE JOIN: 정렬 병합 조인

3. 인덱스 생성

-- 단일 컬럼 인덱스
CREATE INDEX idx_customer_id ON tb_order(customer_id);

-- 복합 인덱스 (순서 중요)
CREATE INDEX idx_order_comp ON tb_order(customer_id, order_date);

-- 인덱스 사용 확인
SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE table_name = "TB_ORDER"
ORDER BY index_name, column_position;

4. 인덱스 활용 안 되는 경우

-- 1. 컬럼 변형
WHERE SUBSTR(name, 1, 3) = "홍길"  -- X
WHERE name LIKE "홍길%"            -- O

-- 2. 묵시적 형변환
WHERE phone_no = 01012345678       -- X (숫자)
WHERE phone_no = "01012345678"     -- O (문자)

-- 3. NULL 비교
WHERE column IS NULL               -- 인덱스 사용 안됨

-- 4. 부정형
WHERE status != "DONE"             -- 인덱스 사용 안됨

-- 5. OR 조건
WHERE col1 = "A" OR col2 = "B"     -- 주의 필요

5. 힌트 사용

-- 인덱스 강제 사용
SELECT /*+ INDEX(o idx_customer_id) */ *
FROM tb_order o
WHERE customer_id = 100;

-- Full 스캔 강제
SELECT /*+ FULL(o) */ * FROM tb_order o;

-- 조인 방식 지정
SELECT /*+ USE_NL(o c) */ o.*, c.name
FROM tb_order o
JOIN tb_customer c ON o.customer_id = c.id;

-- 병렬 처리
SELECT /*+ PARALLEL(o, 4) */ * FROM tb_order o;

6. 통계 정보 갱신

-- 테이블 통계 수집
EXEC DBMS_STATS.GATHER_TABLE_STATS("SCOTT", "TB_ORDER");

-- 스키마 전체 통계
EXEC DBMS_STATS.GATHER_SCHEMA_STATS("SCOTT");

-- 통계 확인
SELECT table_name, num_rows, last_analyzed
FROM user_tables
WHERE table_name = "TB_ORDER";

7. 쿼리 튜닝 체크리스트

8. AWR 리포트

-- AWR 스냅샷 생성
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

-- AWR 리포트 생성
@?/rdbms/admin/awrrpt.sql