Oracle database experiences

Oracle database blog

Plotting graph with SQL*Plus – Part X

Multiple vertical bar graph

The query described in the part VI can be modified in order to display two vertical bar graphs. You will find below a query that displays the history system metrics for the metric “Execute Without Parse Ratio” (+ character) and for the metric “Host CPU Utilization (%)” (* character):

DEFINE METRIC_ID1_IN=2057;
DEFINE METRIC_ID2_IN=2054;

SELECT y_axis*4 y_axis,
       LISTAGG(DECODE(y1_val,1,DECODE(MOD(x_val,2),0,'*','+'),' '),'')  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))*2 x_val, CEIL(value/4) y_val 
      FROM v$sysmetric_history
      WHERE metric_id = &METRIC_ID1_IN 
        AND group_id = 2
   UNION
   SELECT (RANK() OVER (PARTITION BY 1 ORDER BY begin_time)-1)*2+1 x_val, CEIL(value/4) y_val 
    FROM v$sysmetric_history
    WHERE metric_id = &METRIC_ID2_IN 
      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(((2*count(*))-LENGTH(metric_name))/2),' ')
          ||metric_name||LPAD(TO_CHAR(MAX(begin_time),'HH24:MI'),TRUNC(((2*count(*))-LENGTH(metric_name))/2),' ')
   FROM v$sysmetric_history
   WHERE metric_id = &METRIC_ID2_IN
     AND group_id = 2
   GROUP BY metric_name
         ) 
UNION ALL (
   SELECT CEIL(MIN(value)-1000)
   ,      RPAD(TO_CHAR(MIN(begin_time),'HH24:MI'),TRUNC(((2*count(*))-LENGTH(metric_name))/2),' ')
          ||metric_name||LPAD(TO_CHAR(MAX(begin_time),'HH24:MI'),TRUNC(((2*count(*))-LENGTH(metric_name))/2),' ')
   FROM v$sysmetric_history
   WHERE metric_id = &METRIC_ID1_IN
     AND group_id = 2
   GROUP BY metric_name
         ) 
ORDER BY 1 DESC;

Result:

       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 +*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*                                                                                                                                                                                                                                                                                                                                                                               
       -85 06:54                                          Execute Without Parse Ratio                                          08:05                                                                                                                                                                                                                                                                                                                                                                                
      -999 06:54                                            Host CPU Utilization (%)                                            08:05                                                                                                                                                                                                                                                                                                                                                                               

27 rows selected.

Remark:
If you have some display issues with SQL*Plus e.g. characters shifted, you can save the result of the query in a text file using the SPOOL instruction.

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: