Oracle database experiences

Oracle database blog

Generating SVG graphics with SQL*Plus – Part IX

Double line graph

We can modify the query described in the part VII in order to display two different metrics on the same graph as a double line graph. This option allows to display two graphs more compactly.

Modifications

We will add for each graph:

  • The y-axis value: YVAL1, YVAL2,
  • The y-axis maximum value: YVAL1_MAX, YVAL2_MAX,
  • The y-axis minimum value: YVAL1_MIN, YVAL2_MIN,
  • The metric name: METRIC_NAME1, METRIC_NAME2,
  • The metric unit: METRIC_UNIT1, METRIC_UNIT2.

We will also add a right margin.

XSLT variables

We will duplicate the following variables in the XSLT document for each graph:

  • <xsl:variable name="v_path1"><xsl:for-each select="ROWSET/ROW/YVAL1">...

    Polyline of the first graph,

  • <xsl:variable name="v_path2"><xsl:for-each select="ROWSET/ROW/YVAL2">...

    Polyline of the second graph,

  • <xsl:variable name="x_val1"><xsl:value-of select="$margin_left+$bar_width*(position()-1)"/></xsl:variable>

    X-axis value of the first graph,

  • <xsl:variable name="x_val2"><xsl:value-of select="$margin_left+$bar_width*(position()-1)"/></xsl:variable>

    X-axis value of the second graph,

  • <xsl:variable name="y_val1"><xsl:value-of select="round(($graph_height)...</xsl:variable>

    Y-axis value of the first graph,

  • <xsl:variable name="y_val2"><xsl:value-of select="round(($graph_height)...</xsl:variable>

    Y-axis value of the second graph.

Modified query

You will find below the modified query:

DEFINE METRIC_ID1_IN=2057;
DEFINE METRIC_ID2_IN=2050;

SET LINESIZE      1000
SET LONGCHUNKSIZE 30000
SET LONG          30000

SET FEEDBACK OFF
SET VERIFY   OFF
SET PAGESIZE 0

SET HEADING OFF

SPOOL generate_svg_9b.svg

SELECT XMLTRANSFORM
   (DBMS_XMLGEN.GETXMLTYPE
      ('SELECT th1.value YVAL1
        ,      th2.value YVAL2
        ,      (SELECT MAX(value)+DECODE(SIGN(MAX(value)-MIN(value)),0,1+MIN(value),0) FROM v$sysmetric_history WHERE metric_id = &METRIC_ID1_IN AND group_id = 2) YVAL1_MAX
        ,      (SELECT MIN(value) FROM v$sysmetric_history WHERE metric_id = &METRIC_ID1_IN AND group_id = 2) YVAL1_MIN
        ,      (SELECT MAX(value)+DECODE(SIGN(MAX(value)-MIN(value)),0,1+MIN(value),0) FROM v$sysmetric_history WHERE metric_id = &METRIC_ID2_IN AND group_id = 2) YVAL2_MAX
        ,      (SELECT MIN(value) FROM v$sysmetric_history WHERE metric_id = &METRIC_ID2_IN AND group_id = 2) YVAL2_MIN
        ,      th1.metric_name METRIC_NAME1
        ,      th1.metric_unit METRIC_UNIT1
        ,      th2.metric_name METRIC_NAME2
        ,      th2.metric_unit METRIC_UNIT2
        ,      TO_CHAR(th1.begin_time,''HH24:MI'') BEGIN_TIME
        FROM v$sysmetric_history th1
        INNER JOIN v$sysmetric_history th2
           ON th2.begin_time=th1.begin_time
        WHERE th1.metric_id = &METRIC_ID1_IN
          AND th1.group_id = 2 
          AND th2.metric_id = &METRIC_ID2_IN
          AND th2.group_id = 2 
        ORDER BY th1.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">60</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_name1"><xsl:value-of select="/descendant::METRIC_NAME1[position()=1]"/></xsl:variable>
     <xsl:variable name="graph_unit1"><xsl:value-of select="/descendant::METRIC_UNIT1[position()=1]"/></xsl:variable>
     <xsl:variable name="graph_name2"><xsl:value-of select="/descendant::METRIC_NAME2[position()=1]"/></xsl:variable>
     <xsl:variable name="graph_unit2"><xsl:value-of select="/descendant::METRIC_UNIT2[position()=1]"/></xsl:variable>
     <xsl:variable name="yval1_max"><xsl:value-of select="/descendant::YVAL1_MAX[position()=1]"/></xsl:variable>
     <xsl:variable name="yval1_min"><xsl:value-of select="/descendant::YVAL1_MIN[position()=1]"/></xsl:variable>
     <xsl:variable name="yval2_max"><xsl:value-of select="/descendant::YVAL2_MAX[position()=1]"/></xsl:variable>
     <xsl:variable name="yval2_min"><xsl:value-of select="/descendant::YVAL2_MIN[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: blue; stroke: none;font-size:10px;text-anchor=start"><xsl:value-of select="$graph_name1"/></text>
           <text x="{$margin_left+205}" y="{($margin_top)-5}" style="fill: green; stroke: none;font-size:10px;text-anchor=start"><xsl:value-of select="$graph_name2"/></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_unit1"/></text>
           <text x="{($margin_bottom)-($graph_height)}" y="{($graph_width)-($margin_right)+50}" transform="rotate(-90)" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="$graph_unit2"/></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($yval1_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(($yval1_min)+(1*(($yval1_max)-($yval1_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(($yval1_min)+((($yval1_max)-($yval1_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(($yval1_min)+(3*(($yval1_max)-($yval1_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($yval1_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(
          '<text x="{($graph_width)-($margin_right)+12}" y="{($graph_height)-($margin_bottom)-2}"   style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="round($yval2_min)"/></text>
           <text x="{($graph_width)-($margin_right)+12}" y="{($graph_height)-($margin_bottom)-25}"  style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="round(($yval2_min)+(1*(($yval2_max)-($yval2_min)) div 4))"/></text>
           <text x="{($graph_width)-($margin_right)+12}" y="{($graph_height)-($margin_bottom)-50}"  style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="round(($yval2_min)+((($yval2_max)-($yval2_min)) div 2))"/></text>
           <text x="{($graph_width)-($margin_right)+12}" y="{($graph_height)-($margin_bottom)-75}"  style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="round(($yval2_min)+(3*(($yval2_max)-($yval2_min)) div 4))"/></text>
           <text x="{($graph_width)-($margin_right)+12}" y="{($graph_height)-($margin_bottom)-100}" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="round($yval2_max)"/></text>           
           <line x1="{($graph_width)-($margin_right)}" y1="{($graph_height)-($margin_bottom)}" x2="{($graph_width)-($margin_right)}" 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_path1">
             <xsl:for-each select="ROWSET/ROW/YVAL1">
               <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)-((($yval1_min)-(self::node()))*(100 div (($yval1_min)-($yval1_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:blue;stroke-width:1" />
           <xsl:variable name="v_path2">
             <xsl:for-each select="ROWSET/ROW/YVAL2">
               <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)-((($yval2_min)-(self::node()))*(100 div (($yval2_min)-($yval2_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:green;stroke-width:1" />
         </svg>
       </xsl:template>
     </xsl:stylesheet>'
           )
   )
   ) 
FROM dual;

SPOOL OFF
SET HEADING ON

SET VERIFY   ON
SET FEEDBACK ON

Result for the metrics “Host CPU Utilization (%)” (in blue) and “Cursor Cache Hit Ratio” (in green):

generate_svg_9b_2057_2050
Remark:

  • Each y-axis 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: