DB 성능 진단 어떻게 할 것인가? (2회)

실시간 모니터링 어떻게 할 것인가?

일반적으로 DBA들은 V$SESSION을 조회하여 실시간 모니터링을 실시하고 있다. 그러나 여기에는 치명적인 문제점이 몇 가지 있다.

첫째, msec 단위로 실행되는 OLTP성 쿼리를 V$SESSION을 몇 초 단위(부하를 많이 줄 수 있음)로 폴링하여 인간의 눈으로 식별한다는 것이 가능한가? 물론 모니터링하는 사람이 600만불의 사나이와 같은 시력과 처리 능력을 가지고 있거나, 또는 최소 수십 초에서 1분 이상 걸리는 장기 실행 쿼리일 경우에는 이 방법으로도 가능하다.

둘째, 문제가 발생한 최근 10분 간의 경향을 분석하고자 한다면? 특정 쿼리를 개별로 봤을 때는 문제가 없어 보여도, 시계열로 집계하여 분석하면 문제점이 드러난다. 그러나 V$SESSION의 경우 별도로 snapshot을 테이블에 저장해두지 않는 이상 과거 정보를 분석해볼 방법이 없다.

 

위와 같은 문제점을 개선하고자 오라클 10g부터 Active session history(ASH)를 제공하고 있다. ASH는 자동으로 1초 간격으로Active Session만 채집하여 SGA의 Buffer Memory내에 보관하고 있다가 가득 차면 AWR Repository에 내려 쓰게 된다. 조회에 따른 별도의 Latch 경합이 없으며, 자동으로 최근 데이터부터 조회되므로 별도의 Order by 가 불필요하다. 한 마디로 오라클이 제공하는 V$SESSION을 대체할 수 있는, 가장 부하가 적은 새로운 실시간+성능 이력 분석이 가능한 모니터링 방법이다. 필자는 성능이나 잠금 등 문제가 발생하면, 항상 ASH를 먼저 분석해보곤 한다.

 

ASH를 사용한 분석 사례

다음은 ASH에서 2012년 11월 28일 오전 08시부터 09시까지 전체 목록을 조회하는 쿼리다. sample_time을 살펴보면 1초 간격으로 수집되고 있음을 알 수 있다. 오라클 11g 이상에서는 SQL_PLAN_OPERATION과 SQL_PLAN_OPTIONS까지 보여주어, 별도로SQL_PLAN_HASH_VALUE로 V$SQL_PLAN과 조인할 필요가 없으므로 훨씬 간편하다. 아래 쿼리의 조회 결과를 별도로 엑셀에서 상세 분석할 수 있다. ASH 버퍼는 보통 1시간 정도의 분량을 담고 있으나, active 세션이 활발한 DB라면 그 이전에AWR(DBA_HIST_ACTIVE_SESS_HISTORY)로 flush 될 수 있으므로, 필요하다면 ASH Buffer 크기를 늘릴 수 있다.

 

SELECT SAMPLE_TIME,

       SESSION_ID,

       SESSION_SERIAL#,

       USER_ID,

       SQL_ID,

       SQL_PLAN_OPERATION, -- 11g 이상

       SQL_PLAN_OPTIONS, -- 11g 이상

       EVENT,

       P1,

       P2,

       P3,

       WAIT_TIME,

       TIME_WAITED

  FROM v$active_session_history

 WHERE sample_time BETWEEN TO_DATE ('20121128 080000', 'YYYYMMDD HH24MISS')

                       AND TO_DATE ('20121128 090000', 'YYYYMMDD HH24MISS')

 

다음 쿼리는 특정 시간대의 대기 이벤트를 집계하여 분석하는 쿼리다. 매우 유용한 방법이니 필히 숙지하기 바란다.

 

SELECT USER_ID,

         SQL_ID,

         EVENT,

         COUNT (*),

         SUM (WAIT_TIME),

         SUM (TIME_WAITED)

    FROM v$active_session_history

   WHERE sample_time BETWEEN TO_DATE ('20121128 090000', 'YYYYMMDD HH24MISS')

                          AND TO_DATE ('20121128 100000', 'YYYYMMDD HH24MISS')

         AND session_type <> 'BACKGROUND'

GROUP BY USER_ID, SQL_ID, EVENT

ORDER BY COUNT (*) DESC

 

위의 쿼리를 통하여 특정 시간대에 어떤 쿼리에 어떤 이벤트가 가장 많이 발생했는지 파악할 수 있다.

 

마치며

ASH는 V$SESSION의 단점을 보강하기 위해 새롭게 추가된 기능으로, 오라클 버전이 올라가면서 점점 더 많은 칼럼이 추가되어 더욱 편리하게 이용 가능하다.

다음 회에서는 세션 레벨이 아닌, SQL 레벨에서 분석하는 방법을 간략히 살펴볼 것이다.

출처

http://www.dbguide.net/knowledge.db?cmd=specialist_view&boardUid=168689&boardConfigUid=87&boardStep=0