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문을 찾는 쿼리입니다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
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; |
2 Responses to 특정기간동안 CPU점유율 Top 50인 SQL문을 찾는 쿼리