Oracle database experiences

Oracle database blog

Generating SVG graphics with SQL*Plus – Part X

Pie chart

We will describe how to display data as a pie chart. In this approach we will not use an XSLT document.

Path element

We will use the SVG path element in order to generate a pie chart. In the following example we will display two pie sectors of 30 degrees each.

The first pie sector filled in red is defined by:

<path d="M100,100 l100,0 a100,100 0 0,0 -13.4,-50 z" fill="red" stroke="black" stroke-width="1" />

The second pie sector filled in yellow is defined by:

<path d="M100,100 l86.6,-50 a100,100 0 0,0 -36.6,-36.6 z" fill="yellow" stroke="black" stroke-width="1" />

Portions of the path element are represented in the following picture and the coordinates between square brackets represent the absolute position in the SVG canvas.

Pie sector graph example:

generate_svg_10_example

V$sys_time_model

We will generate a pie chart for the following v$sys_time_model statistics:

  • 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.

Cumulative percentage

First of all we will get the cumulative percentage value of the statistics in a descending order using the SUM analytic function:

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

Previous cumulative percentage

Secondly we will get the previous cumulative percentage value of the statistics using the LAG analytic function:

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
     )

Trigonometry calculation

Finally we will generate the SVG path element using trigonometric calculation:

SELECT '<path d="M300,300 l'
||     200*COS(cumulative_percent_prev*(3.141592653589*3.6/180))
||     ','
||     -200*SIN(cumulative_percent_prev*(3.141592653589*3.6/180)) 
||     ' a200,200 0 '
||     DECODE(SIGN(percent_value-50),1,1,0)
||     ',0 ' 
||     (200*COS(cumulative_percent*(3.141592653589*3.6/180))-200*(COS(cumulative_percent_prev*(3.141592653589*3.6/180))))
||     ','
||     -1*((200*SIN(cumulative_percent*(3.141592653589*3.6/180))-200*(SIN(cumulative_percent_prev*(3.141592653589*3.6/180))))) 
||     'z" stroke="none" fill="'
||     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')
||     '" stroke-width="1" />' 
FROM ...

Query

You will find below the full query:

SET LINESIZE      300
SET LONGCHUNKSIZE 30000
SET LONG          30000

SET FEEDBACK OFF
SET PAGESIZE 0

SET HEADING OFF

SPOOL generate_svg_10f.svg

SELECT '<svg xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#" xmlns="http://www.w3.org/2000/svg" height="600" width="600" version="1.1" xmlns:cc="http://creativecommons.org/ns#" xmlns:dc="http://purl.org/dc/elements/1.1/">' 
FROM DUAL
   UNION ALL
SELECT '<path d="M300,300 l'
||     200*COS(cumulative_percent_prev*(3.141592653589*3.6/180))
||     ','
||     -200*SIN(cumulative_percent_prev*(3.141592653589*3.6/180)) 
||     ' a200,200 0 '
||     DECODE(SIGN(percent_value-50),1,1,0)
||     ',0 ' 
||     (200*COS(cumulative_percent*(3.141592653589*3.6/180))-200*(COS(cumulative_percent_prev*(3.141592653589*3.6/180))))
||     ','
||     -1*((200*SIN(cumulative_percent*(3.141592653589*3.6/180))-200*(SIN(cumulative_percent_prev*(3.141592653589*3.6/180))))) 
||     'z" stroke="none" fill="'
||     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')
||     '" stroke-width="1" />' 
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
        )
      )
   UNION ALL
SELECT '</svg>' 
FROM DUAL;

SPOOL OFF
SET HEADING ON

Result for a pie chart whose center is [300,300] and a radius of 200:

generate_svg_10f

Remark:

  • DECODE(SIGN(percent_value-50),1,1,0)

    is used to handle pie sector greater than 180 degrees.

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: