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

이전 글 Real-Time SQL Monitoring - v$sql_plan_mointor 조회하기 ( http://overoid.tistory.com/40?category=357137 ) 에서 사용된 SQL의 경우. LONG SQL 수행 후 문제가 있어 중지하고, 재 수행했다면... 이전에 수행했던 실행계획이 겹쳐서 나오는 단점이 있다.

대신 쿼리가 심플하고 Version 2에 비해서 조회 성능이 더 우수하다. 

Version 1의 중복 이슈를 해결한 Ver2 - v$sql_plan_mointor 조회 쿼리.

WITH SQL_PLAN_INF AS (

    SELECT /*+ materialize ordered */

           SQL_ID, PLAN_LINE_ID, STATUS, PROCESS_NAME, PLAN_DEPTH, PLAN_OPERATION, PLAN_OPTIONS, PLAN_OBJECT_OWNER, PLAN_OBJECT_NAME, OUTPUT_ROWS, LAST_CHANGE_TIME, FIRST_CHANGE_TIME, SQL_EXEC_START

      FROM V$SQL_PLAN_MONITOR A

     WHERE SQL_ID IN (SELECT SQL_ID

                        FROM (SELECT SQL_ID, ROW_NUMBER() OVER(ORDER BY SQL_EXEC_START DESC) AS RN

                               FROM V$SESSION

                               WHERE MODULE = :module_name)

                       WHERE RN = 1

                      )

       AND STATUS = 'EXECUTING'

),

SQL_PLAN_INFO AS (

    SELECT /*+ materialize */* FROM (

        SELECT A.*, RANK() OVER(PARTITION BY SQL_ID ORDER BY SQL_EXEC_START DESC) AS RN

          FROM SQL_PLAN_INF A

      ) X

     WHERE RN = 1

)

SELECT  LPAD(' ', a.plan_DEPTH * 4 ,' ')||A.plan_OPERATION||' '||a.PLAN_OPTIONS, 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 SQL_PLAN_INFO 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 SQL_PLAN_INFO

         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'

ORDER BY A.PLAN_LINE_ID

사용법 및 설명은 이전글(http://overoid.tistory.com/40?category=357137 )을 참고하자.