Real-Time SQL Monitoring - v$sql_plan_mointor 조회하기

오래동안 수행되는 SQL이 얼마나 진행되었는지 알 수 있을까?

예상실행계획으로 확인된 실행계획 그대로 수행되고 있는지를 쿼리 수행중에 확인할 수 있을까?

수억에서 수십, 수백억 레코드를 조인하여 처리하는데.. 언제 끝날지 모른다면 정말로 답답하다. 

수분에서 수십, 수백분이 소요되는  SQL 대량 작업의 경우 잘못된 실행계획으로 쿼리가 실행되고 있다는 것을 실행 초반에 알 수 있다면, 

SQL을 중지하고 수정하여 재실행함으로써 소중한 시간을 아끼고 일찍 퇴근할 수도 있을것이다.  

다행히 오라클에서는 11g부터 Real-Time SQL Monitoring 기능을 제공한다.

물론 쿼리가 수행완료된 이후 dbms_sqltune.report_sql_monitor 로 레포팅을 뽑아 결과를 확인할 수 도 있지만, 

실행중에도 v$sql_mointor, v$sql_plan_mointor 에 값이 변경되므로, 이 뷰를 잘 조회하면 얼마나 실행되었는지? 어떤 실행계획으로 실행되고 있는지 쉽게 파악할 수 있다.

SELECT LPAD(' ', a.plan_DEPTH * 4 ,' ')||A.plan_OPERATION||' '||a.PLAN_OPTIONS plan_text, 

       PLAN_OBJECT_OWNER,PLAN_OBJECT_NAME,  

       B.OUTPUT_ROWS,B.LAST_CHANGE_TIME,  B.FIRST_CHANGE_TIME,

       ROUND(((B.LAST_CHANGE_TIME - B.FIRST_CHANGE_TIME)*24*60*60),2)  AS ELAPSED

FROM V$SQL_PLAN_MONITOR A,

    (

    SELECT PLAN_LINE_ID,SQL_ID,SUM(OUTPUT_ROWS) OUTPUT_ROWS,

           MAX(LAST_CHANGE_TIME) LAST_CHANGE_TIME, 

           MIN(FIRST_CHANGE_TIME) AS FIRST_CHANGE_TIME

     FROM V$SQL_PLAN_MONITOR a

     WHERE 1=1

       AND STATUS = 'EXECUTING'

       AND PROCESS_NAME <> 'ora'

    GROUP BY PLAN_LINE_ID,SQL_ID

    ) B

WHERE A.SQL_ID = B.SQL_ID

  AND A.PLAN_LINE_ID = B.PLAN_LINE_ID

  AND A.PROCESS_NAME = 'ora'

  AND A.SQL_ID IN (SELECT SQL_ID  FROM V$SESSION

                    WHERE MODULE = :module_name

                      AND STATUS = 'ACTIVE'

                   )

ORDER BY  A.PLAN_LINE_ID

;

:module_name에 원하는 모듈을 입력하거나, 해당 부분을 AND A.SQL_ID = :sql_id로 수정하여 sql_id를 직접 입력하면 된다.

sql_id는 어떻게 찾느냐고? 

v$sql을 조회해보자.

일반적으로는 SQL 작성시 SQL을 유일하게 구별할 수 있는 ID를 주석으로 기술한다. 

그 텍스트로 select sql_id from v$sql where sql_text like '%찾는문자열%'로 조회하면 sql_id를 알 수 있다.

아래 그림은 위 SQL 실행결과의 일부분이다. 

이쿼리를 2~3초에 한번씩 실행하면 실행계획을 확인할 수 있으며, 실행계획 순서에 따라서 OUTPUT_ROWS에 처리되고 있는 레코드 값이 변경되는 것을 확인할 수 있다. 그 부분이 현재 수행되고 있는 부분이다.

또한 보여주는 실행계획이 현재 수행중인 실행계획이므로, 쿼리 수행전 확인한 예상실행계획과 동일하게 실행되고 있는지 확인하면 된다.