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


블로그 이미지

운명을바꾸는자

IT와 함께 살아가는 삶

,

Oracle Top 10 Event 추출 스크립트



   SELECT SNAP_TIME,

         TO_CHAR (SUM (DECODE (RNK, 1, VALUE)), 'FM999,999,999,999.999')

            "VALUE Per SEC_1",

         TO_CHAR (SUM (DECODE (RNK, 2, VALUE)), 'FM999,999,999,999.999')

            "VALUE Per SEC_2",

         TO_CHAR (SUM (DECODE (RNK, 3, VALUE)), 'FM999,999,999,999.999')

            "VALUE Per SEC_3",

         TO_CHAR (SUM (DECODE (RNK, 4, VALUE)), 'FM999,999,999,999.999')

            "VALUE Per SEC_4",

         TO_CHAR (SUM (DECODE (RNK, 5, VALUE)), 'FM999,999,999,999.999')

            "VALUE Per SEC_5",

         TO_CHAR (SUM (DECODE (RNK, 6, VALUE)), 'FM999,999,999,999.999')

            "VALUE Per SEC_6",

         TO_CHAR (SUM (DECODE (RNK, 7, VALUE)), 'FM999,999,999,999.999')

            "VALUE Per SEC_7",

         TO_CHAR (SUM (DECODE (RNK, 8, VALUE)), 'FM999,999,999,999.999')

            "VALUE Per SEC_8",

         TO_CHAR (SUM (DECODE (RNK, 9, VALUE)), 'FM999,999,999,999.999')

            "VALUE Per SEC_9",

         TO_CHAR (SUM (DECODE (RNK, 10, VALUE)), 'FM999,999,999,999.999')

            "VALUE Per SEC_10",

         TO_CHAR (SUM (DECODE (RNK, 11, VALUE)), 'FM999,999,999,999.999')

            "VALUE Per SEC_ETC",

         TO_CHAR (SUM (DECODE (RNK, 1, VALUE_DIFF)), 'FM999,999,999,999,999')

            VALUE_DIFF_1,

         TO_CHAR (SUM (DECODE (RNK, 2, VALUE_DIFF)), 'FM999,999,999,999,999')

            VALUE_DIFF_2,

         TO_CHAR (SUM (DECODE (RNK, 3, VALUE_DIFF)), 'FM999,999,999,999,999')

            VALUE_DIFF_3,

         TO_CHAR (SUM (DECODE (RNK, 4, VALUE_DIFF)), 'FM999,999,999,999,999')

            VALUE_DIFF_4,

         TO_CHAR (SUM (DECODE (RNK, 5, VALUE_DIFF)), 'FM999,999,999,999,999')

            VALUE_DIFF_5,

         TO_CHAR (SUM (DECODE (RNK, 6, VALUE_DIFF)), 'FM999,999,999,999,999')

            VALUE_DIFF_6,

         TO_CHAR (SUM (DECODE (RNK, 7, VALUE_DIFF)), 'FM999,999,999,999,999')

            VALUE_DIFF_7,

         TO_CHAR (SUM (DECODE (RNK, 8, VALUE_DIFF)), 'FM999,999,999,999,999')

            VALUE_DIFF_8,

         TO_CHAR (SUM (DECODE (RNK, 9, VALUE_DIFF)), 'FM999,999,999,999,999')

            VALUE_DIFF_9,

         TO_CHAR (SUM (DECODE (RNK, 10, VALUE_DIFF)), 'FM999,999,999,999,999')

            VALUE_DIFF_10,

         TO_CHAR (SUM (DECODE (RNK, 11, VALUE_DIFF)), 'FM999,999,999,999,999')

            VALUE_DIFF_ETC

    FROM (SELECT A.EVENT_NAME,

                 B.EVENT_NAME B_EVENT_NAME,

                 A.SNAP_TIME,

                 A.VALUE,

                 A.VALUE_DIFF,

                 B.RNK RNK

            FROM (SELECT EVENT_NAME,

                         DECODE (G1, 1, 'SUB AVG', SNAP_TIME) SNAP_TIME,

                         VALUE,

                         VALUE_DIFF

                    FROM (  SELECT EVENT_NAME,

                                   END_TIME SNAP_TIME,

                                   AVG (NVL (VALUE, 0)) VALUE,

                                   AVG (NVL (VALUE_DIFF, 0)) VALUE_DIFF,

                                   GROUPING (END_TIME) G1,

                                   GROUPING (EVENT_NAME) G2

                              FROM (SELECT EVENT_NAME,

                                           TO_CHAR (SNAP_TIME_C1,

                                                    'MM.DD HH24:MI')

                                              START_TIME,

                                           TO_CHAR (SNAP_TIME_C2,

                                                    'MM.DD HH24:MI')

                                              END_TIME,

                                           DECODE (

                                              SNAP_TIME_C2,

                                              NULL, 0,

                                              (CASE

                                                  WHEN VALUE_2 < VALUE_1 THEN 0

                                                  ELSE VALUE_2 - VALUE_1

                                               END)

                                              / (1000000

                                                 * (EXTRACT (

                                                       DAY FROM SNAP_TIME_C2

                                                                - SNAP_TIME_C1)

                                                    * 86400

                                                    + EXTRACT (

                                                         HOUR FROM SNAP_TIME_C2

                                                                   - SNAP_TIME_C1)

                                                      * 3600

                                                    + EXTRACT (

                                                         MINUTE FROM SNAP_TIME_C2

                                                                     - SNAP_TIME_C1)

                                                      * 60

                                                    + EXTRACT (

                                                         SECOND FROM SNAP_TIME_C2

                                                                     - SNAP_TIME_C1))))

                                              VALUE,

                                           (CASE

                                               WHEN VALUE_2 < VALUE_1 THEN 0

                                               ELSE VALUE_2 - VALUE_1

                                            END)

                                           / 1000000

                                              VALUE_DIFF,

                                           ROW_NUMBER ()

                                           OVER (

                                              PARTITION BY INSTANCE_NUMBER,

                                                           EVENT_NAME

                                              ORDER BY SNAP_ID)

                                              RNUM,

                                           SNAP_ID,

                                           INSTANCE_NUMBER

                                      FROM (  SELECT SNAP.END_INTERVAL_TIME

                                                        SNAP_TIME_C1,

                                                     LEAD (

                                                        SNAP.END_INTERVAL_TIME)

                                                     OVER (

                                                        PARTITION BY DBI.INSTANCE_NUMBER,

                                                                     EVENT_NAME

                                                        ORDER BY SNAP.SNAP_ID)

                                                        SNAP_TIME_C2,

                                                     EVENT.EVENT_NAME,

                                                     EVENT.TIME_WAITED_MICRO

                                                        VALUE_1,

                                                     LEAD (

                                                        EVENT.TIME_WAITED_MICRO)

                                                     OVER (

                                                        PARTITION BY DBI.INSTANCE_NUMBER,

                                                                     EVENT_NAME

                                                        ORDER BY SNAP.SNAP_ID)

                                                        VALUE_2,

                                                     SNAP.SNAP_ID,

                                                     DBI.INSTANCE_NUMBER

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

                                               WHERE DBI.DBID = SNAP.DBID

                                                     AND DBI.INSTANCE_NUMBER =

                                                            SNAP.INSTANCE_NUMBER

                                                     AND SNAP.SNAP_ID >= :3

                                                     AND SNAP.SNAP_ID <= :4

                                                     AND SNAP.DBID = EVENT.DBID

                                                     AND SNAP.INSTANCE_NUMBER =

                                                            EVENT.INSTANCE_NUMBER

                                                     AND SNAP.SNAP_ID =

                                                            EVENT.SNAP_ID

                                                     AND UPPER (EVENT.WAIT_CLASS) <>

                                                            'IDLE'

                                            ORDER BY SNAP.SNAP_ID))

                             WHERE START_TIME IS NOT NULL

                                   AND END_TIME IS NOT NULL

                          GROUP BY ROLLUP (EVENT_NAME, END_TIME))

                   WHERE NOT (G1 = 1 AND G2 = 1)) A,

                 (SELECT EVENT_NAME, RNK

                    FROM (SELECT EVENT_NAME, RNK

                            FROM (SELECT EVENT_NAME,

                                         RANK ()

                                         OVER (

                                            ORDER BY

                                               VALUE_DIFF DESC,

                                               EVENT_NAME ASC NULLS LAST)

                                            RNK

                                    FROM (SELECT EVENT_NAME,

                                                 DECODE (G1,

                                                         1, 'SUB AVG',

                                                         SNAP_TIME)

                                                    SNAP_TIME,

                                                 VALUE,

                                                 VALUE_DIFF

                                            FROM (  SELECT EVENT_NAME,

                                                           END_TIME SNAP_TIME,

                                                           AVG (NVL (VALUE, 0))

                                                              VALUE,

                                                           AVG (

                                                              NVL (VALUE_DIFF, 0))

                                                              VALUE_DIFF,

                                                           GROUPING (END_TIME) G1,

                                                           GROUPING (EVENT_NAME)

                                                              G2

                                                      FROM (SELECT EVENT_NAME,

                                                                   TO_CHAR (

                                                                      SNAP_TIME_C1,

                                                                      'MM.DD HH24:MI')

                                                                      START_TIME,

                                                                   TO_CHAR (

                                                                      SNAP_TIME_C2,

                                                                      'MM.DD HH24:MI')

                                                                      END_TIME,

                                                                   DECODE (

                                                                      SNAP_TIME_C2,

                                                                      NULL, 0,

                                                                      (CASE

                                                                          WHEN VALUE_2 <

                                                                                  VALUE_1

                                                                          THEN

                                                                             0

                                                                          ELSE

                                                                             VALUE_2

                                                                             - VALUE_1

                                                                       END)

                                                                      / (1000000

                                                                         * (EXTRACT (

                                                                               DAY FROM SNAP_TIME_C2

                                                                                        - SNAP_TIME_C1)

                                                                            * 86400

                                                                            + EXTRACT (

                                                                                 HOUR FROM SNAP_TIME_C2

                                                                                           - SNAP_TIME_C1)

                                                                              * 3600

                                                                            + EXTRACT (

                                                                                 MINUTE FROM SNAP_TIME_C2

                                                                                             - SNAP_TIME_C1)

                                                                              * 60

                                                                            + EXTRACT (

                                                                                 SECOND FROM SNAP_TIME_C2

                                                                                             - SNAP_TIME_C1))))

                                                                      VALUE,

                                                                   (CASE

                                                                       WHEN VALUE_2 <

                                                                               VALUE_1

                                                                       THEN

                                                                          0

                                                                       ELSE

                                                                          VALUE_2

                                                                          - VALUE_1

                                                                    END)

                                                                   / 1000000

                                                                      VALUE_DIFF,

                                                                   ROW_NUMBER ()

                                                                   OVER (

                                                                      PARTITION BY INSTANCE_NUMBER,

                                                                                   EVENT_NAME

                                                                      ORDER BY

                                                                         SNAP_ID)

                                                                      RNUM,

                                                                   SNAP_ID,

                                                                   INSTANCE_NUMBER

                                                              FROM (  SELECT SNAP.END_INTERVAL_TIME

                                                                                SNAP_TIME_C1,

                                                                             LEAD (

                                                                                SNAP.END_INTERVAL_TIME)

                                                                             OVER (

                                                                                PARTITION BY DBI.INSTANCE_NUMBER,

                                                                                             EVENT_NAME

                                                                                ORDER BY

                                                                                   SNAP.SNAP_ID)

                                                                                SNAP_TIME_C2,

                                                                             EVENT.EVENT_NAME,

                                                                             EVENT.TIME_WAITED_MICRO

                                                                                VALUE_1,

                                                                             LEAD (

                                                                                EVENT.TIME_WAITED_MICRO)

                                                                             OVER (

                                                                                PARTITION BY DBI.INSTANCE_NUMBER,

                                                                                             EVENT_NAME

                                                                                ORDER BY

                                                                                   SNAP.SNAP_ID)

                                                                                VALUE_2,

                                                                             SNAP.SNAP_ID,

                                                                             DBI.INSTANCE_NUMBER

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

                                                                       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 =

                                                                                    EVENT.DBID

                                                                             AND SNAP.INSTANCE_NUMBER =

                                                                                    EVENT.INSTANCE_NUMBER

                                                                             AND SNAP.SNAP_ID =

                                                                                    EVENT.SNAP_ID

                                                                             AND UPPER (

                                                                                    EVENT.WAIT_CLASS) <>

                                                                                    'IDLE'

                                                                    ORDER BY SNAP.SNAP_ID))

                                                     WHERE START_TIME IS NOT NULL

                                                           AND END_TIME

                                                                  IS NOT NULL

                                                  GROUP BY ROLLUP (EVENT_NAME,

                                                                   END_TIME))

                                           WHERE NOT (G1 = 1 AND G2 = 1))

                                   WHERE SNAP_TIME = 'SUB AVG')

                           WHERE RNK <= 10)) B

           WHERE A.EVENT_NAME = B.EVENT_NAME

          UNION ALL

            SELECT ' 나머지 Event Sum' EVENT_NAME,

                   NULL B_EVENT_NAME,

                   A.SNAP_TIME,

                   SUM (A.VALUE) VALUE,

                   SUM (A.VALUE_DIFF) VALUE_DIFF,

                   11 RNK

              FROM (SELECT EVENT_NAME,

                           DECODE (G1, 1, 'SUB AVG', SNAP_TIME) SNAP_TIME,

                           VALUE,

                           VALUE_DIFF

                      FROM (  SELECT EVENT_NAME,

                                     END_TIME SNAP_TIME,

                                     AVG (NVL (VALUE, 0)) VALUE,

                                     AVG (NVL (VALUE_DIFF, 0)) VALUE_DIFF,

                                     GROUPING (END_TIME) G1,

                                     GROUPING (EVENT_NAME) G2

                                FROM (SELECT EVENT_NAME,

                                             TO_CHAR (SNAP_TIME_C1,

                                                      'MM.DD HH24:MI')

                                                START_TIME,

                                             TO_CHAR (SNAP_TIME_C2,

                                                      'MM.DD HH24:MI')

                                                END_TIME,

                                             DECODE (

                                                SNAP_TIME_C2,

                                                NULL, 0,

                                                (CASE

                                                    WHEN VALUE_2 < VALUE_1 THEN 0

                                                    ELSE VALUE_2 - VALUE_1

                                                 END)

                                                / (1000000

                                                   * (EXTRACT (

                                                         DAY FROM SNAP_TIME_C2

                                                                  - SNAP_TIME_C1)

                                                      * 86400

                                                      + EXTRACT (

                                                           HOUR FROM SNAP_TIME_C2

                                                                     - SNAP_TIME_C1)

                                                        * 3600

                                                      + EXTRACT (

                                                           MINUTE FROM SNAP_TIME_C2

                                                                       - SNAP_TIME_C1)

                                                        * 60

                                                      + EXTRACT (

                                                           SECOND FROM SNAP_TIME_C2

                                                                       - SNAP_TIME_C1))))

                                                VALUE,

                                             (CASE

                                                 WHEN VALUE_2 < VALUE_1 THEN 0

                                                 ELSE VALUE_2 - VALUE_1

                                              END)

                                             / 1000000

                                                VALUE_DIFF,

                                             ROW_NUMBER ()

                                             OVER (

                                                PARTITION BY INSTANCE_NUMBER,

                                                             EVENT_NAME

                                                ORDER BY SNAP_ID)

                                                RNUM,

                                             SNAP_ID,

                                             INSTANCE_NUMBER

                                        FROM (  SELECT SNAP.END_INTERVAL_TIME

                                                          SNAP_TIME_C1,

                                                       LEAD (

                                                          SNAP.END_INTERVAL_TIME)

                                                       OVER (

                                                          PARTITION BY DBI.INSTANCE_NUMBER,

                                                                       EVENT_NAME

                                                          ORDER BY SNAP.SNAP_ID)

                                                          SNAP_TIME_C2,

                                                       EVENT.EVENT_NAME,

                                                       EVENT.TIME_WAITED_MICRO

                                                          VALUE_1,

                                                       LEAD (

                                                          EVENT.TIME_WAITED_MICRO)

                                                       OVER (

                                                          PARTITION BY DBI.INSTANCE_NUMBER,

                                                                       EVENT_NAME

                                                          ORDER BY SNAP.SNAP_ID)

                                                          VALUE_2,

                                                       SNAP.SNAP_ID,

                                                       DBI.INSTANCE_NUMBER

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

                                                 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 = EVENT.DBID

                                                       AND SNAP.INSTANCE_NUMBER =

                                                              EVENT.INSTANCE_NUMBER

                                                       AND SNAP.SNAP_ID =

                                                              EVENT.SNAP_ID

                                                       AND UPPER (EVENT.WAIT_CLASS) <>

                                                              'IDLE'

                                              ORDER BY SNAP.SNAP_ID))

                               WHERE START_TIME IS NOT NULL

                                     AND END_TIME IS NOT NULL

                            GROUP BY ROLLUP (EVENT_NAME, END_TIME))

                     WHERE NOT (G1 = 1 AND G2 = 1)) A

             WHERE A.EVENT_NAME NOT IN (SELECT EVENT_NAME

                                          FROM (SELECT EVENT_NAME, RNK

                                                  FROM (SELECT EVENT_NAME, RNK

                                                          FROM (SELECT EVENT_NAME,

                                                                       RANK ()

                                                                       OVER (

                                                                          ORDER BY

                                                                             VALUE_DIFF DESC,

                                                                             EVENT_NAME ASC NULLS LAST)

                                                                          RNK

                                                                  FROM (SELECT EVENT_NAME,

                                                                               DECODE (

                                                                                  G1,

                                                                                  1, 'SUB AVG',

                                                                                  SNAP_TIME)

                                                                                  SNAP_TIME,

                                                                               VALUE,

                                                                               VALUE_DIFF

                                                                          FROM (  SELECT EVENT_NAME,

                                                                                         END_TIME

                                                                                            SNAP_TIME,

                                                                                         AVG (

                                                                                            NVL (

                                                                                               VALUE,

                                                                                               0))

                                                                                            VALUE,

                                                                                         AVG (

                                                                                            NVL (

                                                                                               VALUE_DIFF,

                                                                                               0))

                                                                                            VALUE_DIFF,

                                                                                         GROUPING (

                                                                                            END_TIME)

                                                                                            G1,

                                                                                         GROUPING (

                                                                                            EVENT_NAME)

                                                                                            G2

                                                                                    FROM (SELECT EVENT_NAME,

                                                                                                 TO_CHAR (

                                                                                                    SNAP_TIME_C1,

                                                                                                    'MM.DD HH24:MI')

                                                                                                    START_TIME,

                                                                                                 TO_CHAR (

                                                                                                    SNAP_TIME_C2,

                                                                                                    'MM.DD HH24:MI')

                                                                                                    END_TIME,

                                                                                                 DECODE (

                                                                                                    SNAP_TIME_C2,

                                                                                                    NULL, 0,

                                                                                                    (CASE

                                                                                                        WHEN VALUE_2 <

                                                                                                                VALUE_1

                                                                                                        THEN

                                                                                                           0

                                                                                                        ELSE

                                                                                                           VALUE_2

                                                                                                           - VALUE_1

                                                                                                     END)

                                                                                                    / (1000000

                                                                                                       * (EXTRACT (

                                                                                                             DAY FROM SNAP_TIME_C2

                                                                                                                      - SNAP_TIME_C1)

                                                                                                          * 86400

                                                                                                          + EXTRACT (

                                                                                                               HOUR FROM SNAP_TIME_C2

                                                                                                                         - SNAP_TIME_C1)

                                                                                                            * 3600

                                                                                                          + EXTRACT (

                                                                                                               MINUTE FROM SNAP_TIME_C2

                                                                                                                           - SNAP_TIME_C1)

                                                                                                            * 60

                                                                                                          + EXTRACT (

                                                                                                               SECOND FROM SNAP_TIME_C2

                                                                                                                           - SNAP_TIME_C1))))

                                                                                                    VALUE,

                                                                                                 (CASE

                                                                                                     WHEN VALUE_2 <

                                                                                                             VALUE_1

                                                                                                     THEN

                                                                                                        0

                                                                                                     ELSE

                                                                                                        VALUE_2

                                                                                                        - VALUE_1

                                                                                                  END)

                                                                                                 / 1000000

                                                                                                    VALUE_DIFF,

                                                                                                 ROW_NUMBER ()

                                                                                                 OVER (

                                                                                                    PARTITION BY INSTANCE_NUMBER,

                                                                                                                 EVENT_NAME

                                                                                                    ORDER BY

                                                                                                       SNAP_ID)

                                                                                                    RNUM,

                                                                                                 SNAP_ID,

                                                                                                 INSTANCE_NUMBER

                                                                                            FROM (  SELECT SNAP.END_INTERVAL_TIME

                                                                                                              SNAP_TIME_C1,

                                                                                                           LEAD (

                                                                                                              SNAP.END_INTERVAL_TIME)

                                                                                                           OVER (

                                                                                                              PARTITION BY DBI.INSTANCE_NUMBER,

                                                                                                                           EVENT_NAME

                                                                                                              ORDER BY

                                                                                                                 SNAP.SNAP_ID)

                                                                                                              SNAP_TIME_C2,

                                                                                                           EVENT.EVENT_NAME,

                                                                                                           EVENT.TIME_WAITED_MICRO

                                                                                                              VALUE_1,

                                                                                                           LEAD (

                                                                                                              EVENT.TIME_WAITED_MICRO)

                                                                                                           OVER (

                                                                                                              PARTITION BY DBI.INSTANCE_NUMBER,

                                                                                                                           EVENT_NAME

                                                                                                              ORDER BY

                                                                                                                 SNAP.SNAP_ID)

                                                                                                              VALUE_2,

                                                                                                           SNAP.SNAP_ID,

                                                                                                           DBI.INSTANCE_NUMBER

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

                                                                                                     WHERE DBI.DBID =

                                                                                                              SNAP.DBID

                                                                                                           AND DBI.INSTANCE_NUMBER =

                                                                                                                  SNAP.INSTANCE_NUMBER

                                                                                                           AND SNAP.SNAP_ID >=

                                                                                                                  :15

                                                                                                           AND SNAP.SNAP_ID <=

                                                                                                                  :16

                                                                                                           AND SNAP.DBID =

                                                                                                                  EVENT.DBID

                                                                                                           AND SNAP.INSTANCE_NUMBER =

                                                                                                                  EVENT.INSTANCE_NUMBER

                                                                                                           AND SNAP.SNAP_ID =

                                                                                                                  EVENT.SNAP_ID

                                                                                                           AND UPPER (

                                                                                                                  EVENT.WAIT_CLASS) <>

                                                                                                                  'IDLE'

                                                                                                  ORDER BY SNAP.SNAP_ID))

                                                                                   WHERE START_TIME

                                                                                            IS NOT NULL

                                                                                         AND END_TIME

                                                                                                IS NOT NULL

                                                                                GROUP BY ROLLUP (EVENT_NAME,

                                                                                                 END_TIME))

                                                                         WHERE NOT (G1 =

                                                                                       1

                                                                                    AND G2 =

                                                                                           1))

                                                                 WHERE SNAP_TIME =

                                                                          'SUB AVG')

                                                         WHERE RNK <= 10)) B)

          GROUP BY A.SNAP_TIME, A.SNAP_TIME

          ORDER BY RNK, SNAP_TIME)

GROUP BY SNAP_TIME

ORDER BY SNAP_TIME;



블로그 이미지

운명을바꾸는자

IT와 함께 살아가는 삶

,


Oracle DB AWR 활용하여 IO 성능 조회 스크립트



  

SELECT SNAP_TIME,         SNAP_TIME_RANGE,

         TO_CHAR (SUM (DECODE (STAT_NAME, 'session logical reads', VALUE)),

                  'FM999,999,999,999.9')

            "Session Logical Reads/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'physical reads', VALUE)),

                  'FM999,999,999.9')

            "Physical Reads/Sec",

         TO_CHAR (

            SUM (DECODE (STAT_NAME, 'physical read total IO requests', VALUE))

            + SUM (

                 DECODE (STAT_NAME, 'physical write total IO requests', VALUE)),

            'FM999,999,999.9')

            "IOPS",

         TO_CHAR (

            (SUM (DECODE (STAT_NAME, 'physical read total bytes', VALUE))

             + SUM (DECODE (STAT_NAME, 'physical write total bytes', VALUE)))

            / 1024

            / 1024,

            'FM999,999,999.99')

            "MBPS",

         TO_CHAR (

            GREATEST (

               LEAST (

                  (1

                   - (SUM (DECODE (STAT_NAME, 'physical reads', VALUE))

                      - SUM (

                           DECODE (STAT_NAME, 'physical reads direct', VALUE))

                      - SUM (

                           DECODE (STAT_NAME,

                                   'physical reads direct (lob)', VALUE)))

                     / DECODE (

                          (SUM (

                              DECODE (STAT_NAME,

                                      'session logical reads', VALUE))

                           - SUM (

                                DECODE (STAT_NAME,

                                        'physical reads direct', VALUE))

                           - SUM (

                                DECODE (STAT_NAME,

                                        'physical reads direct (lob)', VALUE))),

                          0, 1,

                          (SUM (

                              DECODE (STAT_NAME,

                                      'session logical reads', VALUE))

                           - SUM (

                                DECODE (STAT_NAME,

                                        'physical reads direct', VALUE))

                           - SUM (

                                DECODE (STAT_NAME,

                                        'physical reads direct (lob)', VALUE)))))

                  * 100,

                  100),

               0),

            'FM999.99')

            "Buffer Cache Hit Ratio(%)",

         TO_CHAR (

            NVL (SUM (DECODE (STAT_NAME, 'db file sequential read', WAITS)), 0),

            'FM999,999,999.99')

            "db file sequential r avg.(ms)",

         TO_CHAR (

            NVL (SUM (DECODE (STAT_NAME, 'db file scattered read', WAITS)), 0),

            'FM999,999,999.99')

            "db file scattered r avg.(ms)",

         TO_CHAR (

            NVL (SUM (DECODE (STAT_NAME, 'db file parallel write', WAITS)), 0),

            'FM999,999,999.99')

            "db file parallel w avg.(ms)",

         TO_CHAR (

            NVL (SUM (DECODE (STAT_NAME, 'log file parallel write', WAITS)), 0),

            'FM999,999,999.99')

            "log file parallel w avg.(ms)",

         TO_CHAR (NVL (SUM (DECODE (STAT_NAME, 'log file sync', WAITS)), 0),

                  'FM999,999,999.99')

            "log file sync avg.(ms)",

         TO_CHAR (

            SUM (DECODE (STAT_NAME, 'physical read total IO requests', VALUE)),

            'FM999,999,999,999.9')

            "Physical R total IO req./Sec",

         TO_CHAR (

            SUM (DECODE (STAT_NAME, 'physical write total IO requests', VALUE)),

            'FM999,999,999,999.9')

            "Physical W total IO req./Sec",

         TO_CHAR (

              SUM (DECODE (STAT_NAME, 'physical read total bytes', VALUE))

            / 1024

            / 1024,

            'FM999,999,999,999.9')

            "Physical R total MB/Sec",

         TO_CHAR (

              SUM (DECODE (STAT_NAME, 'physical write total bytes', VALUE))

            / 1024

            / 1024,

            'FM999,999,999,999.9')

            "Physical W total MB/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'physical reads direct', VALUE)),

                  'FM999,999,999,999.9')

            "Physical Reads Direct/Sec",

         TO_CHAR (

            SUM (DECODE (STAT_NAME, 'physical reads direct (lob)', VALUE)),

            'FM999,999,999,999.9')

            "Physical Reads Direct(LOB)/Sec"

    FROM (SELECT STAT_NAME,

                 DECODE (G1,

                         1, 'SUB AVG',

                         SUBSTR (SNAP_TIME, 1, INSTR (SNAP_TIME, '-') - 1))

                    SNAP_TIME,

                 SNAP_TIME SNAP_TIME_RANGE,

                 NVL (VALUE, 0) VALUE,

                 NVL (VALUE_DIFF, 0) VALUE_DIFF,

                 NVL (WAITS, 0) WAITS,

                 NVL (WAITS_DIFF, 0) WAITS_DIFF

            FROM (  SELECT STAT_NAME,

                           START_TIME || '-' || END_TIME SNAP_TIME,

                           ROUND (AVG (NVL (VALUE, 0)), 3) VALUE,

                           ROUND (AVG (NVL (VALUE_DIFF, 0)), 3) VALUE_DIFF,

                           ROUND (AVG (NVL (WAITS, 0)), 3) WAITS,

                           ROUND (AVG (NVL (WAITS_DIFF, 0)), 3) WAITS_DIFF,

                           GROUPING (START_TIME || '-' || END_TIME) G1,

                           GROUPING (STAT_NAME) G2

                      FROM (SELECT STAT_NAME,

                                   TO_CHAR (SNAP_TIME_C1, 'MM.DD HH24:MI')

                                      START_TIME,

                                   TO_CHAR (SNAP_TIME_C2, 'MM.DD HH24:MI')

                                      END_TIME,

                                   DECODE (

                                      SNAP_TIME_C2,

                                      NULL, 0,

                                      ROUND (

                                         (CASE

                                             WHEN VALUE_2 < VALUE_1 THEN 0

                                             ELSE VALUE_2 - VALUE_1

                                          END)

                                         / (EXTRACT (

                                               DAY FROM SNAP_TIME_C2

                                                        - SNAP_TIME_C1)

                                            * 86400

                                            + EXTRACT (

                                                 HOUR FROM SNAP_TIME_C2

                                                           - SNAP_TIME_C1)

                                              * 3600

                                            + EXTRACT (

                                                 MINUTE FROM SNAP_TIME_C2

                                                             - SNAP_TIME_C1)

                                              * 60

                                            + EXTRACT (

                                                 SECOND FROM SNAP_TIME_C2

                                                             - SNAP_TIME_C1)),

                                         1))

                                      VALUE,

                                   (CASE

                                       WHEN VALUE_2 < VALUE_1 THEN 0

                                       ELSE VALUE_2 - VALUE_1

                                    END)

                                      VALUE_DIFF,

                                   NULL WAITS,

                                   NULL WAITS_DIFF,

                                   SNAP_ID,

                                   INSTANCE_NUMBER

                              FROM (  SELECT /*+ LEADING(DBI) USE_HASH(SNAP STAT) */

                                            SNAP.END_INTERVAL_TIME

                                                SNAP_TIME_C1,

                                             LEAD (

                                                SNAP.END_INTERVAL_TIME)

                                             OVER (

                                                PARTITION BY DBI.INSTANCE_NUMBER,

                                                             STAT_NAME

                                                ORDER BY SNAP.SNAP_ID)

                                                SNAP_TIME_C2,

                                             STAT.STAT_NAME,

                                             STAT.VALUE VALUE_1,

                                             LEAD (

                                                STAT.VALUE)

                                             OVER (

                                                PARTITION BY DBI.INSTANCE_NUMBER,

                                                             STAT_NAME

                                                ORDER BY SNAP.SNAP_ID)

                                                VALUE_2,

                                             SNAP.SNAP_ID,

                                             DBI.INSTANCE_NUMBER

                                        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 DBI.DBID = SNAP.DBID

                                             AND SNAP.SNAP_ID >= :3

                                             AND SNAP.SNAP_ID <= :4

                                             AND SNAP.DBID = STAT.DBID

                                             AND SNAP.INSTANCE_NUMBER =

                                                    STAT.INSTANCE_NUMBER

                                             AND SNAP.SNAP_ID = STAT.SNAP_ID

                                             AND STAT.STAT_NAME IN

                                                    ('session logical reads',

                                                     'physical reads',

                                                     'physical read total IO requests',

                                                     'physical write total IO requests',

                                                     'physical read total bytes',

                                                     'physical write total bytes',

                                                     'physical reads direct',

                                                     'physical reads direct (lob)')

                                    ORDER BY SNAP.SNAP_ID)

                             WHERE SNAP_TIME_C2 <> SNAP_TIME_C1

                            UNION ALL

                            SELECT STAT_NAME,

                                   TO_CHAR (SNAP_TIME_C1, 'MM.DD HH24:MI')

                                      START_TIME,

                                   TO_CHAR (SNAP_TIME_C2, 'MM.DD HH24:MI')

                                      END_TIME,

                                   ROUND (

                                      DECODE (

                                         SNAP_TIME_C2,

                                         NULL, 0,

                                         (CASE

                                             WHEN VALUE_2 < VALUE_1 THEN 0

                                             ELSE VALUE_2 - VALUE_1

                                          END)

                                         / (EXTRACT (

                                               DAY FROM SNAP_TIME_C2

                                                        - SNAP_TIME_C1)

                                            * 86400

                                            + EXTRACT (

                                                 HOUR FROM SNAP_TIME_C2

                                                           - SNAP_TIME_C1)

                                              * 3600

                                            + EXTRACT (

                                                 MINUTE FROM SNAP_TIME_C2

                                                             - SNAP_TIME_C1)

                                              * 60

                                            + EXTRACT (

                                                 SECOND FROM SNAP_TIME_C2

                                                             - SNAP_TIME_C1)))

                                      / 1000000,

                                      3)

                                      VALUE,

                                   ROUND (

                                      (CASE

                                          WHEN VALUE_2 < VALUE_1 THEN 0

                                          ELSE VALUE_2 - VALUE_1

                                       END)

                                      / 1000000,

                                      1)

                                      VALUE_DIFF,

                                   ROUND (

                                      DECODE (

                                         SNAP_TIME_C2,

                                         NULL, 0,

                                         (CASE

                                             WHEN WAITS_2 <= WAITS_1 THEN 0

                                             ELSE VALUE_2 - VALUE_1

                                          END)

                                         / (WAITS_2 - WAITS_1))

                                      / 1000,

                                      3)

                                      WAITS,

                                   ROUND (

                                      (CASE

                                          WHEN WAITS_2 <= WAITS_1 THEN 0

                                          ELSE WAITS_2 - WAITS_1

                                       END),

                                      1)

                                      WAITS_DIFF,

                                   SNAP_ID,

                                   INSTANCE_NUMBER

                              FROM (  SELECT SNAP.END_INTERVAL_TIME SNAP_TIME_C1,

                                             LEAD (

                                                SNAP.END_INTERVAL_TIME)

                                             OVER (

                                                PARTITION BY DBI.INSTANCE_NUMBER,

                                                             EVENT_NAME

                                                ORDER BY SNAP.SNAP_ID)

                                                SNAP_TIME_C2,

                                             STAT.EVENT_NAME STAT_NAME,

                                             STAT.TIME_WAITED_MICRO VALUE_1,

                                             LEAD (

                                                STAT.TIME_WAITED_MICRO)

                                             OVER (

                                                PARTITION BY DBI.INSTANCE_NUMBER,

                                                             EVENT_NAME

                                                ORDER BY SNAP.SNAP_ID)

                                                VALUE_2,

                                             STAT.TOTAL_WAITS WAITS_1,

                                             LEAD (

                                                STAT.TOTAL_WAITS)

                                             OVER (

                                                PARTITION BY DBI.INSTANCE_NUMBER,

                                                             EVENT_NAME

                                                ORDER BY SNAP.SNAP_ID)

                                                WAITS_2,

                                             SNAP.SNAP_ID,

                                             DBI.INSTANCE_NUMBER

                                        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_SYSTEM_EVENT 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.EVENT_NAME IN

                                                    ('db file sequential read',

                                                     'db file scattered read',

                                                     'db file parallel write',

                                                     'log file parallel write',

                                                     'log file sync')

                                    ORDER BY SNAP.SNAP_ID)

                             WHERE SNAP_TIME_C2 <> SNAP_TIME_C1

                                   AND WAITS_2 <> WAITS_1)

                     WHERE START_TIME IS NOT NULL AND END_TIME IS NOT NULL

                  GROUP BY ROLLUP (STAT_NAME, START_TIME || '-' || END_TIME))

           WHERE NOT (G1 = 1 AND G2 = 1))

GROUP BY SNAP_TIME, SNAP_TIME_RANGE

ORDER BY SNAP_TIME




블로그 이미지

운명을바꾸는자

IT와 함께 살아가는 삶

,

Oracle AWR을 활용한 주요 Stats 지표 추출 스크립트



 

해당 시점의 SNAP.SNAP_ID를 설정하여 추출한 데이터를 엑셀 또는 시각화 Tool로 뽑아내여 주요 Stats 상태 분석이 가능하도록 해준다. 각 Stats의 명칭을 조정하여 원하는 DB 상태를 조회해볼수 있다.



  SELECT SNAP_TIME,

         SNAP_TIME_RANGE,

         TO_CHAR (SUM (DECODE (STAT_NAME, 'session logical reads', VALUE)),

                  'FM999,999,999,999.9')

            "Session Logical Reads/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'physical reads', VALUE)),

                  'FM999,999,999.9')

            "Physical Reads/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'execute count', VALUE)),

                  'FM999,999,999.9')

            "Execute Count/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'user calls', VALUE)),

                  'FM999,999,999.9')

            "User Calls/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'user commits', VALUE)),

                  'FM999,999,999.9')

            "User Commits/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'user rollbacks', VALUE)),

                  'FM999,999,999.9')

            "User Rollbacks/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'CPU used by this session', VALUE)),

                  'FM999,999,999.9')

            "CPU used by this session/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'DB time', VALUE)),

                  'FM999,999,999.9')

            "DB time/Sec",

         TO_CHAR (

            (SUM (DECODE (STAT_NAME, 'DB time', VALUE)) * 10)

            / DECODE (SUM (DECODE (STAT_NAME, 'user calls', VALUE)),

                      0, 1,

                      SUM (DECODE (STAT_NAME, 'user calls', VALUE))),

            'FM999,999,999.999')

            "User Response Time(ms)/Sec",

         TO_CHAR (

            (SUM (DECODE (STAT_NAME, 'DB time', VALUE)) * 10)

            / DECODE (

                 (SUM (DECODE (STAT_NAME, 'user commits', VALUE))

                  + SUM (DECODE (STAT_NAME, 'user rollbacks', VALUE))),

                 0, 1,

                 (SUM (DECODE (STAT_NAME, 'user commits', VALUE))

                  + SUM (DECODE (STAT_NAME, 'user rollbacks', VALUE)))),

            'FM999,999,999.999')

            "TX Response Time(ms)/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'application wait time', VALUE)),

                  'FM999,999,999.9')

            "Application Wait Time(cs)/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'cluster wait time', VALUE)),

                  'FM999,999,999.9')

            "Cluster Wait Time(cs)/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'concurrency wait time', VALUE)),

                  'FM999,999,999.9')

            "Concurrency Wait Time(cs)/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'user I/O wait time', VALUE)),

                  'FM999,999,999.9')

            "User I/O Wait Time(cs)/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'CR blocks created', VALUE)),

                  'FM999,999,999.9')

            "CR blocks created/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'consistent gets', VALUE)),

                  'FM999,999,999.9')

            "Consistent Gets/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'consistent changes', VALUE)),

                  'FM999,999,999.9')

            "Consistent Changes/Sec",

         TO_CHAR (

            SUM (

               DECODE (

                  STAT_NAME,

                  'data blocks consistent reads - undo records applied', VALUE)),

            'FM999,999,999.9')

            "DB Blocks cr-undo rec appl/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'db block gets', VALUE)),

                  'FM999,999,999.9')

            "DB Block Gets/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'db block changes', VALUE)),

                  'FM999,999,999.9')

            "DB Block Changes/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'enqueue releases', VALUE)),

                  'FM999,999,999.9')

            "Enqueue Releases/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'enqueue waits', VALUE)),

                  'FM999,999,999.9')

            "Enqueue Waits/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'free buffer inspected', VALUE)),

                  'FM999,999,999.9')

            "Free Buffer Inspected/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'free buffer requested', VALUE)),

                  'FM999,999,999.9')

            "Free Buffer Requested/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'DBWR checkpoints', VALUE)),

                  'FM999,999,999.9')

            "DBWR checkpoints/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'DBWR lru scans', VALUE)),

                  'FM999,999,999.9')

            "DBWR lru scans/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'DBWR undo block writes', VALUE)),

                  'FM999,999,999.9')

            "DBWR undo block writes/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'physical writes', VALUE)),

                  'FM999,999,999.9')

            "Physical Writes/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'physical writes direct', VALUE)),

                  'FM999,999,999.9')

            "Physical Writes Direct/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'branch node splits', VALUE)),

                  'FM999,999,999.9')

            "Branch Node Splits/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'leaf node splits', VALUE)),

                  'FM999,999,999.9')

            "Leaf Node Splits/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'leaf node 90-10 splits', VALUE)),

                  'FM999,999,999.9')

            "Leaf Node 90-10 Splits/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'commit cleanouts', VALUE)),

                  'FM999,999,999.9')

            "Commit Cleanouts/Sec",

         TO_CHAR (

            SUM (

               DECODE (STAT_NAME,

                       'cleanouts only - consistent read gets', VALUE)),

            'FM999,999,999.9')

            "Cleanouts Only-CRgets/Sec",

         TO_CHAR (

            SUM (

               DECODE (STAT_NAME,

                       'cleanouts and rollbacks - consistent read gets', VALUE)),

            'FM999,999,999.9')

            "Cleanouts&Rollbacks-CRgets/Sec",

         TO_CHAR (

            SUM (DECODE (STAT_NAME, 'index fast full scans (full)', VALUE)),

            'FM999,999,999.9')

            "Index FastFullScans(full)/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'table fetch by rowid', VALUE)),

                  'FM999,999,999.9')

            "Table Fetch By Rowid/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'table fetch continued row', VALUE)),

                  'FM999,999,999.9')

            "Table Fetch Continued Row/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'table scans (long tables)', VALUE)),

                  'FM999,999,999.9')

            "Table Scans (long tables)/Sec",

         TO_CHAR (

            SUM (DECODE (STAT_NAME, 'table scans (short tables)', VALUE)),

            'FM999,999,999.9')

            "Table Scans (short tables)/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'table scan blocks gotten', VALUE)),

                  'FM999,999,999.9')

            "Table Scan Blocks gotten/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'table scan rows gotten', VALUE)),

                  'FM999,999,999.9')

            "Table Scan Rows gotten/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'logons cumulative', VALUE)),

                  'FM999,999,999.9')

            "Logons Cumulative/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'opened cursors cumulative', VALUE)),

                  'FM999,999,999.9')

            "Opened Cursors Cumulative/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'parse count (total)', VALUE)),

                  'FM999,999,999.9')

            "Parse Count (total)/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'parse count (hard)', VALUE)),

                  'FM999,999,999.9')

            "Parse Count (hard)/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'parse count (failures)', VALUE)),

                  'FM999,999,999.9')

            "Parse Count (failures)/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'parse time cpu', VALUE)),

                  'FM999,999,999.9')

            "Parse Time CPU/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'parse time elapsed', VALUE)),

                  'FM999,999,999.9')

            "Parse Time Elapsed/Sec",

         TO_CHAR (

            SUM (DECODE (STAT_NAME, 'session cursor cache count', VALUE)),

            'FM999,999,999.9')

            "Session Cursor Cache Count/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'session cursor cache hits', VALUE)),

                  'FM999,999,999.9')

            "Session Cursor Cache Hits/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'redo entries', VALUE)),

                  'FM999,999,999.9')

            "Redo Entries/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'redo size', VALUE)),

                  'FM999,999,999.9')

            "Redo Size/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'redo write time', VALUE)),

                  'FM999,999,999.9')

            "Redo Write Time/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'sorts (disk)', VALUE)),

                  'FM999,999,999.9')

            "Sorts (disk)/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'sorts (memory)', VALUE)),

                  'FM999,999,999.9')

            "Sorts (memory)/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'sorts (rows)', VALUE)),

                  'FM999,999,999.9')

            "Sorts (rows)/Sec",

         TO_CHAR (

            SUM (

               DECODE (

                  STAT_NAME,

                  'transaction tables consistent reads - undo records applied', VALUE)),

            'FM999,999,999.9')

            "TX cr Undo Record applied/Sec",

         TO_CHAR (

            SUM (

               DECODE (STAT_NAME,

                       'transaction tables consistent read rollbacks', VALUE)),

            'FM999,999,999.9')

            "TX cr read rollbacks/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'undo change vector size', VALUE)),

                  'FM999,999,999.9')

            "Undo Change Vector Size/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'logons current', VALUE)),

                  'FM999,999,999.9')

            "Logons Current",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'opened cursors current', VALUE)),

                  'FM999,999,999.9')

            "Opened Cursors Current",

         TO_CHAR (

            SUM (

               DECODE (STAT_NAME, 'SQL*Net roundtrips to/from client', VALUE)),

            'FM999,999,999.9')

            "SQLNet RoundTrip Client/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'physical read total bytes', VALUE)),

                  'FM999,999,999.9')

            "Physical Read Total Bytes/Sec",

         TO_CHAR (

            SUM (

               DECODE (STAT_NAME, 'cell physical IO interconnect bytes', VALUE)),

            'FM999,999,999.9')

            "CellPhy.IOInterconnectByte/Sec",

         TO_CHAR (

            SUM (

               DECODE (STAT_NAME,

                       'cell physical IO bytes saved by storage index', VALUE)),

            'FM999,999,999.9')

            "CellPhy.IOBytesSavedbySInd/Sec",

         TO_CHAR (

            SUM (

               DECODE (

                  STAT_NAME,

                  'cell physical IO interconnect bytes returned by smart scan', VALUE)),

            'FM999,999,999.9')

            "CellPhy.IOByteReturnedbySS/Sec",

         TO_CHAR (

            SUM (DECODE (STAT_NAME, 'physical read total IO requests', VALUE)),

            'FM999,999,999.9')

            "Physical Read Total IO Req/Sec",

         TO_CHAR (

            SUM (DECODE (STAT_NAME, 'physical read requests optimized', VALUE)),

            'FM999,999,999.9')

            "Physical Read Req Opt./Sec",

         TO_CHAR (

            SUM (DECODE (STAT_NAME, 'cell flash cache read hits', VALUE)),

            'FM999,999,999.9')

            "Cell Flash Cache Read Hits/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'cell scans', VALUE)),

                  'FM999,999,999.9')

            "Cell Scans/Sec",

         TO_CHAR (SUM (DECODE (STAT_NAME, 'cell index scans', VALUE)),

                  'FM999,999,999.9')

            "Cell Index Scans/Sec"

    FROM (SELECT STAT_NAME,

                 DECODE (G1,

                         1, 'SUB AVG',

                         SUBSTR (SNAP_TIME, 1, INSTR (SNAP_TIME, '-') - 1))

                    SNAP_TIME,

                 SNAP_TIME SNAP_TIME_RANGE,

                 VALUE,

                 VALUE_DIFF

            FROM (  SELECT STAT_NAME,

                           START_TIME || '-' || END_TIME SNAP_TIME,

                           ROUND (AVG (NVL (VALUE, 0)), 1) VALUE,

                           ROUND (AVG (NVL (VALUE_DIFF, 0)), 1) VALUE_DIFF,

                           GROUPING (START_TIME || '-' || END_TIME) G1,

                           GROUPING (STAT_NAME) G2

                      FROM (SELECT STAT_NAME,

                                   TO_CHAR (SNAP_TIME_C1, 'MM.DD HH24:MI')

                                      START_TIME,

                                   TO_CHAR (SNAP_TIME_C2, 'MM.DD HH24:MI')

                                      END_TIME,

                                   DECODE (

                                      SNAP_TIME_C2,

                                      NULL, 0,

                                      ROUND (

                                         (CASE

                                             WHEN VALUE_2 < VALUE_1 THEN 0

                                             ELSE VALUE_2 - VALUE_1

                                          END)

                                         / (EXTRACT (

                                               DAY FROM SNAP_TIME_C2

                                                        - SNAP_TIME_C1)

                                            * 86400

                                            + EXTRACT (

                                                 HOUR FROM SNAP_TIME_C2

                                                           - SNAP_TIME_C1)

                                              * 3600

                                            + EXTRACT (

                                                 MINUTE FROM SNAP_TIME_C2

                                                             - SNAP_TIME_C1)

                                              * 60

                                            + EXTRACT (

                                                 SECOND FROM SNAP_TIME_C2

                                                             - SNAP_TIME_C1)),

                                         1))

                                      VALUE,

                                   (CASE

                                       WHEN VALUE_2 < VALUE_1 THEN 0

                                       ELSE VALUE_2 - VALUE_1

                                    END)

                                      VALUE_DIFF,

                                   ROW_NUMBER ()

                                   OVER (PARTITION BY INSTANCE_NUMBER, STAT_NAME

                                         ORDER BY SNAP_ID)

                                      RNUM,

                                   SNAP_ID,

                                   INSTANCE_NUMBER

                              FROM (  SELECT /*+ LEADING(DBI) USE_HASH(SNAP STAT) */

                                            SNAP.END_INTERVAL_TIME

                                                SNAP_TIME_C1,

                                             LEAD (

                                                SNAP.END_INTERVAL_TIME)

                                             OVER (

                                                PARTITION BY DBI.INSTANCE_NUMBER,

                                                             STAT_NAME

                                                ORDER BY SNAP.SNAP_ID)

                                                SNAP_TIME_C2,

                                             STAT.STAT_NAME,

                                             STAT.VALUE VALUE_1,

                                             LEAD (

                                                STAT.VALUE)

                                             OVER (

                                                PARTITION BY DBI.INSTANCE_NUMBER,

                                                             STAT_NAME

                                                ORDER BY SNAP.SNAP_ID)

                                                VALUE_2,

                                             SNAP.SNAP_ID,

                                             DBI.INSTANCE_NUMBER

                                        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 DBI.DBID = SNAP.DBID

                                             AND SNAP.SNAP_ID >= :3

                                             AND SNAP.SNAP_ID <= :4

                                             AND SNAP.DBID = STAT.DBID

                                             AND SNAP.INSTANCE_NUMBER =

                                                    STAT.INSTANCE_NUMBER

                                             AND SNAP.SNAP_ID = STAT.SNAP_ID

                                             AND STAT.STAT_NAME IN

                                                    ('session logical reads',

                                                     'physical reads',

                                                     'execute count',

                                                     'user calls',

                                                     'user commits',

                                                     'user rollbacks',

                                                     'CPU used by this session',

                                                     'DB time',

                                                     'application wait time',

                                                     'cluster wait time',

                                                     'concurrency wait time',

                                                     'user I/O wait time',

                                                     'CR blocks created',

                                                     'consistent changes',

                                                     'consistent gets',

                                                     'db block changes',

                                                     'data blocks consistent reads - undo records applied',

                                                     'db block gets',

                                                     'enqueue releases',

                                                     'enqueue waits',

                                                     'DBWR checkpoints',

                                                     'DBWR lru scans',

                                                     'DBWR undo block writes',

                                                     'free buffer inspected',

                                                     'free buffer requested',

                                                     'physical writes',

                                                     'physical writes direct',

                                                     'branch node splits',

                                                     'leaf node 90-10 splits',

                                                     'leaf node splits',

                                                     'cleanouts and rollbacks - consistent read gets',

                                                     'cleanouts only - consistent read gets',

                                                     'commit cleanouts',

                                                     'index fast full scans (full)',

                                                     'table fetch by rowid',

                                                     'table fetch continued row',

                                                     'table scan blocks gotten',

                                                     'table scan rows gotten',

                                                     'table scans (long tables)',

                                                     'table scans (short tables)',

                                                     'logons cumulative',

                                                     'opened cursors cumulative',

                                                     'parse count (total)',

                                                     'parse count (hard)',

                                                     'parse count (failures)',

                                                     'parse time cpu',

                                                     'parse time elapsed',

                                                     'session cursor cache count',

                                                     'session cursor cache hits',

                                                     'redo entries',

                                                     'redo size',

                                                     'redo sync time',

                                                     'redo write time',

                                                     'sorts (disk)',

                                                     'sorts (memory)',

                                                     'sorts (rows)',

                                                     'sql area evicted',

                                                     'sql area purged',

                                                     'undo change vector size',

                                                     'SQL*Net roundtrips to/from client',

                                                     'transaction tables consistent reads - undo records applied',

                                                     'transaction tables consistent read rollbacks',

                                                     'physical read total bytes',

                                                     'physical read total IO requests',

                                                     'physical read requests optimized',

                                                     'cell physical IO interconnect bytes',

                                                     'cell physical IO bytes saved by storage index',

                                                     'cell physical IO interconnect bytes returned by smart scan',

                                                     'cell flash cache read hits',

                                                     'cell scans',

                                                     'cell index scans')

                                    ORDER BY SNAP.SNAP_ID)

                             WHERE SNAP_TIME_C2 <> SNAP_TIME_C1)

                     WHERE START_TIME IS NOT NULL AND END_TIME IS NOT NULL

                  GROUP BY ROLLUP (STAT_NAME, START_TIME || '-' || END_TIME))

           WHERE NOT (G1 = 1 AND G2 = 1)

          UNION ALL

          SELECT STAT_NAME,

                 DECODE (G1,

                         1, 'SUB AVG',

                         SUBSTR (SNAP_TIME, 1, INSTR (SNAP_TIME, '-') - 1))

                    SNAP_TIME,

                 SNAP_TIME SNAP_TIME_RANGE,

                 VALUE,

                 NULL VALUE_DIFF

            FROM (  SELECT STAT_NAME,

                           START_TIME || '-' || END_TIME SNAP_TIME,

                           ROUND (

                              AVG (DECODE (SIGN (VALUE), -1, 0, NVL (VALUE, 0))),

                              1)

                              VALUE,

                           GROUPING (START_TIME || '-' || END_TIME) G1,

                           GROUPING (STAT_NAME) G2

                      FROM (SELECT STAT_NAME,

                                   TO_CHAR (SNAP_TIME_1, 'MM.DD HH24:MI')

                                      START_TIME,

                                   TO_CHAR (SNAP_TIME_2, 'MM.DD HH24:MI')

                                      END_TIME,

                                   DECODE (SNAP_TIME_2,

                                           NULL, 0,

                                           ROUND ( (VALUE_1 - VALUE_2), 1))

                                      VALUE,

                                   ROW_NUMBER ()

                                   OVER (PARTITION BY INSTANCE_NUMBER, STAT_NAME

                                         ORDER BY SNAP_ID)

                                      RNUM,

                                   SNAP_ID,

                                   INSTANCE_NUMBER

                              FROM (  SELECT SNAP.BEGIN_INTERVAL_TIME SNAP_TIME_1,

                                             STAT.STAT_NAME,

                                             STAT.VALUE VALUE_1,

                                             0 VALUE_2,

                                             SNAP.END_INTERVAL_TIME SNAP_TIME_2,

                                             SNAP.SNAP_ID,

                                             DBI.INSTANCE_NUMBER

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

                                                    ('opened cursors current',

                                                     'logons current',

                                                     'session pga memory',

                                                     'session uga memory',

                                                     'session pga memory max',

                                                     'session uga memory max')

                                    ORDER BY SNAP.SNAP_ID))

                     WHERE RNUM > 1

                  GROUP BY ROLLUP (STAT_NAME, START_TIME || '-' || END_TIME))

           WHERE NOT (G1 = 1 AND G2 = 1)

          ORDER BY STAT_NAME, SNAP_TIME)

GROUP BY SNAP_TIME, SNAP_TIME_RANGE

ORDER BY SNAP_TIME








블로그 이미지

운명을바꾸는자

IT와 함께 살아가는 삶

,



AWR 활용 Monitoring(DBA_HIST_* View)

    • Oracle AWR의 기능은 활용가치가 매우 높다. 기본적으로 특정 시점 별로 스냅샷을 찍어 보관하고 있어 쌓여 있는 정보들을 어떻게 가공하는지에 따라서 그래픽 한 형태로 추출해낼 수 있다.
    • 단순히 AWR Report나 ADDM Report로 추출하는 것보다 원하는 정보를 SQL문으로 질의하여 추출하는 것이 더 의미 있는 정보를 빨리 얻어 낼 수 있다.
    • 아래 내역들은 SQL문으로 핵심이 되는 Table을 Join하여 데이터를 추출하였으며 더 나아간다면 엑셀이나 Program을 통하여 S/W를 만들어 즉각적으로 분석 및 모니터링 하는 것이 가능하다.

         

  1. DBA_HIST_* View 활용

    • DBA_HIST_* View로 구성되어 있는 AWR은 다양한 DB 정보를 일정 기간 동안 보관하고 있어 과거의 데이터부터 현재까지의 연속된 데이터를 그래프로 사용자 편의에 좋게 그려낼 수 있으며 이상 증후 및 패턴을 분석하여 DB 관리에 있어 모니터링이 가능하다.
    • Statspack의 경우에도 stats$* Table들을 활용하여 많은 정보를 추출 할 수 있다.

       

         

  2. CPU & IO Wait Time, Load 등

- 기본적인 CPU, IO 사용량, Load와 같은 부하 정도를 아래 테이블(DBA_HIST_OSSTAT)을 통하여 측정할 수 있으며 DBA_HIST_SNAPSHOT 테이블의 경우에 해당 시점과 항상 Join하기 때문에 구조와 데이터를 잘 파악해두면 좋다.

   

   

  3.Buffer Busy waits, latch free 등

- Buffer Busy Waits, latch Free와 같은 성능적인 지표(DBA_HIST_SYSTEM_EVENT)에 대하여도 추출할 수 있으며 이슈가 되는 시점을 분석하여 집중적으로 Tuning을 진행할 수 있으며 대처에 대한 부분을 강구할 수 도 있다.

   

   

  4.Top 5 Wait Event

- AWR Report에 추출되는 Top5 Wait Events(DBA_HIST_SYS_TIME_MODEL)를 일자 별로 연결하여 봄으로써 빈번하게 발생되는 Event와 주요 이슈에 대하여 즉각적으로 확인하여 볼 수 있다.

   

  5. SQL 분석

- 기존의 SQL분석 시에 v$sql, v$sqlarea, v$sqlplan과 같은 휘발성 데이터를 이용하다 보니 폭넓은 SQL수집이 어려웠지만 AWR을 구성하는 테이블을 활용한다면 보관 주기 동안에 수집된 SQL문을 모두 확인하여 분석할 수 있다.


블로그 이미지

운명을바꾸는자

IT와 함께 살아가는 삶

,

AWR Report 기초

1. IT Story/DB 2015. 8. 4. 21:30

   

   

1. AWR 이란?

1. AWR 이란?

AWR(Automatic Workload Repository)은 모든 주요 통계와 작업 로드에 대한 정보 스냅샷 형태로 저장하는 저장소이다.

AWR 스냅샷은 기본적으로 시간당 1회 캡처되며 8일간(Oracle Database 11g) 데이터베이스에 보존되며 수집 및

보관주기의 변경이 가능하다.

사용자는 이를 이용하여 성능 문제 분석 및 운영 예측 작업을 수행할 수 있으며 AWR에 의해 수집되는 데이터는 다음과 같다.

수집 데이터

분석 내용

DB 대기 이벤트 및 통계 정보

오라클 자원(경합현상, 메모리, 세그먼트, 락, RAC등) 사용 현황 파악

시스템 통계 정보

CPU, 디스크, 네트워크 등 시스템 자원 이용 통계

데이터베이스 부하 정보

그룹화된 DB 사용 부하의 통계 수치

SQL 수행 정보

SQL 통계 정보 및 실행 계획 정보

활동 세션 정보

ASH(Active Session Hisory)를 이용한 해당 스냅샷 시점의 세션 정보 파악 가능

   

2. AWR 관리

1. AWR 스냅샷 관리 방법

AWR 스냅샷이란 메모리에서 디스크로 저장하는 시점의 AWR 데이터를 의미한다. 스냅샷에서도 저장 공간

크기, 저장 기간, 수집 주기 등을 고려해야 하며 백업 및 복구 등의 관리가 필요하다. 오라클에서는 AWR 스냅샷

관리를 위해 DBMS_WORKLOAD_REPOSITORY 패키지를 제공한다.

항목

사용법

AWR 스냅샷 삭제

BEGIN

DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHO_RANGE

(LOW_SNAP_ID => snap_id, HIGH_SNAP_ID => snapid [DBID => dbid]);

END;

/

AWR 스냅샷 설정 변경

BEGIN

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS

([RETENTION => retention_time][,INTERVAL => interval_time]

[,TOPNSQL => topn_sql_number]);

END;

/

베이스라인 설정

BEGIN

DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE

(START_SNAP_ID => sanp_id, END_SNAPID => sanp_id,

BASELINE_NAME => 'baseline_name'[, DBID => dbid]);

END;

/

베이스라인 삭제

BEGIN

DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE

(BASELINE_NAME => 'baseline_name'[, CASCADE => true|false][,DBID => dbid]);

END;

/

   

2. AWR 데이터 백업 복구

AWR 관리 주기 및 테이블스페이스 공간 부족 등으로 AWR 데이터를 삭제할 경우, 삭제 대상 AWR 데이터를 백업할

필요가 발생할 수 있다. 이때, awrextr.sql 스크립트를 수행하면 데이터 펌프를 이용하여 AWR 데이터를 덤프 파일로

저장 할 수 있으며 추후 이 파일을 이용하여 복구를 수행할 수 있다.

 

1. AWR 데이터 백업

- awrextr.sql를 수행하여 백업 진행 ($ORACLE_HOME/rdbms/admin에 존재)

- 지정한 file name의 ".dmp"로 파일 생성

2. AWR 데이터 복구

- awrload.sql을 수행하여 복구 진행 ($ORACLE_HOME/rdbms/admin에 존재)

- 백업된 AWR 데이터가 저장되어 있던 데이터베이스 ID와 동일한 데이터베이스 ID를 갖는 데이터 베이스에

적재할 수 없음.


3. AWR 보고서 생성

AWR 보고서는 시작 스냅샷 번호와 종료 스냅샷 번호를 설정하여, 두 구간의 대기 이벤트 발생 횟수 및 통계치의

차이를 계산하여 보여준다. 주의할 점은 인스턴스가 종료할 때마다 대기 이벤트 및 통계수치가 재설정되므로,

스냅샷 구간 사이에 인스턴스가 종료된 적이 있다면 AWR 보고서의 수치는 의미가 없으며, 생성되지 않는다.

AWR 보고서는 DBMS_WORKLOAD_REPOSITORY 패키지와 오라클에서 제공하는 스크립트를 사용하여 생성 할 수 있다.

모든 AWR 관련 스크립트는 다음 위치에 있다.

  • $ORACLE_HOME/rdbms/admin/
  • 사용 용도별 스크립트

사용 용도

스크립트

AWR DB 보고서 생성

awrrpt.sql, awrrpti.sql

AWR DB 비교 보고서 생성

awrddrpt.sql, awrddrpi.sql

AWR SQL 보고서 생성

awrsqrpt.sql, awrsqrpi.sql

AWR 정보 검색 보고서

awrinfo.sql

ASH 보고서 생성

ashrpt.sql, ashrpti.sql

ADDM 보고서 생성

addmrpt.sql, addmrpti.sql

기타 스크립트

awrddinp.sql, awrinput.sql, awrinpnmp.sql


1. AWR 보고서 생성

AWR 보고서를 생성하기 위해서는 다음과 같은 권한이 필요하다.

  • SELECT ANY DIRECTORY 권한
  • SYS.DMBS_WORKLOAD_REPOSITORY 패키지 실행 권한

  AWR 보고서를 생성하기 위해서는 awrrpt.sql과 awrpti.sql 스크립트를 사용한다.

스크립트

설명

awrrpt.sql

스크립트를 수행한 인스턴스의 AWR 보고서를 생성한다.

awrrpti.sql

DB ID와 인스턴스 번호를 지정해서 AWR 보고서를 생성한다.

   

1) awrrpt.sql을 이용한 AWR 보고서 생성 단계

 

awrrpt.sql 실행

 

AWR 보고서 출력 타입 지정 ( HTML / TEXT ) : HTML타입으로 지정

 

awrrpt.sql을 수행하는 DB ID가 자동으로 선택 됐으며 스냅샷 ID 리스트를 보여주는 기간을 지정한다.

   

스냅샷 번호 목록이 보이면 AWR 보고서를 생성하려는 구간의 시작 스냅샷 번호와 종료 스냅샷 번호를 지정한다.

생성될 AWR 보고서 이름을 지정한다. awr_test.html로 지정

   

 

2) DBMS_WORKLOAD_REPOSITORY 패키지를 사용한 방법

프로시저

설명

AWR_REPORT_TEXT

텍스트 형식의 AWR 보고서 생성

AWR_REPORT_HTML

웹 문서 형식의 AWR 보고서 생성

 

AWR_REPORT_HTML 사용법

SELECT OUTPUT

FROM TABLE (DBMS_WORKLOAD_REPOSITOR.AWR_REPORT_HTML(

:DBID, :INST_ID, :BEGIN_SNAP, :END_SNAP) );


블로그 이미지

운명을바꾸는자

IT와 함께 살아가는 삶

,