Oracle database experiences

Oracle database blog

Category Archives: Graphic

Generating SVG graphics with SQL*Plus – Part XXVI – Stacked area graph – Session Activity – Light Sampling Sessions – Database Group splitting

Leave a comment Posted by mourmansk on February 21, 2016

Scope

Following the previous post, we will now consolidate the two database activities and generate a graph like in the part XXII. To achieve that, we will create a private database link and three views.
We will make the assumption that the CDB$ROOT container database has a 2 seconds sampling period interval and that the PDB1 pluggable database has a 5 seconds sampling period interval.

CDB$ROOT Database Link

Let’s grant the following system privilege to the common user C##LSS:

@> connect / as sysdba
Connected.
SYS@orcl> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SYS@orcl> GRANT CREATE DATABASE LINK TO C##LSS;

Grant succeeded.

The common User C##LSS can now create private database links. Let’s create a private database link on the PDB1 pluggable database:

@> connect c##lss@orcl
Enter password:
Connected.
C##LSS@orcl> show con_name;

CON_NAME
------------------------------
CDB$ROOT
C##LSS@orcl> CREATE DATABASE LINK "DB_GRP_ELT2" CONNECT TO LSS IDENTIFIED by lss USING 'pdb1';

Database link created.

Let’s revoke the following system privilege from C##LSS:

SYS@orcl> REVOKE CREATE DATABASE LINK FROM C##LSS;

Revoke succeeded.

Let’s try the database link:

C##LSS@orcl> SELECT COUNT(*) FROM LSS$_SAMPLING_SESSION@DB_GRP_ELT2;

  COUNT(*)
----------
      1066

1 row selected.

The base view will be derived from the view v$cdb_pdb_aas_lss, but the inner query of this view will be a consolidation of the first and the second database.

View v$db_grp_elt1_aas_lss

This view is linked to the CDB$ROOT container database:

CREATE OR REPLACE VIEW v$db_grp_elt1_aas_lss
AS   
SELECT TO_CHAR(sample_time,'YYYYMMDDHH24MI') sample_yyyymmddhhmi,
       element_id,
       30 nb_samples,
       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),-1,1)) count_cpu,
       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),0,1,1,1,2,1,3,1,4,1,5,1,7,1,8,1,9,1,10,1,11,1,12,1)) count_wait
FROM lss$_sampling_session
WHERE con_id IN (0,1)
  AND inst_id = 1
GROUP BY element_id,
TO_CHAR(sample_time,'YYYYMMDDHH24MI');

To measure only the activity of the CDB$ROOT database, there is a restriction on the con_id column.

View v$db_grp_elt2_aas_lss

This view is linked to the PDB1 pluggable database:

CREATE OR REPLACE VIEW v$db_grp_elt2_aas_lss
AS   
SELECT TO_CHAR(sample_time,'YYYYMMDDHH24MI') sample_yyyymmddhhmi,
       element_id,
       12 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@db_grp_elt2
WHERE inst_id = 1
GROUP BY element_id,
TO_CHAR(sample_time,'YYYYMMDDHH24MI');

Finally the main view is defined as follow:

View v$db_group1_aas_lss

CREATE OR REPLACE VIEW v$db_group1_aas_lss
AS 
SELECT sample_yyyymmddhhmi,
       SUBSTR(sample_yyyymmddhhmi,9,2) || ':' || SUBSTR(sample_yyyymmddhhmi,11,2)                                        BEGIN_TIME,
       aas_cpu1                                                                                                          C_ELT1_CPU,
       aas_cpu1+aas_wait1                                                                                                C_ELT1_WAIT,
       aas_cpu1+aas_wait1+aas_cpu2                                                                                       C_ELT2_CPU,
       aas_cpu1+aas_wait1+aas_cpu2+aas_wait2                                                                             C_ELT2_WAIT,
       aas_cpu1+aas_wait1+aas_cpu2+aas_wait2+aas_cpu3                                                                    C_ELT3_CPU,
       aas_cpu1+aas_wait1+aas_cpu2+aas_wait2+aas_cpu3+aas_wait3                                                          C_ELT3_WAIT,
       aas_cpu1+aas_wait1+aas_cpu2+aas_wait2+aas_cpu3+aas_wait3+aas_cpu4                                                 C_ELT4_CPU,
       aas_cpu1+aas_wait1+aas_cpu2+aas_wait2+aas_cpu3+aas_wait3+aas_cpu4+aas_wait4                                       C_ELT4_WAIT,
       aas_cpu1+aas_wait1+aas_cpu2+aas_wait2+aas_cpu3+aas_wait3+aas_cpu4+aas_wait4+aas_cpu5                              C_ELT5_CPU,
       aas_cpu1+aas_wait1+aas_cpu2+aas_wait2+aas_cpu3+aas_wait3+aas_cpu4+aas_wait4+aas_cpu5+aas_wait5                    C_ELT5_WAIT,
       aas_cpu1+aas_wait1+aas_cpu2+aas_wait2+aas_cpu3+aas_wait3+aas_cpu4+aas_wait4+aas_cpu5+aas_wait5+aas_cpu6           C_ELT6_CPU,
       aas_cpu1+aas_wait1+aas_cpu2+aas_wait2+aas_cpu3+aas_wait3+aas_cpu4+aas_wait4+aas_cpu5+aas_wait5+aas_cpu6+aas_wait6 C_ELT6_WAIT,
       MAX(CEIL(aas_cpu1+aas_wait1+aas_cpu2+aas_wait2+aas_cpu3+aas_wait3+aas_cpu4+aas_wait4+aas_cpu5+aas_wait5+aas_cpu6+aas_wait6)) OVER() MAX_CONT_AAS
FROM 
   (SELECT NVL(co_base_qry.sample_yyyymmddhhmi,time_window.start_time) sample_yyyymmddhhmi,
           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,
           NVL(co_base_qry.sum_aas_cpu6,0)   aas_cpu6,
           NVL(co_base_qry.sum_aas_wait6,0)  aas_wait6
    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_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,
               SUM(aas_cpu6)  sum_aas_cpu6,
               SUM(aas_wait6) sum_aas_wait6
        FROM 
           (SELECT sample_yyyymmddhhmi,
                   CASE element_id
                      WHEN 1 THEN TRUNC(count_cpu/(nb_samples),3)
                      ELSE 0
                   END aas_cpu1,
                   CASE element_id
                      WHEN 1 THEN TRUNC(count_wait/(nb_samples),3)
                      ELSE 0
                   END aas_wait1,
                   CASE element_id
                      WHEN 2 THEN TRUNC(count_cpu/(nb_samples),3)
                      ELSE 0
                   END aas_cpu2,
                   CASE element_id
                      WHEN 2 THEN TRUNC(count_wait/(nb_samples),3)
                      ELSE 0
                   END aas_wait2,
                   CASE element_id
                      WHEN 3 THEN TRUNC(count_cpu/(nb_samples),3)
                      ELSE 0
                   END aas_cpu3,
                   CASE element_id
                      WHEN 3 THEN TRUNC(count_wait/(nb_samples),3)
                      ELSE 0
                   END aas_wait3,
                   CASE element_id
                      WHEN 4 THEN TRUNC(count_cpu/(nb_samples),3)
                      ELSE 0
                   END aas_cpu4,
                   CASE element_id
                      WHEN 4 THEN TRUNC(count_wait/(nb_samples),3)
                      ELSE 0
                   END aas_wait4,
                   CASE element_id
                      WHEN 5 THEN TRUNC(count_cpu/(nb_samples),3)
                      ELSE 0
                   END aas_cpu5,
                   CASE element_id
                      WHEN 5 THEN TRUNC(count_wait/(nb_samples),3)
                      ELSE 0
                   END aas_wait5,
                   CASE element_id
                      WHEN 6 THEN TRUNC(count_cpu/(nb_samples),3)
                      ELSE 0
                   END aas_cpu6,
                   CASE element_id
                      WHEN 6 THEN TRUNC(count_wait/(nb_samples),3)
                      ELSE 0
                   END aas_wait6
            FROM 
               (SELECT sample_yyyymmddhhmi,
                       element_id,
                       nb_samples,
                       count_cpu,
                       count_wait
                FROM v$db_grp_elt1_aas_lss
                UNION ALL
                SELECT sample_yyyymmddhhmi,
                       element_id,
                       nb_samples,
                       count_cpu,
                       count_wait
                FROM v$db_grp_elt2_aas_lss
                )
           ) GROUP BY sample_yyyymmddhhmi
       ) co_base_qry
      ON time_window.start_time=co_base_qry.sample_yyyymmddhhmi
   );

Remark:

If the local time of the second database is not the same than the local time of the first database, the view v$db_grp_elt2_aas_lss might be modified in order to have a synchronized sample_yyyymmddhhmi column value between the two databases.

Modified source query

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

        SELECT BEGIN_TIME,
               C_ELT1_CPU,
               C_ELT1_WAIT,
               C_ELT2_CPU,
               C_ELT2_WAIT,
               C_ELT3_CPU,
               C_ELT3_WAIT,
               C_ELT4_CPU,
               C_ELT4_WAIT,
               C_ELT5_CPU,
               C_ELT5_WAIT,
               C_ELT6_CPU,
               C_ELT6_WAIT,
               MAX_CONT_AAS
        FROM v$db_group1_aas_lss
        ORDER BY sample_yyyymmddhhmi

XSLT processing

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

The differents “for-each” “ROWSET/ROW/C_CONT0_CPU” to “ROWSET/ROW/C_CONT5_WAIT” have been replaced by “ROWSET/ROW/C_ELT1_CPU” to “ROWSET/ROW/C_ELT6_WAIT”,
The “cpu_count” processing has been removed.

XSLT variables

The variable “graph_name” is set to ‘LSS Active Sessions – Waiting + Working – Database Group splitting’.

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

SELECT XMLTRANSFORM
   (DBMS_XMLGEN.GETXMLTYPE
      ('SELECT BEGIN_TIME,
               C_ELT1_CPU,
               C_ELT1_WAIT,
               C_ELT2_CPU,
               C_ELT2_WAIT,
               C_ELT3_CPU,
               C_ELT3_WAIT,
               C_ELT4_CPU,
               C_ELT4_WAIT,
               C_ELT5_CPU,
               C_ELT5_WAIT,
               C_ELT6_CPU,
               C_ELT6_WAIT,
               MAX_CONT_AAS
        FROM v$db_group1_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 - Database Group 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: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_ELT1_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_ELT1_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_ELT2_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_ELT2_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_ELT3_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_ELT3_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_ELT4_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_ELT4_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_ELT5_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_ELT5_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_ELT6_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_ELT6_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">ELT1 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">ELT1 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">ELT2 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">ELT2 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">ELT3 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">ELT3 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">ELT4 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">ELT4 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">ELT5 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">ELT5 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">ELT6 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">ELT6 Wait</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+88}" width="{10}" height="{6}" fill="limegreen" stroke="black"/> 
         </svg>
       </xsl:template>
     </xsl:stylesheet>'
           )
   )
   ) 
FROM dual;

SPOOL OFF
SET HEADING ON

SET VERIFY   ON
SET FEEDBACK ON

Result:

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

Remarks:

The general shape of the two graphs is similar,
The query has been executed from the CDB$ROOT container by the C##LSS common user,
The views v$db_grp_elt1_aas_lss, v$db_grp_elt2_aas_lss and v$db_group1_aas_lss are created from the CDB$ROOT container in the C##LSS schema,
You could consolidate more databases by creating views of the type v$db_grp_eltY_aas_lss and by modifying the view v$db_group1_aas_lss.

Graphic, Performance Tuning, SQL, XML 12cR1, Analytic function, Average active sessions, CASE, CDB database, CDB$ROOT, CEIL, CONNECT BY, Container, COUNT, Database group, DBMS_XMLGEN, DECODE, Light sampling sessions, LSS, MAX, NVL, PDB, Performance monitoring, Pluggable Database, SQL*Plus, Stacked area graph, SUBSTR, SVG, TO_CHAR, TO_CLOB, TRUNC, XML, XMLTRANSFORM, XMLType, XSLT

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

Leave a comment Posted by mourmansk on November 16, 2015

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