Oracle database experiences

Oracle database blog

Plotting graph with SQL*Plus – Part VI

x-axis label

It might be convenient to have a x-axis label for “performance monitoring” queries. You will find below the last query of part V modified in order to add a x-axis label:

SELECT y_axis*4 y_axis,
  LISTAGG(DECODE(y1_val,1,'*',' '),'')  WITHIN GROUP (ORDER BY x_val) graph_val
FROM
  (SELECT tab_val.x_val,
    tab_y_axis.y_axis,
    DECODE(SIGN(tab_val.y_val-tab_y_axis.y_axis),0,1,1,1,0) y1_val
   FROM
    (SELECT RANK() OVER (PARTITION BY 1 ORDER BY  begin_time) x_val, CEIL(value/4) y_val 
     FROM v$sysmetric_history
     WHERE metric_id = 2057 
       AND group_id = 2
    ) tab_val
  CROSS JOIN
    (SELECT LEVEL y_axis FROM dual CONNECT BY LEVEL <= 25
    ) tab_y_axis
  )
GROUP BY y_axis
UNION ALL
(SELECT CEIL(MIN(value)-99),
  RPAD(TO_CHAR(MIN(begin_time),'HH24:MI'),TRUNC((count(*)-LENGTH(metric_name))/2),' ')
  ||metric_name||LPAD(TO_CHAR(MAX(begin_time),'HH24:MI'),TRUNC((count(*)-LENGTH(metric_name))/2),' ') 
 FROM v$sysmetric_history
 WHERE metric_id = 2057 
   AND group_id = 2
 GROUP BY metric_name)
ORDER BY 1 DESC;

Result

SQL> @bar_graph9full_axis.sql
       100
        96
        92
        88
        84
        80
        76
        72
        68
        64                    *                           * *  *
        60                  ***                *       * ** * **       *
        56                 ***** **            ******  **** *****  *** *
        52                 *********************************************
        48                 *********************************************
        44                 *********************************************
        40                 *********************************************
        36                 *********************************************
        32                **********************************************
        28                **********************************************
        24             *  **********************************************
        20             *  **********************************************
        16  ****      **  **********************************************
        12  *****    ***  **********************************************
         8 ******   ***** **********************************************
         4 *************************************************************
       -97 05:48             Host CPU Utilization (%)             07:02

26 rows selected.
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: