[DB] Oracle AWR 스냅샷을 활용한 SQL 성능 분석

Oracle 데이터베이스의 성능을 분석하는 데 있어 AWR(Automatic Workload Repository) 스냅샷은 매우 중요한 역할을 한다. AWR 스냅샷은 특정 시점의 데이터베이스 성능 데이터를 캡처하여 저장하며, 이를 통해 데이터베이스 성능 문제를 진단하고 최적화하는 데 유용한 정보를 제공한다.

AWR 스냅샷을 활용하여 특정 시간대의 SQL 성능을 분석하는 SQL 쿼리에 대해 확인해보자.

 

1. 스냅샷(Snapshot) ID 추출하기

먼저, 특정 시간대에 해당하는 AWR 스냅샷의 SNAP_ID를 추출한다.

DBA_HIST_SNAPSHOT 뷰에는 각 스냅샷의 시작 시간(BEGIN_INTERVAL_TIME)과 끝 시간(END_INTERVAL_TIME)이 저장되어 있다.

SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME =
  (SELECT MIN(BEGIN_INTERVAL_TIME) 
     FROM DBA_HIST_SNAPSHOT 
    WHERE BEGIN_INTERVAL_TIME >= TO_DATE('20240820120000','YYYYMMDDHH24MISS'))
UNION
SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME =
  (SELECT MAX(BEGIN_INTERVAL_TIME) 
     FROM DBA_HIST_SNAPSHOT 
    WHERE BEGIN_INTERVAL_TIME < TO_DATE('20240820130000','YYYYMMDDHH24MISS'));

위 쿼리는 예시로, 2024년 8월 20일 12시부터 13시까지의 첫 번째와 마지막 스냅샷 ID를 반환한다.

TO_DATE 함수를 사용해 문자열을 날짜 형식으로 변환한 후, 지정된 시간대에 해당하는 가장 이른 스냅샷과 가장 늦은 스냅샷을 추출하여 결과적으로, 해당 시간대의 성능 분석에 필요한 스냅샷을 얻을 수 있다.

 

2. SQL 성능 데이터 분석하기

이제 앞서 얻은 스냅샷 ID를 사용하여, 그 시간대에 실행된 SQL들의 성능 데이터를 분석할 수 있다.

다음 쿼리는 특정 스냅샷 ID에 해당하는 SQL의 CPU 사용량과 실행 횟수를 분석한다.

SELECT x.*,
       DECODE(total_executed,0,0,TRUNC(cputime/total_executed,2)) ctpe,
       y.sql_text
  FROM (
        SELECT sql_id, SUM(cpu_time_delta)/1000000 cputime, SUM(executions_delta) total_executed
          FROM dba_hist_sqlstat
         WHERE  snap_id = '123456'
         GROUP BY sql_id ORDER BY 2 desc ) x, dba_hist_sqltext y
 WHERE x.sql_id = y.sql_id
   AND ROWNUM < 21
 ORDER BY ctpe desc;

위 쿼리는 아래와 같은 과정으로 SQL 성능을 분석한다.

 

1. SQL 성능 데이터 집계: DBA_HIST_SQLSTAT 뷰에서 주어진 SNAP_ID에 해당하는 SQL들의 CPU_TIME_DELTA(CPU 사용 시간)와 EXECUTIONS_DELTA(실행 횟수)를 집계한 뒤, CPU 사용 시간을 밀세컨드 단위로 집계된 후 초 단위로 변환한다.
2. CPU Time per Execution 계산: 각 SQL의 실행당 평균 CPU 시간을 계산함. 이는 DECODE 함수를 사용해 총 실행 횟수가 0인 경우 0을 반환하며, 그렇지 않은 경우 CPU_TIME_DELTA를 총 실행 횟수로 나누어 계산한다.
3. SQL Text 결합: SQL 성능 데이터와 DBA_HIST_SQLTEXT 뷰를 조인하여, 각 SQL ID에 해당하는 실제 SQL 텍스트를 가져온다.
4. 결과 정렬 및 제한: 계산된 실행당 평균 CPU 시간을 기준으로 정렬하며, 상위 20개의 SQL 문에 대한 결과만을 반환한다. 이를 통해 성능 분석에 있어 가장 비용이 많이 드는 SQL을 우선적으로 확인할 수 있다.

dba_hist_sqlstat

위 이미지와 같이 결과값이 나오며, CLOB을 통해 수행된 쿼리를 조회할 수 있다.

 

3. 결론

이와 같은 AWR 스냅샷 기반 SQL 성능 분석은 특정 시간대에 데이터베이스에서 어떤 SQL이 가장 많은 리소스를 소비했는지, 그리고 그 SQL의 실제 내용이 무엇인지 파악하는 데 매우 유용하다. 특히 CPU 사용량이 많은 SQL을 최적화함으로써 전체적인 데이터베이스 성능을 향상시킬 수 있다.

---