Oracle database experiences

Oracle database blog

Generating SVG graphics with SQL*Plus – Part XVII – Stacked area graph

Scope

The vertical stacked bar graph described in the part XVI can be useful, nevertheless we can also display the same data with a “smoother” graph. Instead of having a rectangle area at each event time, we could have a stacked area for each wait class for all the duration of the graph. We will achieve this in 2 steps:

  • In the first step we will display each wait class as a line graph,
  • In the second step we will display each wait class as a stacked area graph.

Line graph

We will use the query defined in the part IX as a starting point. We will modify the source query like in the part XVI 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() SUM_TIME_WAITED
FROM (
   SELECT * 
   FROM (
      SELECT begin_time
      ,      wait_class#
      ,      SUM(time_waited) OVER (PARTITION BY begin_time ORDER BY wait_class#) sum_time_waited_acc
      FROM v$waitclassmetric_history
      WHERE wait_class# <> 6
        ) tb 
   PIVOT (
      MAX(sum_time_waited_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_name1” is renamed to “graph_name” and is set to ‘Wait Classes’,
  • The variable “graph_unit1” is renamed to “graph_unit” and is set to ‘Time waited (in microseconds)’,
  • The variable “graph_name2” is deleted,
  • The variable “graph_unit2” is deleted,
  • The variable “yval1_min” is renamed to “yval_min” and is set to 0,
  • The variable “yval1_max” is renamed to “yval_max” and is set to the first value of the column SUM_TIME_WAITED,
  • The variable “yval2_min” is deleted,
  • The variable “yval2_max” is deleted,
  • The variable “margin_right” is set to 100.

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_17b.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() SUM_TIME_WAITED
        FROM (
           SELECT * 
           FROM (
              SELECT begin_time
              ,      wait_class#
              ,      SUM(time_waited) OVER (PARTITION BY begin_time ORDER BY wait_class#) sum_time_waited_acc
              FROM v$waitclassmetric_history
              WHERE wait_class# <> 6
                ) tb 
           PIVOT (
              MAX(sum_time_waited_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">Wait Classes</xsl:variable>
     <xsl:variable name="graph_unit">Time waited (in microseconds)</xsl:variable>
     <xsl:variable name="yval_max"><xsl:value-of select="/descendant::SUM_TIME_WAITED[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)-20}" y="{($graph_height)-($margin_bottom)-2}"   style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="round($yval_min)"/></text>
           <text x="{($margin_left)-20}" y="{($graph_height)-($margin_bottom)-25}"  style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="round(($yval_min)+(1*(($yval_max)-($yval_min)) div 4))"/></text>
           <text x="{($margin_left)-20}" y="{($graph_height)-($margin_bottom)-50}"  style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="round(($yval_min)+((($yval_max)-($yval_min)) div 2))"/></text>
           <text x="{($margin_left)-20}" y="{($graph_height)-($margin_bottom)-75}"  style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="round(($yval_min)+(3*(($yval_max)-($yval_min)) div 4))"/></text>
           <text x="{($margin_left)-20}" y="{($graph_height)-($margin_bottom)-100}" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="round($yval_max)"/></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>
           <polyline points="{$v_path0}" style="fill:none;stroke:hotpink;stroke-width:1" />
           <xsl:variable name="v_path1">
             <xsl:for-each select="ROWSET/ROW/C_APPLICATION">
               <xsl:variable name="x_val1">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </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>
           <polyline points="{$v_path1}" style="fill:none;stroke:indianred;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>
           <polyline points="{$v_path2}" style="fill:none;stroke:olive;stroke-width:1" />
           <xsl:variable name="v_path3">
             <xsl:for-each select="ROWSET/ROW/C_ADMINISTRATIVE">
               <xsl:variable name="x_val3">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </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>
           <polyline points="{$v_path3}" style="fill:none;stroke:gray;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>
           <polyline points="{$v_path4}" style="fill:none;stroke:sienna;stroke-width:1" />
           <xsl:variable name="v_path5">
             <xsl:for-each select="ROWSET/ROW/C_COMMIT">
               <xsl:variable name="x_val5">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </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>
           <polyline points="{$v_path5}" style="fill:none;stroke:orange;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>
           <polyline points="{$v_path6}" style="fill:none;stroke:tan;stroke-width:1" />
           <xsl:variable name="v_path7">
             <xsl:for-each select="ROWSET/ROW/C_USER_IO">
               <xsl:variable name="x_val7">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </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>
           <polyline points="{$v_path7}" style="fill:none;stroke:royalblue;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>
           <polyline points="{$v_path8}" style="fill:none;stroke:skyblue;stroke-width:1" />'
           )
   ||
   TO_CLOB(
          '<xsl:variable name="v_path9">
             <xsl:for-each select="ROWSET/ROW/C_SCHEDULER">
               <xsl:variable name="x_val9">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </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>
           <polyline points="{$v_path9}" style="fill:none;stroke:lightcyan;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>
           <polyline points="{$v_path10}" style="fill:none;stroke:lightgray;stroke-width:1" />
           <xsl:variable name="v_path11">
             <xsl:for-each select="ROWSET/ROW/C_QUEUEING">
               <xsl:variable name="x_val11">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </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>
           <polyline points="{$v_path11}" style="fill:none;stroke:bisque;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_17b

Remarks:

  • In displaying the wait classes from C_OTHER to C_QUEUEING the color of a line can be the color of the last wait class with the same cumulated time waited if any,
  • Each wait class graph value is processed in a different “for-each” block.
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: