Oracle에서 SQL Plan(실행 계획)이 변경되었는지 추적하는 것은 성능 문제를 사전에 감지하고 해결하기 위한 중요한 작업입니다. SQL Plan의 변경 여부를 추적하는 방법은 다음과 같습니다:
1. SQL ID를 기준으로 실행 계획 추적
• 특정 SQL 문의 SQL_ID를 사용하여 실행 계획을 추적할 수 있습니다. • 실행 계획을 저장하고 비교하려면 V$SQL과 DBA_HIST_SQL_PLAN을 활용합니다.
예제: 현재 실행 계획 확인
SELECT sql_id, plan_hash_value FROM v$sql WHERE sql_text LIKE 'SELECT * FROM employees%';
• **PLAN_HASH_VALUE**는 실행 계획의 변경 여부를 확인하는 주요 기준입니다. 동일한 SQL이라도 PLAN_HASH_VALUE가 변경되면 실행 계획이 바뀐 것입니다.
2. AWR 스냅샷에서 실행 계획 추적
• AWR(Automatic Workload Repository)을 사용하여 SQL Plan의 히스토리를 추적할 수 있습니다. • DBA_HIST_SQL_PLAN 뷰를 활용하면 이전에 실행된 계획을 조회하고 비교 가능합니다.
예제: SQL Plan 히스토리 추적
SELECT sql_id, plan_hash_value, timestamp FROM dba_hist_sql_plan WHERE sql_id = 'SQL_ID값' ORDER BY timestamp;
• **TIMESTAMP**를 기준으로 계획 변경 시점을 확인할 수 있습니다. • AWR에서 주기적으로 스냅샷을 생성해야 SQL Plan 변경 이력을 확보할 수 있습니다.
3. SQL Plan Baseline 활용
• SQL Plan Baseline을 사용하면 특정 SQL에 대해 안정적인 실행 계획을 유지하고 변경을 감지할 수 있습니다. • Baseline에 등록된 실행 계획과 현재 실행 계획을 비교할 수 있습니다.
3.1 SQL Plan Baseline 확인
SELECT sql_handle, plan_name, plan_hash_value FROM dba_sql_plan_baselines WHERE sql_text LIKE 'SELECT * FROM employees%';
3.2 Baseline과 현재 Plan 비교
SELECT s.sql_id, b.plan_name, s.plan_hash_value FROM v$sql s JOIN dba_sql_plan_baselines b ON s.sql_id = b.sql_handle WHERE s.sql_id = 'SQL_ID값';
4. SQL Plan 변경 알림 설정
• Oracle에서 SQL Plan이 변경되었을 때 경고를 받을 수 있도록 모니터링 스크립트를 작성할 수 있습니다.
예제: SQL Plan 변경 모니터링 스크립트
-- SQL Plan 변경 탐지 SELECT sql_id, COUNT(DISTINCT plan_hash_value) AS plan_change_count FROM dba_hist_sql_plan WHERE sql_id = 'SQL_ID값' GROUP BY sql_id HAVING COUNT(DISTINCT plan_hash_value) > 1;
• 이 쿼리는 특정 SQL_ID에 대해 여러 Plan이 생성되었는지 확인합니다.
5. SQL Plan Management(SPM) 사용
• SPM은 SQL Plan 변경을 추적하고, 계획 고정을 통해 성능 저하를 방지합니다.
5.1 SQL Plan 고정(Baseline 생성)
BEGIN
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'SQL_ID값');
END;
5.2 SQL Plan 변경 감지
• DBA_SQL_PLAN_BASELINES를 주기적으로 확인하여 새로운 Plan이 등록되었는지 확인합니다.
SELECT sql_handle, plan_name, plan_hash_value, enabled FROM dba_sql_plan_baselines WHERE sql_handle = 'SQL_HANDLE값';
6. SQL Plan 변경 이력 비교
• DBMS_XPLAN.DISPLAY를 사용해 현재 Plan과 이전 Plan을 비교.
예제: 실행 계획 출력
-- 현재 실행 계획 출력 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => 'SQL_ID값'));
-- 저장된 계획 출력 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('SQL_ID값', plan_hash_value => 'PLAN_HASH값'));
7. SQL Plan 비교를 위한 사용자 정의 스크립트
• 실행 계획의 주요 요소를 비교하는 스크립트를 작성.
예제: 실행 계획 주요 요소 비교
SELECT a.operation, a.options, a.object_name, a.object_type FROM dba_hist_sql_plan a WHERE a.sql_id = 'SQL_ID값' AND a.plan_hash_value IN ('PLAN_HASH1', 'PLAN_HASH2') ORDER BY a.id, a.position;
8. SQL Monitoring 활용
• Oracle Real-Time SQL Monitoring으로 SQL Plan 변경 시점 감지.
예제: 실시간 SQL Plan 확인
SELECT sql_id, plan_hash_value, status FROM v$sql_monitor WHERE sql_id = 'SQL_ID값';
9. 플랜 변경 자동화 감지
• PL/SQL 프로시저를 활용해 주기적으로 Plan을 감지하고 로그를 남기거나 알림을 설정.
BEGIN
FOR rec IN (
SELECT sql_id, COUNT(DISTINCT plan_hash_value) AS plan_count
FROM dba_hist_sql_plan
GROUP BY sql_id
HAVING COUNT(DISTINCT plan_hash_value) > 1
) LOOP
DBMS_OUTPUT.PUT_LINE('SQL_ID: ' || rec.sql_id || ' Plan Change Detected');
END LOOP;
END;
10. Oracle Enterprise Manager(OEM) 활용
• OEM을 통해 SQL Plan 변경을 실시간으로 추적하고 경고를 설정. • SQL Monitoring 기능에서 SQL_ID를 등록하여 Plan 변경 시 알림을 받도록 설정.
요약
• SQL Plan 변경 감지의 핵심 요소: 1. SQL_ID와 PLAN_HASH_VALUE를 기준으로 변경 추적. 2. AWR 및 SPM을 활용하여 히스토리 관리. 3. Baseline을 사용해 안정적인 Plan 유지. 4. DBMS_XPLAN과 모니터링 스크립트를 사용해 변경 비교. 5. OEM 및 SQL Monitoring을 통한 실시간 감지.
이러한 방법을 통해 실행 계획 변경을 효과적으로 추적하고 데이터베이스 성능 문제를 사전에 방지할 수 있습니다.