Oracle database experiences

Oracle database blog

Generating SVG graphics with SQL*Plus – Part XXII – Stacked area graph – Average Active Sessions – Light Sampling Sessions – CDB/PDB splitting

Scope

In the previous post we have displayed the average active sessions for the last hour at the CDB level e.g. from the CDB$ROOT container through all the PDBs. It could be interesting to have a CDB/PDB splitting. We will limit this CDB/PDB splitting to the following containers:

  • CDB Wide e.g. con_id=0,
  • CDB$ROOT (con_id=1),
  • PDB1 (con_id=3) to PDB4 (cond_id=6).

For each CDB/PDB we will display the average active sessions linked to the CPU events and the wait class events. We will create a view in order to display the average active sessions for the CDB/PDB.

View creation – Step 1

Every minute we will count the number of active sessions that are linked to the wait classes or a cpu event for each container as follow:

SELECT TO_CHAR(sample_time,'YYYYMMDDHH24MI') sample_yyyymmddhhmi,
       con_id,
       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),-1,1)) count_cpu,
       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),0,1,1,1,2,1,3,1,4,1,5,1,7,1,8,1,9,1,10,1,11,1,12,1)) count_wait
       FROM lss$_sampling_session
       GROUP BY con_id,
                TO_CHAR(sample_time,'YYYYMMDDHH24MI')

View creation – Step 2

We must then compute the number of samples during the minute interval:

                SELECT TO_CHAR(sample_time,'YYYYMMDDHH24MI') sample_yyyymmddhhmi,
                       con_id,
                       MAX(lss_sample.nb_samples) nb_samples,
                       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),-1,1)) count_cpu,
                       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),0,1,1,1,2,1,3,1,4,1,5,1,7,1,8,1,9,1,10,1,11,1,12,1)) count_wait
                FROM lss$_sampling_session
                INNER JOIN 
                   (SELECT TO_CHAR(sample_time,'YYYYMMDDHH24MI') sample_yyyymmddhhmi,
                           MAX(sample_id) - MIN(sample_id)       nb_samples
                    FROM lss$_sampling_session
                    GROUP BY TO_CHAR(sample_time,'YYYYMMDDHH24MI')
                    ) lss_sample
                  ON lss_sample.sample_yyyymmddhhmi = TO_CHAR(lss$_sampling_session.sample_time,'YYYYMMDDHH24MI')
                GROUP BY con_id,
                        TO_CHAR(sample_time,'YYYYMMDDHH24MI')

View creation – Step 3

To define how many sessions in average are waiting on the wait classes or are using cpu during a minute in a container, we will count the wait class and the cpu events on a period of time of one minute for each container and we will divide by the number of samples plus one as follow:

            SELECT sample_yyyymmddhhmi,
                   CASE con_id
                      WHEN 0 THEN TRUNC(count_cpu/(nb_samples+1),3)
                      ELSE 0
                   END aas_cpu0,
                   CASE con_id
                      WHEN 0 THEN TRUNC(count_wait/(nb_samples+1),3)
                      ELSE 0
                   END aas_wait0,
                   CASE con_id
                      WHEN 1 THEN TRUNC(count_cpu/(nb_samples+1),3)
                      ELSE 0
                   END aas_cpu1,
                   CASE con_id
                      WHEN 1 THEN TRUNC(count_wait/(nb_samples+1),3)
                      ELSE 0
                   END aas_wait1,
                   CASE con_id
                      WHEN 3 THEN TRUNC(count_cpu/(nb_samples+1),3)
                      ELSE 0
                   END aas_cpu2,
                   CASE con_id
                      WHEN 3 THEN TRUNC(count_wait/(nb_samples+1),3)
                      ELSE 0
                   END aas_wait2,
                   CASE con_id
                      WHEN 4 THEN TRUNC(count_cpu/(nb_samples+1),3)
                      ELSE 0
                   END aas_cpu3,
                   CASE con_id
                      WHEN 4 THEN TRUNC(count_wait/(nb_samples+1),3)
                      ELSE 0
                   END aas_wait3,
                   CASE con_id
                      WHEN 5 THEN TRUNC(count_cpu/(nb_samples+1),3)
                      ELSE 0
                   END aas_cpu4,
                   CASE con_id
                      WHEN 5 THEN TRUNC(count_wait/(nb_samples+1),3)
                      ELSE 0
                   END aas_wait4,
                   CASE con_id
                      WHEN 6 THEN TRUNC(count_cpu/(nb_samples+1),3)
                      ELSE 0
                   END aas_cpu5,
                   CASE con_id
                      WHEN 6 THEN TRUNC(count_wait/(nb_samples+1),3)
                      ELSE 0
                   END aas_wait5
            FROM 
               (SELECT TO_CHAR(sample_time,'YYYYMMDDHH24MI') sample_yyyymmddhhmi,
                       con_id,
                       MAX(lss_sample.nb_samples) nb_samples,
                       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),-1,1)) count_cpu,
                       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),0,1,1,1,2,1,3,1,4,1,5,1,7,1,8,1,9,1,10,1,11,1,12,1)) count_wait
                FROM lss$_sampling_session
                INNER JOIN 
                   (SELECT TO_CHAR(sample_time,'YYYYMMDDHH24MI') sample_yyyymmddhhmi,
                           MAX(sample_id) - MIN(sample_id)       nb_samples
                    FROM lss$_sampling_session
                    GROUP BY TO_CHAR(sample_time,'YYYYMMDDHH24MI')
                    ) lss_sample
                  ON lss_sample.sample_yyyymmddhhmi = TO_CHAR(lss$_sampling_session.sample_time,'YYYYMMDDHH24MI')
                GROUP BY con_id,
                        TO_CHAR(sample_time,'YYYYMMDDHH24MI')
                )

Remark: We have restricted the containers to con_id={0,1} and con_id={3,4,5,6}

View creation – Step 4

We will then sum for each selected container, the number of active sessions linked to the wait classes or a cpu event as follow:

        SELECT sample_yyyymmddhhmi,
               SUM(aas_cpu0)  sum_aas_cpu0,
               SUM(aas_wait0) sum_aas_wait0,
               SUM(aas_cpu1)  sum_aas_cpu1,
               SUM(aas_wait1) sum_aas_wait1,
               SUM(aas_cpu2)  sum_aas_cpu2,
               SUM(aas_wait2) sum_aas_wait2,
               SUM(aas_cpu3)  sum_aas_cpu3,
               SUM(aas_wait3) sum_aas_wait3,
               SUM(aas_cpu4)  sum_aas_cpu4,
               SUM(aas_wait4) sum_aas_wait4,
               SUM(aas_cpu5)  sum_aas_cpu5,
               SUM(aas_wait5) sum_aas_wait5
        FROM 
           (SELECT sample_yyyymmddhhmi,
                   CASE con_id
                      WHEN 0 THEN TRUNC(count_cpu/(nb_samples+1),3)
                      ELSE 0
                   END aas_cpu0,
                   CASE con_id
                      WHEN 0 THEN TRUNC(count_wait/(nb_samples+1),3)
                      ELSE 0
                   END aas_wait0,
                   CASE con_id
                      WHEN 1 THEN TRUNC(count_cpu/(nb_samples+1),3)
                      ELSE 0
                   END aas_cpu1,
                   CASE con_id
                      WHEN 1 THEN TRUNC(count_wait/(nb_samples+1),3)
                      ELSE 0
                   END aas_wait1,
                   CASE con_id
                      WHEN 3 THEN TRUNC(count_cpu/(nb_samples+1),3)
                      ELSE 0
                   END aas_cpu2,
                   CASE con_id
                      WHEN 3 THEN TRUNC(count_wait/(nb_samples+1),3)
                      ELSE 0
                   END aas_wait2,
                   CASE con_id
                      WHEN 4 THEN TRUNC(count_cpu/(nb_samples+1),3)
                      ELSE 0
                   END aas_cpu3,
                   CASE con_id
                      WHEN 4 THEN TRUNC(count_wait/(nb_samples+1),3)
                      ELSE 0
                   END aas_wait3,
                   CASE con_id
                      WHEN 5 THEN TRUNC(count_cpu/(nb_samples+1),3)
                      ELSE 0
                   END aas_cpu4,
                   CASE con_id
                      WHEN 5 THEN TRUNC(count_wait/(nb_samples+1),3)
                      ELSE 0
                   END aas_wait4,
                   CASE con_id
                      WHEN 6 THEN TRUNC(count_cpu/(nb_samples+1),3)
                      ELSE 0
                   END aas_cpu5,
                   CASE con_id
                      WHEN 6 THEN TRUNC(count_wait/(nb_samples+1),3)
                      ELSE 0
                   END aas_wait5
            FROM 
               (SELECT TO_CHAR(sample_time,'YYYYMMDDHH24MI') sample_yyyymmddhhmi,
                       con_id,
                       MAX(lss_sample.nb_samples) nb_samples,
                       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),-1,1)) count_cpu,
                       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),0,1,1,1,2,1,3,1,4,1,5,1,7,1,8,1,9,1,10,1,11,1,12,1)) count_wait
                FROM lss$_sampling_session
                INNER JOIN 
                   (SELECT TO_CHAR(sample_time,'YYYYMMDDHH24MI') sample_yyyymmddhhmi,
                           MAX(sample_id) - MIN(sample_id)       nb_samples
                    FROM lss$_sampling_session
                    GROUP BY TO_CHAR(sample_time,'YYYYMMDDHH24MI')
                    ) lss_sample
                  ON lss_sample.sample_yyyymmddhhmi = TO_CHAR(lss$_sampling_session.sample_time,'YYYYMMDDHH24MI')
                GROUP BY con_id,
                        TO_CHAR(sample_time,'YYYYMMDDHH24MI')
                )
           ) GROUP BY sample_yyyymmddhhmi

View creation – Step 5

We will also handle the potential 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.sum_aas_cpu0,0)   aas_cpu0,
           NVL(co_base_qry.sum_aas_wait0,0)  aas_wait0,
           NVL(co_base_qry.sum_aas_cpu1,0)   aas_cpu1,
           NVL(co_base_qry.sum_aas_wait1,0)  aas_wait1,
           NVL(co_base_qry.sum_aas_cpu2,0)   aas_cpu2,
           NVL(co_base_qry.sum_aas_wait2,0)  aas_wait2,
           NVL(co_base_qry.sum_aas_cpu3,0)   aas_cpu3,
           NVL(co_base_qry.sum_aas_wait3,0)  aas_wait3,
           NVL(co_base_qry.sum_aas_cpu4,0)   aas_cpu4,
           NVL(co_base_qry.sum_aas_wait4,0)  aas_wait4,
           NVL(co_base_qry.sum_aas_cpu5,0)   aas_cpu5,
           NVL(co_base_qry.sum_aas_wait5,0)  aas_wait5
    FROM 
       (SELECT TO_CHAR(SYSDATE - (LEVEL/24/60),'YYYYMMDDHH24MI') start_time
        FROM DUAL 
        CONNECT BY LEVEL <= 61
       ) time_window
    LEFT OUTER JOIN 
       (SELECT sample_yyyymmddhhmi,
               SUM(aas_cpu0)  sum_aas_cpu0,
               SUM(aas_wait0) sum_aas_wait0,
               SUM(aas_cpu1)  sum_aas_cpu1,
               SUM(aas_wait1) sum_aas_wait1,
               SUM(aas_cpu2)  sum_aas_cpu2,
               SUM(aas_wait2) sum_aas_wait2,
               SUM(aas_cpu3)  sum_aas_cpu3,
               SUM(aas_wait3) sum_aas_wait3,
               SUM(aas_cpu4)  sum_aas_cpu4,
               SUM(aas_wait4) sum_aas_wait4,
               SUM(aas_cpu5)  sum_aas_cpu5,
               SUM(aas_wait5) sum_aas_wait5
        FROM 
           (SELECT sample_yyyymmddhhmi,
                   CASE con_id
                      WHEN 0 THEN TRUNC(count_cpu/(nb_samples+1),3)
                      ELSE 0
                   END aas_cpu0,
                   CASE con_id
                      WHEN 0 THEN TRUNC(count_wait/(nb_samples+1),3)
                      ELSE 0
                   END aas_wait0,
                   CASE con_id
                      WHEN 1 THEN TRUNC(count_cpu/(nb_samples+1),3)
                      ELSE 0
                   END aas_cpu1,
                   CASE con_id
                      WHEN 1 THEN TRUNC(count_wait/(nb_samples+1),3)
                      ELSE 0
                   END aas_wait1,
                   CASE con_id
                      WHEN 3 THEN TRUNC(count_cpu/(nb_samples+1),3)
                      ELSE 0
                   END aas_cpu2,
                   CASE con_id
                      WHEN 3 THEN TRUNC(count_wait/(nb_samples+1),3)
                      ELSE 0
                   END aas_wait2,
                   CASE con_id
                      WHEN 4 THEN TRUNC(count_cpu/(nb_samples+1),3)
                      ELSE 0
                   END aas_cpu3,
                   CASE con_id
                      WHEN 4 THEN TRUNC(count_wait/(nb_samples+1),3)
                      ELSE 0
                   END aas_wait3,
                   CASE con_id
                      WHEN 5 THEN TRUNC(count_cpu/(nb_samples+1),3)
                      ELSE 0
                   END aas_cpu4,
                   CASE con_id
                      WHEN 5 THEN TRUNC(count_wait/(nb_samples+1),3)
                      ELSE 0
                   END aas_wait4,
                   CASE con_id
                      WHEN 6 THEN TRUNC(count_cpu/(nb_samples+1),3)
                      ELSE 0
                   END aas_cpu5,
                   CASE con_id
                      WHEN 6 THEN TRUNC(count_wait/(nb_samples+1),3)
                      ELSE 0
                   END aas_wait5
            FROM 
               (SELECT TO_CHAR(sample_time,'YYYYMMDDHH24MI') sample_yyyymmddhhmi,
                       con_id,
                       MAX(lss_sample.nb_samples) nb_samples,
                       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),-1,1)) count_cpu,
                       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),0,1,1,1,2,1,3,1,4,1,5,1,7,1,8,1,9,1,10,1,11,1,12,1)) count_wait
                FROM lss$_sampling_session
                INNER JOIN 
                   (SELECT TO_CHAR(sample_time,'YYYYMMDDHH24MI') sample_yyyymmddhhmi,
                           MAX(sample_id) - MIN(sample_id)       nb_samples
                    FROM lss$_sampling_session
                    GROUP BY TO_CHAR(sample_time,'YYYYMMDDHH24MI')
                    ) lss_sample
                  ON lss_sample.sample_yyyymmddhhmi = TO_CHAR(lss$_sampling_session.sample_time,'YYYYMMDDHH24MI')
                GROUP BY con_id,
                        TO_CHAR(sample_time,'YYYYMMDDHH24MI')
                )
           ) GROUP BY sample_yyyymmddhhmi
       ) co_base_qry
      ON time_window.start_time=co_base_qry.sample_yyyymmddhhmi

View creation – Step 6

Finally we will accumulate the different wait class and cpu AAS values for the selected containers as follow:

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

Modified source query

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

        SELECT BEGIN_TIME,
               C_CONT0_CPU,
               C_CONT0_WAIT,
               C_CONT1_CPU,
               C_CONT1_WAIT,
               C_CONT2_CPU,
               C_CONT2_WAIT,
               C_CONT3_CPU,
               C_CONT3_WAIT,
               C_CONT4_CPU,
               C_CONT4_WAIT,
               C_CONT5_CPU,
               C_CONT5_WAIT,
               MAX_CONT_AAS,
               (SELECT value FROM v$parameter WHERE name='cpu_count') CPU_COUNT
        FROM v$cdb_pdb_aas_lss
        ORDER BY sample_yyyymmddhhmi

XSLT variables

  • The variable “graph_name” is set to ‘LSS Active Sessions – Waiting + Working – CDB/PDB splitting’,
  • The variable “yval_max” is set to the first value of the column MAX_CONT_AAS.

XSLT processing

The modification is the following:

  • The differents “for-each” “ROWSET/ROW/C_CPU” to “ROWSET/ROW/C_CLUSTER” have been replaced by “ROWSET/ROW/C_CONT0_CPU” to “ROWSET/ROW/C_CONT5_WAIT”.

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_22h.svg

SELECT XMLTRANSFORM
   (DBMS_XMLGEN.GETXMLTYPE
      ('SELECT BEGIN_TIME,
               C_CONT0_CPU,
               C_CONT0_WAIT,
               C_CONT1_CPU,
               C_CONT1_WAIT,
               C_CONT2_CPU,
               C_CONT2_WAIT,
               C_CONT3_CPU,
               C_CONT3_WAIT,
               C_CONT4_CPU,
               C_CONT4_WAIT,
               C_CONT5_CPU,
               C_CONT5_WAIT,
               MAX_CONT_AAS,
               (SELECT value FROM v$parameter WHERE name=''cpu_count'') CPU_COUNT
        FROM v$cdb_pdb_aas_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">100</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 Active Sessions - Waiting + Working - CDB/PDB splitting</xsl:variable>
     <xsl:variable name="graph_unit">Session Count</xsl:variable>
     <xsl:variable name="yval_max"><xsl:value-of select="/descendant::MAX_CONT_AAS[position()=1]"/></xsl:variable>
     <xsl:variable name="yval_min">0</xsl:variable>
     <xsl:variable name="cpu_count"><xsl:value-of select="/descendant::CPU_COUNT[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="$graph_name"/></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="{($graph_height)-($margin_bottom)-0}"   x2="{($graph_width)-($margin_right)+5}" y2="{($graph_height)-($margin_bottom)-0}"  style="stroke:lightblue;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($graph_height)-($margin_bottom)-25}"  x2="{($graph_width)-($margin_right)+5}" y2="{($graph_height)-($margin_bottom)-25}" style="stroke:lightblue;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($graph_height)-($margin_bottom)-50}"  x2="{($graph_width)-($margin_right)+5}" y2="{($graph_height)-($margin_bottom)-50}" style="stroke:lightblue;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($graph_height)-($margin_bottom)-75}"  x2="{($graph_width)-($margin_right)+5}" y2="{($graph_height)-($margin_bottom)-75}" style="stroke:lightblue;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($graph_height)-($margin_bottom)-100}" x2="{($graph_width)-($margin_right)+5}" y2="{($graph_height)-($margin_bottom)-100}" style="stroke:lightblue;stroke-width:1" />
           <text x="{($margin_left)-24}" y="{($graph_height)-($margin_bottom)-2}"   style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="format-number(($yval_min),''00.00'')"/></text>
           <text x="{($margin_left)-24}" y="{($graph_height)-($margin_bottom)-25}"  style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="format-number((($yval_min)+(1*(($yval_max)-($yval_min)) div 4)),''00.00'')"/></text>
           <text x="{($margin_left)-24}" y="{($graph_height)-($margin_bottom)-50}"  style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="format-number((($yval_min)+((($yval_max)-($yval_min)) div 2)),''00.00'')"/></text>
           <text x="{($margin_left)-24}" y="{($graph_height)-($margin_bottom)-75}"  style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="format-number((($yval_min)+(3*(($yval_max)-($yval_min)) div 4)),''00.00'')"/></text>
           <text x="{($margin_left)-24}" y="{($graph_height)-($margin_bottom)-100}" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="format-number(($yval_max),''00.00'')"/></text>
           <line x1="{$margin_left}" y1="{($graph_height)-($margin_bottom)}" x2="{$margin_left}" y2="{($graph_height)-($margin_bottom)-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="{($graph_height)-($margin_bottom)+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="{($graph_height)-($margin_bottom)+4}" x2="{($margin_left)+($bar_width*(position()-1))}" y2="{($graph_height)-($margin_bottom)-100}" style="stroke:lightblue;stroke-width:1" /> 
               </xsl:when>
               <xsl:otherwise>
                 <line x1="{($margin_left)+($bar_width*(position()-1))}" y1="{($graph_height)-($margin_bottom)+3}" x2="{($margin_left)+($bar_width*(position()-1))}" y2="{($graph_height)-($margin_bottom)+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_CONT0_CPU">
               <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(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val0,'','',$y_val0,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path0} {$x_val0},{($graph_height)-($margin_bottom)} {$margin_left},{($graph_height)-($margin_bottom)}" style="fill:aquamarine;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path1">
             <xsl:for-each select="ROWSET/ROW/C_CONT0_WAIT">
               <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(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val1,'','',$y_val1,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path0}{$v_path1}" style="fill:steelblue;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path2">
             <xsl:for-each select="ROWSET/ROW/C_CONT1_CPU">
               <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(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val2,'','',$y_val2,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path1}{$v_path2}" style="fill:darkkhaki;stroke:none;stroke-width:1" />'
           )
    ||
    TO_CLOB(
          '<xsl:variable name="v_path3">
             <xsl:for-each select="ROWSET/ROW/C_CONT1_WAIT">
               <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(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val3,'','',$y_val3,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path2}{$v_path3}" style="fill:gold;stroke:none;stroke-width:1" />'
           )
    ||
    TO_CLOB(
          '<xsl:variable name="v_path4">
             <xsl:for-each select="ROWSET/ROW/C_CONT2_CPU">
               <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(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val4,'','',$y_val4,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path3}{$v_path4}" style="fill:lightsalmon;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path5">
             <xsl:for-each select="ROWSET/ROW/C_CONT2_WAIT">
               <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(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val5,'','',$y_val5,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path4}{$v_path5}" style="fill:orangered;stroke:none;stroke-width:1" />'
           )
    ||
    TO_CLOB(
          '<xsl:variable name="v_path6">
             <xsl:for-each select="ROWSET/ROW/C_CONT3_CPU">
               <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(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val6,'','',$y_val6,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path5}{$v_path6}" style="fill:plum;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path7">
             <xsl:for-each select="ROWSET/ROW/C_CONT3_WAIT">
               <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(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val7,'','',$y_val7,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path6}{$v_path7}" style="fill:magenta;stroke:none;stroke-width:1" /><xsl:variable name="v_path8">
             <xsl:for-each select="ROWSET/ROW/C_CONT4_CPU">
               <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(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val8,'','',$y_val8,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path7}{$v_path8}" style="fill:skyblue;stroke:none;stroke-width:1" />'
           )
    ||
    TO_CLOB(
          '<xsl:variable name="v_path9">
             <xsl:for-each select="ROWSET/ROW/C_CONT4_WAIT">
               <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(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val9,'','',$y_val9,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path8}{$v_path9}" style="fill:dodgerblue;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path10">
             <xsl:for-each select="ROWSET/ROW/C_CONT5_CPU">
               <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(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val10,'','',$y_val10,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path9}{$v_path10}" style="fill:greenyellow;stroke:none;stroke-width:1" />'
           )
    ||
    TO_CLOB(
          '<xsl:variable name="v_path11">
             <xsl:for-each select="ROWSET/ROW/C_CONT5_WAIT">
               <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(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val11,'','',$y_val11,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path10}{$v_path11}" style="fill:limegreen;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">CDB Wide CPU</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+0}" width="{10}" height="{6}" fill="aquamarine" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+13}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">CDB Wide Wait</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+8}" width="{10}" height="{6}" fill="steelblue" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+21}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">CDB$ROOT CPU</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+16}" width="{10}" height="{6}" fill="darkkhaki" stroke="black"/>   
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+29}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">CDB$ROOT Wait</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+24}" width="{10}" height="{6}" fill="gold" stroke="black"/>   
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+37}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">PDB1 CPU</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+32}" width="{10}" height="{6}" fill="lightsalmon" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+45}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">PDB1 Wait</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+40}" width="{10}" height="{6}" fill="orangered" 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">PDB2 CPU</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+48}" width="{10}" height="{6}" fill="plum" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+61}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">PDB2 Wait</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+56}" width="{10}" height="{6}" fill="magenta" stroke="black"/>
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+69}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">PDB3 CPU</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+64}" width="{10}" height="{6}" fill="skyblue" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+77}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">PDB3 Wait</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+72}" width="{10}" height="{6}" fill="dodgerblue" stroke="black"/>
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+85}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">PDB4 CPU</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+80}" width="{10}" height="{6}" fill="greenyellow" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+93}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">PDB4 Wait</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+88}" width="{10}" height="{6}" fill="limegreen" stroke="black"/> 
           <xsl:choose>
             <xsl:when test="$yval_max&gt;$cpu_count">
               <line x1="{($margin_left)-5}" y1="{($graph_height)-($margin_bottom)-((($yval_min)-($cpu_count))*(100 div (($yval_min)-($yval_max))))}" x2="{($graph_width)-($margin_right)+5}" y2="{($graph_height)-($margin_bottom)-((($yval_min)-($cpu_count))*(100 div (($yval_min)-($yval_max))))}" style="stroke-dasharray: 9, 5;stroke:red;stroke-width:1" />
               <text x="{($margin_left)+2}" y="{($graph_height)-($margin_bottom)-((($yval_min)-($cpu_count))*(100 div (($yval_min)-($yval_max))))-2}" style="fill:red; stroke: none;font-size:8px;text-anchor=start">CPU cores</text>
             </xsl:when>
             <xsl:otherwise>
               <text x="{($margin_left)-38}" y="{($graph_height)-($margin_bottom)-93}" style="fill:red; stroke: none;font-size:6px;text-anchor=start"><xsl:value-of select="format-number((($yval_max) div ($cpu_count))*100,''00'')"/>% cpu cores</text>
             </xsl:otherwise>
           </xsl:choose>   
         </svg>
       </xsl:template>
     </xsl:stylesheet>'
           )
   )
   ) 
FROM dual;

SPOOL OFF
SET HEADING ON

SET VERIFY   ON
SET FEEDBACK ON

Result:

generate_svg_22h_21

Result with the query of part XXI on the same period:

generate_svg_21b

Remark:

  • The two queries have been executed from the CDB$ROOT container by the C##LSS common user,
  • The view v$cdb_pdb_aas_lss has been created from the CDB$ROOT container in the C##LSS schema,
  • We have excluded the PDB$SEED container (con_id=2) but if you want to focus on its activity it could be added in the selected containers.
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: