Oracle database experiences

Oracle database blog

Generating SVG graphics with SQL*Plus – Part XV – Line graph with bubble variation indicator and historical data

V$sysmetric_history

In the Oracle Database Express Edition 11g Release 2 for example, the view v$sysmetric_history is limited to 61 measures for metrics of the group_id=2 which covers about 60 minutes of statistics. It could be interesting to monitor a metric over a larger period.

Hist_sysmetric_history table

Let’s create in a specific schema the table hist_sysmetric_history whose definition is:

CREATE TABLE hist_sysmetric_history 
AS
SELECT * 
FROM v$sysmetric_history
WHERE 1=2;

We could then use the Oracle Scheduler in order to feed this table on a regular basis. You could use the following statement:

MERGE INTO hist_sysmetric_history histsysm
USING (
   SELECT begin_time,
          end_time,
          intsize_csec,
          group_id,
          metric_id,
          metric_name,
          value,
          metric_unit
   FROM v$sysmetric_history
      ) sysm
ON (histsysm.begin_time = sysm.begin_time
    AND histsysm.metric_id = sysm.metric_id
    AND histsysm.group_id = sysm.group_id
    )
WHEN NOT MATCHED THEN 
   INSERT (
      histsysm.begin_time, 
      histsysm.end_time,
      histsysm.intsize_csec,
      histsysm.group_id,
      histsysm.metric_id,
      histsysm.metric_name,
      histsysm.value,
      histsysm.metric_unit)
   VALUES (
      sysm.begin_time, 
      sysm.end_time,
      sysm.intsize_csec,
      sysm.group_id,
      sysm.metric_id,
      sysm.metric_name,
      sysm.value,
      sysm.metric_unit);

Next 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_hist_sysmetric_history WHERE metric_id = &METRIC_ID_IN AND group_id = 2) YVAL_MAX
,      (SELECT MIN(value) FROM v_hist_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_hist_sysmetric_history
WHERE metric_id = &METRIC_ID_IN
  AND group_id = 2 
ORDER BY begin_time

The v_hist_sysmetric_history view is used to select a time interval of statistics from the hist_sysmetric_history table. The period should be contiguous.

XSLT variables

Because the number of measures to display could vary we will modify the variable “graph_width” as follow:

(count(/descendant::YVAL)*($bar_width))+$margin_left+10

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_15c.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_hist_sysmetric_history WHERE metric_id = &METRIC_ID_IN AND group_id = 2) YVAL_MAX
        ,      (SELECT MIN(value) FROM v_hist_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_hist_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="(count(/descendant::YVAL)*($bar_width))+$margin_left+10"/></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_15c

Remark:

  • The Oracle user running this query should have the required privileges on the v$sysmetric_history view. We will discuss this point in a future post.
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: