하지만 옵티마이저가 어떤 상황에서든 최적의 실행계획을 수 립하는 것은 아니며, 잘못된 통계정보로 인한 오판을 일으킬 수 도 있다고 설명했다. 이런 잘못된 실행계획을 바로 잡는 방법으 로 보통 애플리케이션에 오라클 힌트를 추가하는 것을 권고하나, 이 방법은 운영의 중단을 가져오기도 한다(단, 일부 WEB관련 소스에서는 중단되지 않는 경우도 있음).

운영을 하다 보면 애플리케이션에 SQL튜닝을 통한 정기적 배 포를 실시하는 경우도 있고, 예상치 못한 장애로 빠른 대응을 해 야 하는 경우도 있다. 장애가 발생하면 모든 개발자 및 엔지니어 들은 문제 해결을 위한 준비상태를 확실히 갖추고 있어야 비로소 빠른 대응이 가능해진다.

1분 1초가 급박한 순간에 실행계획 변경에 따른 장애 현상이 발견된다면? 거기다 애플리케이션 배포를 위한 개발자 도착까지 많은 시간이 걸린다면? 개발자가 도착할 때까지 장애 상황을 지 켜봐야만 할 것인가? 물론 그럴 수만은 없다.

이번 회에는 실행 계획 변경으로 인한 장애를 예방하고, 장애 상황이 발생했을 때 우회 처리하는 방안에 대해 알아보자.

SQL의 과거 히스토리를 분석하자

오라클의 SQL은 각 구문마다 Hash함수를 적용한 Hash_ Value나 SQL_ID(10g 이상) 등으로 관리되고 있다. 실행계획 또 한 Plan_Hash_Value를 통해 동일한 실행계획인지 아닌지 판별 가능하다.

그렇다면 과거와 현재의 실행계획을 비교하기 위해 과거 시점 의 데이터를 임시 테이블에 주기적으로 복사해 비교해보면 어떨 까? 현재 실행계획이 올바르게 돌아가고 있는지 혹은 그렇지 않 은지 관리가 가능해질 것이다.

이러한 기능들은 현재 유료 툴에 서 많이 사용되는 방식으로 관리자가 수동으로 관리·운영하는 것이 가능하다. 자동으로는 오라클 9i 이전 버전에서 Statpack을 활용하거나 오라클 10g 이상에서 AWR을 활용해 데이터를 수집 하고 성능데이터를 추출하는 방법이 있다.

지금부터 설명하는 내용은 오라클 10g 이상부터 활용되는 AWR과 관련된 정보를 이용하는 방법임을 미리 밝혀둔다. 오라 클 8i 및 9i 버전은 Statpack 유저의 STATS$* 뷰를 활용해 비슷 한 내용을 추출해낼 수 있다.

[AWR(Automatic Workload Repository)]

• - 오라클 10g 이상부터 지원되는 데이터 자동 수집 기능
• - MMON, MMNL 백그라운드 프로세서에 의해 데이터 수집
• - SYSAUX 테이블스페이스에 저장되며, 수집 주기 및 저장 기간 설정 가능
• - 저장 기간을 늘리기 위해서는 SYSAUX 테이블스페이스의 증가 필요

정책변경 방법 : DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS

• ( INTERVAL=> 30 , RETENTION => 60*24*30)
• - INTERVAL : 수집 주기를 설정, 30분마다 자동 수집 설정(분 단위)
• - RETENTION : 저장 기간을 설정, 30일 동안 보관 설정(분 단위)

AWR의 데이터는 기본적으로 SYSAUX 테이블스페이스 WRH$*나 WRM$* 테이블에 저장되며, DBA_HIST_* 딕셔너 리를 통해 데이터 추출이 가능하다. SQL에 대한 추세 분석이 필 요할 때는 DBA_HIST_SQLSTAT 뷰를 이용하면 된다.

DBA_HIST_SQLSTAT으로 현재 실행계획과 과거 실행계획 을 비교해 보자.

• [리스트1] 실행계획이 틀린SQL 추출

• SELECT sql_id, COUNT(DISTINCT plan_hash_value) p_cnt 
       , MAX(module) module
       , MAX(ROUND(elapsed_time_total/DECODE(excutions_total,0,1,executions_total),0)/1000/1000) max_els_time
       , MIN(ROUND(elapsed_time_total/DECODE(excutions_total,0,1,executions_total),0)/1000/1000) min_els_time
    FROM dba_hist_sqlstat
   WHERE snap_id >= (SELECT MAX(snap_id)-48 FROM dba_hist_sqlstat)
   GROUP BY sql_id
  HAVING COUNT(DISTINCT plan_hash_value)>1
    

[리스트1]의 SQL을 활용해 하루 동안 실행계획이 변경된 SQL을 찾아낼 수 있다.

MAX_ELS_TIME, MIN_ELS_TIME으로 최저 응답속도 및 최고 응답속도를 비교할 수 있으며, 이 수치의 차이가 많이 나는 것은 SQL에 힌트를 추가해 성능을 고정할 수 있다.

[리스트1]에서 추출된 SQL 중에‘g1fyytvwhhv1w’가 문제 라면 SQL_ID를 이용한다. 각 구간별 응답속도 및 실행 횟수 등의 성능 데이터를 추출해 언제 실행계획이 변경되었는지 알 아보자.

• [리스트2] SQL_ID별 성능 추출 SQL

• SELECT snap_id, sql_id, plan_hash_value, executions_total
       , ROUND(elapsed_time_total/DECODE(excutions_total,0,1,executions_total),0)/1000/1000 elapsed_time
       , ROUND(buffer_gets_total/DECODE(excutions_total,0,1,executions_total),0)/1000/1000 buffer_get
    FROM dba_hist_sqlstat
   WHERE sql_id='g1fyytvwhhv1w'
   ORDER BY snap_id DESC
    

[리스트2]를 실행하면 SNAP_ID 역순으로 정렬된 성능 데이 터가 추출된다.

운영자는 추출된 데이터의 SNAP_ID와 PLAN_HASH_VALUE를 통해 어떤 SQL에 실행계획 변경이 일어났는지 알 수 있고, 현재 실행계획과 과거 실행계획 내용은 DBA_HIST _SQL_PLAN 뷰를 이용해 추출해낼 수 있다.

DBA_HIST_SQL_PLAN에서 추출된 실행계획 정보를 이용 하면 수행속도가 좋은 실행계획을 선택할 수 있다. 장애 예방을 위해서는 애플리케이션 수정을 통한 안정적인 운영을 해야 한다 는 것을 잊지 말아야 한다.

애플리케이션 수정 없이 실행계획 변경하기

운영 중 실행계획이 변경되어 애플리케이션 재배포 시간이 길 어진다면 오라클 내부의 Stored Outline과 SQL Profile을 고려 해봐야 한다

Stored Outline

• - 오라클 업그레이드 등으로 환경 변화 시 주요 SQL의 플랜 변경을 고정 하기 위한 기능
• - 악성 쿼리의 Outline과 최적화 쿼리의 Outline을 생성해, 애플리케이션 의 구조변경 없이 DBMS에서 플랜 고정 가능

SQL Profile

• - 10g 이상에서 Profile에 Outline 힌트를 추가해 실행계획 생성 시 조언 을 주는 기능

특히 패키지 같은 프로그램은 이미 개발된 채로 배포되므로 수 정이 불가피한 경우가 종종 있다. 프로그램 소스가 존재하지 않 아 문제가 발생할 경우에는 앞서 설명한 두 가지 방법을 활용해 일정 부분 해결이 가능하다.

'일정 부분 해결이 가능하다'는 말 은 앞서 설명한 방법으로도 오라클이 100% 실행계획을 고정시 킬 수는 없다는 뜻이다. 오라클은 자체적으로 힌트를 분석한 뒤 자동적으로 적절한 힌트를 사용하도록 되어 있어서 간혹 사용자 의 힌트를 무시하는 경우도 발생하기 때문이다.

지금까지 옵티마이저를 통한 오라클 운영에 있어 위험요소가 되는 실행계획을 분석하고 제어하는 방법에 대해 알아봤다. 운 영 시스템의 과거 내용을 찾아보고 미래 장애요인을 예방함으 로써 안정적인 운영에 한발 더 가까워지는 계기가 되기를 기대 해본다.