Oracle database experiences

Oracle database blog

Generating SVG graphics with SQL*Plus – Part XII – Pie chart

Percentage value

In the previous post, we had to compute the percentage values. In some cases, we can have the percentage values already computed. We will display the percentage of space used in the recovery areas using the view v$flash_recovery_area_usage. We will modify the query as follow:

SELECT TRUNC(COS(cumulative_percent*(3.141592653589*3.6/180)),15)      COS_CUMUL_PERCENT
,      TRUNC(SIN(cumulative_percent*(3.141592653589*3.6/180)),15)      SIN_CUMUL_PERCENT
,      TRUNC(COS(cumulative_percent_prev*(3.141592653589*3.6/180)),15) COS_CUMUL_PERCENT_PREV
,      TRUNC(SIN(CUMULATIVE_PERCENT_PREV*(3.141592653589*3.6/180)),15) SIN_CUMUL_PERCENT_PREV
,      DECODE(SIGN(percent_value-50),1,1,0) ARC_CODE
,      stat_name                            STAT_NAME
,      TRUNC(percent_value,2)               PERCENT_VALUE
,      DECODE(stat_name,'FREE SPACE','lightgreen','CONTROL FILE','lightblue',
                        'REDO LOG','red','ARCHIVED LOG','orange',
                        'BACKUP PIECE','blue','IMAGE COPY','yellow',
                        'FLASHBACK LOG','grey','FOREIGN ARCHIVED LOG','lightgrey','black') PIE_COLOR
FROM (
   SELECT cumulative_percent
   ,      stat_name
   ,      LAG(cumulative_percent, 1, 0) OVER (ORDER BY percent_space_used DESC, stat_name) AS cumulative_percent_prev
   ,      percent_space_used percent_value
   FROM (
      SELECT SUM(percent_space_used) OVER ( ORDER BY percent_space_used DESC, file_type) cumulative_percent
      ,   file_type stat_name
      ,   percent_space_used 
      FROM (
         SELECT file_type
         ,      percent_space_used 
         FROM v$flash_recovery_area_usage
        UNION ALL
         SELECT 'FREE SPACE'
         ,      100-SUM(percent_space_used) percent_space_used 
         FROM v$flash_recovery_area_usage
           )
      ORDER BY percent_space_used DESC
        )
      )

The framework is similar to the previous query with the SUM and the LAG analytic functions. But in this case we must add and compute the “FREE SPACE” area.

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

SPOOL generate_svg_12c.svg

SELECT XMLTRANSFORM
   (DBMS_XMLGEN.GETXMLTYPE
      ('SELECT TRUNC(COS(cumulative_percent*(3.141592653589*3.6/180)),15) COS_CUMUL_PERCENT
       ,       TRUNC(SIN(cumulative_percent*(3.141592653589*3.6/180)),15) SIN_CUMUL_PERCENT
       ,       TRUNC(COS(cumulative_percent_prev*(3.141592653589*3.6/180)),15) COS_CUMUL_PERCENT_PREV
       ,       TRUNC(SIN(CUMULATIVE_PERCENT_PREV*(3.141592653589*3.6/180)),15) SIN_CUMUL_PERCENT_PREV
       ,       DECODE(SIGN(percent_value-50),1,1,0) ARC_CODE
       ,       stat_name                            STAT_NAME
       ,       TRUNC(percent_value,2)               PERCENT_VALUE
       ,       DECODE(stat_name,''FREE SPACE'',''lightgreen'',''CONTROL FILE'',''lightblue'',
                                ''REDO LOG'',''red'',''ARCHIVED LOG'',''orange'',
                                ''BACKUP PIECE'',''blue'',''IMAGE COPY'',''yellow'',
                                ''FLASHBACK LOG'',''grey'',''FOREIGN ARCHIVED LOG'',''lightgrey'',''black'') PIE_COLOR
       FROM (
          SELECT cumulative_percent
          ,      stat_name
          ,      LAG(cumulative_percent, 1, 0) OVER (ORDER BY percent_space_used DESC, stat_name) AS cumulative_percent_prev
          ,      percent_space_used percent_value
          FROM (
             SELECT SUM(percent_space_used) OVER ( ORDER BY percent_space_used DESC, file_type) cumulative_percent
             ,   file_type stat_name
             ,   percent_space_used 
             FROM (
                SELECT file_type
                ,      percent_space_used 
                FROM v$flash_recovery_area_usage
               UNION ALL
                SELECT ''FREE SPACE''
                ,      100-SUM(percent_space_used) percent_space_used 
                FROM v$flash_recovery_area_usage
                  )
             ORDER BY percent_space_used DESC
               )
      )')
,      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="pie_radius">100</xsl:variable>
     <xsl:variable name="pie_x_center">110</xsl:variable>
     <xsl:variable name="pie_y_center">110</xsl:variable>
     <xsl:variable name="stat_nb"><xsl:value-of select="count(/descendant::STAT_NAME)"/></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="75" y="15" style="fill:#000000; stroke: none;font-size:10px;text-anchor=start">Recovery areas usage</text>
           <xsl:for-each select="ROWSET/ROW">
             <text x="{($margin_left)+(2*($pie_radius))+33}" y="{($margin_top)+(8*(position()))}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="(descendant::STAT_NAME)"/></text>
             <rect x="{($margin_left)+(2*($pie_radius))+20}" y="{($margin_top)+(8*(position()-1))+3}" width="{10}" height="{6}" fill="{(descendant::PIE_COLOR)}" stroke="black"/>    
             <text x="{($margin_left)+(2*($pie_radius))+173}" y="{($margin_top)+(8*(position()))}" style="fill: #000000; stroke: none;font-size:8px;text-anchor=end"><xsl:value-of select="format-number((descendant::PERCENT_VALUE),''00.00'')"/>%</text>
           </xsl:for-each>
           <rect x="{($margin_left)+(2*($pie_radius))+15}" y="{($margin_top)}" width="{185}" height="{5+(($stat_nb)*8)}" fill="none" stroke="blue"/> 
           <xsl:for-each select="ROWSET/ROW">
             <path d="M{($pie_x_center)+($margin_left)},{($pie_y_center)+($margin_top)} l{(descendant::COS_CUMUL_PERCENT_PREV)*($pie_radius)},{(descendant::SIN_CUMUL_PERCENT_PREV)*($pie_radius)*(-1)} a{($pie_radius)},{($pie_radius)} 0 {(descendant::ARC_CODE)},0 
{(($pie_radius)*((descendant::COS_CUMUL_PERCENT)-(descendant::COS_CUMUL_PERCENT_PREV)))},{(($pie_radius)*((descendant::SIN_CUMUL_PERCENT_PREV)-(descendant::SIN_CUMUL_PERCENT)))} z" stroke="none" fill="{(descendant::PIE_COLOR)}" stroke-width="1"/>
           </xsl:for-each>
         </svg>
       </xsl:template>
     </xsl:stylesheet>')
   )
   ) 
FROM dual;

SPOOL OFF
SET HEADING ON

Result:

generate_svg_12c

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: