Oracle database experiences

Oracle database blog

Plotting graph with SQL*Plus – Part VII

v$sysmetric_history scale values

The query presented in the part VI is well suited for y values between 0 and 100. However when you want to plot a specific metric using v$sysmetric_history, you don’t know in advance the minimum and the maximun values. In this case a non proportional y-axis with no gap between each distinct values can be usefull and will show you in one graph the overall picture of the metric variations.

You will find below the modified query with the substitution variable METRIC_ID_IN:

DEFINE METRIC_ID_IN=2093;

SELECT 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) y_val 
      FROM v$sysmetric_history
      WHERE metric_id = &metric_id_in
        AND group_id = 2
       ) tab_val
CROSS JOIN ( 
   SELECT DISTINCT CEIL(value) y_axis
   FROM v$sysmetric_history
   WHERE metric_id = &metric_id_in
     AND group_id = 2
          ) 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 = &metric_id_in 
     AND group_id = 2
   GROUP BY metric_name
         ) 
ORDER BY 1 DESC;

Result

SQL> @sysmetric_vbgraph_a1.sql
   1297242              *
   1145788              *                *
    105950              *             *  *
     75549              *             *  *                    *
     75524              *             *  *          *         *
     74275    *         *             *  *          *         *
     29765    *         *             *  **         *         *
     28757    *      *  *             *  **         *         *
     27938   **      *  *             *  **         *         *
     23621   **      *  *             * ***         *         *
     23297   **      *  *             * ***         *        **
     21569   **      *  *     *       * ***         *        **
     21555   **      *  **    *       * ***         *        **
     20894   **      *  **    *    *  * ***         *        **
     20389   **      *  **    *    *  * ***         *       ***
     19752   **      *  **    *    *  * ***         *       ***    *
     19746   **      *  **    *    *  * ***         *    *  ***    *
     19661   ***     *  **    *    *  * ***         *    *  ***    *
     18842   ***     *  **    *    *  ***** *       *    *  ***    *
     17841 * ***     *  **    *    *  ***** *       *    *  ****   *  *
     17837 * ***     *  **    *    *  ***** *       **   *  ****   *  *
     17750 * ***     *  **    *    *  ***** *    *  **   *  ****   *  *
     17295 * ***  *  *  **    *    *  ***** *    *  **   *  ****   *  *
     16106 * ***  *  *  **    *    *  ***** **   *  **   *  ****   *  *
     16098 * ***  *  *  **    *    *  ***** **   ** **   *  ****   *  *
     14928 * **** *  *  **    *    *  ***** **   ** ***  *  ****   *  *
     14925 * **** *  *  **    *    *  ***** **   ** ***  *  *****  *  *
     14291 ****** *  *  **    *    *  ***** **   ** ***  *  *****  *  *
     14200 ****** ** * ***    *    *  ***** **   ** ***  * ******  * **
     14197 ********* * ***    *    *  ***** **   ** **** * ******* * **
     14165 ********* * ***    *    *  ***** **   ** **** * ******* * ***
     13642 ********* *****    *    *  ***** **   ** **** * ******* * ***
     13381 ********* ***** *  *    *  ***** ** **** **** * ******* * ***
     13376 ********* ***** ** ** * * ****** ** **** **** * ******* * ***
     13294 ********* ***** ** ** * ******** ** **** **** * ******* * ***
     13108 ********* ***** ** ** * ******** ** **** ********************
     12444 ********* ***** ** ** * ******** ** *************************
     12425 ********* ***** ** **** ******** ** *************************
     12288 ********* ******** **** *************************************
     12284 ********* ***************************************************
     11833 *************************************************************
     11734 06:23         Physical Read Total Bytes Per Sec         07:40

42 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: