[DB] 특정 기간중 DB CPU 부하가 높은 SQL 찾기

특정 기간 중, CPU 사용율이 높은 쿼리를 조회한다.
CPU 점유율이 높은 쿼리를 찾아서 튜닝하기 위해서 사용한다. 기본적으로 oracle에서 제공하는 view를 활용한다.

사용 테이블

• DBA_HIST_SQLSTAT : SQL Historical Statixtics Information
  DB에서 실행된 SQL에 대한 성능 통계치 view
• DBA_HIST_SNAPSHOT : SnapShot Information
  워크로드 저장소의 스냅샷에 대한 정보 view
• DBA_HIST_SQLTEXT : SQL Text
  워크로드 저장소에 캡쳐된 공유 SQL 커서에 속한 SQL 문의 텍스트 표시.
  이 view는 주로 V$SQL view와 함께 사용됨.

최종 쿼리

  WITH REF_DATE AS (
      /* 대상 기간 지정 */
      SELECT
          TO_DATE('20220401' || ' 000000', 'YYYYMMDD HH24MISS') AS BGN_DE
          , TO_DATE('20230420' || ' 235959', 'YYYYMMDD HH24MISS') AS END_DE
      FROM DUAL
  )
  SELECT
      X.SQL_ID
      , X.CPU_TIME
      , X.EXECUTIONS_DELTA
      , X.CPU_TIME_PER_EXECUTIONS
      , DBMS_LOB.SUBSTR(SUBSTR(D.SQL_TEXT, 1, 200)) AS SQL_TEXT
      , D.SQL_TEXT AS SQL_FULLTEXT
  FROM (
          SELECT
              SQL.DBID
              , SQL.SQL_ID
              , SUM(SQL.CPU_TIME_DELTA) / 1000000 AS CPU_TIME
              , SUM(SQL.EXECUTIONS_DELTA) AS EXECUTIONS_DELTA
              , ROUND((SUM(SQL.CPU_TIME_DELTA) / 1000000) / DECODE(SUM(SQL.EXECUTIONS_DELTA), 0, 1, SUM(SQL.EXECUTIONS_DELTA)) / DECODE(SQL.PX_SERVERS_EXECS_DELTA, 0, 1, SQL.PX_SERVERS_EXECS_DELTA)) AS CPU_TIME_PER_EXECUTIONS
          FROM DBA_HIST_SQLSTAT SQL
              , (
                  SELECT
                      MIN(SNAP_ID) AS START_SNAP_ID
                      , MAX(SNAP_ID) AS END_SNAP_ID
                      , MIN(BEGIN_INTERVAL_TIME) AS BEGIN_INTERVAL_TIME
                      , MAX(END_INTERVAL_TIME) AS END_INTERVAL_TIME
                  FROM DBA_HIST_SNAPSHOT, REF_DATE
                  WHERE BEGIN_INTERVAL_TIME BETWEEN REF_DATE.BGN_DE
                                              AND REF_DATE.END_DE
              ) SNAP
          WHERE SQL.SNAP_ID BETWEEN SNAP.START_SNAP_ID AND SNAP.END_SNAP_ID
          GROUP BY SQL.DBID, SQL.SQL_ID, SQL.PX_SERVERS_EXECS_DELTA
          HAVING SUM(SQL.EXECUTIONS_DELTA) >= 0
          ORDER BY CPU_TIME_PER_EXECUTIONS DESC
  ) X
  INNER JOIN DBA_HIST_SQLTEXT D
      ON D.SQL_ID = X.SQL_ID
      AND D.DBID = X.DBID
  WHERE ROWNUM <= 40
  ;

분리

  SELECT
      SQL.DBID
      , SUM(SQL.CPU_TIME_DELTA) / 1000000 AS CPU_TIME
      , SUM(SQL.EXECUTIONS_DELTA) AS EXECUTIONS_DELTA
      , ROUND((SUM(SQL.CPU_TIME_DELTA) / 1000000) / DECODE(SUM(SQL.EXECUTIONS_DELTA), 0, 1, SUM(SQL.EXECUTIONS_DELTA)) / DECODE(SQL.PX_SERVERS_EXECS_DELTA, 0, 1, SQL.PX_SERVERS_EXECS_DELTA)) AS CPU_TIME_PER_EXECUTIONS
  FROM DBA_HIST_SQLSTAT SQL
      , (
          SELECT
              MIN(SNAP_ID) AS START_SNAP_ID
              , MAX(SNAP_ID) AS END_SNAP_ID
              , MIN(BEGIN_INTERVAL_TIME) AS BEGIN_INTERVAL_TIME
              , MAX(END_INTERVAL_TIME) AS END_INTERVAL_TIME
          FROM DBA_HIST_SNAPSHOT
          WHERE BEGIN_INTERVAL_TIME BETWEEN TO_DATE('20230401' || ' 000000', 'YYYYMMDD HH24MISS')
                                      AND TO_DATE('20230419' || ' 235959', 'YYYYMMDD HH24MISS')
      ) SNAP
  WHERE SQL.SNAP_ID BETWEEN SNAP.START_SNAP_ID AND SNAP.END_SNAP_ID
  GROUP BY SQL.DBID, SQL.SQL_ID, SQL.PX_SERVERS_EXECS_DELTA
  HAVING SUM(SQL.EXECUTIONS_DELTA) >= 0
  ORDER BY CPU_TIME_PER_EXECUTIONS
  ;

  SELECT
      MIN(SNAP_ID) AS START_SNAP_ID
      , MAX(SNAP_ID) AS END_SNAP_ID
      , MIN(BEGIN_INTERVAL_TIME) AS BEGIN_INTERVAL_TIME
      , MAX(END_INTERVAL_TIME) AS END_INTERVAL_TIME
  FROM DBA_HIST_SNAPSHOT
  WHERE BEGIN_INTERVAL_TIME BETWEEN TO_DATE('20230401' || ' 000000', 'YYYYMMDD HH24MISS')
                              AND TO_DATE('20230419' || ' 235959', 'YYYYMMDD HH24MISS')
  ;