Oracle database experiences

Oracle database blog

Generating SVG graphics with SQL*Plus – Part XIX – Stacked area graph – Average Active Sessions – Waiting

Average Active Sessions

The graph described in the part XVIII gives an indication about the “time waited” linked to the wait classes. However it could be interesting to know how many sessions in average are waiting on such or such wait class. This indication is given by the column “average_waiter_count” of the view v$waitclassmetric_history.

We will modify the source query as follow:

SELECT TO_CHAR(begin_time,'HH24:MI') BEGIN_TIME
,      c_other          C_OTHER
,      c_application    C_APPLICATION
,      c_configuration  C_CONFIGURATION
,      c_administrative C_ADMINISTRATIVE
,      c_concurrency    C_CONCURRENCY
,      c_commit         C_COMMIT
,      c_network        C_NETWORK
,      c_user_io        C_USER_IO
,      c_system_io      C_SYSTEM_IO
,      c_scheduler      C_SCHEDULER
,      c_cluster        C_CLUSTER
,      c_queueing       C_QUEUEING
,      MAX(c_queueing) OVER() MAX_AVERAGE_WAITER_COUNT
FROM (
   SELECT * 
   FROM (
      SELECT begin_time
      ,      wait_class#
      ,      SUM(average_waiter_count) OVER (PARTITION BY begin_time ORDER BY wait_class#) average_waiter_count_acc
      FROM v$waitclassmetric_history
      WHERE wait_class# <> 6
        ) tb 
   PIVOT (
      MAX(average_waiter_count_acc) 
      FOR wait_class# IN (0 AS c_other, 1 AS c_application,2 AS c_configuration,3 AS c_administrative,4 AS c_concurrency,5 AS c_commit, 
                          7 AS c_network, 8 AS c_user_io, 9 AS c_system_io, 10 AS c_scheduler,11 AS c_cluster,12 AS c_queueing)
         )
   ORDER BY begin_time
     )
ORDER BY begin_time

XSLT variables

  • The variable “graph_name” is set to ‘Active Sessions – Waiting’,
  • The variable “graph_unit” is set to ‘Session Count’,
  • The variable “yval_max” is set to the first value of the column MAX_AVERAGE_WAITER_COUNT.

The values displayed on the y-axis are formatted with the function “format-number”.

Modified query

You will find below the modified query:

SET LINESIZE      1000
SET LONGCHUNKSIZE 30000
SET LONG          30000

SET FEEDBACK OFF
SET VERIFY   OFF
SET PAGESIZE 0

SET HEADING OFF

SPOOL generate_svg_19c.svg

SELECT XMLTRANSFORM
   (DBMS_XMLGEN.GETXMLTYPE
      ('SELECT TO_CHAR(begin_time,''HH24:MI'') BEGIN_TIME
        ,      c_other          C_OTHER
        ,      c_application    C_APPLICATION
        ,      c_configuration  C_CONFIGURATION
        ,      c_administrative C_ADMINISTRATIVE
        ,      c_concurrency    C_CONCURRENCY
        ,      c_commit         C_COMMIT
        ,      c_network        C_NETWORK
        ,      c_user_io        C_USER_IO
        ,      c_system_io      C_SYSTEM_IO
        ,      c_scheduler      C_SCHEDULER
        ,      c_cluster        C_CLUSTER
        ,      c_queueing       C_QUEUEING
        ,      MAX(c_queueing) OVER() MAX_AVERAGE_WAITER_COUNT
        FROM (
           SELECT * 
           FROM (
              SELECT begin_time
              ,      wait_class#
              ,      SUM(average_waiter_count) OVER (PARTITION BY begin_time ORDER BY wait_class#) average_waiter_count_acc
              FROM v$waitclassmetric_history
              WHERE wait_class# <> 6
                ) tb 
           PIVOT (
              MAX(average_waiter_count_acc) 
              FOR wait_class# IN (0 AS c_other, 1 AS c_application,2 AS c_configuration,3 AS c_administrative,4 AS c_concurrency,5 AS c_commit, 
                                  7 AS c_network, 8 AS c_user_io, 9 AS c_system_io, 10 AS c_scheduler,11 AS c_cluster,12 AS c_queueing)
                 )
           ORDER BY begin_time
             )
        ORDER BY begin_time')
,      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">5</xsl:variable>
     <xsl:variable name="graph_width"><xsl:value-of select="300+$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">Active Sessions - Waiting</xsl:variable>
     <xsl:variable name="graph_unit">Session Count</xsl:variable>
     <xsl:variable name="yval_max"><xsl:value-of select="/descendant::MAX_AVERAGE_WAITER_COUNT[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:choose>    
           </xsl:for-each>
           <xsl:variable name="v_path0">
             <xsl:for-each select="ROWSET/ROW/C_OTHER">
               <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:hotpink;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path1">
             <xsl:for-each select="ROWSET/ROW/C_APPLICATION">
               <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:indianred;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path2">
             <xsl:for-each select="ROWSET/ROW/C_CONFIGURATION">
               <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:olive;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path3">
             <xsl:for-each select="ROWSET/ROW/C_ADMINISTRATIVE">
               <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:gray;stroke:none;stroke-width:1" />'
           )
   ||
   TO_CLOB(
          '<xsl:variable name="v_path4">
             <xsl:for-each select="ROWSET/ROW/C_CONCURRENCY">
               <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:sienna;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path5">
             <xsl:for-each select="ROWSET/ROW/C_COMMIT">
               <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:orange;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path6">
             <xsl:for-each select="ROWSET/ROW/C_NETWORK">
               <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:tan;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path7">
             <xsl:for-each select="ROWSET/ROW/C_USER_IO">
               <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:royalblue;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path8">
             <xsl:for-each select="ROWSET/ROW/C_SYSTEM_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:skyblue;stroke:none;stroke-width:1" />'
           )
   ||
   TO_CLOB(
          '<xsl:variable name="v_path9">
             <xsl:for-each select="ROWSET/ROW/C_SCHEDULER">
               <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:lightcyan;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path10">
             <xsl:for-each select="ROWSET/ROW/C_CLUSTER">
               <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:lightgray;stroke:none;stroke-width:1" />'
           )
   ||
   TO_CLOB(
          '<xsl:variable name="v_path11">
             <xsl:for-each select="ROWSET/ROW/C_QUEUEING">
               <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:bisque;stroke:none;stroke-width:1" />
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+8}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Other</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+3}" width="{10}" height="{6}" fill="hotpink" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+16}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Application</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+11}" width="{10}" height="{6}" fill="indianred" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+24}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Configuration</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+19}" width="{10}" height="{6}" fill="olive" stroke="black"/>   
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+32}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Administrative</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+27}" width="{10}" height="{6}" fill="gray" stroke="black"/>   
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+40}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Concurrency</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+35}" width="{10}" height="{6}" fill="sienna" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+48}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Commit</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+43}" width="{10}" height="{6}" fill="orange" stroke="black"/>'
           ) 
    || 
    TO_CLOB(
          '<text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+56}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Network</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+51}" width="{10}" height="{6}" fill="tan" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+64}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">User IO</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+59}" width="{10}" height="{6}" fill="royalblue" stroke="black"/>  
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+72}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">System IO</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+67}" width="{10}" height="{6}" fill="skyblue" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+80}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Scheduler</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+75}" width="{10}" height="{6}" fill="lightcyan" stroke="black"/>
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+88}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Cluster</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+83}" width="{10}" height="{6}" fill="lightgray" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+96}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Queueing</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+91}" width="{10}" height="{6}" fill="bisque" stroke="black"/> 
         </svg>
       </xsl:template>
     </xsl:stylesheet>'
           )
   )
   ) 
FROM dual;

SPOOL OFF
SET HEADING ON

SET VERIFY   ON
SET FEEDBACK ON

Result:

generate_svg_19c

Result with the query of part XVIII for the same period:

generate_svg_19c_18a

Remark:

  • The queries have been executed from a PDB.

Shortly

We will add the “Working” part for the average active sessions.

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: