Here are two scripts for showing GV$SYSMETRIC_HISTORY contents in a simple tabular way:

• vstat.sql - show up to one hour of current history at 1-minute granularity from memory
• awr/dstat.sql - show 1-minute granularity sysmetric history across your entire AWR history

The database metrics infrastructure was introduced back in Oracle 10g, so you (and built-in tuning advisors) could use typically needed metrics in a standardized precomputed manner, without having to do various V$ snapshots and performance counter math yourself.

Some of the metric types (metric groups) are described in the V$METRIC documentation. I used some of these metrics in my scripts to produce something vaguely similar to what Linux OS vmstat and dstat tools show, but for Oracle metrics.

I like Linux dstat as it can show lots of different metrics in a tabular format and renders its columns neatly:

The Oracle vstat.sql script uses GV$SYSMETRIC_HISTORY view that calculates and stores Oracle performance metrics with a 1-minute granularity (and keeps only 60 minutes of history in memory). But these metrics are later stored in AWR history, retaining their per-minute granularity, regardless of your AWR snapshot interval. The awr/dstat.sql shows metrics from AWR, so you need a Diagnostics Pack license. But the V$ view-based short history does not, it should even work on Standard Edition. You could even write your own 5-line PL/SQL loop that wakes up every few minutes and persists the latest GV$ metrics into a custom history table.

Here’s a pretty wide screenshot of dstat.sql output, where I listed one hour of history from the total AWR timeline. You might want to right-click and open these images in a new browser tab to zoom in:

As the output is quite wide, you can use SQL Developer (or the new VS Code extension) that gives you vertical scrolling out of the box. Or you can just spool the output to a text file and open it with an editor to work around terminal wrapping issues.

You can look into the scripts to see from which metrics these columns come from and query V$METRICNAME (or use my metric.sql script) to see the metric descriptions and units, all self-documented by the database engine itself:

SQL> @metric sync

GID GROUP_NAME                          METRIC_NAME                                      METRIC_UNIT
---- ----------------------------------- ------------------------------------------------ ---------------------------------------------
2 System Metrics Long Duration        Average Synchronous Single-Block Read Latency    Milliseconds

SQL> @metric redo

GID GROUP_NAME                          METRIC_NAME                   METRIC_UNIT
---- ----------------------------------- ----------------------------- ------------------------------
2 System Metrics Long Duration        Redo Allocation Hit Ratio     % (#Redo - RedoSpaceReq)/#Redo
2 System Metrics Long Duration        Redo Generated Per Sec        Bytes Per Second
2 System Metrics Long Duration        Redo Generated Per Txn        Bytes Per Txn
2 System Metrics Long Duration        Redo Writes Per Sec           Writes Per Second
2 System Metrics Long Duration        Redo Writes Per Txn           Writes Per Txn
3 System Metrics Short Duration       Redo Generated Per Sec        Bytes Per Second
3 System Metrics Short Duration       Redo Generated Per Txn        Bytes Per Txn
3 System Metrics Short Duration       Redo Writes Per Sec           Writes Per Second
3 System Metrics Short Duration       Redo Writes Per Txn           Writes Per Txn
18 PDB System Metrics Long Duration    Redo Generated Per Sec        Bytes Per Second
18 PDB System Metrics Long Duration    Redo Generated Per Txn        Bytes Per Txn
18 PDB System Metrics Long Duration    Redo Writes Per Sec           Writes Per Second
18 PDB System Metrics Long Duration    Redo Writes Per Txn           Writes Per Txn

Or you can just run @metric % to see all metrics and their descriptions, I get over 400 rows of output on Oracle 19c. By the way, not all computed metrics are stored in the metric history views.

These metrics are essentially standardized computations based on various performance counter views (like V$SYSSTAT). I like that they’re stored with 1-minute granularity even in AWR, so you can get a more detailed view into spikes in the system utilization and latencies, without writing custom collector scripts or using sampled data from ASH.