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.