Oracle database experiences

Oracle database blog

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

Area

The lines graph described in the part XVII will be used as a border for the areas graph and we will see that the order of the points in a line graph is important. Let’s start with two lines graph as described in the figure below:

generate_svg_18_ex1

We have a red line graph whose points are displayed from wa1 to wa3. We have also a blue line graph whose points are displayed from wb1 to wb3. Suppose that we want to display all those points in the same line graph starting by the points {wa1,wa2,wa3} and ending by the points {wb1,wb2,wb3}. The result is shown in the following figure:

generate_svg_18_ex2

If we consider that this line graph is the border of a polygon {wa1,wa2,wa3,wb1,wb2,wb3} let’s see what happens if we fill the interior area in the figure below:

generate_svg_18_ex3

It’s not exactly the expected result. Now, let’s rearrange the points of the line graph as {wa1,wa2,wa3,wb3,wb2,wb1} (that is to say that the points of the second line graph have been rearranged in a reverse order) and let’s restart the previous operation:

generate_svg_18_ex4

This is now the expected result! In order to display this area graph we can use the polyline element or the polygon element. We will use the polygon element.

XSLT processing

We will modify the XSLT processing defined in the part XVII as follow:

A) For the line graph defined by the variable “v_pathN” where N=2*i and i > 0:

  • The “v_pathN” generation is the same,
  • The area is defined by the points “{$v_path(N-1)}{$v_pathN}” as follow:
    <polygon points="{$v_path(N-1)}{$v_pathN}" style="fill:olive;stroke:none;stroke-width:1" />

B) For the line graph defined by the variable “v_pathM” where M=2*i+1:

  • The “x_val” value is read in the reverse order:
    <xsl:sort select="position()" order="descending" data-type="number"/>

    and

    <xsl:value-of select="$margin_left+$bar_width*(last()-position())"/>
  • The area is defined by the points “{$v_path(M-1)}{$v_pathM}” as follow:
  • <polygon points="{$v_path(M-1)}{$v_pathM}" style="fill:indianred;stroke:none;stroke-width:1" />

C) For the line graph defined by the variable “v_path0” there is a special processing because the first line graph is the y-axis line:

<polygon points="{$v_path0} {$x_val0},{($graph_height)-($margin_bottom)} {$margin_left},{($graph_height)-($margin_bottom)}" style="fill:hotpink;stroke:none;stroke-width:1" />

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_18a.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>
           <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_18a

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: