Exadata provides a lot of useful metrics to monitor the Cells.

The Metrics can be of various types:

  • Cumulative: Cumulative statistics since the metric was created.
  • Instantaneous: Value at the time that the metric is collected.
  • Rate: Rates computed by averaging statistics over observation periods.
  • Transition: Are collected at the time when the value of the metrics has changed, and typically captures important transitions in hardware status.

One attribute of the cumulative metric is the collectionTime.

For example, let’s have a look to one of them:

CellCLI> list METRICCURRENT DB_IO_WT_SM detail
         name:                   DB_IO_WT_SM
         alertState:             normal
         collectionTime:         2013-09-12T23:46:14+02:00
         metricObjectName:       EXABDT
         metricType:             Cumulative
         metricValue:            120 ms
         objectType:             IORM_DATABASE

The collectionTime attribute is the time at which the metric was collected.

Why does it matter ?

Based on it, we can compute the delta in second between 2 collections.

Let’s see two use cases.

First use case: Suppose, you decided to extract real-time metrics from the cumulative ones. To do so, you created a script that takes a snapshot of the cumulative metrics each second (default interval) and computes the delta with the previous snapshot (yes, I am describing my exadata_metrics.pl script introduced into this post :-) ).

Then, if the delta value of the metric is 0, you need to know why (two explanations are possible as we’ll see).

Let’s see an example: I’ll take a snapshot with a 40 seconds interval of 2 IORM cumulative metrics:

./exadata_metrics.pl 40 cell=exacell1  name='DB_IO_WT_.*' objectname='EXABDT'
--------------------------------------
----------COLLECTING DATA-------------
--------------------------------------

00:19:21   CELL                    NAME                         OBJECTNAME                                                  VALUE
00:19:21   ----                    ----                         ----------                                                  -----
00:19:21   exacell1                DB_IO_WT_LG                  EXABDT                                                      0.00 ms
00:19:21   exacell1                DB_IO_WT_SM                  EXABDT                                                      0.00 ms

Well, as you can see the computed (delta) value is 0.00 ms but:

  • does it mean that no IO has been queued by the IORM ?
  • or does it mean that the 2 snaps are based on the same collectionTime? (could be the case if the collection interval is greater than the interval you are using with my script).

To answer those questions, I modified the script so that it takes care of the collectionTime: It computes the delta in seconds of the collectionTime recorded into the snapshots.

Let’s see it in action:

Enable the IORM plan:

CellCLI> alter iormplan objective=auto;
IORMPLAN successfully altered

and launch the script with a 40 seconds interval:

./exadata_metrics.pl 40 cell=exacell1  name='DB_IO_WT_.*' objectname='EXABDT'

--------------------------------------
----------COLLECTING DATA-------------
--------------------------------------

DELTA(s)   CELL                    NAME                         OBJECTNAME                                                  VALUE
--------   ----                    ----                         ----------                                                  -----
61         exacell1                DB_IO_WT_SM                  EXABDT                                                      0.00 ms
61         exacell1                DB_IO_WT_LG                  EXABDT                                                      1444922.00 ms

--------------------------------------
----------COLLECTING DATA-------------
--------------------------------------

DELTA(s)   CELL                    NAME                         OBJECTNAME                                                  VALUE
--------   ----                    ----                         ----------                                                  -----
60         exacell1                DB_IO_WT_SM                  EXABDT                                                      1.00 ms
60         exacell1                DB_IO_WT_LG                  EXABDT                                                      2573515.00 ms

--------------------------------------
----------COLLECTING DATA-------------
--------------------------------------

DELTA(s)   CELL                    NAME                         OBJECTNAME                                                  VALUE
--------   ----                    ----                         ----------                                                  -----
0          exacell1                DB_IO_WT_LG                  EXABDT                                                      0.00 ms
0          exacell1                DB_IO_WT_SM                  EXABDT                                                      0.00 ms

Look at the DELTA(s) column: It indicates the delta in seconds for the collectionTime attribute.

So that:

  • DELTA(s) > 0: Means you can check the metric value as the snaps are from 2 distinct collectionTime.
  • DELTA(s) = 0: Means the snaps come from the same collectionTime and then a metric value of 0 is obvious.

Second use case:

As we now have the DELTA(s) value we can compute by our own the associated (_SEC) rate metrics.

For example, from:

./exadata_metrics_orig_new.pl 10 cell=exacell1 name='DB_IO_.*' objectname='EXABDT'
--------------------------------------
----------COLLECTING DATA-------------
--------------------------------------

DELTA(s)   CELL                    NAME                         OBJECTNAME                                                  VALUE                
--------   ----                    ----                         ----------                                                  -----                
60         exacell1                DB_IO_WT_SM                  EXABDT                                                      0.00 ms        
60         exacell1                DB_IO_RQ_SM                  EXABDT                                                      153.00 IO requests
60         exacell1                DB_IO_RQ_LG                  EXABDT                                                      292.00 IO requests
60         exacell1                DB_IO_WT_LG                  EXABDT                                                      830399.00 ms

We can conclude, that:

  • the number of large IO request per second is 292/60=4.87.
  • the number of small IO request per second is 153/60=2.55.

Let’s verify those numbers with their associated rate metrics (DB_IO_RQ_LG_SEC and DB_IO_RQ_SM_SEC):

cellcli -e "list metriccurrent attributes name,metrictype,metricobjectname,metricvalue,collectionTime where name like 'DB_IO_.*' and metricobjectname='EXABDT' and metrictype='Rate'"
         DB_IO_RQ_LG_SEC         Rate    EXABDT  4.9 IO/sec              2013-09-13T16:13:40+02:00
         DB_IO_RQ_SM_SEC         Rate    EXABDT  2.6 IO/sec              2013-09-13T16:13:40+02:00
         DB_IO_WT_LG_RQ          Rate    EXABDT  2,844 ms/request        2013-09-13T16:13:40+02:00
         DB_IO_WT_SM_RQ          Rate    EXABDT  0.0 ms/request          2013-09-13T16:13:40+02:00

Great, that’s the same numbers.

Conclusion:

The collectionTime metric attribute can be very useful when you extract real-time metrics from the cumulative ones as:

  • It provides a way to interpret the results.
  • it provides a way to extract the rate metrics (_SEC) from their cumulatives ones.

Regarding the script:

  • You are able to collect real-time metrics based on cumulative metrics.
  • You can choose the number of snapshots to display and the time to wait between snapshots.
  • You can choose to filter on name and objectname based on predicates (see the help).
  • You can work on all the cells or a subset thanks to the CELL or the GROUPFILE parameter.
  • You can decide the way to compute the metrics with no aggregation, aggregation on cell, objectname or both.

You can download the exadata_metrics.pl script from this repository.