'OS 상태 조회'에 해당되는 글 1건

SELECT SNAP_TIME,

         SUM (CASE STAT_NAME WHEN 'USER_TIME' THEN VALUE END) USER_TIME,

         SUM (CASE STAT_NAME WHEN 'SYS_TIME' THEN VALUE END) SYS_TIME,

         SUM (CASE STAT_NAME WHEN 'BUSY_TIME' THEN VALUE END) BUSY_TIME,

         SUM (CASE STAT_NAME WHEN 'IOWAIT_TIME' THEN VALUE END) IOWAIT_TIME,

         SUM (CASE STAT_NAME WHEN 'IDLE_TIME' THEN VALUE END) IDLE_TIME,

         SUM (CASE STAT_NAME WHEN 'AVG_USER_TIME' THEN VALUE END) AVG_USER_TIME,

         SUM (CASE STAT_NAME WHEN 'AVG_SYS_TIME' THEN VALUE END) AVG_SYS_TIME,

         SUM (CASE STAT_NAME WHEN 'AVG_BUSY_TIME' THEN VALUE END) AVG_BUSY_TIME,

         SUM (CASE STAT_NAME WHEN 'AVG_IOWAIT_TIME' THEN VALUE END)

            AVG_IOWAIT_TIME,

         SUM (CASE STAT_NAME WHEN 'AVG_IDLE_TIME' THEN VALUE END) AVG_IDLE_TIME,

         SUM (CASE STAT_NAME WHEN 'LOAD' THEN VALUE END) LOAD,

         SUM (CASE STAT_NAME WHEN 'NUM_CPUS' THEN VALUE END) NUM_CPUS,

         SUM (CASE STAT_NAME WHEN 'NUM_CPU_SOCKETS' THEN VALUE END)

            NUM_CPU_SOCKETS,

         SUM (CASE STAT_NAME WHEN 'OS_CPU_WAIT_TIME' THEN VALUE END)

            OS_CPU_WAIT_TIME,

         SUM (CASE STAT_NAME WHEN 'PHYSICAL_MEMORY_BYTES' THEN VALUE END)

            PHYSICAL_MEMORY_BYTES,

         SUM (CASE STAT_NAME WHEN 'RSRC_MGR_CPU_WAIT_TIME' THEN VALUE END)

            RSRC_MGR_CPU_WAIT_TIME,

         SUM (CASE STAT_NAME WHEN 'VM_IN_BYTES' THEN VALUE END) VM_IN_BYTES,

         SUM (CASE STAT_NAME WHEN 'VM_OUT_BYTES' THEN VALUE END) VM_OUT_BYTES

    FROM (SELECT STAT_NAME,

                 START_TIME SNAP_TIME,

                 VALUE,

                 VALUE_DIFF

            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 END_TIME SNAP_TIME_C1,

                                 LEAD (

                                    END_TIME)

                                 OVER (PARTITION BY INSTANCE_NUMBER, STAT_NAME

                                       ORDER BY SNAP_ID)

                                    SNAP_TIME_C2,

                                 VALUE VALUE_1,

                                 LEAD (

                                    VALUE)

                                 OVER (PARTITION BY INSTANCE_NUMBER, STAT_NAME

                                       ORDER BY SNAP_ID)

                                    VALUE_2,

                                 STAT_NAME,

                                 SNAP_ID,

                                 INSTANCE_NUMBER

                            FROM (SELECT SNAP.END_INTERVAL_TIME END_TIME,

                                         SNAP.SNAP_ID,

                                         DHO.STAT_NAME,

                                         DHO.VALUE,

                                         DHO.INSTANCE_NUMBER

                                    FROM DBA_HIST_OSSTAT DHO,

                                         DBA_HIST_SNAPSHOT SNAP

                                   WHERE DHO.SNAP_ID = SNAP.SNAP_ID

                                         AND DHO.DBID = SNAP.DBID

                                         AND DHO.INSTANCE_NUMBER =

                                                SNAP.INSTANCE_NUMBER

                                         AND DHO.DBID = (select dbid from v$database) --변경

                                         AND DHO.INSTANCE_NUMBER = (select  INSTANCE_NUMBER from v$instance)  --변경

                                         AND DHO.SNAP_ID >= :3

                                         AND DHO.SNAP_ID <= :4))))

GROUP BY SNAP_TIME

ORDER BY SNAP_TIME