'DB 성능 조회'에 해당되는 글 1건


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와 함께 살아가는 삶

,