Oracle 10g 이상에서 기본적으로 제공하는 view를 활용하여, CPU 점유율이 큰 쿼리를 찾아서 튜닝을 하는데 목적이 있는 쿼리입니다.

활용하는 View는 아래 3가지 이며
DBA_HIST_SQLSTAT : SQL Historical Statistics Information
DBA_HIST_SNAPSHOT : SnapShot Information
DBA_HIST_SQLTEXT : SQL Text

특정기간동안 CPU점유율 Top 50인 SQL문을 찾는 쿼리입니다.

SELECT X.SQL_ID
      ,X.CPU_TIME
      ,X.EXECUTIONS
      ,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
                ,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('201205140600','YYYYMMDDHH24MI')
                                               AND TO_DATE('201205150600','YYYYMMDDHH24MI')
                ) 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
      ,DBA_HIST_SQLTEXT D
 WHERE D.SQL_ID = X.SQL_ID
   AND D.DBID = X.DBID
   AND ROWNUM <= 50;