Oracle database experiences

Oracle database blog

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

Scope

In the previous post we have displayed the top 20 activity sessions. It could be interesting to know how much a session is waiting on such or such event or is using the cpu. We will change the sampling period interval to 2 seconds.
We will create a view in order to display the session activity and we will use a function to get the session id.

Function get_current_session

This function will return the session id that we want to analyse. Basically you could store the session id in a table and retrieve it with this function. You will find a example below:

CREATE OR REPLACE FUNCTION get_current_session RETURN NUMBER 
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 TO_NUMBER(v_param_value);
   
   EXCEPTION
      WHEN TOO_MANY_ROWS THEN
         RETURN -1;
      WHEN NO_DATA_FOUND THEN
         RETURN -1;
      WHEN OTHERS THEN
         RAISE;
         
END get_current_session;

View creation – Step 1

The starting point of this view will be v$aas_light_sampling_sessions.
The query name session_events gives the top 12 event activities linked to the wait events of a session (excluded the cpu activity) as follow:

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
   )

View creation – Step 2

Every minute we will count the number of events that are linked to a wait class or a cpu event for the target session as follow:

            SELECT TO_CHAR(sample_time,'YYYYMMDDHH24MI') sample_yyyymmddhhmi,
                   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 lss.sid = get_current_session
            GROUP BY TO_CHAR(sample_time,'YYYYMMDDHH24MI')

We will also store the different event names.

View creation – Step 3

In the LSS sampling process, it might happen that a specific period of time does not contain any data e.g. LSS process is not working during this period. We will handle this sampling process inactivity during the last hour as follow:

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

View creation – Step 4

To define how many events in average that are linked to the target session, we will count the top 12 wait events plus the cpu event on a period of time of one minute and we will divide by the number of samples plus one as follow:

    SELECT sample_yyyymmddhhmi,
           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_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,
                   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 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
       )

View creation – Step 5

Finally we will accumulate the different events AVGE from the EVENT0 to EVENT12 event as follow:

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(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_yyyymmddhhmi,
           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_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,
                   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 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
       )
  );

Modified source query

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_activity_lss
        ORDER BY sample_yyyymmddhhmi

XSLT processing

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

  • The differents “for-each” “ROWSET/ROW/C_CPU” to “ROWSET/ROW/C_CLUSTER” have been replaced by “ROWSET/ROW/C_EVENT0” to “ROWSET/ROW/C_EVENT12”,
  • Utilization of two variables “var_mult” and “var_beg” to simplify formulas.

XSLT variables

We will add three additional variables in the XSLT document:

  • <xsl:variable name="var_mult"><xsl:value-of select="100 div (($yval_min)-($yval_max))"/></xsl:variable>

    Variable used to simplify several formulas,

  • <xsl:variable name="var_beg"><xsl:value-of select="($graph_height)-($margin_bottom)"/></xsl:variable>

    Variable also used to simplify several formulas,

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

    Target session id.

Furthermore:

  • The variable “margin_rigth” is set to 160,
  • The variable “graph_name” is set to ‘LSS Session Activity : ‘,
  • The variable “yval_max” is set to the first value of the column MAX_EVENT.

Modified query

SET LINESIZE      2000
SET LONGCHUNKSIZE 30000
SET LONG          30000

SET FEEDBACK OFF
SET VERIFY   OFF
SET PAGESIZE 0

SET HEADING OFF

SPOOL generate_svg_24d.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_activity_lss
        ORDER BY sample_yyyymmddhhmi')
,      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">10</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: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 5=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" />
           <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="/descendant::EVENT_NAME0[position()=1]"/></text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+0}" width="{10}" height="{6}" fill="limegreen" stroke="black"/> 
           <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="/descendant::EVENT_NAME1[position()=1]"/></text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+8}" width="{10}" height="{6}" fill="hotpink" stroke="black"/> 
           <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="/descendant::EVENT_NAME2[position()=1]"/></text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+16}" width="{10}" height="{6}" fill="indianred" stroke="black"/>   
           <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="/descendant::EVENT_NAME3[position()=1]"/></text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+24}" width="{10}" height="{6}" fill="olive" stroke="black"/>   
           <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="/descendant::EVENT_NAME4[position()=1]"/></text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+32}" width="{10}" height="{6}" fill="gray" stroke="black"/> 
           <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="/descendant::EVENT_NAME5[position()=1]"/></text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+40}" width="{10}" height="{6}" fill="sienna" stroke="black"/>'
           ) 
    || 
    TO_CLOB(
          '<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="/descendant::EVENT_NAME6[position()=1]"/></text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+48}" width="{10}" height="{6}" fill="orange" stroke="black"/> 
           <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="/descendant::EVENT_NAME7[position()=1]"/></text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+56}" width="{10}" height="{6}" fill="tan" stroke="black"/>  
           <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="/descendant::EVENT_NAME8[position()=1]"/></text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+64}" width="{10}" height="{6}" fill="royalblue" stroke="black"/> 
           <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="/descendant::EVENT_NAME9[position()=1]"/></text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+72}" width="{10}" height="{6}" fill="skyblue" stroke="black"/>
           <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="/descendant::EVENT_NAME10[position()=1]"/></text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+80}" width="{10}" height="{6}" fill="lightcyan" stroke="black"/> 
           <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="/descendant::EVENT_NAME11[position()=1]"/></text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+88}" width="{10}" height="{6}" fill="lightgray" stroke="black"/> 
           <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="/descendant::EVENT_NAME12[position()=1]"/></text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+96}" width="{10}" height="{6}" fill="bisque" stroke="black"/>   
         </svg>
       </xsl:template>
     </xsl:stylesheet>'
           )
   )
   ) 
FROM dual;

SPOOL OFF
SET HEADING ON

SET VERIFY   ON
SET FEEDBACK ON

Result:

generate_svg_24d

Remarks:

  • The query has been executed from the CDB$ROOT container by the C##LSS common user,
  • The v$session_activity_lss has been created from the CDB$ROOT container in the C##LSS schema,
  • The views defined in the part XXI to part XXIII could be modified in order to use v$active_session_history (ASH) if available and licensed,
  • The view v$session_activity_lss is a session id consolidation e.g. it might exist several sessions in different CDB/PDBs with the same session id.
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: