Oracle database experiences

Oracle database blog

Average Active Sessions – Light Sampling Sessions – Views update

Scope

The views v$aas_light_sampling_sessions, v$cdb_pdb_aas_lss, v$top_activity_session_lss and v$session_activity_lss defined in the Part XXI, Part XXII, Part XXIII and Part XXIV respectively, are better suited for high database activity periods e.g. at leat one active session per sample, but are not well suited for low database activity periods.

We will modify the following views in order to handle high database activity periods as well as low database activity periods.

Assumption:

  • Those views are based on a sampling period interval set to 2 seconds.

View v$aas_light_sampling_sessions (Part XXI)

CREATE OR REPLACE VIEW v$aas_light_sampling_sessions
AS
SELECT sample_yyyymmddhhmi,
       SUBSTR(sample_yyyymmddhhmi,9,2) || ':' || SUBSTR(sample_yyyymmddhhmi,11,2) BEGIN_TIME,
       aas1                                                                 C_CPU,
       aas1+aas2                                                            C_OTHER,
       aas1+aas2+aas3                                                       C_APPLICATION,
       aas1+aas2+aas3+aas4                                                  C_CONFIGURATION,
       aas1+aas2+aas3+aas4+aas5                                             C_ADMINISTRATIVE,
       aas1+aas2+aas3+aas4+aas5+aas6                                        C_CONCURRENCY,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7                                   C_COMMIT,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8                              C_NETWORK,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9                         C_USER_IO,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10                   C_SYSTEM_IO,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11             C_SCHEDULER,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11+aas12       C_CLUSTER,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11+aas12+aas13 C_QUEUEING,
       MAX(CEIL(aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11+aas12+aas13)) OVER() MAX_AAS
FROM
   (SELECT sample_yyyymmddhhmi,
           TRUNC((count_cpu/(nb_samples)),3)            aas1,  --aas_cpu,
           TRUNC((count_other/(nb_samples)),3)          aas2,  --aas_other,
           TRUNC((count_application/(nb_samples)),3)    aas3,  --aas_application,
           TRUNC((count_configuration/(nb_samples)),3)  aas4,  --aas_configuration,
           TRUNC((count_administrative/(nb_samples)),3) aas5,  --aas_administrative,
           TRUNC((count_concurrency/(nb_samples)),3)    aas6,  --aas_concurrency,
           TRUNC((count_commit/(nb_samples)),3)         aas7,  --aas_commit,
           TRUNC((count_network/(nb_samples)),3)        aas8,  --aas_network,
           TRUNC((count_user_io/(nb_samples)),3)        aas9,  --aas_user_io,
           TRUNC((count_system_io/(nb_samples)),3)      aas10, --aas_system_io,
           TRUNC((count_scheduler/(nb_samples)),3)      aas11, --aas_scheduler,
           TRUNC((count_cluster/(nb_samples)),3)        aas12, --aas_cluster,
           TRUNC((count_queueing/(nb_samples)),3)       aas13  --aas_queueing    
    FROM 
       (SELECT NVL(co_base_qry.sample_yyyymmddhhmi,time_window.start_time) sample_yyyymmddhhmi,
               NVL(co_base_qry.nb_samples,1)           nb_samples,
               NVL(co_base_qry.count_cpu,0)            count_cpu,
               NVL(co_base_qry.count_other,0)          count_other,
               NVL(co_base_qry.count_application,0)    count_application,
               NVL(co_base_qry.count_configuration,0)  count_configuration,
               NVL(co_base_qry.count_administrative,0) count_administrative,
               NVL(co_base_qry.count_concurrency,0)    count_concurrency,
               NVL(co_base_qry.count_commit,0)         count_commit,
               NVL(co_base_qry.count_network,0)        count_network,
               NVL(co_base_qry.count_user_io,0)        count_user_io,
               NVL(co_base_qry.count_system_io,0)      count_system_io,
               NVL(co_base_qry.count_scheduler,0)      count_scheduler,
               NVL(co_base_qry.count_cluster,0)        count_cluster,
               NVL(co_base_qry.count_queueing,0)       count_queueing
        FROM
           (SELECT TO_CHAR(SYSDATE - (LEVEL/24/60),'YYYYMMDDHH24MI') start_time
            FROM DUAL 
            CONNECT BY LEVEL <= 61
            ) time_window
        LEFT OUTER JOIN 
           (SELECT TO_CHAR(sample_time,'YYYYMMDDHH24MI') sample_yyyymmddhhmi,
                   30 nb_samples,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),-1,1)) count_cpu,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),0,1))  count_other,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),1,1))  count_application,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),2,1))  count_configuration,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),3,1))  count_administrative,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),4,1))  count_concurrency,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),5,1))  count_commit,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),7,1))  count_network,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),8,1))  count_user_io,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),9,1))  count_system_io,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),10,1)) count_scheduler,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),11,1)) count_cluster,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),12,1)) count_queueing
            FROM lss$_sampling_session
            GROUP BY TO_CHAR(sample_time,'YYYYMMDDHH24MI')
            ) co_base_qry
          ON time_window.start_time=co_base_qry.sample_yyyymmddhhmi
       )
  );

View v$cdb_pdb_aas_lss (Part XXII)

CREATE OR REPLACE VIEW v$cdb_pdb_aas_lss
AS          
SELECT sample_yyyymmddhhmi,
       SUBSTR(sample_yyyymmddhhmi,9,2) || ':' || SUBSTR(sample_yyyymmddhhmi,11,2)                                        BEGIN_TIME,
       aas_cpu0                                                                                                          C_CONT0_CPU,
       aas_cpu0+aas_wait0                                                                                                C_CONT0_WAIT,
       aas_cpu0+aas_wait0+aas_cpu1                                                                                       C_CONT1_CPU,
       aas_cpu0+aas_wait0+aas_cpu1+aas_wait1                                                                             C_CONT1_WAIT,
       aas_cpu0+aas_wait0+aas_cpu1+aas_wait1+aas_cpu2                                                                    C_CONT2_CPU,
       aas_cpu0+aas_wait0+aas_cpu1+aas_wait1+aas_cpu2+aas_wait2                                                          C_CONT2_WAIT,
       aas_cpu0+aas_wait0+aas_cpu1+aas_wait1+aas_cpu2+aas_wait2+aas_cpu3                                                 C_CONT3_CPU,
       aas_cpu0+aas_wait0+aas_cpu1+aas_wait1+aas_cpu2+aas_wait2+aas_cpu3+aas_wait3                                       C_CONT3_WAIT,
       aas_cpu0+aas_wait0+aas_cpu1+aas_wait1+aas_cpu2+aas_wait2+aas_cpu3+aas_wait3+aas_cpu4                              C_CONT4_CPU,
       aas_cpu0+aas_wait0+aas_cpu1+aas_wait1+aas_cpu2+aas_wait2+aas_cpu3+aas_wait3+aas_cpu4+aas_wait4                    C_CONT4_WAIT,
       aas_cpu0+aas_wait0+aas_cpu1+aas_wait1+aas_cpu2+aas_wait2+aas_cpu3+aas_wait3+aas_cpu4+aas_wait4+aas_cpu5           C_CONT5_CPU,
       aas_cpu0+aas_wait0+aas_cpu1+aas_wait1+aas_cpu2+aas_wait2+aas_cpu3+aas_wait3+aas_cpu4+aas_wait4+aas_cpu5+aas_wait5 C_CONT5_WAIT,
       MAX(CEIL(aas_cpu0+aas_wait0+aas_cpu1+aas_wait1+aas_cpu2+aas_wait2+aas_cpu3+aas_wait3+aas_cpu4+aas_wait4+aas_cpu5+aas_wait5)) OVER() MAX_CONT_AAS
