Oracle database experiences

Oracle database blog

Generating SVG graphics with SQL*Plus – Part XIV – Line graph with bubble variation indicator

Graph variations

We will go back to the line graph described in the part VII. Visually you can see the variations of the statistics but you can not say at first glance how big are the variations. We will modify the base query as follow:

SELECT value YVAL
,      (SELECT MAX(value)+DECODE(SIGN(MAX(value)-MIN(value)),0,1+MIN(value),0) FROM v$sysmetric_history WHERE metric_id = &METRIC_ID_IN AND group_id = 2) YVAL_MAX
,      (SELECT MIN(value) FROM v$sysmetric_history WHERE metric_id = &METRIC_ID_IN AND group_id = 2) YVAL_MIN
,      value-LAG(value, 1, 0) OVER (ORDER BY begin_time) DIFF_PREV_VALUE
,      metric_name METRIC_NAME
,      metric_unit METRIC_UNIT
,      TO_CHAR(begin_time,'HH24:MI') BEGIN_TIME
FROM v$sysmetric_history
WHERE metric_id = &METRIC_ID_IN
  AND group_id = 2 
ORDER BY begin_time

Basically we added the DIFF_PREV_VALUE column which gives the difference between the current “value” of the graph and the previous “value” of the graph using the LAG analytic function.

Bubble variation indicator

The idea behind this concept is to display under each point of the graph a “bubble” whose:

  • Radius is proportional to the difference between the current “value” of the graph and the previous “value” of the graph,
  • Color is yellow when the variation is positive and orange when the variation is negative.

XSLT variables

First of all, we have the “common” variables that we often use:

  • margin_top,
  • margin_bottom,
  • margin_left,
  • bar_width,
  • graph_width,
  • graph_heigth,
  • graph_name,
  • graph_unit,
  • yval_max,
  • yval_min.

We have also added new variables:

  • “bubble_radius”: Radius of the bubble which is set to 10*(value-previous_value)/(YMax-YMin),
  • “bubble_color” : Color of the bubble which is set to yellow for a positive variation and orange for a negative variation,
  • “dis_radius” : Radius of the displayed bubble (to have always a positive radius).

In order to display the bubbles we have modified some XSLT settings:

  • “margin_top” was increased,
  • The x-axis labels were displayed lower,
  • The vertical lines indicating the x-axis labels are longer.

Modified query

You will find below the modified query:

DEFINE METRIC_ID_IN=2108;

SET LINESIZE      1000
SET LONGCHUNKSIZE 30000
SET LONG          30000

SET FEEDBACK OFF
SET VERIFY   OFF
SET PAGESIZE 0

SET HEADING OFF

SPOOL generate_svg_14d.svg

SELECT XMLTRANSFORM
   (DBMS_XMLGEN.GETXMLTYPE
      ('SELECT value YVAL
        ,      (SELECT MAX(value)+DECODE(SIGN(MAX(value)-MIN(value)),0,1+MIN(value),0) FROM v$sysmetric_history WHERE metric_id = &METRIC_ID_IN AND group_id = 2) YVAL_MAX
        ,      (SELECT MIN(value) FROM v$sysmetric_history WHERE metric_id = &METRIC_ID_IN AND group_id = 2) YVAL_MIN
        ,      value-LAG(value, 1, 0) OVER (ORDER BY begin_time) DIFF_PREV_VALUE
        ,      metric_name METRIC_NAME
        ,      metric_unit METRIC_UNIT
        ,      TO_CHAR(begin_time,''HH24:MI'') BEGIN_TIME
        FROM v$sysmetric_history
        WHERE metric_id = &METRIC_ID_IN
          AND group_id = 2 
        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">30</xsl:variable>
     <xsl:variable name="margin_bottom">30</xsl:variable>
     <xsl:variable name="margin_left">40</xsl:variable>
     <xsl:variable name="bar_width">5</xsl:variable>
     <xsl:variable name="graph_width"><xsl:value-of select="315+$margin_left"/></xsl:variable>
     <xsl:variable name="graph_height"><xsl:value-of select="100+$margin_top+$margin_bottom"/></xsl:variable>
     <xsl:variable name="graph_name"><xsl:value-of select="/descendant::METRIC_NAME[position()=1]"/></xsl:variable>
     <xsl:variable name="graph_unit"><xsl:value-of select="/descendant::METRIC_UNIT[position()=1]"/></xsl:variable>
     <xsl:variable name="yval_max"><xsl:value-of select="/descendant::YVAL_MAX[position()=1]"/></xsl:variable>
     <xsl:variable name="yval_min"><xsl:value-of select="/descendant::YVAL_MIN[position()=1]"/></xsl:variable>
       <xsl:template match="/">
         <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)-15}" 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)-1}" y2="{($graph_height)-($margin_bottom)-0}"  style="stroke:lavender;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($graph_height)-($margin_bottom)-25}"  x2="{($graph_width)-1}" y2="{($graph_height)-($margin_bottom)-25}" style="stroke:lavender;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($graph_height)-($margin_bottom)-50}"  x2="{($graph_width)-1}" y2="{($graph_height)-($margin_bottom)-50}" style="stroke:lavender;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($graph_height)-($margin_bottom)-75}"  x2="{($graph_width)-1}" y2="{($graph_height)-($margin_bottom)-75}" style="stroke:lavender;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($graph_height)-($margin_bottom)-100}" x2="{($graph_width)-1}" y2="{($graph_height)-($margin_bottom)-100}" style="stroke:lavender;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:lavender;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)+22}" 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)+14}" x2="{($margin_left)+($bar_width*(position()-1))}" y2="{($graph_height)-($margin_bottom)-100}" style="stroke:lavender;stroke-width:1" /> 
               </xsl:when>
             </xsl:choose>    
           </xsl:for-each>
           <xsl:for-each select="ROWSET/ROW">
             <xsl:variable name="bubble_radius">
               <xsl:value-of select="10*(descendant::DIFF_PREV_VALUE)div(($yval_max)-($yval_min))"/>
             </xsl:variable>
             <xsl:variable name="bubble_color">
               <xsl:choose>
                 <xsl:when test="$bubble_radius > 0">
                   <xsl:text>yellow</xsl:text>
                 </xsl:when>
                 <xsl:otherwise>
                   <xsl:text>orange</xsl:text>
                 </xsl:otherwise>
               </xsl:choose>
             </xsl:variable>
             <xsl:variable name="dis_radius">
               <xsl:choose>
                 <xsl:when test="$bubble_radius > 0">
                   <xsl:value-of select="$bubble_radius"/>
                 </xsl:when>
                 <xsl:otherwise>
                   <xsl:value-of select="($bubble_radius)*-1"/>
                 </xsl:otherwise>
               </xsl:choose>
             </xsl:variable>
             <xsl:choose>
               <xsl:when test="position() > 1">
                 <circle cx="{$margin_left+$bar_width*(position()-1)}" cy="{round(($graph_height)-($margin_bottom)-((($yval_min)-(descendant::YVAL))*(100 div (($yval_min)-($yval_max)))))}" r="{$dis_radius}" style="stroke: black; fill: {$bubble_color};" />
               </xsl:when>
             </xsl:choose>
           </xsl:for-each>
           <xsl:variable name="v_path">
             <xsl:for-each select="ROWSET/ROW/YVAL">
               <xsl:variable name="x_val">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </xsl:variable>
               <xsl:variable name="y_val">
                 <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_val,'','',$y_val,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polyline points="{$v_path}" style="fill:none;stroke:blue;stroke-width:1" />
         </svg>
       </xsl:template>
     </xsl:stylesheet>'
           )
   )
   ) 
FROM dual;

SPOOL OFF
SET HEADING ON

SET VERIFY   ON
SET FEEDBACK ON

Result for the metric “Database CPU Time Ratio”:

generate_svg_14d_2108

Result for the metric “Host CPU Usage Per Sec”:

generate_svg_14d_2155

Remarks:

  • We used the symbol “>” in the “choose” conditional processing instruction,
  • The variation bubble is only displayed after the first value.
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: