Oracle database experiences

Oracle database blog

Plotting graph with SQL*Plus – Part VIII

x-axis label with hour and minute

The query presented in the part VII can be used to help diagnose performance issues. However it doesn’t allow to know the exact time (hour and minute) when a metric took place.
The below modified query has 4 extra lines under the x-axis label displaying the column begin_time vertically in the HH24MI format:

DEFINE METRIC_ID_IN=2057;

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
         ) 
UNION ALL (
   SELECT (y_axis*-1)-999999 y_axis
   ,      LISTAGG(element_time_val,'') WITHIN GROUP (ORDER BY x_val) graph_time_val
   FROM (
      SELECT tab_time.x_val
      ,      tab_y_axis.y_axis
      ,      SUBSTR(tab_time.time_val,tab_y_axis.y_axis,1) element_time_val
      FROM (
         SELECT RANK() OVER (PARTITION BY 1 ORDER BY  begin_time) x_val
         ,      TO_CHAR(begin_time,'HH24')||TO_CHAR(begin_time,'MI') time_val
         FROM v$sysmetric_history
         WHERE metric_id = &metric_id_in
           AND group_id = 2
           ) tab_time
         CROSS JOIN (
            SELECT LEVEL y_axis 
            FROM dual 
            CONNECT BY LEVEL <= 4 
                    ) tab_y_axis
           )
      GROUP BY y_axis
           )
ORDER BY 1 DESC;

Result

SQL> @sysmetric_vbgraph_c1.sql
        23                   *
        21                   *                                         *
        20                  **                                         *
        17                  **                                        **
        16                  ***                                       **
        14                  ***    *                                 ***
        13                  ***   **  **                             ***
        12  *    *          ***   **  **                             ***
        11  *    *  *       ***   **  ***    *                       ***
        10 **    *  *       ***   **  ***    *                       ***
         9 ***  ** **       ***   **  ****  **                       ***
         8 **** *****       ***   **  ********                       ***
         7 **** *****    * ****   **  ********                       ***
         6 **********  * * ****  ***  ********                    *  ***
         5 **********  *** ****  **** *********     *            **  ***
         4 ********************  **** *********     * *   *    *********
         3 ********************  **************     *** *** *  *********
         2 ********************  ***************  ********* *  *********
         1 *************************************************************
       -98 05:55             Host CPU Utilization (%)             07:07
  -1000000 0000000000000000000000000000000000000000000000000000000000000
  -1000001 5556666666666666666666666666666666666666666666666666677777777
  -1000002 5550000000001111111222222333333334444444444555555555500000000
  -1000003 5780134567890235689124578013467890123456789012345678901234567

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