'ASH TOP Activity'에 해당되는 글 1건

 SELECT sysmetric_history.sample_time,

         cpu / 60 AS cpu,

         bcpu / 60 AS bcpu,

         DECODE (SIGN ( (cpu + bcpu) / 60 - cpu_ora_consumed),

                 -1, 0,

                 ( (cpu + bcpu) / 60 - cpu_ora_consumed))

            AS cpu_ora_wait,

         scheduler / 60 AS scheduler,

         uio / 60 AS uio,

         sio / 60 AS sio,

         concurrency / 60 AS concurrency,

         application / 60 AS application,

         COMMIT / 60 AS COMMIT,

         configuration / 60 AS configuration,

         administrative / 60 AS administrative,

         network / 60 AS network,

         queueing / 60 AS queueing,

         clust / 60 AS clust,

         other / 60 AS other

    FROM (SELECT TRUNC (sample_time, 'MI') AS sample_time,

                 DECODE (

                    session_state,

                    'ON CPU', DECODE (session_type,

                                      'BACKGROUND', 'BCPU',

                                      'ON CPU'),

                    wait_class)

                    AS wait_class

            FROM v$active_session_history

           WHERE sample_time > TO_DATE ('201501120000', 'YYYYMMDDHH24MI') -- 일자 조정 필요

                 AND sample_time <= TRUNC (SYSDATE, 'MI'))               --ASH

                                                          PIVOT (COUNT (*)

                                                           FOR wait_class

                                                           IN  ('ON CPU' AS cpu,

                                                               'BCPU' AS bcpu,

                                                               'Scheduler' AS scheduler,

                                                               'User I/O' AS uio,

                                                               'System I/O' AS sio,

                                                               'Concurrency' AS concurrency,

                                                               'Application' AS application,

                                                               'Commit' AS COMMIT,

                                                               'Configuration' AS configuration,

                                                               'Administrative' AS administrative,

                                                               'Network' AS network,

                                                               'Queueing' AS queueing,

                                                               'Cluster' AS clust,

                                                               'Other' AS other)) ash,

         (SELECT TRUNC (begin_time, 'MI') AS sample_time,

                 VALUE / 100 AS cpu_ora_consumed

            FROM v$sysmetric_history              -- 해당 최대 BEGIN_TIME까지만 조회 가능

           WHERE GROUP_ID = 2 AND metric_name = 'CPU Usage Per Sec') sysmetric_history

   WHERE ash.sample_time(+) = sysmetric_history.sample_time

ORDER BY sample_time;

/* Formatted on 2015-02-25 15:29:21 (QP5 v5.163.1008.3004) */

-- v$active_session_history,v$sysmetric_history

-- With CPU wait

-- With Oracle pre 11g

  SELECT sysmetric_history.sample_time,

         cpu,

         bcpu,

         DECODE (SIGN ( (cpu + bcpu) - cpu_ora_consumed),

                 -1, 0,

                 ( (cpu + bcpu) - cpu_ora_consumed))

            AS cpu_ora_wait,

         scheduler,

         uio,

         sio,

         concurrency,

         application,

         COMMIT,

         configuration,

         administrative,

         network,

         queueing,

         clust,

         other

    FROM (  SELECT TRUNC (sample_time, 'MI') AS sample_time,

                   SUM (

                      DECODE (

                         session_state,

                         'ON CPU', DECODE (session_type, 'BACKGROUND', 0, 1),

                         0))

                   / 60

                      AS cpu,

                   SUM (

                      DECODE (

                         session_state,

                         'ON CPU', DECODE (session_type, 'BACKGROUND', 1, 0),

                         0))

                   / 60

                      AS bcpu,

                   SUM (DECODE (wait_class, 'Scheduler', 1, 0)) / 60 AS scheduler,

                   SUM (DECODE (wait_class, 'User I/O', 1, 0)) / 60 AS uio,

                   SUM (DECODE (wait_class, 'System I/O', 1, 0)) / 60 AS sio,

                   SUM (DECODE (wait_class, 'Concurrency', 1, 0)) / 60

                      AS concurrency,

                   SUM (DECODE (wait_class, 'Application', 1, 0)) / 60

                      AS application,

                   SUM (DECODE (wait_class, 'Commit', 1, 0)) / 60 AS COMMIT,

                   SUM (DECODE (wait_class, 'Configuration', 1, 0)) / 60

                      AS configuration,

                   SUM (DECODE (wait_class, 'Administrative', 1, 0)) / 60

                      AS administrative,

                   SUM (DECODE (wait_class, 'Network', 1, 0)) / 60 AS network,

                   SUM (DECODE (wait_class, 'Queueing', 1, 0)) / 60 AS queueing,

                   SUM (DECODE (wait_class, 'Cluster', 1, 0)) / 60 AS clust,

                   SUM (DECODE (wait_class, 'Other', 1, 0)) / 60 AS other

              FROM v$active_session_history

             WHERE sample_time > TO_DATE ('201501120000', 'YYYYMMDDHH24MI')                -- 간격조정

                   AND sample_time <= TRUNC (SYSDATE, 'MI')

          GROUP BY TRUNC (sample_time, 'MI')) ash,

         (SELECT TRUNC (begin_time, 'MI') AS sample_time,

                 VALUE / 100 AS cpu_ora_consumed

            FROM v$sysmetric_history -- 해당 최대 BEGIN_TIME까지만 조회 가능

           WHERE GROUP_ID = 2 AND metric_name = 'CPU Usage Per Sec') sysmetric_history

   WHERE ash.sample_time(+) = sysmetric_history.sample_time

ORDER BY sample_time;

/* Formatted on 2015-02-25 15:45:53 (QP5 v5.163.1008.3004) */

--Without CPU Wait

-- v$active_session_history

SELECT sample_time,

       cpu / 60 AS cpu,

       bcpu / 60 AS bcpu,

       scheduler / 60 AS scheduler,

       uio / 60 AS uio,

       sio / 60 AS sio,

       concurrency / 60 AS concurrency,

       application / 60 AS application,

       COMMIT / 60 AS COMMIT,

       configuration / 60 AS configuration,

       administrative / 60 AS administrative,

       network / 60 AS network,

       queueing / 60 AS queueing,

       clust / 60 AS clust,

       other / 60 AS other

  FROM (SELECT TRUNC (sample_time, 'MI') AS sample_time,

               DECODE (

                  session_state,

                  'ON CPU', DECODE (session_type,

                                    'BACKGROUND', 'BCPU',

                                    'ON CPU'),

                  wait_class)

                  AS wait_class

          FROM v$active_session_history

         WHERE sample_time > TO_DATE ('201501120000', 'YYYYMMDDHH24MI') -- 일자 조정 필요

               AND sample_time <= TRUNC (SYSDATE, 'MI'))                 --ash

                                                        PIVOT (COUNT (*)

                                                         FOR wait_class

                                                         IN  ('ON CPU' AS cpu,

                                                             'BCPU' AS bcpu,

                                                             'Scheduler' AS scheduler,

                                                             'User I/O' AS uio,

                                                             'System I/O' AS sio,

                                                             'Concurrency' AS concurrency,

                                                             'Application' AS application,

                                                             'Commit' AS COMMIT,

                                                             'Configuration' AS configuration,

                                                             'Administrative' AS administrative,

                                                             'Network' AS network,

                                                             'Queueing' AS queueing,

                                                             'Cluster' AS clust,

                                                             'Other' AS other));