Oracle ASH TOP Activity

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