Oracle database experiences

Oracle database blog

Average Active Sessions – Portable Light Sampling Sessions – Part II

Scope

In the previous post, we have sampled active sessions in the global temporary table PLAN_TABLE. If we want to display the average active sessions like in the part XXI, we will have first to process the data. Nevertheless, in this case we could not use directly the view v$aas_light_sampling_sessions. Instead, we will use a SELECT query based on this view and stored in the PLAN_TABLE table.

aas_light_sampling_sessions

The underlying SELECT of the view v$aas_light_sampling_sessions will be stored in the table PLAN_TABLE. Basically the table lss$_sampling_session will be replaced by the following sub-query:

SELECT lss."SESSION_STATE"                           session_state, 
       lss."WAIT_CLASS#"                             wait_class#,
       TO_DATE(lss."SAMPLE_TIME",'YYYYMMDDHH24MISS') sample_time
FROM plan_table,
     XMLTABLE('/ROW'
     PASSING xmltype(plan_table.other_xml)
     COLUMNS 
         "SESSION_STATE" CHAR(1)      PATH 'SESSION_STATE',
         "WAIT_CLASS#"   NUMBER       PATH 'WAIT_CLASSH',
         "SAMPLE_TIME"   VARCHAR2(16) PATH 'SAMPLE_TIME') 
     lss
WHERE id > 0
  AND id IS NOT NULL;

The XML data field values are available as column values with the XMLTABLE function. You will find below a SQL script to store the SELECT statement linked to aas_light_sampling_sessions:

-- =======================================================================
--  File Name     : aas_light_sampling_sessions.sql
--  Area          :
--  Description   : Portable Light Sampling Sessions
--  Author        : Arnaud Fargues
--  Tested On     : 12.1, 11.2
--  Plateform     : 
--  Requires      : 
--  Creation Date : 14.05.2016
--  Modified      : 31.07.2016  
-- =======================================================================

INSERT INTO plan_table(object_name,other_xml)
VALUES ('aas_light_sampling_sessions',TO_CLOB('
SELECT sample_yyyymmddhhmi,
SUBSTR(sample_yyyymmddhhmi,9,2) || '':'' || SUBSTR(sample_yyyymmddhhmi,11,2) BEGIN_TIME,
aas1 C_CPU,
aas1+aas2 C_OTHER,
aas1+aas2+aas3 C_APPLICATION,
aas1+aas2+aas3+aas4 C_CONFIGURATION,
aas1+aas2+aas3+aas4+aas5 C_ADMINISTRATIVE,
aas1+aas2+aas3+aas4+aas5+aas6 C_CONCURRENCY,
aas1+aas2+aas3+aas4+aas5+aas6+aas7 C_COMMIT,
aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8 C_NETWORK,
aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9 C_USER_IO,
aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10 C_SYSTEM_IO,
aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11 C_SCHEDULER,
aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11+aas12 C_CLUSTER,
aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11+aas12+aas13 C_QUEUEING,
MAX(CEIL(aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11+aas12+aas13)) OVER() MAX_AAS
FROM (SELECT sample_yyyymmddhhmi,
TRUNC((ccpu/(nb_samples)),3) aas1,
TRUNC((cother/(nb_samples)),3) aas2,
TRUNC((capplication/(nb_samples)),3) aas3,
TRUNC((cconfiguration/(nb_samples)),3) aas4,
TRUNC((cadministrative/(nb_samples)),3) aas5,
TRUNC((cconcurrency/(nb_samples)),3) aas6,
TRUNC((ccommit/(nb_samples)),3) aas7,
TRUNC((cnetwork/(nb_samples)),3) aas8,
TRUNC((cuser_io/(nb_samples)),3) aas9,
TRUNC((csystem_io/(nb_samples)),3) aas10, 
TRUNC((cscheduler/(nb_samples)),3) aas11, 
TRUNC((ccluster/(nb_samples)),3) aas12, 
TRUNC((cqueueing/(nb_samples)),3)aas13
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.ccpu,0) ccpu,
NVL(co_base_qry.cother,0) cother,
NVL(co_base_qry.capplication,0) capplication,
NVL(co_base_qry.cconfiguration,0) cconfiguration,
NVL(co_base_qry.cadministrative,0) cadministrative,
NVL(co_base_qry.cconcurrency,0) cconcurrency,
NVL(co_base_qry.ccommit,0) ccommit,
NVL(co_base_qry.cnetwork,0) cnetwork,
NVL(co_base_qry.cuser_io,0) cuser_io,
NVL(co_base_qry.csystem_io,0) csystem_io,
NVL(co_base_qry.cscheduler,0) cscheduler,
NVL(co_base_qry.ccluster,0) ccluster,
NVL(co_base_qry.cqueueing,0)cqueueing
FROM (SELECT TO_CHAR(SYSDATE - (LEVEL/24/60),''YYYYMMDDHH24MI'') start_time
FROM DUAL 
CONNECT BY LEVEL <= 61
) time_window
LEFT OUTER JOIN 
(SELECT TO_CHAR(sample_time,''YYYYMMDDHH24MI'') sample_yyyymmddhhmi,
30 nb_samples,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),-1,1)) ccpu,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),0,1)) cother,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),1,1)) capplication,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),2,1)) cconfiguration,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),3,1)) cadministrative,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),4,1)) cconcurrency,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),5,1)) ccommit,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),7,1)) cnetwork,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),8,1)) cuser_io,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),9,1)) csystem_io,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),10,1)) cscheduler,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),11,1)) ccluster,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),12,1)) cqueueing
FROM (
SELECT lss."SESSION_STATE" session_state, 
lss."WAIT_CLASS#" wait_class#,
TO_DATE(lss."SAMPLE_TIME",''YYYYMMDDHH24MISS'') sample_time
FROM plan_table,
XMLTABLE(''/ROW''
PASSING xmltype(plan_table.other_xml)
COLUMNS 
"SESSION_STATE" CHAR(1) PATH ''SESSION_STATE'',
"WAIT_CLASS#" NUMBER PATH ''WAIT_CLASSH'',
"SAMPLE_TIME" VARCHAR2(16) PATH ''SAMPLE_TIME'') 
lss
WHERE id > 0
AND id IS NOT NULL)
GROUP BY TO_CHAR(sample_time,''YYYYMMDDHH24MI'')
) co_base_qry
ON time_window.start_time=co_base_qry.sample_yyyymmddhhmi
)
) ORDER BY sample_yyyymmddhhmi'));

COMMIT;

As you can see, the query has less than 4000 characters. It is due to the fact that we are using the function DBMS_XMLGEN.GETXMLTYPE in a way that can handle only queries with less than 4000 characters.

Modified SVG 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 XMLTRANSFORM
   (DBMS_XMLGEN.GETXMLTYPE(other_xml)
    ...
FROM plan_table
WHERE object_name='aas_light_sampling_sessions';

XSLT variables

  • The variable “graph_name” is set to ‘PLSS Active Sessions – Waiting + Working’,
  • The variable “cpu_count” is set to ‘4’.

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

SELECT XMLTRANSFORM
   (DBMS_XMLGEN.GETXMLTYPE(other_xml)
,      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">PLSS Active Sessions - Waiting + Working</xsl:variable>
     <xsl:variable name="graph_unit">Session Count</xsl:variable>
     <xsl:variable name="yval_max"><xsl:value-of select="/descendant::MAX_AAS[position()=1]"/></xsl:variable>
     <xsl:variable name="yval_min">0</xsl:variable>
     <xsl:variable name="cpu_count"><xsl:value-of select="4"/></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_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:limegreen;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path1">
             <xsl:for-each select="ROWSET/ROW/C_OTHER">
               <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:hotpink;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path2">
             <xsl:for-each select="ROWSET/ROW/C_APPLICATION">
               <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:indianred;stroke:none;stroke-width:1" />'
           )
   ||
   TO_CLOB(
          '<xsl:variable name="v_path3">
             <xsl:for-each select="ROWSET/ROW/C_CONFIGURATION">
               <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:olive;stroke:none;stroke-width:1" />'
           )
   ||
   TO_CLOB(
          '<xsl:variable name="v_path4">
             <xsl:for-each select="ROWSET/ROW/C_ADMINISTRATIVE">
               <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:gray;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path5">
             <xsl:for-each select="ROWSET/ROW/C_CONCURRENCY">
               <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:sienna;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path6">
             <xsl:for-each select="ROWSET/ROW/C_COMMIT">
               <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:orange;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path7">
             <xsl:for-each select="ROWSET/ROW/C_NETWORK">
               <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:tan;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path8">
             <xsl:for-each select="ROWSET/ROW/C_USER_IO">
               <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:royalblue;stroke:none;stroke-width:1" />'
           )
   ||
   TO_CLOB(
          '<xsl:variable name="v_path9">
             <xsl:for-each select="ROWSET/ROW/C_SYSTEM_IO">
               <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:skyblue;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path10">
             <xsl:for-each select="ROWSET/ROW/C_SCHEDULER">
               <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:lightcyan;stroke:none;stroke-width:1" />'
           )
   ||
   TO_CLOB(
          '<xsl:variable name="v_path11">
             <xsl:for-each select="ROWSET/ROW/C_CLUSTER">
               <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:lightgray;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path12">
             <xsl:for-each select="ROWSET/ROW/C_QUEUEING">
               <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(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </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">CPU</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">Other</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">Application</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">Configuration</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">Administrative</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">Concurrency</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">Commit</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">Network</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">User IO</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">System IO</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">Scheduler</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">Cluster</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">Queueing</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+96}" width="{10}" height="{6}" fill="bisque" 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 plan_table
WHERE object_name='aas_light_sampling_sessions';

SPOOL OFF
SET HEADING ON

SET VERIFY   ON
SET FEEDBACK ON

This query will be saved in the file generate_svg_plss.sql.

Sampling + SVG generation

In order to display the average active sessions of the PDB1 container database we will:

  1. Connect to the PDB1 container database as the PLSS user,
  2. Execute the previous script aas_light_sampling_sessions.sql in order to store the aas_light_sampling_sessions query,
  3. Execute the script plss_sampling.sql in order to sample the active sessions every 2 seconds on a one minute period,
  4. Execute the script generate_svg_plss.sql in order to generate a SVG graph,
  5. Repeat the steps 3 and 4 as many times as you want.

You will find below an example of such a script:

-- =======================================================================
--  File Name     : plss_master.sql
--  Area          :
--  Description   : Portable Light Sampling Sessions
--  Author        : Arnaud Fargues
--  Tested On     : 12.1, 11.2
--  Plateform     : 
--  Requires      : 
--  Creation Date : 31.07.2016
--  Modified      :   
-- =======================================================================

SET DEFINE OFF

--
--Load aas_light_sampling_sessions query into PLAN_TABLE
--
@aas_light_sampling_sessions.sql

--
--1
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--2
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--3
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--4
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--5
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--6
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--7
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--8
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--9
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--10
--
@plss_sampling.sql

@generate_svg_plss.sql

Result:

generate_svg_plss

Remark:

  • You could repeat the steps @plss_sampling.sql and @generate_svg_plss.sql a high number of times and if you want to display the average active sessions every 5 minutes for example, you could copy the file generate_svg_plss.svg every 5 minutes to check this activity.
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: