Monday, May 7, 2012

Performance Metrics Views

Topic: GV$ views of the 'metrics family' and how they can help in tuning + links to scripts I use.

Oracle has plenty of instrumentation, statistics counters and wait event data are the bread and butter of Oracle monitoring and tuning.
However counters are typically incremented during the life of the instances/sessions, so delta values are often needed to make sense of data for analysis. AWR reports are a way to do that for instance-wide data.
A class of GV$ views that can be of help for faster/ 'online' monitoring are the V$ views that I call of the 'metric family'. Of those I find myself using often: gv$sysmetric, gv$eventmetric and gv$iofuncmetric
The great thing about metrics is that they report values accumulated over shorts period of times (15 and 60 sec are typical).  Effectively this provides the needed deltas.
Still one minor problem: a lot of data is reported by the metrics, more than what is needed normally. So a good filtering of the metrics of interest can be helpful.
Below a few examples of scripts that I have written and I use for this. (Scripts can be found here).

sysmetric_details.sql reports on a dozen of metrics and lists values for instance (in RAC).

GV$IOFUNCMETRIC is new in 11gR2 and provides details on the type of IO the system is doing. An example below. This time iometric.sql aggregates over all instances of RAC.

Conclusions: gv$ views of the metric family provide a very useful set of tools for reactive performance analysis. They provide a quick view of key metrics, events consumed and IO done.
Limitations: views of the metric family don't provide a session-based view or a SQL-based view. They are often  more useful for finding the symptoms rather than the root causes of performance issues.

No comments:

Post a Comment