www.1q.co.kr(park1q.com)

ID :  Password : Auto  

   회원:0명 / 손님:0명
 

 

Technote

자료 및 관리팁

  • DBA Notes
  • Q & A

    북마크
  • Asktom
       (Oracle 의 대표 Forum)
  • Technical Bulltin(KR)
       (기술지원게시판)
  • Dbazine

  •  

     


     저성능 SQL 추출 FROM AWR
    park1q  2009-09-02 14:49:46, 조회 : 5,641, 추천 : 809

    • AWR 에서 SQL 추출시 용이하게 사용할수 있는 SQL 입니다. LAG 함수를 사용했기때문에 오히려 AWR REPORT 에 나오는 COUNT 보다 더 정확하다 할수 있습니다.
    • __sortColumn__ 은 알아서 사용하시면 됩니다.

     
    SELECT *
      FROM( SELECT S.SQL_ID,
                   (SELECT USERNAME FROM DBA_USERS WHERE USER_ID = S.PARSING_SCHEMA_ID) PARSING_SCHEMA_NAME,
                   S.MODULE,
                   to_char(SUBSTR(T.SQL_TEXT,0,50)) SQL_TEXT,
                   EXECUTIONS, -- 5
                   BUFFER_GETS,   -- 6
                   ROUND(BUFFER_GETS / EXECUTIONS) BUFFER_GETS_PER_EXEC,--  7
                   DISK_READS,    -- 8
                   ROUND(DISK_READS / EXECUTIONS) DISK_READS_PER_EXEC,--  9
                   ROUND(CPU_TIME/1000000,2) CPU_TIME,   -- 10
                   ROUND(CPU_TIME / EXECUTIONS / 1000000,2) CPU_TIME_PER_EXEC ,--  11
                   ROUND(ELAPSED_TIME/1000000,2) ELAPSED_TIME,  -- 12
                   ROUND(ELAPSED_TIME / EXECUTIONS / 1000000,2) ELAP_TIME_PER_EXEC,--  13
                   ROUND((CPU_TIME + ELAPSED_TIME)/1000000,2) RUN_TIME, -- 14
                   ROUND((CPU_TIME / EXECUTIONS + ELAPSED_TIME / EXECUTIONS) / 1000000,2) RUN_TIME_PER_EXEC, --15
                   FETCHES,-- 16
                   ROUND(FETCHES / EXECUTIONS) FETCHES_PER_EXEC,--  17
                   SORTS,-- 18
                   ROUND(SORTS / EXECUTIONS) SORTS_PER_EXEC,--  19
                   ROWS_PROCESSED, --  20
                   ROUND(ROWS_PROCESSED / EXECUTIONS) ROWS_PER_EXEC,--  21
                   PARSE_CALLS,--  22
                   ROUND(PARSE_CALLS / EXECUTIONS) PARSE_CALLS_EXEC,--  23
                   IOWAIT,--  24
                   ROUND(IOWAIT / EXECUTIONS) IOWAIT_EXEC,--  25
                   CLWAIT,--  26
                   ROUND(CLWAIT / EXECUTIONS) CLWAIT_EXEC,--  27
                   APWAIT,--  28
                   ROUND(APWAIT / EXECUTIONS) APWAIT_EXEC,--  29
                   CCWAIT,--  30
                   ROUND(CCWAIT / EXECUTIONS) CCWAIT_EXEC,--  31
                   DIRECT_WRITES,--  32
                   ROUND(DIRECT_WRITES / EXECUTIONS) DIRECT_WRITES_EXEC,--  33
                   PLSEXEC_TIME,--  34
                   ROUND(PLSEXEC_TIME / EXECUTIONS) PLSEXEC_TIME_EXEC,--  35
                   ROUND(EXECUTIONS / DATE_INTERVAL * 31,0) MONTH_EXECS,
                   ROUND((BUFFER_GETS / SUM(BUFFER_GETS) OVER()) * 100,2) BUFFER_GETS_SHARE_RATIO,
                   ROUND((DISK_READS / SUM(DISK_READS) OVER()) * 100,2) DISK_READS_SHARE_RATIO,
                   ROUND((CPU_TIME / SUM(CPU_TIME) OVER()) * 100,2) CPU_TIME_SHARE_RATIO,
                   ROUND((ELAPSED_TIME / SUM(ELAPSED_TIME) OVER()) * 100,2) ELAPSED_TIME_SHARE_RATIO
              FROM (SELECT SQL_ID,
                           MIN(PARSING_SCHEMA_ID) PARSING_SCHEMA_ID,
                           MIN(MODULE) MODULE,
                           MIN(DATE_INTERVAL) DATE_INTERVAL,
                           SUM(DECODE(BEF_EXECUTIONS_TOTAL,NULL,EXECUTIONS_DELTA,DECODE(SIGN(EXECUTIONS_TOTAL - BEF_EXECUTIONS_TOTAL),-1,EXECUTIONS_DELTA,EXECUTIONS_TOTAL - BEF_EXECUTIONS_TOTAL ))) EXECUTIONS,
                           SUM(DECODE(BEF_CPU_TIME_TOTAL,NULL,CPU_TIME_DELTA,DECODE(SIGN(CPU_TIME_TOTAL - BEF_CPU_TIME_TOTAL),-1,CPU_TIME_DELTA,CPU_TIME_TOTAL - BEF_CPU_TIME_TOTAL))) CPU_TIME,
                           SUM(DECODE(BEF_ELAPSED_TIME_TOTAL,NULL,ELAPSED_TIME_DELTA,DECODE(SIGN(ELAPSED_TIME_TOTAL - BEF_ELAPSED_TIME_TOTAL),-1,ELAPSED_TIME_DELTA,ELAPSED_TIME_TOTAL - BEF_ELAPSED_TIME_TOTAL ))) ELAPSED_TIME,
                           SUM(DECODE(BEF_BUFFER_GETS_TOTAL,NULL,BUFFER_GETS_DELTA,DECODE(SIGN(BUFFER_GETS_TOTAL - BEF_BUFFER_GETS_TOTAL),-1,BUFFER_GETS_DELTA,BUFFER_GETS_TOTAL - BEF_BUFFER_GETS_TOTAL ))) BUFFER_GETS,
                           SUM(DECODE(BEF_DISK_READS_TOTAL,NULL,DISK_READS_DELTA,DECODE(SIGN(DISK_READS_TOTAL - BEF_DISK_READS_TOTAL),-1,DISK_READS_DELTA,DISK_READS_TOTAL - BEF_DISK_READS_TOTAL ))) DISK_READS,
                           SUM(DECODE(BEF_FETCHES_TOTAL,NULL,FETCHES_DELTA,DECODE(SIGN(FETCHES_TOTAL - BEF_FETCHES_TOTAL),-1,FETCHES_DELTA,FETCHES_TOTAL - BEF_FETCHES_TOTAL ))) FETCHES,
                           SUM(DECODE(BEF_SORTS_TOTAL,NULL,SORTS_DELTA,DECODE(SIGN(SORTS_TOTAL - BEF_SORTS_TOTAL),-1,SORTS_DELTA,SORTS_TOTAL - BEF_SORTS_TOTAL ))) SORTS,
                           SUM(DECODE(BEF_ROWS_PROCESSED_TOTAL,NULL,ROWS_PROCESSED_DELTA,DECODE(SIGN(ROWS_PROCESSED_TOTAL - BEF_ROWS_PROCESSED_TOTAL),-1,ROWS_PROCESSED_DELTA,ROWS_PROCESSED_TOTAL - BEF_ROWS_PROCESSED_TOTAL ))) ROWS_PROCESSED,
                           SUM(DECODE(BEF_PARSE_CALLS_TOTAL,NULL,PARSE_CALLS_DELTA,DECODE(SIGN(PARSE_CALLS_TOTAL - BEF_PARSE_CALLS_TOTAL),-1,PARSE_CALLS_DELTA,PARSE_CALLS_TOTAL - BEF_PARSE_CALLS_TOTAL ))) PARSE_CALLS ,
                           SUM(DECODE(BEF_IOWAIT_TOTAL,NULL,IOWAIT_DELTA,DECODE(SIGN(IOWAIT_TOTAL - BEF_IOWAIT_TOTAL),-1,IOWAIT_DELTA,IOWAIT_TOTAL - BEF_IOWAIT_TOTAL ))) IOWAIT,
                           SUM(DECODE(BEF_CLWAIT_TOTAL,NULL,CLWAIT_DELTA,DECODE(SIGN(CLWAIT_TOTAL - BEF_CLWAIT_TOTAL),-1,CLWAIT_DELTA,CLWAIT_TOTAL - BEF_CLWAIT_TOTAL ))) CLWAIT,
                           SUM(DECODE(BEF_APWAIT_TOTAL,NULL,APWAIT_DELTA,DECODE(SIGN(APWAIT_TOTAL - BEF_APWAIT_TOTAL),-1,APWAIT_DELTA,APWAIT_TOTAL - BEF_APWAIT_TOTAL ))) APWAIT,
                           SUM(DECODE(BEF_CCWAIT_TOTAL,NULL,CCWAIT_DELTA,DECODE(SIGN(CCWAIT_TOTAL - BEF_CCWAIT_TOTAL),-1,CCWAIT_DELTA,CCWAIT_TOTAL - BEF_CCWAIT_TOTAL ))) CCWAIT,
                           SUM(DECODE(BEF_DIRECT_WRITES_TOTAL,NULL,DIRECT_WRITES_DELTA,DECODE(SIGN(DIRECT_WRITES_TOTAL - BEF_DIRECT_WRITES_TOTAL),-1,DIRECT_WRITES_DELTA,DIRECT_WRITES_TOTAL - BEF_DIRECT_WRITES_TOTAL ))) DIRECT_WRITES,
                           SUM(DECODE(BEF_PLSEXEC_TIME_TOTAL,NULL,PLSEXEC_TIME_DELTA,DECODE(SIGN(PLSEXEC_TIME_TOTAL - BEF_PLSEXEC_TIME_TOTAL),-1,PLSEXEC_TIME_DELTA,PLSEXEC_TIME_TOTAL - BEF_PLSEXEC_TIME_TOTAL ))) PLSEXEC_TIME
                      FROM (SELECT S.SNAP_ID,
                                   S.SQL_ID,
                                   S.PARSING_SCHEMA_ID,
                                   S.MODULE,
                                   S.CPU_TIME_TOTAL,
                                   LAG(S.CPU_TIME_TOTAL,1) OVER(PARTITION BY S.SQL_ID ORDER BY S.SNAP_ID) BEF_CPU_TIME_TOTAL ,
                                   S.CPU_TIME_DELTA,
                                   S.EXECUTIONS_TOTAL,
                                   LAG(S.EXECUTIONS_TOTAL,1) OVER(PARTITION BY S.SQL_ID ORDER BY S.SNAP_ID) BEF_EXECUTIONS_TOTAL ,
                                   S.EXECUTIONS_DELTA,
                                   S.ELAPSED_TIME_TOTAL,
                                   LAG(S.ELAPSED_TIME_TOTAL,1) OVER(PARTITION BY S.SQL_ID ORDER BY S.SNAP_ID) BEF_ELAPSED_TIME_TOTAL ,
                                   S.ELAPSED_TIME_DELTA,
                                   S.BUFFER_GETS_TOTAL,
                                   LAG(S.BUFFER_GETS_TOTAL,1) OVER(PARTITION BY S.SQL_ID ORDER BY S.SNAP_ID) BEF_BUFFER_GETS_TOTAL ,
                                   S.BUFFER_GETS_DELTA,
                                   S.DISK_READS_TOTAL,
                                   LAG(S.DISK_READS_TOTAL,1) OVER(PARTITION BY S.SQL_ID ORDER BY S.SNAP_ID) BEF_DISK_READS_TOTAL ,
                                   S.DISK_READS_DELTA,
                                   S.SORTS_TOTAL,
                                   LAG(S.SORTS_TOTAL,1) OVER(PARTITION BY S.SQL_ID ORDER BY S.SNAP_ID) BEF_SORTS_TOTAL ,
                                   S.SORTS_DELTA,
                                   S.FETCHES_TOTAL,
                                   LAG(S.FETCHES_TOTAL,1) OVER(PARTITION BY S.SQL_ID ORDER BY S.SNAP_ID) BEF_FETCHES_TOTAL ,
                                   S.FETCHES_DELTA,
                                   S.ROWS_PROCESSED_TOTAL,
                                   LAG(S.ROWS_PROCESSED_TOTAL,1) OVER(PARTITION BY S.SQL_ID ORDER BY S.SNAP_ID) BEF_ROWS_PROCESSED_TOTAL ,
                                   S.ROWS_PROCESSED_DELTA,
                                   S.PARSE_CALLS_TOTAL,
                                   LAG(S.PARSE_CALLS_TOTAL,1) OVER(PARTITION BY S.SQL_ID ORDER BY S.SNAP_ID) BEF_PARSE_CALLS_TOTAL ,
                                   S.PARSE_CALLS_DELTA,
                                   S.IOWAIT_TOTAL,
                                   LAG(S.IOWAIT_TOTAL,1) OVER(PARTITION BY S.SQL_ID ORDER BY S.SNAP_ID) BEF_IOWAIT_TOTAL ,
                                   S.IOWAIT_DELTA,
                                   S.CLWAIT_TOTAL,
                                   LAG(S.CLWAIT_TOTAL,1) OVER(PARTITION BY S.SQL_ID ORDER BY S.SNAP_ID) BEF_CLWAIT_TOTAL ,
                                   S.CLWAIT_DELTA,
                                   S.APWAIT_TOTAL,
                                   LAG(S.APWAIT_TOTAL,1) OVER(PARTITION BY S.SQL_ID ORDER BY S.SNAP_ID) BEF_APWAIT_TOTAL ,
                                   S.APWAIT_DELTA,
                                   S.CCWAIT_TOTAL,
                                   LAG(S.CCWAIT_TOTAL,1) OVER(PARTITION BY S.SQL_ID ORDER BY S.SNAP_ID) BEF_CCWAIT_TOTAL ,
                                   S.CCWAIT_DELTA,
                                   S.DIRECT_WRITES_TOTAL,
                                   LAG(S.DIRECT_WRITES_TOTAL,1) OVER(PARTITION BY S.SQL_ID ORDER BY S.SNAP_ID) BEF_DIRECT_WRITES_TOTAL ,
                                   S.DIRECT_WRITES_DELTA,
                                   S.PLSEXEC_TIME_TOTAL,
                                   LAG(S.PLSEXEC_TIME_TOTAL,1) OVER(PARTITION BY S.SQL_ID ORDER BY S.SNAP_ID) BEF_PLSEXEC_TIME_TOTAL ,
                                   S.PLSEXEC_TIME_DELTA,
                                   DATE_INTERVAL
                              FROM (SELECT MIN(SN.SNAP_ID) MIN_SNAP_ID,
                                           MAX(SN.SNAP_ID) MAX_SNAP_ID,
                                           MAX(I.INSTANCE_NUMBER) INSTANCE_NUMBER,
                                           MAX(D.DBID) DBID,
                                           TRUNC(SYSDATE) + (MAX(SN.END_INTERVAL_TIME) - MIN(SN.BEGIN_INTERVAL_TIME)) - TRUNC(SYSDATE) DATE_INTERVAL
                                      FROM DBA_HIST_SNAPSHOT SN,
                                           V$INSTANCE I,
                                           V$DATABASE D
                                     WHERE SN.SNAP_ID BETWEEN :1 AND :2
                                   ) SN ,
                                   DBA_HIST_SQLSTAT S
                             WHERE S.SNAP_ID BETWEEN SN.MIN_SNAP_ID AND SN.MAX_SNAP_ID
                               AND S.DBID = SN.DBID
                               AND S.INSTANCE_NUMBER = SN.INSTANCE_NUMBER
                           )
                     GROUP BY
                           SQL_ID
                   ) S,
                   DBA_HIST_SQLTEXT T
             WHERE S.SQL_ID = T.sql_id
               AND EXECUTIONS > 0
             ORDER BY __sortColumn__ DESC
           )
     WHERE ROWNUM <= :3


      추천하기 프린트   목록보기

    Copyright 1999-2018 Zeroboard

     

     
     
    [Today:5 / Total:155151]    Design by p@rk1q