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

Report

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:

Generic metric report

1 file(s) 4.00 KB

Download

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.

Examples

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 🙂

Tomasz