Oracle database experiences

Oracle database blog

Generating SVG graphics with SQL*Plus – Part XXV – Stacked area graph – Session Activity – Light Sampling Sessions – Update

Scope

In this post we will focus on tracking session activity at CDB/PDB level and on increasing the accuracy of the graph. The previous post displays the consolidated session id at the instance level. If we want to track a session at the CDB/PDB level we need:

  • A session id,
  • A serial#,
  • A container id.

Function get_current_session

We will slightly modify the function get_current_session in order to get the triplet ‘[session_id],[serial#],[con_id]’:

CREATE OR REPLACE FUNCTION get_current_session RETURN VARCHAR2
AS 
   v_param_value lss$parameter.parameter_value%TYPE;
BEGIN

   SELECT parameter_value
   INTO v_param_value
   FROM lss$parameter
   WHERE parameter_name = 'current_session';
   
   RETURN v_param_value;
   
   EXCEPTION
      WHEN TOO_MANY_ROWS THEN
         RETURN '<UNKNOWN>';
      WHEN NO_DATA_FOUND THEN
         RETURN '<UNKNOWN>';
      WHEN OTHERS THEN
         RAISE;
         
END get_current_session;

Basically the triplet will be stored in the table lss$parameter as a string.

Increase graph accuracy

Instead of counting the events on a period of time of one minute we will count them on a period of time of 30 seconds. We will create a new view v$session_activity30s_lss in order to display the session activity every 30 seconds. The starting point will be the view v$session_activity_lss.

View creation – Step 1

The differences with the previous post are:

WITH session_events AS
   (
   SELECT event,
          COUNT(*) count_event, 
          RANK() OVER (ORDER BY COUNT(*) DESC, EVENT) rank_event 
   FROM lss$_sampling_session
   WHERE TO_CHAR(sid) || ',' || TO_CHAR(serial#) || ',' || TO_CHAR(con_id) = 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
   )

The session identifier is now ‘[session_id],[serial#],[con_id]’.

View creation – Step 2

The differences with the previous post are:

            SELECT TO_CHAR(sample_time,'YYYYMMDDHH24MI') || TO_CHAR(TRUNC(TO_NUMBER(TO_CHAR(sample_time,'SS'))/30)) sample_yyyymmddhhmi30s,
                   MAX(sample_id) - MIN(sample_id) 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 TO_CHAR(lss.sid) || ',' || TO_CHAR(lss.serial#) || ',' || TO_CHAR(lss.con_id) = get_current_session
            GROUP BY TO_CHAR(sample_time,'YYYYMMDDHH24MI') || TO_CHAR(TRUNC(TO_NUMBER(TO_CHAR(sample_time,'SS'))/30))

The session identifier is now ‘[session_id],[serial#],[con_id]’. In order to count the events every 30 seconds, we will define the first 30 seconds of a minute as “0” and the last 30 seconds of a minute as “1”. The time dimension is now called sample_yyyymmddhhmi30s.

View creation – Step 3

The differences with the previous post are:

        SELECT NVL(co_base_qry.sample_yyyymmddhhmi30s,time_window.start_time) sample_yyyymmddhhmi30s,
               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*30)/24/60/60),'YYYYMMDDHH24MI') || TO_CHAR(TRUNC(TO_NUMBER(TO_CHAR(SYSDATE - ((LEVEL*30)/24/60/60),'SS'))/30)) start_time
            FROM DUAL 
            CONNECT BY LEVEL <= 122
            ) time_window
        LEFT OUTER JOIN 
           (SELECT TO_CHAR(sample_time,'YYYYMMDDHH24MI') || TO_CHAR(TRUNC(TO_NUMBER(TO_CHAR(sample_time,'SS'))/30)) sample_yyyymmddhhmi30s,
                   MAX(sample_id) - MIN(sample_id) 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 TO_CHAR(lss.sid) || ',' || TO_CHAR(lss.serial#) || ',' || TO_CHAR(lss.con_id) = get_current_session
            GROUP BY TO_CHAR(sample_time,'YYYYMMDDHH24MI') || TO_CHAR(TRUNC(TO_NUMBER(TO_CHAR(sample_time,'SS'))/30))
            ) co_base_qry
          ON time_window.start_time=co_base_qry.sample_yyyymmddhhmi30s

The time_windows query has changed in order to have 30 second intervals.

View creation – Step 4

The differences with the previous post are:

    SELECT sample_yyyymmddhhmi30s,
           TRUNC((count_event0/(nb_samples+1)),3)  avge1,  
           TRUNC((count_event1/(nb_samples+1)),3)  avge2,  
           TRUNC((count_event2/(nb_samples+1)),3)  avge3, 
           TRUNC((count_event3/(nb_samples+1)),3)  avge4,  
           TRUNC((count_event4/(nb_samples+1)),3)  avge5,  
           TRUNC((count_event5/(nb_samples+1)),3)  avge6,  
           TRUNC((count_event6/(nb_samples+1)),3)  avge7,  
           TRUNC((count_event7/(nb_samples+1)),3)  avge8,  
           TRUNC((count_event8/(nb_samples+1)),3)  avge9,  
           TRUNC((count_event9/(nb_samples+1)),3)  avge10, 
           TRUNC((count_event10/(nb_samples+1)),3) avge11, 
           TRUNC((count_event11/(nb_samples+1)),3) avge12, 
           TRUNC((count_event12/(nb_samples+1)),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_yyyymmddhhmi30s,time_window.start_time) sample_yyyymmddhhmi30s,
               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*30)/24/60/60),'YYYYMMDDHH24MI') || TO_CHAR(TRUNC(TO_NUMBER(TO_CHAR(SYSDATE - ((LEVEL*30)/24/60/60),'SS'))/30)) start_time
            FROM DUAL 
            CONNECT BY LEVEL <= 122
            ) time_window
        LEFT OUTER JOIN 
           (SELECT TO_CHAR(sample_time,'YYYYMMDDHH24MI') || TO_CHAR(TRUNC(TO_NUMBER(TO_CHAR(sample_time,'SS'))/30)) sample_yyyymmddhhmi30s,
                   MAX(sample_id) - MIN(sample_id) 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 TO_CHAR(lss.sid) || ',' || TO_CHAR(lss.serial#) || ',' || TO_CHAR(lss.con_id) = get_current_session
            GROUP BY TO_CHAR(sample_time,'YYYYMMDDHH24MI') || TO_CHAR(TRUNC(TO_NUMBER(TO_CHAR(sample_time,'SS'))/30))
            ) co_base_qry
          ON time_window.start_time=co_base_qry.sample_yyyymmddhhmi30s
       )

View creation – Step 5

The differences with the previous post are:

CREATE OR REPLACE VIEW v$session_activity30s_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 TO_CHAR(sid) || ',' || TO_CHAR(serial#) || ',' || TO_CHAR(con_id) = 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_yyyymmddhhmi30s,
       SUBSTR(sample_yyyymmddhhmi30s,9,2) || ':' || SUBSTR(sample_yyyymmddhhmi30s,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(CEIL(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_yyyymmddhhmi30s,
           TRUNC((count_event0/(nb_samples+1)),3)  avge1,  
           TRUNC((count_event1/(nb_samples+1)),3)  avge2,  
           TRUNC((count_event2/(nb_samples+1)),3)  avge3, 
           TRUNC((count_event3/(nb_samples+1)),3)  avge4,  
           TRUNC((count_event4/(nb_samples+1)),3)  avge5,  
           TRUNC((count_event5/(nb_samples+1)),3)  avge6,  
           TRUNC((count_event6/(nb_samples+1)),3)  avge7,  
           TRUNC((count_event7/(nb_samples+1)),3)  avge8,  
           TRUNC((count_event8/(nb_samples+1)),3)  avge9,  
           TRUNC((count_event9/(nb_samples+1)),3)  avge10, 
           TRUNC((count_event10/(nb_samples+1)),3) avge11, 
           TRUNC((count_event11/(nb_samples+1)),3) avge12, 
           TRUNC((count_event12/(nb_samples+1)),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_yyyymmddhhmi30s,time_window.start_time) sample_yyyymmddhhmi30s,
               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*30)/24/60/60),'YYYYMMDDHH24MI') || TO_CHAR(TRUNC(TO_NUMBER(TO_CHAR(SYSDATE - ((LEVEL*30)/24/60/60),'SS'))/30)) start_time
            FROM DUAL 
            CONNECT BY LEVEL <= 122
            ) time_window
        LEFT OUTER JOIN 
           (SELECT TO_CHAR(sample_time,'YYYYMMDDHH24MI') || TO_CHAR(TRUNC(TO_NUMBER(TO_CHAR(sample_time,'SS'))/30)) sample_yyyymmddhhmi30s,
                   MAX(sample_id) - MIN(sample_id) 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 TO_CHAR(lss.sid) || ',' || TO_CHAR(lss.serial#) || ',' || TO_CHAR(lss.con_id) = get_current_session
            GROUP BY TO_CHAR(sample_time,'YYYYMMDDHH24MI') || TO_CHAR(TRUNC(TO_NUMBER(TO_CHAR(sample_time,'SS'))/30))
            ) co_base_qry
          ON time_window.start_time=co_base_qry.sample_yyyymmddhhmi30s
       )
  );

Modified source query

We will use the query defined in the part XXIV as a starting point. We will modify the source query as follow:

        SELECT BEGIN_TIME,
               C_EVENT0,
               C_EVENT1,
               C_EVENT2,
               C_EVENT3,
               C_EVENT4,
               C_EVENT5,
               C_EVENT6,
               C_EVENT7,
               C_EVENT8,
               C_EVENT9,
               C_EVENT10,
               C_EVENT11,
               C_EVENT12,
               SESSION_ID,
               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,
               MAX_EVENT
        FROM v$session_activity30s_lss
        ORDER BY sample_yyyymmddhhmi30s

XSLT processing

We will use the query defined in the part XXIV as a starting point with the following modifications:

  • We will only display the EVENT_NAMEyy legends that are meaningful e.g. not NULL. In order to do that we will add “choose” elements,
  • A vertical line and a time label are now displayed every 10 positions.

XSLT variables

We will add twelve additional variables in the XSLT document:

  • <xsl:variable name="event_name0"><xsl:value-of select="/descendant::EVENT_NAME0[position()=1]"/></xsl:variable>

    Event name 0,

  • <xsl:variable name="event_name12"><xsl:value-of select="/descendant::EVENT_NAME12[position()=1]"/></xsl:variable>

    Event name 12.

Furthermore:

  • The variable “bar_width” is set to 5.

Modified query

SET LINESIZE      4000
SET LONGCHUNKSIZE 60000
SET LONG          60000

SET FEEDBACK OFF
SET VERIFY   OFF
SET PAGESIZE 0

SET HEADING OFF

SPOOL generate_svg_25e.svg

SELECT XMLTRANSFORM
   (DBMS_XMLGEN.GETXMLTYPE
      ('SELECT BEGIN_TIME,
               C_EVENT0,
               C_EVENT1,
               C_EVENT2,
               C_EVENT3,
               C_EVENT4,
               C_EVENT5,
               C_EVENT6,
               C_EVENT7,
               C_EVENT8,
               C_EVENT9,
               C_EVENT10,
               C_EVENT11,
               C_EVENT12,
               SESSION_ID,
               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,
               MAX_EVENT
        FROM v$session_activity30s_lss
        ORDER BY sample_yyyymmddhhmi30s')
,      XMLTYPE.CREATEXML
   (TO_CLOB(
    '<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
     <xsl:variable name="margin_top">20</xsl:variable>
     <xsl:variable name="margin_bottom">30</xsl:variable>
     <xsl:variable name="margin_left">40</xsl:variable>
     <xsl:variable name="margin_right">160</xsl:variable>
     <xsl:variable name="bar_width">5</xsl:variable>
     <xsl:variable name="graph_width"><xsl:value-of select="600+$margin_left+$margin_right"/></xsl:variable>
     <xsl:variable name="graph_height"><xsl:value-of select="100+$margin_top+$margin_bottom"/></xsl:variable>
     <xsl:variable name="graph_name">LSS Session Activity : </xsl:variable>
     <xsl:variable name="graph_unit">% Activity</xsl:variable>
     <xsl:variable name="yval_max"><xsl:value-of select="/descendant::MAX_EVENT[position()=1]"/></xsl:variable>
     <xsl:variable name="yval_min">0</xsl:variable>
     <xsl:variable name="var_mult"><xsl:value-of select="100 div (($yval_min)-($yval_max))"/></xsl:variable>
     <xsl:variable name="var_beg"><xsl:value-of select="($graph_height)-($margin_bottom)"/></xsl:variable>
     <xsl:variable name="sid"><xsl:value-of select="/descendant::SESSION_ID[position()=1]"/></xsl:variable>
     <xsl:variable name="event_name0"><xsl:value-of select="/descendant::EVENT_NAME0[position()=1]"/></xsl:variable>
     <xsl:variable name="event_name1"><xsl:value-of select="/descendant::EVENT_NAME1[position()=1]"/></xsl:variable>
     <xsl:variable name="event_name2"><xsl:value-of select="/descendant::EVENT_NAME2[position()=1]"/></xsl:variable>
     <xsl:variable name="event_name3"><xsl:value-of select="/descendant::EVENT_NAME3[position()=1]"/></xsl:variable>
     <xsl:variable name="event_name4"><xsl:value-of select="/descendant::EVENT_NAME4[position()=1]"/></xsl:variable>
     <xsl:variable name="event_name5"><xsl:value-of select="/descendant::EVENT_NAME5[position()=1]"/></xsl:variable>
     <xsl:variable name="event_name6"><xsl:value-of select="/descendant::EVENT_NAME6[position()=1]"/></xsl:variable>
     <xsl:variable name="event_name7"><xsl:value-of select="/descendant::EVENT_NAME7[position()=1]"/></xsl:variable>
     <xsl:variable name="event_name8"><xsl:value-of select="/descendant::EVENT_NAME8[position()=1]"/></xsl:variable>
     <xsl:variable name="event_name9"><xsl:value-of select="/descendant::EVENT_NAME9[position()=1]"/></xsl:variable>
     <xsl:variable name="event_name10"><xsl:value-of select="/descendant::EVENT_NAME10[position()=1]"/></xsl:variable>
     <xsl:variable name="event_name11"><xsl:value-of select="/descendant::EVENT_NAME11[position()=1]"/></xsl:variable>
     <xsl:variable name="event_name12"><xsl:value-of select="/descendant::EVENT_NAME12[position()=1]"/></xsl:variable>
       <xsl:template match="/">'
           )
    || 
    TO_CLOB(
         '<svg xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#" xmlns="http://www.w3.org/2000/svg" width="{$graph_width}" height="{$graph_height}">
           <text x="{$margin_left+1}" y="{($margin_top)-5}" style="fill: #000000; stroke: none;font-size:10px;text-anchor=start"><xsl:value-of select="concat($graph_name,$sid)"/></text>
           <text x="{($margin_bottom)-($graph_height)}" y="10" transform="rotate(-90)" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="$graph_unit"/></text>
           <line x1="{($margin_left)-5}" y1="{($var_beg)-0}"   x2="{($graph_width)-($margin_right)+5}" y2="{($var_beg)-0}"  style="stroke:lightblue;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($var_beg)-25}"  x2="{($graph_width)-($margin_right)+5}" y2="{($var_beg)-25}" style="stroke:lightblue;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($var_beg)-50}"  x2="{($graph_width)-($margin_right)+5}" y2="{($var_beg)-50}" style="stroke:lightblue;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($var_beg)-75}"  x2="{($graph_width)-($margin_right)+5}" y2="{($var_beg)-75}" style="stroke:lightblue;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($var_beg)-100}" x2="{($graph_width)-($margin_right)+5}" y2="{($var_beg)-100}" style="stroke:lightblue;stroke-width:1" />
           <text x="{($margin_left)-24}" y="{($var_beg)-2}"   style="fill: #000000; stroke: none;font-size:8px;text-anchor=start">0</text>
           <text x="{($margin_left)-24}" y="{($var_beg)-25}"  style="fill: #000000; stroke: none;font-size:8px;text-anchor=start">25</text>
           <text x="{($margin_left)-24}" y="{($var_beg)-50}"  style="fill: #000000; stroke: none;font-size:8px;text-anchor=start">50</text>
           <text x="{($margin_left)-24}" y="{($var_beg)-75}"  style="fill: #000000; stroke: none;font-size:8px;text-anchor=start">75</text>
           <text x="{($margin_left)-24}" y="{($var_beg)-100}" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start">100</text>
           <line x1="{$margin_left}" y1="{($var_beg)}" x2="{$margin_left}" y2="{($var_beg)-100}" style="stroke:lightblue;stroke-width:1" />'
           )
    || 
    TO_CLOB(
          '<xsl:for-each select="ROWSET/ROW/BEGIN_TIME">
             <xsl:choose>
               <xsl:when test="(position()-1) mod 10=0">
                 <text x="{($margin_left)-9+($bar_width*(position()-1))}" y="{($var_beg)+12}" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="self::node()"/></text>
                 <line x1="{($margin_left)+($bar_width*(position()-1))}" y1="{($var_beg)+4}" x2="{($margin_left)+($bar_width*(position()-1))}" y2="{($var_beg)-100}" style="stroke:lightblue;stroke-width:1" /> 
               </xsl:when>
               <xsl:otherwise>
                 <line x1="{($margin_left)+($bar_width*(position()-1))}" y1="{($var_beg)+3}" x2="{($margin_left)+($bar_width*(position()-1))}" y2="{($var_beg)+0}" style="stroke:lightblue;stroke-width:1" /> 
               </xsl:otherwise>
             </xsl:choose>    
           </xsl:for-each>
           <xsl:variable name="v_path0">
             <xsl:for-each select="ROWSET/ROW/C_EVENT0">
               <xsl:variable name="x_val0">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </xsl:variable>
               <xsl:variable name="y_val0">
                 <xsl:value-of select="round(($var_beg)-((($yval_min)-(self::node()))*($var_mult)))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val0,'','',$y_val0,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path0} {$x_val0},{($var_beg)} {$margin_left},{($var_beg)}" style="fill:limegreen;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path1">
             <xsl:for-each select="ROWSET/ROW/C_EVENT1">
               <xsl:sort select="position()" order="descending" data-type="number"/>
               <xsl:variable name="x_val1">
                 <xsl:value-of select="$margin_left+$bar_width*(last()-position())"/>
               </xsl:variable>
               <xsl:variable name="y_val1">
                 <xsl:value-of select="round(($var_beg)-((($yval_min)-(self::node()))*($var_mult)))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val1,'','',$y_val1,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path0}{$v_path1}" style="fill:hotpink;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path2">
             <xsl:for-each select="ROWSET/ROW/C_EVENT2">
               <xsl:variable name="x_val2">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </xsl:variable>
               <xsl:variable name="y_val2">
                 <xsl:value-of select="round(($var_beg)-((($yval_min)-(self::node()))*($var_mult)))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val2,'','',$y_val2,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path1}{$v_path2}" style="fill:indianred;stroke:none;stroke-width:1" />'
           )
   ||
   TO_CLOB(
          '<xsl:variable name="v_path3">
             <xsl:for-each select="ROWSET/ROW/C_EVENT3">
               <xsl:sort select="position()" order="descending" data-type="number"/>
               <xsl:variable name="x_val3">
                 <xsl:value-of select="$margin_left+$bar_width*(last()-position())"/>
               </xsl:variable>
               <xsl:variable name="y_val3">
                 <xsl:value-of select="round(($var_beg)-((($yval_min)-(self::node()))*($var_mult)))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val3,'','',$y_val3,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path2}{$v_path3}" style="fill:olive;stroke:none;stroke-width:1" />'
           )
   ||
   TO_CLOB(
          '<xsl:variable name="v_path4">
             <xsl:for-each select="ROWSET/ROW/C_EVENT4">
               <xsl:variable name="x_val4">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </xsl:variable>
               <xsl:variable name="y_val4">
                 <xsl:value-of select="round(($var_beg)-((($yval_min)-(self::node()))*($var_mult)))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val4,'','',$y_val4,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path3}{$v_path4}" style="fill:gray;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path5">
             <xsl:for-each select="ROWSET/ROW/C_EVENT5">
               <xsl:sort select="position()" order="descending" data-type="number"/>
               <xsl:variable name="x_val5">
                 <xsl:value-of select="$margin_left+$bar_width*(last()-position())"/>
               </xsl:variable>
               <xsl:variable name="y_val5">
                 <xsl:value-of select="round(($var_beg)-((($yval_min)-(self::node()))*($var_mult)))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val5,'','',$y_val5,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path4}{$v_path5}" style="fill:sienna;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path6">
             <xsl:for-each select="ROWSET/ROW/C_EVENT6">
               <xsl:variable name="x_val6">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </xsl:variable>
               <xsl:variable name="y_val6">
                 <xsl:value-of select="round(($var_beg)-((($yval_min)-(self::node()))*($var_mult)))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val6,'','',$y_val6,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path5}{$v_path6}" style="fill:orange;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path7">
             <xsl:for-each select="ROWSET/ROW/C_EVENT7">
               <xsl:sort select="position()" order="descending" data-type="number"/>
               <xsl:variable name="x_val7">
                 <xsl:value-of select="$margin_left+$bar_width*(last()-position())"/>
               </xsl:variable>
               <xsl:variable name="y_val7">
                 <xsl:value-of select="round(($var_beg)-((($yval_min)-(self::node()))*($var_mult)))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val7,'','',$y_val7,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path6}{$v_path7}" style="fill:tan;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path8">
             <xsl:for-each select="ROWSET/ROW/C_EVENT8">
               <xsl:variable name="x_val8">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </xsl:variable>
               <xsl:variable name="y_val8">
                 <xsl:value-of select="round(($var_beg)-((($yval_min)-(self::node()))*($var_mult)))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val8,'','',$y_val8,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path7}{$v_path8}" style="fill:royalblue;stroke:none;stroke-width:1" />'
           )
   ||
   TO_CLOB(
          '<xsl:variable name="v_path9">
             <xsl:for-each select="ROWSET/ROW/C_EVENT9">
               <xsl:sort select="position()" order="descending" data-type="number"/>
               <xsl:variable name="x_val9">
                 <xsl:value-of select="$margin_left+$bar_width*(last()-position())"/>
               </xsl:variable>
               <xsl:variable name="y_val9">
                 <xsl:value-of select="round(($var_beg)-((($yval_min)-(self::node()))*($var_mult)))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val9,'','',$y_val9,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path8}{$v_path9}" style="fill:skyblue;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path10">
             <xsl:for-each select="ROWSET/ROW/C_EVENT10">
               <xsl:variable name="x_val10">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </xsl:variable>
               <xsl:variable name="y_val10">
                 <xsl:value-of select="round(($var_beg)-((($yval_min)-(self::node()))*($var_mult)))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val10,'','',$y_val10,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path9}{$v_path10}" style="fill:lightcyan;stroke:none;stroke-width:1" />'
           )
   ||
   TO_CLOB(
          '<xsl:variable name="v_path11">
             <xsl:for-each select="ROWSET/ROW/C_EVENT11">
               <xsl:sort select="position()" order="descending" data-type="number"/>
               <xsl:variable name="x_val11">
                 <xsl:value-of select="$margin_left+$bar_width*(last()-position())"/>
               </xsl:variable>
               <xsl:variable name="y_val11">
                 <xsl:value-of select="round(($var_beg)-((($yval_min)-(self::node()))*($var_mult)))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val11,'','',$y_val11,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path10}{$v_path11}" style="fill:lightgray;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path12">
             <xsl:for-each select="ROWSET/ROW/C_EVENT12">
               <xsl:variable name="x_val12">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </xsl:variable>
               <xsl:variable name="y_val12">
                 <xsl:value-of select="round(($var_beg)-((($yval_min)-(self::node()))*($var_mult)))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val12,'','',$y_val12,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path11}{$v_path12}" style="fill:bisque;stroke:none;stroke-width:1" />'
           )
   ||
   TO_CLOB(
          '<xsl:choose>
             <xsl:when test="string-length($event_name0)&gt;0">
               <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+5}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="$event_name0"/></text>
               <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+0}" width="{10}" height="{6}" fill="limegreen" stroke="black"/> 
             </xsl:when>
           </xsl:choose> 
           <xsl:choose>
             <xsl:when test="string-length($event_name1)&gt;0">
               <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+13}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="$event_name1"/></text>
               <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+8}" width="{10}" height="{6}" fill="hotpink" stroke="black"/> 
             </xsl:when>
           </xsl:choose> 
           <xsl:choose>
             <xsl:when test="string-length($event_name2)&gt;0">
               <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+21}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="$event_name2"/></text>
               <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+16}" width="{10}" height="{6}" fill="indianred" stroke="black"/> 
             </xsl:when>
           </xsl:choose> 
           <xsl:choose>
             <xsl:when test="string-length($event_name3)&gt;0">  
               <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+29}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="$event_name3"/></text>
               <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+24}" width="{10}" height="{6}" fill="olive" stroke="black"/> 
             </xsl:when>
           </xsl:choose>  
           <xsl:choose>
             <xsl:when test="string-length($event_name4)&gt;0"> 
               <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+37}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="$event_name4"/></text>
               <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+32}" width="{10}" height="{6}" fill="gray" stroke="black"/> 
             </xsl:when>
           </xsl:choose> 
           <xsl:choose>
             <xsl:when test="string-length($event_name5)&gt;0">
               <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+45}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="$event_name5"/></text>
               <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+40}" width="{10}" height="{6}" fill="sienna" stroke="black"/>
             </xsl:when>
           </xsl:choose>'
           ) 
    || 
    TO_CLOB(
          '<xsl:choose>
             <xsl:when test="string-length($event_name6)&gt;0">
               <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+53}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="$event_name6"/></text>
               <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+48}" width="{10}" height="{6}" fill="orange" stroke="black"/> 
             </xsl:when>
           </xsl:choose> 
           <xsl:choose>
             <xsl:when test="string-length($event_name7)&gt;0">
               <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+61}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="$event_name7"/></text>
               <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+56}" width="{10}" height="{6}" fill="tan" stroke="black"/>  
             </xsl:when>
           </xsl:choose>
           <xsl:choose>
             <xsl:when test="string-length($event_name8)&gt;0">
               <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+69}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="$event_name8"/></text>
               <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+64}" width="{10}" height="{6}" fill="royalblue" stroke="black"/> 
             </xsl:when>
           </xsl:choose> 
           <xsl:choose>
             <xsl:when test="string-length($event_name9)&gt;0">
               <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+77}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="$event_name9"/></text>
               <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+72}" width="{10}" height="{6}" fill="skyblue" stroke="black"/>
             </xsl:when>
           </xsl:choose> 
           <xsl:choose>
             <xsl:when test="string-length($event_name10)&gt;0">
               <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+85}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="$event_name10"/></text>
               <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+80}" width="{10}" height="{6}" fill="lightcyan" stroke="black"/> 
             </xsl:when>
           </xsl:choose> 
           <xsl:choose>
             <xsl:when test="string-length($event_name11)&gt;0">
               <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+93}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="$event_name11"/></text>
               <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+88}" width="{10}" height="{6}" fill="lightgray" stroke="black"/> 
             </xsl:when>
           </xsl:choose>  
           <xsl:choose>
             <xsl:when test="string-length($event_name12)&gt;0">
               <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+101}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="$event_name12"/></text>
               <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+96}" width="{10}" height="{6}" fill="bisque" stroke="black"/>
             </xsl:when>
           </xsl:choose>   
         </svg>
       </xsl:template>
     </xsl:stylesheet>'
           )
   )
   ) 
FROM dual;

SPOOL OFF
SET HEADING ON

SET VERIFY   ON
SET FEEDBACK ON

Result:

generate_svg_25e

Remarks:

  • The query has been executed from the CDB$ROOT container by the C##LSS common user,
  • The view v$session_activity30s_lss has been created from the CDB$ROOT container in the C##LSS schema,
  • The system variables LINESIZE and LONGCHUNKSIZE have been increased to 4000 and 60000 respectively,
  • The LSS sampling period interval is set to 2 seconds.
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: