Oracle SQL Stat 조회 스크립트
WITH A AS ( SELECT /*+ MATERIALIZE LEADING(DBI SNAP) USE_HASH(SQLS TXT) NO_MERGE(SQLS) NO_MERGE(TXT) */ SQLS.DBID, SQLS.SQL_ID, SQLS.OPTIMIZER_MODE, SQLS.MODULE, SUM (SQLS.FETCHES_DELTA) FETCHES, SUM (SQLS.EXECUTIONS_DELTA) EXECUTIONS, SUM (SQLS.SORTS_DELTA) SORTS, SUM (SQLS.DISK_READS_DELTA) DISK_READS, SUM (SQLS.BUFFER_GETS_DELTA) BUFFER_GETS, SUM (SQLS.ROWS_PROCESSED_DELTA) ROWS_PROCESSED, SUM (SQLS.CPU_TIME_DELTA) / 1000000 CPU_TIME, SUM (SQLS.ELAPSED_TIME_DELTA) / 1000000 ELAPSED_TIME, SUM (SQLS.IOWAIT_DELTA) / 1000000 IOWAIT, SUM (SQLS.CLWAIT_DELTA) / 1000000 CLWAIT, SUM (SQLS.APWAIT_DELTA) / 1000000 APWAIT, SUM (SQLS.CCWAIT_DELTA) / 1000000 CCWAIT, SUM (DIRECT_WRITES_DELTA) DIRECT_WRITES, NULL RNUM FROM (SELECT DI.DBID, DI.INSTANCE_NUMBER, DI.STARTUP_TIME FROM DBA_HIST_DATABASE_INSTANCE DI WHERE DI.DBID = (SELECT dbid FROM v$database) --변경 AND DI.INSTANCE_NUMBER = (SELECT INSTANCE_NUMBER FROM v$instance) --변경 AND ROWNUM <= 1) DBI, DBA_HIST_SNAPSHOT SNAP, DBA_HIST_SQLSTAT SQLS WHERE DBI.DBID = SNAP.DBID AND DBI.INSTANCE_NUMBER = SNAP.INSTANCE_NUMBER AND SNAP.SNAP_ID >= :3 + 1 AND SNAP.SNAP_ID <= :4 AND SNAP.DBID = SQLS.DBID AND SNAP.INSTANCE_NUMBER = SQLS.INSTANCE_NUMBER AND SNAP.SNAP_ID = SQLS.SNAP_ID GROUP BY SQLS.DBID, SQLS.SQL_ID, SQLS.OPTIMIZER_MODE, SQLS.MODULE HAVING SUM (SQLS.EXECUTIONS_DELTA) > 0), A_TOT AS (SELECT 0 DBID, 'TOT' SQL_ID, NULL OPTIMIZER_MODE, NULL MODULE, MAX (STAT.EXECUTIONS) EXECUTIONS, SUM (A.FETCHES) FETCHES, (MAX (STAT.SORTS_D) + MAX (STAT.SORTS_M)) SORTS, MAX (STAT.BUFFER_GETS) BUFFER_GETS, MAX (STAT.DISK_READS) DISK_READS, SUM (A.ROWS_PROCESSED) ROWS_PROCESSED, MAX (TIME.DB_CPU) CPU_TIME, MAX (TIME.DB_TIME) ELAPSED_TIME, MAX (STAT.BUFFER_GETS) / MAX (STAT.EXECUTIONS) BUF_EXEC, MAX (STAT.DISK_READS) / MAX (STAT.EXECUTIONS) DISK_EXEC, SUM (A.ROWS_PROCESSED) / SUM (A.EXECUTIONS) ROWS_EXEC, MAX (TIME.DB_CPU) / MAX (STAT.EXECUTIONS) CPU_EXEC, MAX (TIME.DB_TIME) / MAX (STAT.EXECUTIONS) ELAP_EXEC, MAX (STAT.IOWAIT) IOWAIT, MAX (STAT.CLWAIT) CLWAIT, MAX (STAT.APWAIT) APWAIT, MAX (STAT.CCWAIT) CCWAIT, COUNT (DISTINCT SQL_ID) RNUM FROM A, (SELECT MAX (DECODE (STAT_NAME, 'execute count', TOT_VALUE)) EXECUTIONS, MAX ( DECODE (STAT_NAME, 'session logical reads', TOT_VALUE)) BUFFER_GETS, MAX (DECODE (STAT_NAME, 'physical reads', TOT_VALUE)) DISK_READS, MAX (DECODE (STAT_NAME, 'sorts (disk)', TOT_VALUE)) SORTS_D, MAX (DECODE (STAT_NAME, 'sorts (memory)', TOT_VALUE)) SORTS_M, MAX ( DECODE (STAT_NAME, 'cluster wait time', TOT_VALUE / 100)) CLWAIT, MAX ( DECODE (STAT_NAME, 'application wait time', TOT_VALUE / 100)) APWAIT, MAX ( DECODE (STAT_NAME, 'concurrency wait time', TOT_VALUE / 100)) CCWAIT, MAX ( DECODE (STAT_NAME, 'user I/O wait time', TOT_VALUE / 100)) IOWAIT FROM ( SELECT STAT_NAME, SUM (TOT_VALUE) TOT_VALUE FROM (SELECT STAT_NAME, TO_CHAR (SNAP_TIME_1, 'HH24:MI:SS') SNAP_TIME, DECODE (SNAP_TIME_2, NULL, 0, ROUND ( (VALUE_1 - VALUE_2))) TOT_VALUE, (EXTRACT ( DAY FROM SNAP_TIME_1 - SNAP_TIME_2) * 86400 + EXTRACT ( HOUR FROM SNAP_TIME_1 - SNAP_TIME_2) * 3600 + EXTRACT ( MINUTE FROM SNAP_TIME_1 - SNAP_TIME_2) * 60 + EXTRACT ( SECOND FROM SNAP_TIME_1 - SNAP_TIME_2)) TOT_TIME_SEC FROM ( SELECT /*+ LEADING(DBI) USE_HASH(SNAP STAT) NO_MERGE(SNAP) NO_MERGE(STAT) */ SNAP.END_INTERVAL_TIME SNAP_TIME_1, STAT.STAT_NAME, STAT.VALUE VALUE_1, LAG ( STAT.VALUE) OVER (PARTITION BY STAT_NAME ORDER BY SNAP.SNAP_ID) VALUE_2, LAG ( SNAP.END_INTERVAL_TIME) OVER (PARTITION BY STAT_NAME ORDER BY SNAP.SNAP_ID) SNAP_TIME_2, SNAP.SNAP_ID FROM (SELECT /*+ NO_MERGE */ DI.DBID, DI.INSTANCE_NUMBER, DI.STARTUP_TIME FROM DBA_HIST_DATABASE_INSTANCE DI WHERE DI.DBID = (SELECT dbid FROM v$database) --변경 AND DI.INSTANCE_NUMBER = (SELECT INSTANCE_NUMBER FROM v$instance) --변경 AND ROWNUM <= 1) DBI, DBA_HIST_SNAPSHOT SNAP, DBA_HIST_SYSSTAT STAT WHERE DBI.DBID = SNAP.DBID AND DBI.INSTANCE_NUMBER = SNAP.INSTANCE_NUMBER AND SNAP.SNAP_ID >= :7 AND SNAP.SNAP_ID <= :8 AND SNAP.DBID = STAT.DBID AND SNAP.INSTANCE_NUMBER = STAT.INSTANCE_NUMBER AND SNAP.SNAP_ID = STAT.SNAP_ID AND STAT.STAT_NAME IN ('execute count', 'session logical reads', 'sorts (disk)', 'sorts (memory)', 'physical reads', 'cluster wait time', 'application wait time', 'user I/O wait time', 'concurrency wait time') ORDER BY SNAP.SNAP_ID)) GROUP BY STAT_NAME)) STAT, (SELECT MAX ( DECODE (STAT_NAME, 'DB CPU', TOT_VALUE / 1000000)) DB_CPU, MAX ( DECODE (STAT_NAME, 'DB time', TOT_VALUE / 1000000)) DB_TIME FROM ( SELECT STAT_NAME, SUM (TOT_VALUE) TOT_VALUE FROM (SELECT STAT_NAME, TO_CHAR (SNAP_TIME_1, 'HH24:MI:SS') SNAP_TIME, DECODE (SNAP_TIME_2, NULL, 0, ROUND ( (VALUE_1 - VALUE_2))) TOT_VALUE, (EXTRACT ( DAY FROM SNAP_TIME_1 - SNAP_TIME_2) * 86400 + EXTRACT ( HOUR FROM SNAP_TIME_1 - SNAP_TIME_2) * 3600 + EXTRACT ( MINUTE FROM SNAP_TIME_1 - SNAP_TIME_2) * 60 + EXTRACT ( SECOND FROM SNAP_TIME_1 - SNAP_TIME_2)) TOT_TIME_SEC FROM ( SELECT /*+ LEADING(DBI) USE_HASH(SNAP STAT) NO_MERGE(SNAP) NO_MERGE(STAT) */ SNAP.END_INTERVAL_TIME SNAP_TIME_1, STAT.STAT_NAME, STAT.VALUE VALUE_1, LAG ( STAT.VALUE) OVER (PARTITION BY STAT_NAME ORDER BY SNAP.SNAP_ID) VALUE_2, LAG ( SNAP.END_INTERVAL_TIME) OVER (PARTITION BY STAT_NAME ORDER BY SNAP.SNAP_ID) SNAP_TIME_2, SNAP.SNAP_ID FROM (SELECT /*+ NO_MERGE */ DI.DBID, DI.INSTANCE_NUMBER, DI.STARTUP_TIME FROM DBA_HIST_DATABASE_INSTANCE DI WHERE DI.DBID = (SELECT dbid FROM v$database) --변경 AND DI.INSTANCE_NUMBER = (SELECT INSTANCE_NUMBER FROM v$instance) --변경 AND ROWNUM <= 1) DBI, DBA_HIST_SNAPSHOT SNAP, DBA_HIST_SYS_TIME_MODEL STAT WHERE DBI.DBID = SNAP.DBID AND DBI.INSTANCE_NUMBER = SNAP.INSTANCE_NUMBER AND SNAP.SNAP_ID >= :11 AND SNAP.SNAP_ID <= :12 AND SNAP.DBID = STAT.DBID AND SNAP.INSTANCE_NUMBER = STAT.INSTANCE_NUMBER AND SNAP.SNAP_ID = STAT.SNAP_ID AND STAT.STAT_NAME IN ('DB CPU', 'DB time') ORDER BY SNAP.SNAP_ID)) GROUP BY STAT_NAME)) TIME), A_ET AS (SELECT * FROM (SELECT 'ELAPSED TIME ALL' VIEWTYPE, DBID, SQL_ID, OPTIMIZER_MODE, MODULE, EXECUTIONS, FETCHES, SORTS, BUFFER_GETS, DISK_READS, ROWS_PROCESSED, CPU_TIME, ELAPSED_TIME, BUFFER_GETS / EXECUTIONS BUF_EXEC, DISK_READS / EXECUTIONS DISK_EXEC, ROWS_PROCESSED / EXECUTIONS ROWS_EXEC, CPU_TIME / EXECUTIONS CPU_EXEC, ELAPSED_TIME / EXECUTIONS ELAP_EXEC, IOWAIT, CLWAIT, APWAIT, CCWAIT, ROW_NUMBER () OVER ( ORDER BY ELAPSED_TIME DESC, CPU_TIME DESC, ROWNUM) RNUM FROM A UNION ALL SELECT 'ELAPSED TIME ALL' VIEWTYPE, NULL DBID, NULL SQL_ID, NULL OPTIMIZER_MODE, NULL MODULE, NULL EXECUTIONS, NULL FETCHES, NULL SORTS, NULL BUFFER_GETS, NULL DISK_READS, NULL ROWS_PROCESSED, NULL CPU_TIME, NULL ELAPSED_TIME, NULL BUF_EXEC, NULL DISK_EXEC, NULL ROWS_EXEC, NULL CPU_EXEC, NULL ELAP_EXEC, NULL IOWAIT, NULL CLWAIT, NULL APWAIT, NULL CCWAIT, NULL RNUM FROM A WHERE ROWNUM <= :13) WHERE RNUM <= :14 AND ROWNUM <= :15), A_CT AS (SELECT * FROM (SELECT 'CPU TIME ALL' VIEWTYPE, DBID, SQL_ID, OPTIMIZER_MODE, MODULE, EXECUTIONS, FETCHES, SORTS, BUFFER_GETS, DISK_READS, ROWS_PROCESSED, CPU_TIME, ELAPSED_TIME, BUFFER_GETS / EXECUTIONS BUF_EXEC, DISK_READS / EXECUTIONS DISK_EXEC, ROWS_PROCESSED / EXECUTIONS ROWS_EXEC, CPU_TIME / EXECUTIONS CPU_EXEC, ELAPSED_TIME / EXECUTIONS ELAP_EXEC, IOWAIT, CLWAIT, APWAIT, CCWAIT, ROW_NUMBER () OVER ( ORDER BY CPU_TIME DESC, BUFFER_GETS DESC, ROWNUM) RNUM FROM A UNION ALL SELECT 'CPU TIME ALL' VIEWTYPE, NULL DBID, NULL SQL_ID, NULL OPTIMIZER_MODE, NULL MODULE, NULL EXECUTIONS, NULL FETCHES, NULL SORTS, NULL BUFFER_GETS, NULL DISK_READS, NULL ROWS_PROCESSED, NULL CPU_TIME, NULL ELAPSED_TIME, NULL BUF_EXEC, NULL DISK_EXEC, NULL ROWS_EXEC, NULL CPU_EXEC, NULL ELAP_EXEC, NULL IOWAIT, NULL CLWAIT, NULL APWAIT, NULL CCWAIT, NULL RNUM FROM A WHERE ROWNUM <= :16) WHERE RNUM <= :17 AND ROWNUM <= :18), A_BG AS (SELECT * FROM (SELECT 'BUFFER GETS ALL' VIEWTYPE, DBID, SQL_ID, OPTIMIZER_MODE, MODULE, EXECUTIONS, FETCHES, SORTS, BUFFER_GETS, DISK_READS, ROWS_PROCESSED, CPU_TIME, ELAPSED_TIME, BUFFER_GETS / EXECUTIONS BUF_EXEC, DISK_READS / EXECUTIONS DISK_EXEC, ROWS_PROCESSED / EXECUTIONS ROWS_EXEC, CPU_TIME / EXECUTIONS CPU_EXEC, ELAPSED_TIME / EXECUTIONS ELAP_EXEC, IOWAIT, CLWAIT, APWAIT, CCWAIT, ROW_NUMBER () OVER ( ORDER BY BUFFER_GETS DESC, CPU_TIME DESC, ROWNUM) RNUM FROM A UNION ALL SELECT 'BUFFER GETS ALL' VIEWTYPE, NULL DBID, NULL SQL_ID, NULL OPTIMIZER_MODE, NULL MODULE, NULL EXECUTIONS, NULL FETCHES, NULL SORTS, NULL BUFFER_GETS, NULL DISK_READS, NULL ROWS_PROCESSED, NULL CPU_TIME, NULL ELAPSED_TIME, NULL BUF_EXEC, NULL DISK_EXEC, NULL ROWS_EXEC, NULL CPU_EXEC, NULL ELAP_EXEC, NULL IOWAIT, NULL CLWAIT, NULL APWAIT, NULL CCWAIT, NULL RNUM FROM A WHERE ROWNUM <= :19) WHERE RNUM <= :20 AND ROWNUM <= :21), A_DR AS (SELECT * FROM (SELECT 'DISK READS ALL' VIEWTYPE, DBID, SQL_ID, OPTIMIZER_MODE, MODULE, EXECUTIONS, FETCHES, SORTS, BUFFER_GETS, DISK_READS, ROWS_PROCESSED, CPU_TIME, ELAPSED_TIME, BUFFER_GETS / EXECUTIONS BUF_EXEC, DISK_READS / EXECUTIONS DISK_EXEC, ROWS_PROCESSED / EXECUTIONS ROWS_EXEC, CPU_TIME / EXECUTIONS CPU_EXEC, ELAPSED_TIME / EXECUTIONS ELAP_EXEC, IOWAIT, CLWAIT, APWAIT, CCWAIT, ROW_NUMBER () OVER (ORDER BY DISK_READS DESC, IOWAIT DESC, ROWNUM) RNUM FROM A UNION ALL SELECT 'DISK READS ALL' VIEWTYPE, NULL DBID, NULL SQL_ID, NULL OPTIMIZER_MODE, NULL MODULE, NULL EXECUTIONS, NULL FETCHES, NULL SORTS, NULL BUFFER_GETS, NULL DISK_READS, NULL ROWS_PROCESSED, NULL CPU_TIME, NULL ELAPSED_TIME, NULL BUF_EXEC, NULL DISK_EXEC, NULL ROWS_EXEC, NULL CPU_EXEC, NULL ELAP_EXEC, NULL IOWAIT, NULL CLWAIT, NULL APWAIT, NULL CCWAIT, NULL RNUM FROM A WHERE ROWNUM <= :22) WHERE RNUM <= :23 AND ROWNUM <= :24), A_CL AS (SELECT * FROM (SELECT 'CLUSTER WAIT ALL' VIEWTYPE, DBID, SQL_ID, OPTIMIZER_MODE, MODULE, EXECUTIONS, FETCHES, SORTS, BUFFER_GETS, DISK_READS, ROWS_PROCESSED, CPU_TIME, ELAPSED_TIME, BUFFER_GETS / EXECUTIONS BUF_EXEC, DISK_READS / EXECUTIONS DISK_EXEC, ROWS_PROCESSED / EXECUTIONS ROWS_EXEC, CPU_TIME / EXECUTIONS CPU_EXEC, ELAPSED_TIME / EXECUTIONS ELAP_EXEC, IOWAIT, CLWAIT, APWAIT, CCWAIT, ROW_NUMBER () OVER (ORDER BY CLWAIT DESC, DISK_READS DESC, ROWNUM) RNUM FROM A UNION ALL SELECT 'CLUSTER WAIT ALL' VIEWTYPE, NULL DBID, NULL SQL_ID, NULL OPTIMIZER_MODE, NULL MODULE, NULL EXECUTIONS, NULL FETCHES, NULL SORTS, NULL BUFFER_GETS, NULL DISK_READS, NULL ROWS_PROCESSED, NULL CPU_TIME, NULL ELAPSED_TIME, NULL BUF_EXEC, NULL DISK_EXEC, NULL ROWS_EXEC, NULL CPU_EXEC, NULL ELAP_EXEC, NULL IOWAIT, NULL CLWAIT, NULL APWAIT, NULL CCWAIT, NULL RNUM FROM A WHERE ROWNUM <= :25) WHERE RNUM <= :26 AND ROWNUM <= :27), A_CC AS (SELECT * FROM (SELECT 'CONSISTENT WAIT ALL' VIEWTYPE, DBID, SQL_ID, OPTIMIZER_MODE, MODULE, EXECUTIONS, FETCHES, SORTS, BUFFER_GETS, DISK_READS, ROWS_PROCESSED, CPU_TIME, ELAPSED_TIME, BUFFER_GETS / EXECUTIONS BUF_EXEC, DISK_READS / EXECUTIONS DISK_EXEC, ROWS_PROCESSED / EXECUTIONS ROWS_EXEC, CPU_TIME / EXECUTIONS CPU_EXEC, ELAPSED_TIME / EXECUTIONS ELAP_EXEC, IOWAIT, CLWAIT, APWAIT, CCWAIT, ROW_NUMBER () OVER (ORDER BY CCWAIT DESC, BUFFER_GETS DESC, ROWNUM) RNUM FROM A UNION ALL SELECT 'CONSISTENT WAIT ALL' VIEWTYPE, NULL DBID, NULL SQL_ID, NULL OPTIMIZER_MODE, NULL MODULE, NULL EXECUTIONS, NULL FETCHES, NULL SORTS, NULL BUFFER_GETS, NULL DISK_READS, NULL ROWS_PROCESSED, NULL CPU_TIME, NULL ELAPSED_TIME, NULL BUF_EXEC, NULL DISK_EXEC, NULL ROWS_EXEC, NULL CPU_EXEC, NULL ELAP_EXEC, NULL IOWAIT, NULL CLWAIT, NULL APWAIT, NULL CCWAIT, NULL RNUM FROM A WHERE ROWNUM <= :28) WHERE RNUM <= :29 AND ROWNUM <= :30), A_IW AS (SELECT * FROM (SELECT 'IO WAIT ALL' VIEWTYPE, DBID, SQL_ID, OPTIMIZER_MODE, MODULE, EXECUTIONS, FETCHES, SORTS, BUFFER_GETS, DISK_READS, ROWS_PROCESSED, CPU_TIME, ELAPSED_TIME, BUFFER_GETS / EXECUTIONS BUF_EXEC, DISK_READS / EXECUTIONS DISK_EXEC, ROWS_PROCESSED / EXECUTIONS ROWS_EXEC, CPU_TIME / EXECUTIONS CPU_EXEC, ELAPSED_TIME / EXECUTIONS ELAP_EXEC, IOWAIT, CLWAIT, APWAIT, CCWAIT, ROW_NUMBER () OVER (ORDER BY IOWAIT DESC, DISK_READS DESC, ROWNUM) RNUM FROM A UNION ALL SELECT 'IO WAIT ALL' VIEWTYPE, NULL DBID, NULL SQL_ID, NULL OPTIMIZER_MODE, NULL MODULE, NULL EXECUTIONS, NULL FETCHES, NULL SORTS, NULL BUFFER_GETS, NULL DISK_READS, NULL ROWS_PROCESSED, NULL CPU_TIME, NULL ELAPSED_TIME, NULL BUF_EXEC, NULL DISK_EXEC, NULL ROWS_EXEC, NULL CPU_EXEC, NULL ELAP_EXEC, NULL IOWAIT, NULL CLWAIT, NULL APWAIT, NULL CCWAIT, NULL RNUM FROM A WHERE ROWNUM <= :31) WHERE RNUM <= :32 AND ROWNUM <= :33), A_AW AS (SELECT * FROM (SELECT 'LOCK WAIT ALL' VIEWTYPE, DBID, SQL_ID, OPTIMIZER_MODE, MODULE, EXECUTIONS, FETCHES, SORTS, BUFFER_GETS, DISK_READS, ROWS_PROCESSED, CPU_TIME, ELAPSED_TIME, BUFFER_GETS / EXECUTIONS BUF_EXEC, DISK_READS / EXECUTIONS DISK_EXEC, ROWS_PROCESSED / EXECUTIONS ROWS_EXEC, CPU_TIME / EXECUTIONS CPU_EXEC, ELAPSED_TIME / EXECUTIONS ELAP_EXEC, IOWAIT, CLWAIT, APWAIT, CCWAIT, ROW_NUMBER () OVER (ORDER BY APWAIT DESC, CPU_TIME DESC, ROWNUM) RNUM FROM A UNION ALL SELECT 'LOCK WAIT ALL' VIEWTYPE, NULL DBID, NULL SQL_ID, NULL OPTIMIZER_MODE, NULL MODULE, NULL EXECUTIONS, NULL FETCHES, NULL SORTS, NULL BUFFER_GETS, NULL DISK_READS, NULL ROWS_PROCESSED, NULL CPU_TIME, NULL ELAPSED_TIME, NULL BUF_EXEC, NULL DISK_EXEC, NULL ROWS_EXEC, NULL CPU_EXEC, NULL ELAP_EXEC, NULL IOWAIT, NULL CLWAIT, NULL APWAIT, NULL CCWAIT, NULL RNUM FROM A WHERE ROWNUM <= :34) WHERE RNUM <= :35 AND ROWNUM <= :36), A_EC AS (SELECT * FROM (SELECT 'EXEC CNT ALL' VIEWTYPE, DBID, SQL_ID, OPTIMIZER_MODE, MODULE, EXECUTIONS, FETCHES, SORTS, BUFFER_GETS, DISK_READS, ROWS_PROCESSED, CPU_TIME, ELAPSED_TIME, BUFFER_GETS / EXECUTIONS BUF_EXEC, DISK_READS / EXECUTIONS DISK_EXEC, ROWS_PROCESSED / EXECUTIONS ROWS_EXEC, CPU_TIME / EXECUTIONS CPU_EXEC, ELAPSED_TIME / EXECUTIONS ELAP_EXEC, IOWAIT, CLWAIT, APWAIT, CCWAIT, ROW_NUMBER () OVER ( ORDER BY EXECUTIONS DESC, CPU_TIME DESC, ROWNUM) RNUM FROM A UNION ALL SELECT 'EXEC CNT ALL' VIEWTYPE, NULL DBID, NULL SQL_ID, NULL OPTIMIZER_MODE, NULL MODULE, NULL EXECUTIONS, NULL FETCHES, NULL SORTS, NULL BUFFER_GETS, NULL DISK_READS, NULL ROWS_PROCESSED, NULL CPU_TIME, NULL ELAPSED_TIME, NULL BUF_EXEC, NULL DISK_EXEC, NULL ROWS_EXEC, NULL CPU_EXEC, NULL ELAP_EXEC, NULL IOWAIT, NULL CLWAIT, NULL APWAIT, NULL CCWAIT, NULL RNUM FROM A WHERE ROWNUM <= :37) WHERE RNUM <= :38 AND ROWNUM <= :39) SELECT /*+ OPT_PARAM('_gby_hash_aggregation_enabled','TRUE') OPT_PARAM('_optimizer_distinct_agg_transform','FALSE') BUG_9002336 */ SQLSTAT.VIEWTYPE, SQLSTAT.SQL_ID, SQLSTAT.OPTIMIZER_MODE, SQLSTAT.MODULE, TO_CHAR (SQLSTAT.EXECUTIONS, 'FM999,999,999,999,999,999') EXECUTIONS, TO_CHAR (SQLSTAT.FETCHES, 'FM999,999,999,999,999,999') FETCHES, TO_CHAR (SQLSTAT.SORTS, 'FM999,999,999,999,999,999') SORTS, TO_CHAR (SQLSTAT.BUFFER_GETS, 'FM999,999,999,999,999,999') BUFFER_GETS, TO_CHAR (SQLSTAT.DISK_READS, 'FM999,999,999,999,999,999') DISK_READS, TO_CHAR (SQLSTAT.ROWS_PROCESSED, 'FM999,999,999,999,999,999') ROWS_PROCESSED, TO_CHAR (SQLSTAT.CPU_TIME, 'FM999,999,999,999,999,999.9') CPU_TIME, TO_CHAR (SQLSTAT.ELAPSED_TIME, 'FM999,999,999,999,999,999.9') ELAPSED_TIME, TO_CHAR (SQLSTAT.BUF_EXEC, 'FM999,999,999,999,999,999.9') BUF_EXEC, TO_CHAR (SQLSTAT.DISK_EXEC, 'FM999,999,999,999,999,999.9') DISK_EXEC, TO_CHAR (SQLSTAT.ROWS_EXEC, 'FM999,999,999,999,999,999.9') ROWS_EXEC, TO_CHAR (SQLSTAT.CPU_EXEC, 'FM999,999,999,999,999.999') CPU_EXEC, TO_CHAR (SQLSTAT.ELAP_EXEC, 'FM999,999,999,999,999.999') ELAP_EXEC, TO_CHAR (SQLSTAT.IOWAIT, 'FM999,999,999,999,999,999.9') IOWAIT, TO_CHAR (SQLSTAT.CLWAIT, 'FM999,999,999,999,999,999.9') CLWAIT, TO_CHAR (SQLSTAT.APWAIT, 'FM999,999,999,999,999,999.9') APWAIT, TO_CHAR (SQLSTAT.CCWAIT, 'FM999,999,999,999,999,999.9') CCWAIT, SQLSTAT.RNUM, DBMS_LOB.SUBSTR (SQLT.SQL_TEXT, 3000, 1) SQL_TEXT, NULL SQL_PLAN, (SELECT 'Table Count:' || COUNT ( DISTINCT CASE WHEN OBJECT_ALIAS IS NOT NULL AND OPERATION NOT IN ('VIEW') THEN OBJECT_ALIAS END) || CHR (10) || 'View Count:' || COUNT ( DISTINCT CASE WHEN OBJECT_ALIAS IS NOT NULL AND OPERATION IN ('VIEW') THEN OBJECT_ALIAS END) || CHR (10) || 'Window Func Count:' || COUNT (CASE WHEN OPERATION IN ('WINDOW') THEN OPTIONS END) || CHR (10) || 'Query Block Count:' || COUNT (DISTINCT QBLOCK_NAME) || CHR (10) || 'TIME:' || MAX (TIME) || CHR (10) || 'COST:' || TO_CHAR (MAX (COST), 'FM999,999,999') || CHR (10) || 'CPU COST:' || TO_CHAR (MAX (CPU_COST), 'FM999,999,999,999,999') || CHR (10) || 'IO COST:' || TO_CHAR (MAX (IO_COST), 'FM999,999,999,999') FROM V$SQL_PLAN WHERE SQL_ID = SQLSTAT.SQL_ID) PLAN_COST1, (SELECT 'Table Count:' || COUNT ( DISTINCT CASE WHEN OBJECT_ALIAS IS NOT NULL AND OPERATION NOT IN ('VIEW') THEN OBJECT_ALIAS END) || CHR (10) || 'View Count:' || COUNT ( DISTINCT CASE WHEN OBJECT_ALIAS IS NOT NULL AND OPERATION IN ('VIEW') THEN OBJECT_ALIAS END) || CHR (10) || 'Window Func Count:' || COUNT (CASE WHEN OPERATION IN ('WINDOW') THEN OPTIONS END) || CHR (10) || 'Query Block Count:' || COUNT (DISTINCT QBLOCK_NAME) || CHR (10) || 'TIME:' || MAX (TIME) || CHR (10) || 'COST:' || TO_CHAR (MAX (COST), 'FM999,999,999') || CHR (10) || 'CPU COST:' || TO_CHAR (MAX (CPU_COST), 'FM999,999,999,999,999') || CHR (10) || 'IO COST:' || TO_CHAR (MAX (IO_COST), 'FM999,999,999,999') FROM DBA_HIST_SQL_PLAN WHERE SQL_ID = SQLSTAT.SQL_ID AND DBID = SQLSTAT.DBID AND TIMESTAMP IN (SELECT MAX (TIMESTAMP) FROM DBA_HIST_SQL_PLAN WHERE SQL_ID = SQLSTAT.SQL_ID AND DBID = SQLSTAT.DBID)) PLAN_COST2, NULL BIND_VALUE, NULL SQL_PROFILE FROM (SELECT * FROM A_ET UNION ALL SELECT 'ELAPSED TIME ALL' VIEWTYPE, 0 DBID, 'ETC' SQL_ID, NULL OPTIMIZER_MODE, 'ETC' MODULE, V2.EXECUTIONS - V1.EXECUTIONS EXECUTIONS, V2.FETCHES - V1.FETCHES FETCHES, V2.SORTS - V1.SORTS SORTS, V2.BUFFER_GETS - V1.BUFFER_GETS BUFFER_GETS, V2.DISK_READS - V1.DISK_READS DISK_READS, V2.ROWS_PROCESSED - V1.ROWS_PROCESSED ROWS_PROCESSED, V2.CPU_TIME - V1.CPU_TIME CPU_TIME, V2.ELAPSED_TIME - V1.ELAPSED_TIME ELAPSED_TIME, 0 BUF_EXEC, 0 DISK_EXEC, 0 ROWS_EXEC, 0 CPU_EXEC, 0 ELAP_EXEC, V2.IOWAIT - V1.IOWAIT IOWAIT, V2.CLWAIT - V1.CLWAIT CLWAIT, V2.APWAIT - V1.APWAIT APWAIT, V2.CCWAIT - V1.CCWAIT CCWAIT, NULL RNUM FROM (SELECT SUM (EXECUTIONS) EXECUTIONS, SUM (FETCHES) FETCHES, SUM (SORTS) SORTS, SUM (BUFFER_GETS) BUFFER_GETS, SUM (DISK_READS) DISK_READS, SUM (ROWS_PROCESSED) ROWS_PROCESSED, SUM (CPU_TIME) CPU_TIME, SUM (ELAPSED_TIME) ELAPSED_TIME, SUM (IOWAIT) IOWAIT, SUM (CLWAIT) CLWAIT, SUM (APWAIT) APWAIT, SUM (CCWAIT) CCWAIT FROM A_ET) V1, (SELECT SUM (EXECUTIONS) EXECUTIONS, SUM (FETCHES) FETCHES, SUM (SORTS) SORTS, SUM (BUFFER_GETS) BUFFER_GETS, SUM (DISK_READS) DISK_READS, SUM (ROWS_PROCESSED) ROWS_PROCESSED, SUM (CPU_TIME) CPU_TIME, SUM (ELAPSED_TIME) ELAPSED_TIME, SUM (IOWAIT) IOWAIT, SUM (CLWAIT) CLWAIT, SUM (APWAIT) APWAIT, SUM (CCWAIT) CCWAIT FROM A_TOT) V2 UNION ALL SELECT * FROM (SELECT 'ELAPSED TIME ONE' VIEWTYPE, DBID, SQL_ID, OPTIMIZER_MODE, MODULE, EXECUTIONS, FETCHES, SORTS, BUFFER_GETS, DISK_READS, ROWS_PROCESSED, CPU_TIME, ELAPSED_TIME, BUFFER_GETS / EXECUTIONS BUF_EXEC, DISK_READS / EXECUTIONS DISK_EXEC, ROWS_PROCESSED / EXECUTIONS ROWS_EXEC, CPU_TIME / EXECUTIONS CPU_EXEC, ELAPSED_TIME / EXECUTIONS ELAP_EXEC, IOWAIT, CLWAIT, APWAIT, CCWAIT, ROW_NUMBER () OVER ( ORDER BY ELAPSED_TIME / EXECUTIONS DESC, CPU_TIME DESC, ROWNUM) RNUM FROM A UNION ALL SELECT 'ELAPSED TIME ONE' VIEWTYPE, NULL DBID, NULL SQL_ID, NULL OPTIMIZER_MODE, NULL MODULE, NULL EXECUTIONS, NULL FETCHES, NULL SORTS, NULL BUFFER_GETS, NULL DISK_READS, NULL ROWS_PROCESSED, NULL CPU_TIME, NULL ELAPSED_TIME, NULL BUF_EXEC, NULL DISK_EXEC, NULL ROWS_EXEC, NULL CPU_EXEC, NULL ELAP_EXEC, NULL IOWAIT, NULL CLWAIT, NULL APWAIT, NULL CCWAIT, NULL RNUM FROM A WHERE ROWNUM <= :40 + 1) WHERE RNUM <= :41 + 1 AND ROWNUM <= :42 + 1 UNION ALL SELECT * FROM A_CT UNION ALL SELECT 'CPU TIME ALL' VIEWTYPE, 0 DBID, 'ETC' SQL_ID, NULL OPTIMIZER_MODE, 'ETC' MODULE, V2.EXECUTIONS - V1.EXECUTIONS EXECUTIONS, V2.FETCHES - V1.FETCHES FETCHES, V2.SORTS - V1.SORTS SORTS, V2.BUFFER_GETS - V1.BUFFER_GETS BUFFER_GETS, V2.DISK_READS - V1.DISK_READS DISK_READS, V2.ROWS_PROCESSED - V1.ROWS_PROCESSED ROWS_PROCESSED, V2.CPU_TIME - V1.CPU_TIME CPU_TIME, V2.ELAPSED_TIME - V1.ELAPSED_TIME ELAPSED_TIME, 0 BUF_EXEC, 0 DISK_EXEC, 0 ROWS_EXEC, 0 CPU_EXEC, 0 ELAP_EXEC, V2.IOWAIT - V1.IOWAIT IOWAIT, V2.CLWAIT - V1.CLWAIT CLWAIT, V2.APWAIT - V1.APWAIT APWAIT, V2.CCWAIT - V1.CCWAIT CCWAIT, NULL RNUM FROM (SELECT SUM (EXECUTIONS) EXECUTIONS, SUM (FETCHES) FETCHES, SUM (SORTS) SORTS, SUM (BUFFER_GETS) BUFFER_GETS, SUM (DISK_READS) DISK_READS, SUM (ROWS_PROCESSED) ROWS_PROCESSED, SUM (CPU_TIME) CPU_TIME, SUM (ELAPSED_TIME) ELAPSED_TIME, SUM (IOWAIT) IOWAIT, SUM (CLWAIT) CLWAIT, SUM (APWAIT) APWAIT, SUM (CCWAIT) CCWAIT FROM A_CT) V1, (SELECT SUM (EXECUTIONS) EXECUTIONS, SUM (FETCHES) FETCHES, SUM (SORTS) SORTS, SUM (BUFFER_GETS) BUFFER_GETS, SUM (DISK_READS) DISK_READS, SUM (ROWS_PROCESSED) ROWS_PROCESSED, SUM (CPU_TIME) CPU_TIME, SUM (ELAPSED_TIME) ELAPSED_TIME, SUM (IOWAIT) IOWAIT, SUM (CLWAIT) CLWAIT, SUM (APWAIT) APWAIT, SUM (CCWAIT) CCWAIT FROM A_TOT) V2 UNION ALL SELECT * FROM (SELECT 'CPU TIME ONE' VIEWTYPE, DBID, SQL_ID, OPTIMIZER_MODE, MODULE, EXECUTIONS, FETCHES, SORTS, BUFFER_GETS, DISK_READS, ROWS_PROCESSED, CPU_TIME, ELAPSED_TIME, BUFFER_GETS / EXECUTIONS BUF_EXEC, DISK_READS / EXECUTIONS DISK_EXEC, ROWS_PROCESSED / EXECUTIONS ROWS_EXEC, CPU_TIME / EXECUTIONS CPU_EXEC, ELAPSED_TIME / EXECUTIONS ELAP_EXEC, IOWAIT, CLWAIT, APWAIT, CCWAIT, ROW_NUMBER () OVER ( ORDER BY CPU_TIME / EXECUTIONS DESC, BUFFER_GETS DESC, ROWNUM) RNUM FROM A UNION ALL SELECT 'CPU TIME ONE' VIEWTYPE, NULL DBID, NULL SQL_ID, NULL OPTIMIZER_MODE, NULL MODULE, NULL EXECUTIONS, NULL FETCHES, NULL SORTS, NULL BUFFER_GETS, NULL DISK_READS, NULL ROWS_PROCESSED, NULL CPU_TIME, NULL ELAPSED_TIME, NULL BUF_EXEC, NULL DISK_EXEC, NULL ROWS_EXEC, NULL CPU_EXEC, NULL ELAP_EXEC, NULL IOWAIT, NULL CLWAIT, NULL APWAIT, NULL CCWAIT, NULL RNUM FROM A WHERE ROWNUM <= :43 + 1) WHERE RNUM <= :44 + 1 AND ROWNUM <= :45 + 1 UNION ALL SELECT * FROM A_BG UNION ALL SELECT 'BUFFER GETS ALL' VIEWTYPE, 0 DBID, 'ETC' SQL_ID, NULL OPTIMIZER_MODE, 'ETC' MODULE, V2.EXECUTIONS - V1.EXECUTIONS EXECUTIONS, V2.FETCHES - V1.FETCHES FETCHES, V2.SORTS - V1.SORTS SORTS, V2.BUFFER_GETS - V1.BUFFER_GETS BUFFER_GETS, V2.DISK_READS - V1.DISK_READS DISK_READS, V2.ROWS_PROCESSED - V1.ROWS_PROCESSED ROWS_PROCESSED, V2.CPU_TIME - V1.CPU_TIME CPU_TIME, V2.ELAPSED_TIME - V1.ELAPSED_TIME ELAPSED_TIME, 0 BUF_EXEC, 0 DISK_EXEC, 0 ROWS_EXEC, 0 CPU_EXEC, 0 ELAP_EXEC, V2.IOWAIT - V1.IOWAIT IOWAIT, V2.CLWAIT - V1.CLWAIT CLWAIT, V2.APWAIT - V1.APWAIT APWAIT, V2.CCWAIT - V1.CCWAIT CCWAIT, NULL RNUM FROM (SELECT SUM (EXECUTIONS) EXECUTIONS, SUM (FETCHES) FETCHES, SUM (SORTS) SORTS, SUM (BUFFER_GETS) BUFFER_GETS, SUM (DISK_READS) DISK_READS, SUM (ROWS_PROCESSED) ROWS_PROCESSED, SUM (CPU_TIME) CPU_TIME, SUM (ELAPSED_TIME) ELAPSED_TIME, SUM (IOWAIT) IOWAIT, SUM (CLWAIT) CLWAIT, SUM (APWAIT) APWAIT, SUM (CCWAIT) CCWAIT FROM A_BG) V1, (SELECT SUM (EXECUTIONS) EXECUTIONS, SUM (FETCHES) FETCHES, SUM (SORTS) SORTS, SUM (BUFFER_GETS) BUFFER_GETS, SUM (DISK_READS) DISK_READS, SUM (ROWS_PROCESSED) ROWS_PROCESSED, SUM (CPU_TIME) CPU_TIME, SUM (ELAPSED_TIME) ELAPSED_TIME, SUM (IOWAIT) IOWAIT, SUM (CLWAIT) CLWAIT, SUM (APWAIT) APWAIT, SUM (CCWAIT) CCWAIT FROM A_TOT) V2 UNION ALL SELECT * FROM (SELECT 'BUFFER GETS ONE' VIEWTYPE, DBID, SQL_ID, OPTIMIZER_MODE, MODULE, EXECUTIONS, FETCHES, SORTS, BUFFER_GETS, DISK_READS, ROWS_PROCESSED, CPU_TIME, ELAPSED_TIME, BUFFER_GETS / EXECUTIONS BUF_EXEC, DISK_READS / EXECUTIONS DISK_EXEC, ROWS_PROCESSED / EXECUTIONS ROWS_EXEC, CPU_TIME / EXECUTIONS CPU_EXEC, ELAPSED_TIME / EXECUTIONS ELAP_EXEC, IOWAIT, CLWAIT, APWAIT, CCWAIT, ROW_NUMBER () OVER ( ORDER BY BUFFER_GETS / EXECUTIONS DESC, CPU_TIME DESC, ROWNUM) RNUM FROM A UNION ALL SELECT 'BUFFER GETS ONE' VIEWTYPE, NULL DBID, NULL SQL_ID, NULL OPTIMIZER_MODE, NULL MODULE, NULL EXECUTIONS, NULL FETCHES, NULL SORTS, NULL BUFFER_GETS, NULL DISK_READS, NULL ROWS_PROCESSED, NULL CPU_TIME, NULL ELAPSED_TIME, NULL BUF_EXEC, NULL DISK_EXEC, NULL ROWS_EXEC, NULL CPU_EXEC, NULL ELAP_EXEC, NULL IOWAIT, NULL CLWAIT, NULL APWAIT, NULL CCWAIT, NULL RNUM FROM A WHERE ROWNUM <= :46 + 1) WHERE RNUM <= :47 + 1 AND ROWNUM <= :48 + 1 UNION ALL SELECT * FROM A_DR UNION ALL SELECT 'DISK READS ALL' VIEWTYPE, 0 DBID, 'ETC' SQL_ID, NULL OPTIMIZER_MODE, 'ETC' MODULE, V2.EXECUTIONS - V1.EXECUTIONS EXECUTIONS, V2.FETCHES - V1.FETCHES FETCHES, V2.SORTS - V1.SORTS SORTS, V2.BUFFER_GETS - V1.BUFFER_GETS BUFFER_GETS, V2.DISK_READS - V1.DISK_READS DISK_READS, V2.ROWS_PROCESSED - V1.ROWS_PROCESSED ROWS_PROCESSED, V2.CPU_TIME - V1.CPU_TIME CPU_TIME, V2.ELAPSED_TIME - V1.ELAPSED_TIME ELAPSED_TIME, 0 BUF_EXEC, 0 DISK_EXEC, 0 ROWS_EXEC, 0 CPU_EXEC, 0 ELAP_EXEC, V2.IOWAIT - V1.IOWAIT IOWAIT, V2.CLWAIT - V1.CLWAIT CLWAIT, V2.APWAIT - V1.APWAIT APWAIT, V2.CCWAIT - V1.CCWAIT CCWAIT, NULL RNUM FROM (SELECT SUM (EXECUTIONS) EXECUTIONS, SUM (FETCHES) FETCHES, SUM (SORTS) SORTS, SUM (BUFFER_GETS) BUFFER_GETS, SUM (DISK_READS) DISK_READS, SUM (ROWS_PROCESSED) ROWS_PROCESSED, SUM (CPU_TIME) CPU_TIME, SUM (ELAPSED_TIME) ELAPSED_TIME, SUM (IOWAIT) IOWAIT, SUM (CLWAIT) CLWAIT, SUM (APWAIT) APWAIT, SUM (CCWAIT) CCWAIT FROM A_DR) V1, (SELECT SUM (EXECUTIONS) EXECUTIONS, SUM (FETCHES) FETCHES, SUM (SORTS) SORTS, SUM (BUFFER_GETS) BUFFER_GETS, SUM (DISK_READS) DISK_READS, SUM (ROWS_PROCESSED) ROWS_PROCESSED, SUM (CPU_TIME) CPU_TIME, SUM (ELAPSED_TIME) ELAPSED_TIME, SUM (IOWAIT) IOWAIT, SUM (CLWAIT) CLWAIT, SUM (APWAIT) APWAIT, SUM (CCWAIT) CCWAIT FROM A_TOT) V2 UNION ALL SELECT * FROM (SELECT 'DISK READS ONE' VIEWTYPE, DBID, SQL_ID, OPTIMIZER_MODE, MODULE, EXECUTIONS, FETCHES, SORTS, BUFFER_GETS, DISK_READS, ROWS_PROCESSED, CPU_TIME, ELAPSED_TIME, BUFFER_GETS / EXECUTIONS BUF_EXEC, DISK_READS / EXECUTIONS DISK_EXEC, ROWS_PROCESSED / EXECUTIONS ROWS_EXEC, CPU_TIME / EXECUTIONS CPU_EXEC, ELAPSED_TIME / EXECUTIONS ELAP_EXEC, IOWAIT, CLWAIT, APWAIT, CCWAIT, ROW_NUMBER () OVER ( ORDER BY DISK_READS / EXECUTIONS DESC, CLWAIT DESC, ROWNUM) RNUM FROM A UNION ALL SELECT 'DISK READS ONE' VIEWTYPE, NULL DBID, NULL SQL_ID, NULL OPTIMIZER_MODE, NULL MODULE, NULL EXECUTIONS, NULL FETCHES, NULL SORTS, NULL BUFFER_GETS, NULL DISK_READS, NULL ROWS_PROCESSED, NULL CPU_TIME, NULL ELAPSED_TIME, NULL BUF_EXEC, NULL DISK_EXEC, NULL ROWS_EXEC, NULL CPU_EXEC, NULL ELAP_EXEC, NULL IOWAIT, NULL CLWAIT, NULL APWAIT, NULL CCWAIT, NULL RNUM FROM A WHERE ROWNUM <= :49 + 1) WHERE RNUM <= :50 + 1 AND ROWNUM <= :51 + 1 UNION ALL SELECT * FROM A_CL UNION ALL SELECT 'CLUSTER WAIT ALL' VIEWTYPE, 0 DBID, 'ETC' SQL_ID, NULL OPTIMIZER_MODE, 'ETC' MODULE, V2.EXECUTIONS - V1.EXECUTIONS EXECUTIONS, V2.FETCHES - V1.FETCHES FETCHES, V2.SORTS - V1.SORTS SORTS, V2.BUFFER_GETS - V1.BUFFER_GETS BUFFER_GETS, V2.DISK_READS - V1.DISK_READS DISK_READS, V2.ROWS_PROCESSED - V1.ROWS_PROCESSED ROWS_PROCESSED, V2.CPU_TIME - V1.CPU_TIME CPU_TIME, V2.ELAPSED_TIME - V1.ELAPSED_TIME ELAPSED_TIME, 0 BUF_EXEC, 0 DISK_EXEC, 0 ROWS_EXEC, 0 CPU_EXEC, 0 ELAP_EXEC, V2.IOWAIT - V1.IOWAIT IOWAIT, V2.CLWAIT - V1.CLWAIT CLWAIT, V2.APWAIT - V1.APWAIT APWAIT, V2.CCWAIT - V1.CCWAIT CCWAIT, NULL RNUM FROM (SELECT SUM (EXECUTIONS) EXECUTIONS, SUM (FETCHES) FETCHES, SUM (SORTS) SORTS, SUM (BUFFER_GETS) BUFFER_GETS, SUM (DISK_READS) DISK_READS, SUM (ROWS_PROCESSED) ROWS_PROCESSED, SUM (CPU_TIME) CPU_TIME, SUM (ELAPSED_TIME) ELAPSED_TIME, SUM (IOWAIT) IOWAIT, SUM (CLWAIT) CLWAIT, SUM (APWAIT) APWAIT, SUM (CCWAIT) CCWAIT FROM A_CL) V1, (SELECT SUM (EXECUTIONS) EXECUTIONS, SUM (FETCHES) FETCHES, SUM (SORTS) SORTS, SUM (BUFFER_GETS) BUFFER_GETS, SUM (DISK_READS) DISK_READS, SUM (ROWS_PROCESSED) ROWS_PROCESSED, SUM (CPU_TIME) CPU_TIME, SUM (ELAPSED_TIME) ELAPSED_TIME, SUM (IOWAIT) IOWAIT, SUM (CLWAIT) CLWAIT, SUM (APWAIT) APWAIT, SUM (CCWAIT) CCWAIT FROM A_TOT) V2 UNION ALL SELECT * FROM A_CC UNION ALL SELECT 'CONSISTENT WAIT ALL' VIEWTYPE, 0 DBID, 'ETC' SQL_ID, NULL OPTIMIZER_MODE, 'ETC' MODULE, V2.EXECUTIONS - V1.EXECUTIONS EXECUTIONS, V2.FETCHES - V1.FETCHES FETCHES, V2.SORTS - V1.SORTS SORTS, V2.BUFFER_GETS - V1.BUFFER_GETS BUFFER_GETS, V2.DISK_READS - V1.DISK_READS DISK_READS, V2.ROWS_PROCESSED - V1.ROWS_PROCESSED ROWS_PROCESSED, V2.CPU_TIME - V1.CPU_TIME CPU_TIME, V2.ELAPSED_TIME - V1.ELAPSED_TIME ELAPSED_TIME, 0 BUF_EXEC, 0 DISK_EXEC, 0 ROWS_EXEC, 0 CPU_EXEC, 0 ELAP_EXEC, V2.IOWAIT - V1.IOWAIT IOWAIT, V2.CLWAIT - V1.CLWAIT CLWAIT, V2.APWAIT - V1.APWAIT APWAIT, V2.CCWAIT - V1.CCWAIT CCWAIT, NULL RNUM FROM (SELECT SUM (EXECUTIONS) EXECUTIONS, SUM (FETCHES) FETCHES, SUM (SORTS) SORTS, SUM (BUFFER_GETS) BUFFER_GETS, SUM (DISK_READS) DISK_READS, SUM (ROWS_PROCESSED) ROWS_PROCESSED, SUM (CPU_TIME) CPU_TIME, SUM (ELAPSED_TIME) ELAPSED_TIME, SUM (IOWAIT) IOWAIT, SUM (CLWAIT) CLWAIT, SUM (APWAIT) APWAIT, SUM (CCWAIT) CCWAIT FROM A_CC) V1, (SELECT SUM (EXECUTIONS) EXECUTIONS, SUM (FETCHES) FETCHES, SUM (SORTS) SORTS, SUM (BUFFER_GETS) BUFFER_GETS, SUM (DISK_READS) DISK_READS, SUM (ROWS_PROCESSED) ROWS_PROCESSED, SUM (CPU_TIME) CPU_TIME, SUM (ELAPSED_TIME) ELAPSED_TIME, SUM (IOWAIT) IOWAIT, SUM (CLWAIT) CLWAIT, SUM (APWAIT) APWAIT, SUM (CCWAIT) CCWAIT FROM A_TOT) V2 UNION ALL SELECT * FROM A_IW UNION ALL SELECT 'IO WAIT ALL' VIEWTYPE, 0 DBID, 'ETC' SQL_ID, NULL OPTIMIZER_MODE, 'ETC' MODULE, V2.EXECUTIONS - V1.EXECUTIONS EXECUTIONS, V2.FETCHES - V1.FETCHES FETCHES, V2.SORTS - V1.SORTS SORTS, V2.BUFFER_GETS - V1.BUFFER_GETS BUFFER_GETS, V2.DISK_READS - V1.DISK_READS DISK_READS, V2.ROWS_PROCESSED - V1.ROWS_PROCESSED ROWS_PROCESSED, V2.CPU_TIME - V1.CPU_TIME CPU_TIME, V2.ELAPSED_TIME - V1.ELAPSED_TIME ELAPSED_TIME, 0 BUF_EXEC, 0 DISK_EXEC, 0 ROWS_EXEC, 0 CPU_EXEC, 0 ELAP_EXEC, V2.IOWAIT - V1.IOWAIT IOWAIT, V2.CLWAIT - V1.CLWAIT CLWAIT, V2.APWAIT - V1.APWAIT APWAIT, V2.CCWAIT - V1.CCWAIT CCWAIT, NULL RNUM FROM (SELECT SUM (EXECUTIONS) EXECUTIONS, SUM (FETCHES) FETCHES, SUM (SORTS) SORTS, SUM (BUFFER_GETS) BUFFER_GETS, SUM (DISK_READS) DISK_READS, SUM (ROWS_PROCESSED) ROWS_PROCESSED, SUM (CPU_TIME) CPU_TIME, SUM (ELAPSED_TIME) ELAPSED_TIME, SUM (IOWAIT) IOWAIT, SUM (CLWAIT) CLWAIT, SUM (APWAIT) APWAIT, SUM (CCWAIT) CCWAIT FROM A_IW) V1, (SELECT SUM (EXECUTIONS) EXECUTIONS, SUM (FETCHES) FETCHES, SUM (SORTS) SORTS, SUM (BUFFER_GETS) BUFFER_GETS, SUM (DISK_READS) DISK_READS, SUM (ROWS_PROCESSED) ROWS_PROCESSED, SUM (CPU_TIME) CPU_TIME, SUM (ELAPSED_TIME) ELAPSED_TIME, SUM (IOWAIT) IOWAIT, SUM (CLWAIT) CLWAIT, SUM (APWAIT) APWAIT, SUM (CCWAIT) CCWAIT FROM A_TOT) V2 UNION ALL SELECT * FROM A_AW UNION ALL SELECT 'LOCK WAIT ALL' VIEWTYPE, 0 DBID, 'ETC' SQL_ID, NULL OPTIMIZER_MODE, 'ETC' MODULE, V2.EXECUTIONS - V1.EXECUTIONS EXECUTIONS, V2.FETCHES - V1.FETCHES FETCHES, V2.SORTS - V1.SORTS SORTS, V2.BUFFER_GETS - V1.BUFFER_GETS BUFFER_GETS, V2.DISK_READS - V1.DISK_READS DISK_READS, V2.ROWS_PROCESSED - V1.ROWS_PROCESSED ROWS_PROCESSED, V2.CPU_TIME - V1.CPU_TIME CPU_TIME, V2.ELAPSED_TIME - V1.ELAPSED_TIME ELAPSED_TIME, 0 BUF_EXEC, 0 DISK_EXEC, 0 ROWS_EXEC, 0 CPU_EXEC, 0 ELAP_EXEC, V2.IOWAIT - V1.IOWAIT IOWAIT, V2.CLWAIT - V1.CLWAIT CLWAIT, V2.APWAIT - V1.APWAIT APWAIT, V2.CCWAIT - V1.CCWAIT CCWAIT, NULL RNUM FROM (SELECT SUM (EXECUTIONS) EXECUTIONS, SUM (FETCHES) FETCHES, SUM (SORTS) SORTS, SUM (BUFFER_GETS) BUFFER_GETS, SUM (DISK_READS) DISK_READS, SUM (ROWS_PROCESSED) ROWS_PROCESSED, SUM (CPU_TIME) CPU_TIME, SUM (ELAPSED_TIME) ELAPSED_TIME, SUM (IOWAIT) IOWAIT, SUM (CLWAIT) CLWAIT, SUM (APWAIT) APWAIT, SUM (CCWAIT) CCWAIT FROM A_AW) V1, (SELECT SUM (EXECUTIONS) EXECUTIONS, SUM (FETCHES) FETCHES, SUM (SORTS) SORTS, SUM (BUFFER_GETS) BUFFER_GETS, SUM (DISK_READS) DISK_READS, SUM (ROWS_PROCESSED) ROWS_PROCESSED, SUM (CPU_TIME) CPU_TIME, SUM (ELAPSED_TIME) ELAPSED_TIME, SUM (IOWAIT) IOWAIT, SUM (CLWAIT) CLWAIT, SUM (APWAIT) APWAIT, SUM (CCWAIT) CCWAIT FROM A_TOT) V2 UNION ALL SELECT * FROM A_EC UNION ALL SELECT 'EXEC CNT ALL' VIEWTYPE, 0 DBID, 'ETC' SQL_ID, NULL OPTIMIZER_MODE, 'ETC' MODULE, V2.EXECUTIONS - V1.EXECUTIONS EXECUTIONS, V2.FETCHES - V1.FETCHES FETCHES, V2.SORTS - V1.SORTS SORTS, V2.BUFFER_GETS - V1.BUFFER_GETS BUFFER_GETS, V2.DISK_READS - V1.DISK_READS DISK_READS, V2.ROWS_PROCESSED - V1.ROWS_PROCESSED ROWS_PROCESSED, V2.CPU_TIME - V1.CPU_TIME CPU_TIME, V2.ELAPSED_TIME - V1.ELAPSED_TIME ELAPSED_TIME, 0 BUF_EXEC, 0 DISK_EXEC, 0 ROWS_EXEC, 0 CPU_EXEC, 0 ELAP_EXEC, V2.IOWAIT - V1.IOWAIT IOWAIT, V2.CLWAIT - V1.CLWAIT CLWAIT, V2.APWAIT - V1.APWAIT APWAIT, V2.CCWAIT - V1.CCWAIT CCWAIT, NULL RNUM FROM (SELECT SUM (EXECUTIONS) EXECUTIONS, SUM (FETCHES) FETCHES, SUM (SORTS) SORTS, SUM (BUFFER_GETS) BUFFER_GETS, SUM (DISK_READS) DISK_READS, SUM (ROWS_PROCESSED) ROWS_PROCESSED, SUM (CPU_TIME) CPU_TIME, SUM (ELAPSED_TIME) ELAPSED_TIME, SUM (IOWAIT) IOWAIT, SUM (CLWAIT) CLWAIT, SUM (APWAIT) APWAIT, SUM (CCWAIT) CCWAIT FROM A_EC) V1, (SELECT SUM (EXECUTIONS) EXECUTIONS, SUM (FETCHES) FETCHES, SUM (SORTS) SORTS, SUM (BUFFER_GETS) BUFFER_GETS, SUM (DISK_READS) DISK_READS, SUM (ROWS_PROCESSED) ROWS_PROCESSED, SUM (CPU_TIME) CPU_TIME, SUM (ELAPSED_TIME) ELAPSED_TIME, SUM (IOWAIT) IOWAIT, SUM (CLWAIT) CLWAIT, SUM (APWAIT) APWAIT, SUM (CCWAIT) CCWAIT FROM A_TOT) V2 UNION ALL SELECT 'TOTAL' VIEWTYPE, DBID, SQL_ID, OPTIMIZER_MODE, MODULE, EXECUTIONS, FETCHES, SORTS, BUFFER_GETS, DISK_READS, ROWS_PROCESSED, CPU_TIME, ELAPSED_TIME, BUF_EXEC, DISK_EXEC, ROWS_EXEC, CPU_EXEC, ELAP_EXEC, IOWAIT, CLWAIT, APWAIT, CCWAIT, RNUM FROM A_TOT) SQLSTAT, DBA_HIST_SQLTEXT SQLT WHERE SQLSTAT.SQL_ID = SQLT.SQL_ID(+) AND SQLSTAT.DBID = SQLT.DBID(+) ORDER BY VIEWTYPE, RNUM --2.SQL_PLAN /* Formatted on 2015-02-17 오후 2:17:18 (QP5 v5.163.1008.3004) */ SELECT LPAD (' ', DEPTH) || OPERATION || '_' || OPTIONS || ' ' || OBJECT_NAME operation FROM DBA_HIST_SQL_PLAN WHERE SQL_ID = '8uru9t6frg2mw' AND TIMESTAMP IN (SELECT MAX (TIMESTAMP) FROM DBA_HIST_SQL_PLAN WHERE SQL_ID = '8uru9t6frg2mw' AND DBID = (SELECT dbid FROM v$database)) --변경 ORDER BY ID, PLAN_HASH_VALUE; /* Formatted on 2015-02-17 오후 1:17:13 (QP5 v5.163.1008.3004) */ SELECT DECODE (id, 0, CHR (10)) || LPAD (' ', 1 * (LEVEL - 1)) || OPERATION || DECODE ( id, 0, ' : ' || optimizer || ' : ' || TO_CHAR (TIMESTAMP, 'YYYY.MM.DD HH24:MI:SS') || ' [' || PLAN_HASH_VALUE || ']') || DECODE (options, NULL, NULL, ' (') || options || DECODE (options, NULL, NULL, ')') || DECODE (object_name, NULL, NULL, ' : ') || object_owner || DECODE (object_owner, NULL, NULL, '.') || OBJECT_NAME || DECODE ( operation, 'TABLE ACCESS', DECODE ( options, 'FULL', ' <' || TRIM ( TO_CHAR ( (SELECT num_rows FROM all_tables at WHERE at.owner = object_owner AND at.table_name = object_name))) || '>')) || DECODE ( operation, 'INDEX', ' <' || TRIM ( TO_CHAR ( (SELECT avg_data_blocks_per_key FROM all_indexes ai WHERE ai.owner = object_owner AND ai.index_name = object_name))) || '>') || DECODE ( SUBSTR (operation, 1, 5), 'INDEX', '(' || SEARCH_COLUMNS || ':' || REPLACE ( TRIM ( (SELECT /*+ LEADING(DIC.IO) USE_NL(DIC.IDX DIC.IC DIC.I) INDEX(DIC.BASE DIC.IDX) */ MAX ( DECODE (column_position, 1, column_name)) || ' ' || MAX ( DECODE (column_position, 2, column_name)) || ' ' || MAX ( DECODE (column_position, 3, column_name)) || ' ' || MAX ( DECODE (column_position, 4, column_name)) || ' ' || MAX ( DECODE (column_position, 5, column_name)) || ' ' || MAX ( DECODE (column_position, 6, column_name)) || ' ' || MAX ( DECODE (column_position, 7, column_name)) || ' ' || MAX ( DECODE (column_position, 8, column_name)) || ' ' || MAX ( DECODE (column_position, 9, column_name)) || ' ' || MAX ( DECODE (column_position, 10, column_name)) || ' ' || MAX ( DECODE (column_position, 11, column_name)) || ' ' FROM all_ind_columns dic WHERE dic.index_owner = object_owner AND dic.index_name = object_name)), ' ', ',') || ')', NULL) || DECODE (other_tag, NULL, NULL, '(') || other_tag || DECODE (other_tag, NULL, NULL, ')') || DECODE (partition_start, NULL, NULL, ' pst:') || partition_start || DECODE (partition_start, NULL, NULL, ' pstop: ') || partition_stop AS OPERATION FROM (SELECT /*+ ALL_ROWS NO_MERGE */ TO_CHAR (DBID), SQL_ID, PLAN_HASH_VALUE, ID, OPERATION, OPTIONS, OBJECT_NODE, OBJECT#, OBJECT_OWNER, OBJECT_NAME, OBJECT_ALIAS, OBJECT_TYPE, OPTIMIZER, PARENT_ID, DEPTH, POSITION, SEARCH_COLUMNS, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID, OTHER, DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES, FILTER_PREDICATES, PROJECTION, TIME, QBLOCK_NAME, REMARKS, TIMESTAMP, RPAD (TO_CHAR (ID), 10, ' ') || TO_CHAR (TIMESTAMP, 'YYYYMMDDHH24MISS') || PLAN_HASH_VALUE || 'P' || SQL_ID ID_M, RPAD (TO_CHAR (PARENT_ID), 10, ' ') || TO_CHAR (TIMESTAMP, 'YYYYMMDDHH24MISS') || PLAN_HASH_VALUE || 'P' || SQL_ID PID_M FROM DBA_HIST_SQL_PLAN WHERE SQL_ID = :1 AND DBID = (select dbid from v$database) --변경 AND TIMESTAMP IN (SELECT MAX (TIMESTAMP) FROM DBA_HIST_SQL_PLAN WHERE SQL_ID = :3 AND DBID = (select dbid from v$database) --변경 )) CONNECT BY PRIOR ID_M = PID_M START WITH ID = 0 AND SQL_ID = :5; --3.SQL_PROFILE 내역 /* Formatted on 2015-02-17 오후 12:58:52 (QP5 v5.163.1008.3004) */ SELECT LPAD ('DATE', 12, ' ') || LPAD ('BUFFER GETS TOTAL', 20, ' ') || LPAD ('CPU TIME TOTAL', 15, ' ') || LPAD ('CC WAIT TIME', 14, ' ') || LPAD ('IO WAIT TIME', 14, ' ') || LPAD ('ROWS PROCESSED', 15, ' ') || LPAD ('BUFF/EXEC', 15, ' ') || LPAD ('CPU/EXEC', 14, ' ') || LPAD ('CC/EXEC', 14, ' ') || LPAD ('IO/EXEC', 14, ' ') || CHR (10) || LPAD ('TOTAL EXECU', 12, ' ') || LPAD ('DISK READS TOTAL', 20, ' ') || LPAD ('ELAPSED TIME', 15, ' ') || LPAD ('CL WAIT TIME', 14, ' ') || LPAD ('AP WAIT TIME', 14, ' ') || LPAD ('ROWS PROC/EXEC', 15, ' ') || LPAD ('DISK/EXEC', 15, ' ') || LPAD ('ELAP/EXEC', 14, ' ') || LPAD ('CL/EXEC', 14, ' ') || LPAD ('AP/EXEC', 14, ' ') A FROM DUAL UNION ALL SELECT LPAD ('-', 147, '-') A FROM DUAL UNION ALL SELECT A FROM ( SELECT LPAD (TO_CHAR (END_INTERVAL_TIME, 'YYYY.MM.DD'), 12, ' ') || LPAD ( TO_CHAR (SUM (BUFFER_GETS_DELTA), 'FM999,999,999,999,999'), 20, ' ') || LPAD ( TO_CHAR (ROUND (SUM (CPU_TIME_DELTA) / 1000000, 1), 'FM999,999,999.9'), 15, ' ') || LPAD ( TO_CHAR (ROUND (SUM (CCWAIT_DELTA) / 1000000, 1), 'FM999,999,999.9'), 14, ' ') || LPAD ( TO_CHAR (ROUND (SUM (IOWAIT_DELTA) / 1000000, 1), 'FM999,999,999.9'), 14, ' ') || LPAD ( TO_CHAR (SUM (ROWS_PROCESSED_DELTA), 'FM999,999,999,999,999.9'), 15, ' ') || LPAD ( TO_CHAR ( ROUND ( SUM (BUFFER_GETS_DELTA) / DECODE (SUM (EXECUTIONS_DELTA), 0, 1, SUM (EXECUTIONS_DELTA)), 0), 'FM999,999,999,999.9'), 15, ' ') || LPAD ( TO_CHAR ( ROUND ( (SUM (CPU_TIME_DELTA) / 1000000) / DECODE (SUM (EXECUTIONS_DELTA), 0, 1, SUM (EXECUTIONS_DELTA)), 1), 'FM999,999.9'), 14, ' ') || LPAD ( TO_CHAR ( ROUND ( (SUM (CCWAIT_DELTA) / 1000000) / DECODE (SUM (EXECUTIONS_DELTA), 0, 1, SUM (EXECUTIONS_DELTA)), 1), 'FM999,999.9'), 14, ' ') || LPAD ( TO_CHAR ( ROUND ( (SUM (IOWAIT_DELTA) / 1000000) / DECODE (SUM (EXECUTIONS_DELTA), 0, 1, SUM (EXECUTIONS_DELTA)), 1), 'FM999,999.9'), 14, ' ') || CHR (10) || LPAD (TO_CHAR (SUM (EXECUTIONS_DELTA), 'FM999,999,999'), 12, ' ') || LPAD ( TO_CHAR (SUM (DISK_READS_DELTA), 'FM999,999,999,999,999'), 20, ' ') || LPAD ( TO_CHAR (ROUND (SUM (ELAPSED_TIME_DELTA) / 1000000, 1), 'FM999,999,999.9'), 15, ' ') || LPAD ( TO_CHAR (ROUND (SUM (CLWAIT_DELTA) / 1000000, 1), 'FM999,999,999.9'), 14, ' ') || LPAD ( TO_CHAR (ROUND (SUM (APWAIT_DELTA) / 1000000, 1), 'FM999,999,999.9'), 14, ' ') || LPAD ( TO_CHAR ( ROUND ( SUM (ROWS_PROCESSED_DELTA) / DECODE (SUM (EXECUTIONS_DELTA), 0, 1, SUM (EXECUTIONS_DELTA)), 1), 'FM999,999,999.9'), 15, ' ') || LPAD ( TO_CHAR ( ROUND ( SUM (DISK_READS_DELTA) / DECODE (SUM (EXECUTIONS_DELTA), 0, 1, SUM (EXECUTIONS_DELTA)), 0), 'FM999,999,999,999.9'), 15, ' ') || LPAD ( TO_CHAR ( ROUND ( (SUM (ELAPSED_TIME_DELTA) / 1000000) / DECODE (SUM (EXECUTIONS_DELTA), 0, 1, SUM (EXECUTIONS_DELTA)), 1), 'FM999,999.9'), 14, ' ') || LPAD ( TO_CHAR ( ROUND ( (SUM (CLWAIT_DELTA) / 1000000) / DECODE (SUM (EXECUTIONS_DELTA), 0, 1, SUM (EXECUTIONS_DELTA)), 1), 'FM999,999.9'), 14, ' ') || LPAD ( TO_CHAR ( ROUND ( (SUM (APWAIT_DELTA) / 1000000) / DECODE (SUM (EXECUTIONS_DELTA), 0, 1, SUM (EXECUTIONS_DELTA)), 1), 'FM999,999.9'), 14, ' ') A FROM DBA_HIST_SQLSTAT DHSQL, DBA_HIST_SNAPSHOT DHSS WHERE DHSQL.DBID = (select dbid from v$database) --변경 AND DHSQL.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance) --변경 AND DHSQL.SQL_ID = :3 AND DHSS.DBID = DHSQL.DBID AND DHSS.SNAP_ID = DHSQL.SNAP_ID AND DHSS.INSTANCE_NUMBER = DHSQL.INSTANCE_NUMBER GROUP BY TO_CHAR (END_INTERVAL_TIME, 'YYYY.MM.DD') ORDER BY 1); |
'1. IT Story > Scripts' 카테고리의 다른 글
| Oracle Rawdevice구성 RMAN Backup스크립트 (0) | 2019.01.15 |
|---|---|
| 오라클 SQL TRACE 추출 스크립트 (0) | 2019.01.13 |
| Oracle DB 기본 점검 스크립트 (0) | 2019.01.10 |
| Oracle RMAN FULL BACKUP 스크립트 (0) | 2019.01.09 |
| Oracle Top 10 Event 추출 스크립트 (0) | 2019.01.08 |
| Oracle DB AWR 활용하여 IO 성능 조회 스크립트 (0) | 2019.01.07 |
| Oracle DB 기본 상태 조회 스크립트 (0) | 2019.01.07 |
| Oracle AWR을 활용한 주요 Stats 지표 추출 스크립트 (0) | 2019.01.07 |
운명을바꾸는자
IT와 함께 살아가는 삶
