
|
 |

|

 |
 Àú¼º´É SQL ÃßÃâ FROM AWR
|
park1q
|
2017-10-12 15:10:04, Á¶È¸ : 7,241, Ãßõ : 1005 |

- 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 |
|
|
 |
|
|
|
|