Oracle database experiences

Oracle database blog

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

XSLT document

We will generate the SVG document linked to the query described in the part X using an XSLT document. We will modify the source 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,'DB CPU','blue','connection management call elapsed time','lightblue',
                        'sequence load elapsed time','red','sql execute elapsed time','orange',
                        'parse time elapsed','green','PL/SQL execution elapsed time','lightgreen',
                        'inbound PL/SQL rpc elapsed time','grey','PL/SQL compilation elapsed time','lightgrey',
                        'Java execution elapsed time','purple','repeated bind elapsed time','yellow','black') PIE_COLOR
FROM (
   SELECT cumulative_percent
   ,      stat_name
   ,      LAG(cumulative_percent, 1, 0) OVER (ORDER BY value DESC, stat_name) AS cumulative_percent_prev
   ,      (value/sum_value*100) percent_value
   FROM (
      SELECT SUM(value) OVER ( ORDER BY value DESC, stat_name) / 
         (
            SELECT SUM(value) 
            FROM v$sys_time_model 
            WHERE stat_name IN ('DB CPU','connection management call elapsed time','sequence load elapsed time','sql execute elapsed time','parse time elapsed'
                               ,'PL/SQL execution elapsed time','inbound PL/SQL rpc elapsed time','PL/SQL compilation elapsed time','Java execution elapsed time'
                               ,'repeated bind elapsed time')
         ) * 100 cumulative_percent
      ,   stat_name
      ,   value
      ,  (
            SELECT SUM(value) 
            FROM v$sys_time_model  
            WHERE stat_name IN ('DB CPU','connection management call elapsed time','sequence load elapsed time','sql execute elapsed time','parse time elapsed'
                               ,'PL/SQL execution elapsed time','inbound PL/SQL rpc elapsed time','PL/SQL compilation elapsed time','Java execution elapsed time'
                               ,'repeated bind elapsed time')
         ) sum_value 
      FROM v$sys_time_model
      WHERE stat_name IN ('DB CPU','connection management call elapsed time','sequence load elapsed time','sql execute elapsed time','parse time elapsed'
                         ,'PL/SQL execution elapsed time','inbound PL/SQL rpc elapsed time','PL/SQL compilation elapsed time','Java execution elapsed time'
                         ,'repeated bind elapsed time')
      ORDER BY value DESC
        )
     )

The modifications are:

  • The trigonometric calculations will be always done in this query but will be limited to a pie chart radius of 1,
  • We will add the statistics name,
  • We will add the percentage value.

XSLT variables

We will define 6 variables in the XSLT document already seen in the previous posts:

  • <xsl:variable name="margin_top">20</xsl:variable>

    Top margin of the SVG graph (Part III),

  • <xsl:variable name="margin_bottom">30</xsl:variable>

    Bottom margin of the SVG graph (Part V),

  • <xsl:variable name="margin_left">10</xsl:variable>

    Left margin of the SVG graph (Part IV),

  • <xsl:variable name="margin_right">200</xsl:variable>

    Right margin of the SVG graph (Part IX),

  • <xsl:variable name="graph_width"><xsl:value-of select="600+$margin_left+$margin_right"/></xsl:variable>

    Width of the graph (Part IV),

  • <xsl:variable name="graph_heigth"><xsl:value-of select="300+$margin_top+$margin_bottom"/></xsl:variable>

    Height of the graph (Part IV).

We will add 4 variables:

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

    Radius of the pie chart,

  • <xsl:variable name="pie_x_center">110</xsl:variable>

    X center of the pie chart,

  • <xsl:variable name="pie_y_center">110</xsl:variable>

    Y center of the pie chart,

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

    Number of statistics.

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_11f.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,''DB CPU'',''blue'',''connection management call elapsed time'',''lightblue'',
                                ''sequence load elapsed time'',''red'',''sql execute elapsed time'',''orange'',
                                ''parse time elapsed'',''green'',''PL/SQL execution elapsed time'',''lightgreen'',
                                ''inbound PL/SQL rpc elapsed time'',''grey'',''PL/SQL compilation elapsed time'',''lightgrey'',
                                ''Java execution elapsed time'',''purple'',''repeated bind elapsed time'',''yellow'',''black'') PIE_COLOR
       FROM (
          SELECT cumulative_percent
          ,      stat_name
          ,      LAG(cumulative_percent, 1, 0) OVER (ORDER BY value DESC, stat_name) AS cumulative_percent_prev
          ,      (value/sum_value*100) percent_value
          FROM (
             SELECT SUM(value) OVER ( ORDER BY value DESC, stat_name) / 
                (
                   SELECT SUM(value) 
                   FROM v$sys_time_model 
                   WHERE stat_name IN (''DB CPU'',''connection management call elapsed time'',''sequence load elapsed time'',''sql execute elapsed time'',''parse time elapsed''
                                      ,''PL/SQL execution elapsed time'',''inbound PL/SQL rpc elapsed time'',''PL/SQL compilation elapsed time'',''Java execution elapsed time''
                                      ,''repeated bind elapsed time'')
                ) * 100 cumulative_percent
             ,   stat_name
             ,   value
             ,  (
                   SELECT SUM(value) 
                   FROM v$sys_time_model  
                   WHERE stat_name IN (''DB CPU'',''connection management call elapsed time'',''sequence load elapsed time'',''sql execute elapsed time'',''parse time elapsed''
                                      ,''PL/SQL execution elapsed time'',''inbound PL/SQL rpc elapsed time'',''PL/SQL compilation elapsed time'',''Java execution elapsed time''
                                      ,''repeated bind elapsed time'')
                ) sum_value 
             FROM v$sys_time_model
             WHERE stat_name IN (''DB CPU'',''connection management call elapsed time'',''sequence load elapsed time'',''sql execute elapsed time'',''parse time elapsed''
                                ,''PL/SQL execution elapsed time'',''inbound PL/SQL rpc elapsed time'',''PL/SQL compilation elapsed time'',''Java execution elapsed time''
                                ,''repeated bind elapsed time'')
             ORDER BY value 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">Time model statistics</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_11f

Remarks:

  • The SVG path element is processed in a “for-each” loop,
  • The keyword “descendant::” is an XPath axis. In our case it is used in a “for-each” loop and it means that we will get the value of the specified “column” e.g. “descendant::COS_CUMUL_PERCENT”,
  • We have used the “format-number” function to format the percentage 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: