Oracle database experiences

Oracle database blog

Plotting graph with SQL*Plus – Part V

Performance monitoring

The query described in the part III can be used in order to display system metrics. The following query will display the history system metrics for the metric “Host CPU Utilization (%)” for y belonging to [0,30].

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(tab_y_axis.y_axis,tab_val.y_val,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 = 2057 AND GROUP_ID = 2
    ) tab_val
  CROSS JOIN
    (SELECT level y_axis FROM dual CONNECT BY LEVEL <= 30
    ) tab_y_axis
  )
GROUP BY y_axis
ORDER BY 1 DESC;

Result

SQL> @plot_graph9
        30
        29
        28
        27                                                             *
        26
        25
        24
        23
        22
        21
        20
        19
        18                                       *
        17
        16
        15                                  *
        14                 *
        13
        12
        11                                        *
        10                  *                                *       *
         9          *                              *            *
         8     *                                    *          *  *
         7        *    *                               *    *    * *
         6   *     *    *                  *             *
         5       *        *   *           *               **  *
         4 ** * *        *        ** **      **      ** *           *
         3           **        * *      *       *
         2                      *   *  * *
         1                   *                 *

30 rows selected.

Vertical bar graph

The display could be enhanced using vertical bar graph as it is shown with the following query:

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 = 2057 AND GROUP_ID = 2
    ) tab_val
  CROSS JOIN
    (SELECT level y_axis FROM dual CONNECT BY LEVEL <= 30
    ) tab_y_axis
  )
GROUP BY y_axis
ORDER BY 1 DESC;

Result

SQL> @bar_graph9
        30                                                            *
        29                                                            *
        28                                                            *
        27                                                            **
        26                                                            **
        25                                                            **
        24                                                            **
        23                                                            **
        22                                                            **
        21                                                            **
        20                                                            **
        19                                                            **
        18                                       *                    **
        17                                       *                    **
        16                                       *                    **
        15                                  *    *                    **
        14                 *                *    *                    **
        13                 *                *    *                    **
        12                 *                *    *                    **
        11                 *                *    **                   **
        10                 **               *    **          *       ***
         9          *      **               *    ***         *  *    ***
         8     *    *      **               *    ****        * ** *  ***
         7     *  * *  *   **               *    ****  *    ** ***** ***
         6   * *  ***  **  **              **    ****  * *  ** ***** ***
         5   * * ****  ** *** *           ***    ****  * *********** ***
         4 **********  ****** *   ** **   *****  ***********************
         3 ****************** ** *** ** * ***** ************************
         2 ****************** ***************** ************************
         1 *************************************************************

30 rows selected.

You could also display this vertical bar graph on a vertical percentage scale (0%-100%) as follow:

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
ORDER BY 1 DESC;

Result

SQL> @bar_graph9full
       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 *************************************************************

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