Oracle: 새 문서: Oracle에서 SQL Plan(실행 계획)이 변경되었는지 추적하는 것은 성능 문제를 사전에 감지하고 해결하기 위한 중요한 작업입니다. SQL Plan의 변경 여부를 추적하는 방법은 다음과 같습니다: 1. SQL ID를 기준으로 실행 계획 추적 • 특정 SQL 문의 SQL_ID를 사용하여 실행 계획을 추적할 수 있습니다. • 실행 계획을 저장하고 비교하려면 V$SQL과 DBA_HIST_SQL_PLAN을 활용합니다....

2024-11-23T01:36:53Z

새 문서: Oracle에서 SQL Plan(실행 계획)이 변경되었는지 추적하는 것은 성능 문제를 사전에 감지하고 해결하기 위한 중요한 작업입니다. SQL Plan의 변경 여부를 추적하는 방법은 다음과 같습니다: 1. SQL ID를 기준으로 실행 계획 추적 • 특정 SQL 문의 SQL_ID를 사용하여 실행 계획을 추적할 수 있습니다. • 실행 계획을 저장하고 비교하려면 V$SQL과 DBA_HIST_SQL_PLAN을 활용합니다....

새 문서

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을 통한 실시간 감지.

이러한 방법을 통해 실행 계획 변경을 효과적으로 추적하고 데이터베이스 성능 문제를 사전에 방지할 수 있습니다.

SQL 플랜 변경 추적 방법 - 편집 역사