FROM 
   (SELECT NVL(co_base_qry.sample_yyyymmddhhmi,time_window.start_time) sample_yyyymmddhhmi,
           NVL(co_base_qry.sum_aas_cpu0,0)   aas_cpu0,
           NVL(co_base_qry.sum_aas_wait0,0)  aas_wait0,
           NVL(co_base_qry.sum_aas_cpu1,0)   aas_cpu1,
           NVL(co_base_qry.sum_aas_wait1,0)  aas_wait1,
           NVL(co_base_qry.sum_aas_cpu2,0)   aas_cpu2,
           NVL(co_base_qry.sum_aas_wait2,0)  aas_wait2,
           NVL(co_base_qry.sum_aas_cpu3,0)   aas_cpu3,
           NVL(co_base_qry.sum_aas_wait3,0)  aas_wait3,
           NVL(co_base_qry.sum_aas_cpu4,0)   aas_cpu4,
           NVL(co_base_qry.sum_aas_wait4,0)  aas_wait4,
           NVL(co_base_qry.sum_aas_cpu5,0)   aas_cpu5,
           NVL(co_base_qry.sum_aas_wait5,0)  aas_wait5
    FROM 
       (SELECT TO_CHAR(SYSDATE - (LEVEL/24/60),'YYYYMMDDHH24MI') start_time
        FROM DUAL 
        CONNECT BY LEVEL <= 61
       ) time_window
    LEFT OUTER JOIN 
       (SELECT sample_yyyymmddhhmi,
               SUM(aas_cpu0)  sum_aas_cpu0,
               SUM(aas_wait0) sum_aas_wait0,
               SUM(aas_cpu1)  sum_aas_cpu1,
               SUM(aas_wait1) sum_aas_wait1,
               SUM(aas_cpu2)  sum_aas_cpu2,
               SUM(aas_wait2) sum_aas_wait2,
               SUM(aas_cpu3)  sum_aas_cpu3,
               SUM(aas_wait3) sum_aas_wait3,
               SUM(aas_cpu4)  sum_aas_cpu4,
               SUM(aas_wait4) sum_aas_wait4,
               SUM(aas_cpu5)  sum_aas_cpu5,
               SUM(aas_wait5) sum_aas_wait5
        FROM 
           (SELECT sample_yyyymmddhhmi,
                   CASE con_id
                      WHEN 0 THEN TRUNC(count_cpu/(nb_samples),3)
                      ELSE 0
                   END aas_cpu0,
                   CASE con_id
                      WHEN 0 THEN TRUNC(count_wait/(nb_samples),3)
                      ELSE 0
                   END aas_wait0,
                   CASE con_id
                      WHEN 1 THEN TRUNC(count_cpu/(nb_samples),3)
                      ELSE 0
                   END aas_cpu1,
                   CASE con_id
                      WHEN 1 THEN TRUNC(count_wait/(nb_samples),3)
                      ELSE 0
                   END aas_wait1,
                   CASE con_id
                      WHEN 3 THEN TRUNC(count_cpu/(nb_samples),3)
                      ELSE 0
                   END aas_cpu2,
                   CASE con_id
                      WHEN 3 THEN TRUNC(count_wait/(nb_samples),3)
                      ELSE 0
                   END aas_wait2,
                   CASE con_id
                      WHEN 4 THEN TRUNC(count_cpu/(nb_samples),3)
                      ELSE 0
                   END aas_cpu3,
                   CASE con_id
                      WHEN 4 THEN TRUNC(count_wait/(nb_samples),3)
                      ELSE 0
                   END aas_wait3,
                   CASE con_id
                      WHEN 5 THEN TRUNC(count_cpu/(nb_samples),3)
                      ELSE 0
                   END aas_cpu4,
                   CASE con_id
                      WHEN 5 THEN TRUNC(count_wait/(nb_samples),3)
                      ELSE 0
                   END aas_wait4,
                   CASE con_id
                      WHEN 6 THEN TRUNC(count_cpu/(nb_samples),3)
                      ELSE 0
                   END aas_cpu5,
                   CASE con_id
                      WHEN 6 THEN TRUNC(count_wait/(nb_samples),3)
                      ELSE 0
                   END aas_wait5
            FROM 
               (SELECT TO_CHAR(sample_time,'YYYYMMDDHH24MI') sample_yyyymmddhhmi,
                       con_id,
                       30 nb_samples,
                       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),-1,1)) count_cpu,
                       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),0,1,1,1,2,1,3,1,4,1,5,1,7,1,8,1,9,1,10,1,11,1,12,1)) count_wait
                FROM lss$_sampling_session
                GROUP BY con_id,
                        TO_CHAR(sample_time,'YYYYMMDDHH24MI')
                )
           ) GROUP BY sample_yyyymmddhhmi
       ) co_base_qry
      ON time_window.start_time=co_base_qry.sample_yyyymmddhhmi
   );

View v$top_activity_session_lss (Part XXIII)

CREATE OR REPLACE VIEW v$top_activity_session_lss
AS
SELECT sid,
       serial#,
       user#,
       user_program,
       user_con_id,
       count_total,
       aas1                                                                 C_CPU,
       aas1+aas2                                                            C_OTHER,
       aas1+aas2+aas3                                                       C_APPLICATION,
       aas1+aas2+aas3+aas4                                                  C_CONFIGURATION,
       aas1+aas2+aas3+aas4+aas5                                             C_ADMINISTRATIVE,
       aas1+aas2+aas3+aas4+aas5+aas6                                        C_CONCURRENCY,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7                                   C_COMMIT,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8                              C_NETWORK,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9                         C_USER_IO,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10                   C_SYSTEM_IO,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11             C_SCHEDULER,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11+aas12       C_CLUSTER,
       aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11+aas12+aas13 C_QUEUEING,
       MAX(aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11+aas12+aas13) OVER() MAX_AAS
FROM 
   (SELECT sid,
           serial#,
           max_user#   user#,
           max_program user_program,
           max_con_id  user_con_id,
           TRUNC((count_cpu/(max_samples)),3)            aas1,  --aas_cpu
           TRUNC((count_other/(max_samples)),3)          aas2,  --aas_other
           TRUNC((count_application/(max_samples)),3)    aas3,  --aas_application
           TRUNC((count_configuration/(max_samples)),3)  aas4,  --aas_configuration
           TRUNC((count_administrative/(max_samples)),3) aas5,  --aas_administrative
           TRUNC((count_concurrency/(max_samples)),3)    aas6,  --aas_concurrency
           TRUNC((count_commit/(max_samples)),3)         aas7,  --aas_commit
           TRUNC((count_network/(max_samples)),3)        aas8,  --aas_network
           TRUNC((count_user_io/(max_samples)),3)        aas9,  --aas_user_io
           TRUNC((count_system_io/(max_samples)),3)      aas10, --aas_system_io
           TRUNC((count_scheduler/(max_samples)),3)      aas11, --aas_scheduler
           TRUNC((count_cluster/(max_samples)),3)        aas12, --aas_cluster
           TRUNC((count_queueing/(max_samples)),3)       aas13, --aas_queueing 
           count_total
    FROM
       (SELECT sid,
               serial#,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),-1,1)) count_cpu,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),0,1))  count_other,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),1,1))  count_application,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),2,1))  count_configuration,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),3,1))  count_administrative,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),4,1))  count_concurrency,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),5,1))  count_commit,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),7,1))  count_network,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),8,1))  count_user_io,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),9,1))  count_system_io,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),10,1)) count_scheduler,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),11,1)) count_cluster,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),12,1)) count_queueing,
               COUNT(*)                             count_total,
               300                                  max_samples,
               MAX(user#)                           max_user#,
               MAX(program)                         max_program,
               MAX(con_id)                          max_con_id
        FROM lss$_sampling_session
        WHERE sample_time >= SYSDATE -(10/24/60)
        GROUP BY sid,
                 serial#
        ORDER BY count_total DESC
        FETCH FIRST 20 ROWS ONLY
        )
   );

View v$session_activity_lss (Part XXIV)

CREATE OR REPLACE VIEW v$session_activity_lss
AS
WITH session_events AS
   (
   SELECT event,
          COUNT(*) count_event, 
          RANK() OVER (ORDER BY COUNT(*) DESC, EVENT) rank_event 
   FROM lss$_sampling_session
   WHERE SID = get_current_session
     AND sample_time >= SYSDATE -(1/24) 
     AND session_state <> 'C'
   GROUP BY event
   ORDER BY count_event DESC
   FETCH FIRST 12 ROWS ONLY
   )
SELECT sample_yyyymmddhhmi,
       SUBSTR(sample_yyyymmddhhmi,9,2) || ':' || SUBSTR(sample_yyyymmddhhmi,11,2) BEGIN_TIME,
       avge1                                                                             C_EVENT0,
       avge1+avge2                                                                       C_EVENT1,
       avge1+avge2+avge3                                                                 C_EVENT2,
       avge1+avge2+avge3+avge4                                                           C_EVENT3,
       avge1+avge2+avge3+avge4+avge5                                                     C_EVENT4,
       avge1+avge2+avge3+avge4+avge5+avge6                                               C_EVENT5,
       avge1+avge2+avge3+avge4+avge5+avge6+avge7                                         C_EVENT6,
       avge1+avge2+avge3+avge4+avge5+avge6+avge7+avge8                                   C_EVENT7,
       avge1+avge2+avge3+avge4+avge5+avge6+avge7+avge8+avge9                             C_EVENT8,
       avge1+avge2+avge3+avge4+avge5+avge6+avge7+avge8+avge9+avge10                      C_EVENT9,
       avge1+avge2+avge3+avge4+avge5+avge6+avge7+avge8+avge9+avge10+avge11               C_EVENT10,
       avge1+avge2+avge3+avge4+avge5+avge6+avge7+avge8+avge9+avge10+avge11+avge12        C_EVENT11,
       avge1+avge2+avge3+avge4+avge5+avge6+avge7+avge8+avge9+avge10+avge11+avge12+avge13 C_EVENT12,
       MAX(avge1+avge2+avge3+avge4+avge5+avge6+avge7+avge8+avge9+avge10+avge11+avge12+avge13) OVER() MAX_EVENT,
       (SELECT get_current_session FROM DUAL) session_id,
       MAX(event_name0)  OVER() EVENT_NAME0,
       MAX(event_name1)  OVER() EVENT_NAME1,
       MAX(event_name2)  OVER() EVENT_NAME2,
       MAX(event_name3)  OVER() EVENT_NAME3,
       MAX(event_name4)  OVER() EVENT_NAME4,
       MAX(event_name5)  OVER() EVENT_NAME5,
       MAX(event_name6)  OVER() EVENT_NAME6,
       MAX(event_name7)  OVER() EVENT_NAME7,
       MAX(event_name8)  OVER() EVENT_NAME8,
       MAX(event_name9)  OVER() EVENT_NAME9,
       MAX(event_name10) OVER() EVENT_NAME10,
       MAX(event_name11) OVER() EVENT_NAME11,
       MAX(event_name12) OVER() EVENT_NAME12
FROM
   (SELECT sample_yyyymmddhhmi,
           TRUNC((count_event0/(nb_samples)),3)  avge1,  
           TRUNC((count_event1/(nb_samples)),3)  avge2,  
           TRUNC((count_event2/(nb_samples)),3)  avge3, 
           TRUNC((count_event3/(nb_samples)),3)  avge4,  
           TRUNC((count_event4/(nb_samples)),3)  avge5,  
           TRUNC((count_event5/(nb_samples)),3)  avge6,  
           TRUNC((count_event6/(nb_samples)),3)  avge7,  
           TRUNC((count_event7/(nb_samples)),3)  avge8,  
           TRUNC((count_event8/(nb_samples)),3)  avge9,  
           TRUNC((count_event9/(nb_samples)),3)  avge10, 
           TRUNC((count_event10/(nb_samples)),3) avge11, 
           TRUNC((count_event11/(nb_samples)),3) avge12, 
           TRUNC((count_event12/(nb_samples)),3) avge13,  
           event_name0,
           event_name1,
           event_name2,
           event_name3,
           event_name4,
           event_name5,
           event_name6,
           event_name7,
           event_name8,
           event_name9,
           event_name10,
           event_name11,
           event_name12
    FROM 
       (SELECT NVL(co_base_qry.sample_yyyymmddhhmi,time_window.start_time) sample_yyyymmddhhmi,
               NVL(co_base_qry.nb_samples,1)    nb_samples,
               NVL(co_base_qry.count_event0,0)  count_event0,
               NVL(co_base_qry.count_event1,0)  count_event1,
               NVL(co_base_qry.count_event2,0)  count_event2,
               NVL(co_base_qry.count_event3,0)  count_event3,
               NVL(co_base_qry.count_event4,0)  count_event4,
               NVL(co_base_qry.count_event5,0)  count_event5,
               NVL(co_base_qry.count_event6,0)  count_event6,
               NVL(co_base_qry.count_event7,0)  count_event7,
               NVL(co_base_qry.count_event8,0)  count_event8,
               NVL(co_base_qry.count_event9,0)  count_event9,
               NVL(co_base_qry.count_event10,0) count_event10,
               NVL(co_base_qry.count_event11,0) count_event11,
               NVL(co_base_qry.count_event12,0) count_event12,
               co_base_qry.event_name0,
               co_base_qry.event_name1,
               co_base_qry.event_name2,
               co_base_qry.event_name3,
               co_base_qry.event_name4,
               co_base_qry.event_name5,
               co_base_qry.event_name6,
               co_base_qry.event_name7,
               co_base_qry.event_name8,
               co_base_qry.event_name9,
               co_base_qry.event_name10,
               co_base_qry.event_name11,
               co_base_qry.event_name12
        FROM
           (SELECT TO_CHAR(SYSDATE - (LEVEL/24/60),'YYYYMMDDHH24MI') start_time
            FROM DUAL 
            CONNECT BY LEVEL <= 61
            ) time_window
        LEFT OUTER JOIN 
           (SELECT TO_CHAR(sample_time,'YYYYMMDDHH24MI') sample_yyyymmddhhmi,
                   30 nb_samples,
                   COUNT(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),-1,1)) count_event0,
                   COUNT(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),1,1))  count_event1,
                   COUNT(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),2,1))  count_event2,
                   COUNT(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),3,1))  count_event3,
                   COUNT(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),4,1))  count_event4,
                   COUNT(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),5,1))  count_event5,
                   COUNT(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),6,1))  count_event6,
                   COUNT(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),7,1))  count_event7,
                   COUNT(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),8,1))  count_event8,
                   COUNT(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),9,1))  count_event9,
                   COUNT(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),10,1)) count_event10,
                   COUNT(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),11,1)) count_event11,
                   COUNT(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),12,1)) count_event12,
                   'cpu'    event_name0,
                   MAX(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),1,sess_event.event))  event_name1,
                   MAX(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),2,sess_event.event))  event_name2,
                   MAX(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),3,sess_event.event))  event_name3,
                   MAX(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),4,sess_event.event))  event_name4,
                   MAX(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),5,sess_event.event))  event_name5,
                   MAX(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),6,sess_event.event))  event_name6,
                   MAX(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),7,sess_event.event))  event_name7,
                   MAX(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),8,sess_event.event))  event_name8,
                   MAX(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),9,sess_event.event))  event_name9,
                   MAX(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),10,sess_event.event)) event_name10,
                   MAX(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),11,sess_event.event)) event_name11,
                   MAX(DECODE(DECODE(session_state,'C',-1,sess_event.rank_event),12,sess_event.event)) event_name12
            FROM lss$_sampling_session lss
            LEFT OUTER JOIN session_events sess_event
              ON sess_event.event = lss.event
            WHERE lss.sid = get_current_session
            GROUP BY TO_CHAR(sample_time,'YYYYMMDDHH24MI')
            ) co_base_qry
          ON time_window.start_time=co_base_qry.sample_yyyymmddhhmi
       )
  );

Remarks:

  • The views v$aas_light_sampling_sessions, v$cdb_pdb_aas_lss, v$session_activity_lss and v$top_activity_session_lss are created from the CDB$ROOT container in the C##LSS schema,
  • The view v$session_activity30s_lss (Part XXV) will be updated shortly.

Errata

  • In the view v$top_activity_session_lss, the “max_samples” column value should be 300 instead of 30 (the view has been fixed to reflect this change).
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: