Oracle metrics from AWR tables using sqldevloper report

In case you don’t have Oracle Enterprise Manager at hand you can always generate quick reports in sqldeveloper that will show activity on your database.

Oracle metrics

There are tones of metrics which are collected by database on regular bases and a lot of them are very interesting.

Metrics definition can be found in view V$METRIC. There are about 200 metrics in 11g some examples of them below

select metric_name, metric_unit 
from v$metric
order by 1
Metric name Metric unit
Average Active Sessions Active Sessions
Blocked User Session Count Sessions
Buffer Cache Hit Ratio % (LogRead – PhyRead)/LogRead
CPU Usage Per Sec CentiSeconds Per Second
Current Logons Count Logons
Database Wait Time Ratio % Wait/DB_Time
Executions Per Sec Executes Per Second
Hard Parse Count Per Sec Parses Per Second
Host CPU Utilization (%) % Busy/(Idle+Busy)
I/O Megabytes per Second Megabtyes per Second
Logical Reads Per Sec Reads Per Second
Logons Per Sec Logons Per Second
Memory Sorts Ratio % MemSort/(MemSort + DiskSort)
Open Cursors Per Sec Cursors Per Second
PGA Cache Hit % % Bytes/TotalBytes
Physical Read Bytes Per Sec Bytes Per Second
Physical Write Bytes Per Sec Bytes Per Second
Redo Generated Per Sec Bytes Per Second
SQL Service Response Time CentiSeconds Per Call
Session Count Sessions
User Calls Per Second User Calls Per Second
User Calls Ratio % UserCalls/AllCalls
User Commits Per Sec Commits Per Second
User Transaction Per Sec Transactions Per Second


Let’s create some simple reports in sqldeveloper. I have created very simple report based on table DBA_HIST_SYSMETRIC_SUMMARY:

DBA_HIST_SYSMETRIC_SUMMARY displays a history of statistical summary of all metric values in the System Metrics Long Duration group. This view contains snapshots of V$SYSMETRIC_SUMMARY

Full code of report is in here:

You can import this report to sqldeveloper “View”->”Reports”->”User defined reports” – click right mouse button you should see “Open Report” and select the report. It should import the report to your sqldeveloper.


Now it’s time to present how the report works. When you run report you are asked about 2 parameters:

metric_name – metrics name to report. In case you don’t know metric name just type a single word like: CPU,memory etc it will list all metrics from V$METRIC which includes the word

instance_id – instance id in case you are using RAC default is 1

Network Traffic Volume Per Sec

Host CPU Usage Per Sec

Logical Reads Per Sec

Have a fun 🙂








10 thoughts on “Oracle metrics from AWR tables using sqldevloper report

Leave a Reply

Your email address will not be published. Required fields are marked *