Pivot query on Automatic Workload Repository

I’ve just tried to get a few information from the Automatic Workload Repository (AWR). I actually wanted to put together an overview of various system metrics from DBA_HIST_SYSMETRIC_SUMMARY to create a chart. Unfortunately the data is stored as name/value pairs and not in columns. So it’s time again to convert rows to columns. There are several more or less difficult solutions for any kind of oracle release. Enclosed I’ll show a simple variant for Oracle 11g with PIVOT.

In my query I’m interested in the metrics Current Logons Count, Process Limit Usage (%) and Session Limit Usage (%). A description of these and other metrics can be found in the EM 12c online documentation Database-Related Metrics Reference Manual. The AWR view DBA_HIST_SYSMETRIC_SUMMARY is explained in the Oracle Database Reference.

Simple query with name / value pairs.

col  METRIC_NAME for a25
alter session set nls_date_format='DD.MM.YYYY HH24:MI:SS';
select
    SNAP_ID, 
    END_TIME,
    METRIC_NAME, 
    MAXVAL 
from 
    DBA_HIST_SYSMETRIC_SUMMARY 
where 
    METRIC_NAME in ('Current Logons Count','Process Limit %','Session Limit %')
order by SNAP_ID;

   SNAP_ID END_TIME            METRIC_NAME                   MAXVAL
---------- ------------------- ------------------------- ----------
       589 03.06.2013 00:21:44 Process Limit %           31,7142857
       589 03.06.2013 00:21:44 Current Logons Count             110
       589 03.06.2013 00:21:44 Session Limit %           22,2222222
       590 03.06.2013 01:20:44 Process Limit %           14,8571429
       590 03.06.2013 01:20:44 Current Logons Count              51
       590 03.06.2013 01:20:44 Session Limit %           11,8055556
       591 03.06.2013 02:21:44 Session Limit %           11,6319444
       591 03.06.2013 02:21:44 Process Limit %           14,2857143
       591 03.06.2013 02:21:44 Current Logons Count              49
       592 03.06.2013 03:20:44 Current Logons Count              47
       592 03.06.2013 03:20:44 Process Limit %           13,7142857
       592 03.06.2013 03:20:44 Session Limit %           11,1111111
...

Lets query the same data with a PIVOT query.

select 
    *
from 
    (select 
        SNAP_ID,
        END_TIME,
        METRIC_NAME,
        MAXVAL
    from 
        DBA_HIST_SYSMETRIC_SUMMARY)
pivot
   ( max(MAXVAL) 
   for METRIC_NAME in ('Current Logons Count' ,'Process Limit %','Session Limit %'))
order by SNAP_ID;

   SNAP_ID END_TIME            'Current Logons Count' 'Process Limit %' 'Session Limit %'
---------- ------------------- ---------------------- ----------------- -----------------
       589 03.06.2013 00:21:44                    110        31,7142857        22,2222222
       590 03.06.2013 01:20:44                     51        14,8571429        11,8055556
       591 03.06.2013 02:21:44                     49        14,2857143        11,6319444
       592 03.06.2013 03:20:44                     47        13,7142857        11,1111111
       593 03.06.2013 04:21:44                     47        13,7142857           10,9375
       594 03.06.2013 05:21:44                     49        14,2857143        11,2847222
       595 03.06.2013 06:21:44                     48                14        11,2847222
       596 03.06.2013 07:21:44                     49        14,2857143        11,2847222
       597 03.06.2013 08:21:44                     48                14        11,2847222
       598 03.06.2013 09:21:45                     49        14,2857143        11,2847222
       599 03.06.2013 10:21:45                     47        13,7142857           10,9375
...

Et voilà, the result can now be used to create a fancy excel chart.

Other solutions with DECODE, CASE or SYS_CONNECT_BY_PATH are also possible. These would even run on older Oracle releases.