Oracle database experiences

Oracle database blog

Generating SVG graphics with SQL*Plus – Part XXI – Stacked area graph – Average Active Sessions – Light Sampling Sessions

Scope

In a previous post we have set up Light Sampling Sessions (LSS) and we would like to display the average active sessions for the last hour like in the part XX. The difference is that LSS is a sampling process meanwhile the view v$waitclassmetric_history is an accumulating statistics process. We will keep the sampling period interval to 5 seconds.

Instead of using directly the table lss$_sampling_session, we will create a view in order to display the average active sessions.

View creation – Step 1

Every minute we will count the number of active sessions that are linked to a wait class or a cpu state as follow:

SELECT TO_CHAR(sample_time,'YYYYMMDDHH24MI') sample_yyyymmddhhmi,
       MAX(sample_id) - MIN(sample_id) nb_samples,
       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),-1,1)) count_cpu,
       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),0,1))  count_other,
       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),1,1))  count_application,
       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),2,1))  count_configuration,
       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),3,1))  count_administrative,
       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),4,1))  count_concurrency,
       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),5,1))  count_commit,
       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),7,1))  count_network,
       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),8,1))  count_user_io,
       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),9,1))  count_system_io,
       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),10,1)) count_scheduler,
       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),11,1)) count_cluster,
       COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),12,1)) count_queueing
FROM lss$_sampling_session
GROUP BY TO_CHAR(sample_time,'YYYYMMDDHH24MI')

Remark:

  • The session state means ‘C’ for a CPU state and ‘W’ for a wait event state,
  • The CPU is linked to the virtual wait class -1.

View creation – Step 2

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

SELECT NVL(co_base_qry.sample_yyyymmddhhmi,time_window.start_time) sample_yyyymmddhhmi,
       NVL(co_base_qry.nb_samples,1)           nb_samples,
       NVL(co_base_qry.count_cpu,0)            count_cpu,
       NVL(co_base_qry.count_other,0)          count_other,
       NVL(co_base_qry.count_application,0)    count_application,
       NVL(co_base_qry.count_configuration,0)  count_configuration,
       NVL(co_base_qry.count_administrative,0) count_administrative,
       NVL(co_base_qry.count_concurrency,0)    count_concurrency,
       NVL(co_base_qry.count_commit,0)         count_commit,
       NVL(co_base_qry.count_network,0)        count_network,
       NVL(co_base_qry.count_user_io,0)        count_user_io,
       NVL(co_base_qry.count_system_io,0)      count_system_io,
       NVL(co_base_qry.count_scheduler,0)      count_scheduler,
       NVL(co_base_qry.count_cluster,0)        count_cluster,
       NVL(co_base_qry.count_queueing,0)       count_queueing
FROM
   (SELECT TO_CHAR(SYSDATE - (LEVEL/24/60),'YYYYMMDDHH24MI') start_time
    FROM DUAL 
    CONNECT BY LEVEL <= 61
    ) time_window
LEFT OUTER JOIN 
   (SELECT TO_CHAR(sample_time,'YYYYMMDDHH24MI') sample_yyyymmddhhmi,
           MAX(sample_id) - MIN(sample_id) nb_samples,
           COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),-1,1)) count_cpu,
           COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),0,1))  count_other,
           COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),1,1))  count_application,
           COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),2,1))  count_configuration,
           COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),3,1))  count_administrative,
           COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),4,1))  count_concurrency,
           COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),5,1))  count_commit,
           COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),7,1))  count_network,
           COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),8,1))  count_user_io,
           COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),9,1))  count_system_io,
           COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),10,1)) count_scheduler,
           COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),11,1)) count_cluster,
           COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),12,1)) count_queueing
    FROM lss$_sampling_session
    GROUP BY TO_CHAR(sample_time,'YYYYMMDDHH24MI')
    ) co_base_qry
  ON time_window.start_time=co_base_qry.sample_yyyymmddhhmi

View creation – Step 3

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

SELECT sample_yyyymmddhhmi,
       TRUNC((count_cpu/(nb_samples+1)),3)            aas1,  --aas_cpu,
       TRUNC((count_other/(nb_samples+1)),3)          aas2,  --aas_other,
       TRUNC((count_application/(nb_samples+1)),3)    aas3,  --aas_application,
       TRUNC((count_configuration/(nb_samples+1)),3)  aas4,  --aas_configuration,
       TRUNC((count_administrative/(nb_samples+1)),3) aas5,  --aas_administrative,
       TRUNC((count_concurrency/(nb_samples+1)),3)    aas6,  --aas_concurrency,
       TRUNC((count_commit/(nb_samples+1)),3)         aas7,  --aas_commit,
       TRUNC((count_network/(nb_samples+1)),3)        aas8,  --aas_network,
       TRUNC((count_user_io/(nb_samples+1)),3)        aas9,  --aas_user_io,
       TRUNC((count_system_io/(nb_samples+1)),3)      aas10, --aas_system_io,
       TRUNC((count_scheduler/(nb_samples+1)),3)      aas11, --aas_scheduler,
       TRUNC((count_cluster/(nb_samples+1)),3)        aas12, --aas_cluster,
       TRUNC((count_queueing/(nb_samples+1)),3)       aas13  --aas_queueing    
FROM 
   (SELECT NVL(co_base_qry.sample_yyyymmddhhmi,time_window.start_time) sample_yyyymmddhhmi,
           NVL(co_base_qry.nb_samples,1)           nb_samples,
           NVL(co_base_qry.count_cpu,0)            count_cpu,
           NVL(co_base_qry.count_other,0)          count_other,
           NVL(co_base_qry.count_application,0)    count_application,
           NVL(co_base_qry.count_configuration,0)  count_configuration,
           NVL(co_base_qry.count_administrative,0) count_administrative,
           NVL(co_base_qry.count_concurrency,0)    count_concurrency,
           NVL(co_base_qry.count_commit,0)         count_commit,
           NVL(co_base_qry.count_network,0)        count_network,
           NVL(co_base_qry.count_user_io,0)        count_user_io,
           NVL(co_base_qry.count_system_io,0)      count_system_io,
           NVL(co_base_qry.count_scheduler,0)      count_scheduler,
           NVL(co_base_qry.count_cluster,0)        count_cluster,
           NVL(co_base_qry.count_queueing,0)       count_queueing
    FROM
       (SELECT TO_CHAR(SYSDATE - (LEVEL/24/60),'YYYYMMDDHH24MI') start_time
        FROM DUAL 
        CONNECT BY LEVEL <= 61
        ) time_window
    LEFT OUTER JOIN 
       (SELECT TO_CHAR(sample_time,'YYYYMMDDHH24MI') sample_yyyymmddhhmi,
               MAX(sample_id) - MIN(sample_id) nb_samples,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),-1,1)) count_cpu,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),0,1))  count_other,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),1,1))  count_application,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),2,1))  count_configuration,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),3,1))  count_administrative,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),4,1))  count_concurrency,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),5,1))  count_commit,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),7,1))  count_network,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),8,1))  count_user_io,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),9,1))  count_system_io,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),10,1)) count_scheduler,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),11,1)) count_cluster,
               COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),12,1)) count_queueing
        FROM lss$_sampling_session
        GROUP BY TO_CHAR(sample_time,'YYYYMMDDHH24MI')
        ) co_base_qry
      ON time_window.start_time=co_base_qry.sample_yyyymmddhhmi
   )

At this stage we have a similar result than the one given by the view v$waitclassmetric_history except that we have already pivoted the AAS (Average Active Sessions) of the different wait class and cpu events.

View creation – Step 4

Finally we will accumulate the different wait class and cpu AAS values from the CPU to the QUEUEING wait class as follow:

CREATE OR REPLACE VIEW v$aas_light_sampling_sessions
AS
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((count_cpu/(nb_samples+1)),3)            aas1,  --aas_cpu,
           TRUNC((count_other/(nb_samples+1)),3)          aas2,  --aas_other,
           TRUNC((count_application/(nb_samples+1)),3)    aas3,  --aas_application,
           TRUNC((count_configuration/(nb_samples+1)),3)  aas4,  --aas_configuration,
           TRUNC((count_administrative/(nb_samples+1)),3) aas5,  --aas_administrative,
           TRUNC((count_concurrency/(nb_samples+1)),3)    aas6,  --aas_concurrency,
           TRUNC((count_commit/(nb_samples+1)),3)         aas7,  --aas_commit,
           TRUNC((count_network/(nb_samples+1)),3)        aas8,  --aas_network,
           TRUNC((count_user_io/(nb_samples+1)),3)        aas9,  --aas_user_io,
           TRUNC((count_system_io/(nb_samples+1)),3)      aas10, --aas_system_io,
           TRUNC((count_scheduler/(nb_samples+1)),3)      aas11, --aas_scheduler,
           TRUNC((count_cluster/(nb_samples+1)),3)        aas12, --aas_cluster,
           TRUNC((count_queueing/(nb_samples+1)),3)       aas13  --aas_queueing    
    FROM 
       (SELECT NVL(co_base_qry.sample_yyyymmddhhmi,time_window.start_time) sample_yyyymmddhhmi,
               NVL(co_base_qry.nb_samples,1)           nb_samples,
               NVL(co_base_qry.count_cpu,0)            count_cpu,
               NVL(co_base_qry.count_other,0)          count_other,
               NVL(co_base_qry.count_application,0)    count_application,
               NVL(co_base_qry.count_configuration,0)  count_configuration,
               NVL(co_base_qry.count_administrative,0) count_administrative,
               NVL(co_base_qry.count_concurrency,0)    count_concurrency,
               NVL(co_base_qry.count_commit,0)         count_commit,
               NVL(co_base_qry.count_network,0)        count_network,
               NVL(co_base_qry.count_user_io,0)        count_user_io,
               NVL(co_base_qry.count_system_io,0)      count_system_io,
               NVL(co_base_qry.count_scheduler,0)      count_scheduler,
               NVL(co_base_qry.count_cluster,0)        count_cluster,
               NVL(co_base_qry.count_queueing,0)       count_queueing
        FROM
           (SELECT TO_CHAR(SYSDATE - (LEVEL/24/60),'YYYYMMDDHH24MI') start_time
            FROM DUAL 
            CONNECT BY LEVEL <= 61
            ) time_window
        LEFT OUTER JOIN 
           (SELECT TO_CHAR(sample_time,'YYYYMMDDHH24MI') sample_yyyymmddhhmi,
                   MAX(sample_id) - MIN(sample_id) nb_samples,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),-1,1)) count_cpu,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),0,1))  count_other,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),1,1))  count_application,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),2,1))  count_configuration,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),3,1))  count_administrative,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),4,1))  count_concurrency,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),5,1))  count_commit,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),7,1))  count_network,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),8,1))  count_user_io,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),9,1))  count_system_io,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),10,1)) count_scheduler,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),11,1)) count_cluster,
                   COUNT(DECODE(DECODE(session_state,'C',-1,wait_class#),12,1)) count_queueing
            FROM lss$_sampling_session
            GROUP BY TO_CHAR(sample_time,'YYYYMMDDHH24MI')
            ) co_base_qry
          ON time_window.start_time=co_base_qry.sample_yyyymmddhhmi
       )
  );

Modified source query

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

SELECT BEGIN_TIME,
       C_CPU,
       C_OTHER,
       C_APPLICATION,
       C_CONFIGURATION,
       C_ADMINISTRATIVE,
       C_CONCURRENCY,
       C_COMMIT,
       C_NETWORK,
       C_USER_IO,
       C_SYSTEM_IO,
       C_SCHEDULER,
       C_CLUSTER,
       C_QUEUEING ,
       MAX_AAS,
       (SELECT value FROM v$parameter WHERE name='cpu_count') CPU_COUNT
FROM v$aas_light_sampling_sessions
ORDER BY sample_yyyymmddhhmi

XSLT variables

  • The variable “graph_name” is set to ‘LSS Active Sessions – Waiting + Working’,
  • The variable “yval_max” is set to the first value of the column MAX_AAS,
  • The variable “bar_width” is set to ’10’,
  • The variable “graph_width” is set to ‘600+$margin_left+$margin_right’.

The X axis scale time has been modified by adding small vertical lines every minutes.

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

SELECT XMLTRANSFORM
   (DBMS_XMLGEN.GETXMLTYPE
      ('SELECT BEGIN_TIME,
               C_CPU,
               C_OTHER,
               C_APPLICATION,
               C_CONFIGURATION,
               C_ADMINISTRATIVE,
               C_CONCURRENCY,
               C_COMMIT,
               C_NETWORK,
               C_USER_IO,
               C_SYSTEM_IO,
               C_SCHEDULER,
               C_CLUSTER,
               C_QUEUEING ,
               MAX_AAS,
       (SELECT value FROM v$parameter WHERE name=''cpu_count'') CPU_COUNT
       FROM v$aas_light_sampling_sessions
       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</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="/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_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 dual;

SPOOL OFF
SET HEADING ON

SET VERIFY   ON
SET FEEDBACK ON

Result:

generate_svg_21b

Result with the query of part XX with “bar_width” set to ’10’ and “graph_width” set to ‘600+$margin_left+$margin_right’ for approximately the same period:

generate_svg_21b_20f_evo

Remarks:

  • The two queries have been executed from the CDB$ROOT container by the C##LSS common user,
  • The procedure DBMS_SAMPLING_SESSION.start_sampling has been also executed from the CDB$ROOT container by the C##LSS common user,
  • The view v$aas_light_sampling_sessions has been created from the CDB$ROOT container in the C##LSS schema,
  • The time period of the two previous graphs are not the same because the view v$waitclassmetric_history is not refreshed exactly every minute,
  • The graph shapes are not exactly the same but they look alike.
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: