Oracle database experiences

Oracle database blog

Generating SVG graphics with SQL*Plus – Part XIII – Horizontal bar graph

Horizontal bar graph

We will use an horizontal bar graph in order to display the space used by the permanent tablespaces. The base query will be defined as follow:

SELECT DTFL.TABLESPACE_NAME
,      (SELECT MAX(LENGTH(TABLESPACE_NAME)) FROM DBA_DATA_FILES) LEN_TABLESPACE_NAME
,      DTFL.TOTAL_SIZE/1024/1024 TOTAL_SPACE_MO 
,      FRSP.TOTAL_SIZE/1024/1024 FREE_SPACE_MO 
,      (DTFL.TOTAL_SIZE-FRSP.TOTAL_SIZE)/1024/1024 USED_SPACE_MO
,      TRUNC((DTFL.TOTAL_SIZE-FRSP.TOTAL_SIZE)/DTFL.TOTAL_SIZE*100,2) USED_PERCENT
FROM (
   SELECT TABLESPACE_NAME
   ,      SUM(BYTES) total_size 
   FROM DBA_DATA_FILES
   GROUP BY TABLESPACE_NAME) DTFL
LEFT OUTER JOIN (
   SELECT TABLESPACE_NAME
   ,      SUM(BYTES) total_size  
   FROM DBA_FREE_SPACE
   GROUP BY TABLESPACE_NAME) FRSP
ON FRSP.TABLESPACE_NAME=DTFL.TABLESPACE_NAME
ORDER BY DTFL.TABLESPACE_NAME

We have added the LEN_TABLESPACE_NAME column in order to display the tablespace names dynamically.

XSLT variables

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

  • margin_top,
  • margin_bottom,
  • margin_left,
  • margin_right,
  • graph_width,
  • graph_heigth.

We have also added new XSLT variables:

  • <xsl:variable name="bar_length">100</xsl:variable>

    Length of the horizontal bar,

  • <xsl:variable name="tablespace_nb"><xsl:value-of select="count(/descendant::TABLESPACE_NAME)"/></xsl:variable>

    Number of tablespaces,

  • <xsl:variable name="len_tblsp_name"><xsl:value-of select="/descendant::LEN_TABLESPACE_NAME[position()=1]"/></xsl:variable>

    Length of the tablespace names.

Threshold

We would like to display:

  • A green bar when the space used percentage is less than 85%,
  • An orange bar when the space used percentage is less than 97% and greater than 85%,
  • A red bar when the space used percentage is greater than 97%.

In order to achieve this we have used the XSLT variable “color_bar” and the conditional processing instruction “choose”. Note that we have used the code:

&gt;

for the > sign, which implies the DEFINE OFF instruction in the beginning of the script.

Modified query

You will find below the modified query:

SET LINESIZE      300
SET LONGCHUNKSIZE 30000
SET LONG          30000

SET FEEDBACK OFF
SET PAGESIZE 0

SET HEADING OFF
SET DEFINE  OFF

SPOOL generate_svg_13d.svg

SELECT XMLTRANSFORM
   (DBMS_XMLGEN.GETXMLTYPE
      ('SELECT DTFL.TABLESPACE_NAME
       ,      (SELECT MAX(LENGTH(TABLESPACE_NAME)) FROM DBA_DATA_FILES) LEN_TABLESPACE_NAME
       ,      DTFL.TOTAL_SIZE/1024/1024 TOTAL_SPACE_MO 
       ,      FRSP.TOTAL_SIZE/1024/1024 FREE_SPACE_MO 
       ,      (DTFL.TOTAL_SIZE-FRSP.TOTAL_SIZE)/1024/1024 USED_SPACE_MO
       ,      TRUNC((DTFL.TOTAL_SIZE-FRSP.TOTAL_SIZE)/DTFL.TOTAL_SIZE*100,2) USED_PERCENT
       FROM (
          SELECT TABLESPACE_NAME
          ,      SUM(BYTES) total_size 
          FROM DBA_DATA_FILES
          GROUP BY TABLESPACE_NAME) DTFL
       LEFT OUTER JOIN (
          SELECT TABLESPACE_NAME
          ,      SUM(BYTES) total_size  
          FROM DBA_FREE_SPACE
          GROUP BY TABLESPACE_NAME) FRSP
       ON FRSP.TABLESPACE_NAME=DTFL.TABLESPACE_NAME
       ORDER BY DTFL.TABLESPACE_NAME')
,      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">10</xsl:variable>
     <xsl:variable name="margin_right">200</xsl:variable>
     <xsl:variable name="bar_length">100</xsl:variable>
     <xsl:variable name="tablespace_nb"><xsl:value-of select="count(/descendant::TABLESPACE_NAME)"/></xsl:variable>
     <xsl:variable name="len_tblsp_name"><xsl:value-of select="/descendant::LEN_TABLESPACE_NAME[position()=1]"/></xsl:variable>
     <xsl:variable name="graph_width"><xsl:value-of select="600+$margin_left+$margin_right"/></xsl:variable>
     <xsl:variable name="graph_heigth"><xsl:value-of select="300+$margin_top+$margin_bottom"/></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_heigth}">
           <text x="{($margin_left)+5}" y="{($margin_top)-5}" style="fill:#000000; stroke: none;font-size:12px;text-anchor=start">Permanent tablespaces space usage</text>
           <xsl:for-each select="ROWSET/ROW">
             <xsl:variable name="color_bar">
               <xsl:choose>
                 <xsl:when test="(descendant::USED_PERCENT)&gt;= 97">
                   <xsl:text>red</xsl:text>
                 </xsl:when>
                 <xsl:when test="(descendant::USED_PERCENT)&gt;= 85">
                   <xsl:text>orange</xsl:text>
                 </xsl:when>
                 <xsl:otherwise>
                   <xsl:text>lightgreen</xsl:text>
                 </xsl:otherwise>
               </xsl:choose>
             </xsl:variable>
             <text x="{($margin_left)+5}" y="{($margin_top)+(12*(position()))}" style="fill:#000000; stroke: none;font-size:10px;text-anchor=start"><xsl:value-of select="(descendant::TABLESPACE_NAME)"/></text>
             <rect x="{($margin_left)+($len_tblsp_name)*7}" y="{($margin_top)+(12*(position()-1))+3}" width="{(descendant::USED_PERCENT) * (($bar_length) div 100)}" height="9" fill="{$color_bar}" stroke="black"/>  
             <rect x="{($margin_left)+($len_tblsp_name)*7}" y="{($margin_top)+(12*(position()-1))+3}" width="{($bar_length)}" height="9" fill="none" stroke="black"/>   
             <text x="{($margin_left)+(($len_tblsp_name)*7)+($bar_length)+4}" y="{($margin_top)+(12*(position()))}" style="fill: #000000; stroke: none;font-size:10px;text-anchor=end"><xsl:value-of select="format-number((descendant::USED_PERCENT),''00.00'')"/>%</text>
           </xsl:for-each>
           <rect x="{($margin_left)+3}" y="{($margin_top)}" width="{($margin_left)+(($len_tblsp_name)*7)+($bar_length)+25}" height="{5+(($tablespace_nb)*12)}" fill="none" stroke="blue"/> 
         </svg>
       </xsl:template>
     </xsl:stylesheet>')
   )
   ) 
FROM dual;

SPOOL OFF
SET HEADING ON
SET DEFINE  ON

Result for “bar_length” = 100 :

generate_svg_13d_100

Result for “bar_length” = 150 :

generate_svg_13d_150

Remarks:

  • We are drawing in fact two rectangle objects. The first one for the filled color and the second one for the black contour,
  • The blue box is dynamically defined with respect to the number of tablespaces and the length of the tablespace names.
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: