The Blog from the DBA Classroom

By: Joel Goodman

Measuring Exadata Offload Efficiency

Posted by Joel Goodman on 23/06/2009

More customers have begun to look at Exadata since the announcement last autumn and I have been teaching the Oracle Exadata Seminar this year to both an EMEA Audience and audiences in North America.

Exadata permits intelligent offloading of certain types of IO to the Cells where it is processed locally reducing the amount of traffic on the storage network and thereby reducing contention, latency and improving performance. Exadata can offload the following:

1. Filters or where clauses on SQL statements – in this case the SQL is offloaded to the cell which reads the blocks and applies the filters to the rows before returning the results over the Exadata Infiniband Storage Interconnect to the requesting host node.

2. Column Projections on SQL statements – in this case the column list is a subset of the columns of a table and the SQL is offloaded to the cell which reads the blocks and returns only the selected columns to the requesting host node. Of course case 1 and 2 may be combined.

3. Certain Data Warehouse Joins – in this case a cell may join data from multiple tables that are joined in the downloaded SQL using the Bloom filtering technique to reduce the number of rows that are returned over the storage network to be post-processed back in the Oracle Database instance.

4. Data File Creation or Extension formatting – When Create Tablespace DDL statements are issued a  datafile is created. Likewise if a data file is added to a tablespace or a file is resized, or autoextends, then this is offloaded to the cells for formatting the blocks of the file reducing both IO and workload on the server.

5. RMAN Fast Incremental Backups– When Block Change Tracking is used with RMAN and Exadata the Cell does the incremental backups at a granularity of the individual block, rather than at the granularity of a group of blocks as is done without Exadata.

To measure these activities there are several V$ Views and here are some of the ways one may view the benefit of these offloads:

1. Look at the total offload efficiency using system stats. V$SYSSTAT lists 16 statistics involving cells and we can use some of them straight from the view and some require a calculation. Here are the stats:

cell blocks helped by commit cache
cell blocks helped by minscn optimization
cell blocks processed by cache layer
cell blocks processed by data layer
cell blocks processed by index layer
cell blocks processed by txn layer
cell blocks skipped due to chained rows
cell commit cache queries
cell physical IO bytes eligible for predicate offload
cell physical IO bytes saved during optimized RMAN file restore
cell physical IO bytes saved during optimized file creation
cell physical IO interconnect bytes
cell scans
cell simulated physical IO bytes eligible for predicate offload
cell simulated physical IO bytes returned by predicate offload
cell transactions found in commit cache

The Statistic “cell physical IO bytes saved during optimized file creation” tells us how we have benefited using Exadata for Datafile create and extend.

The Statistic “cell physical IO bytes saved during optimized RMAN file restore” is an indication of RMAN benefits using Exadata.

To determine offload benefit for SQL where clauses (filtering), Column Projections and Joins we use the “cell physical IO interconnect bytes” and “cell physical IO bytes eligible for predicate offload” statistics as follows:

select 100 – 100*s1.value/s2.value io_filtering_percentage
from v$mystat s1
, v$mystat s2
, v$statname n1
, v$statname n2
where s1.statistic# = n1.statistic#
and s2.statistic# = n2.statistic#
and = ‘cell physical IO interconnect bytes’
and = ‘cell physical IO bytes eligible for predicate offload’;


This measure the number of bytes that were offloaded over the Exadata iterconnect as a percentage of the nuber of bytes theoretcially eligible for predicate offload.

Another way to examine the efficiency is to look at Cell Specific Wait events to see the type, frequency and average duration of the events. There are currently 8 cell oriented wait events for which an Oracle process may wait as follows:

cell multiblock physical read
cell single block physical read
cell smart file creation
cell smart incremental backup
cell smart index scan
cell smart restore from backup
cell smart table scan
cell statistics gather

Just looking at the list is an indication of the types of offloads that may occur and for which a process may wait and this includes table scans, index fast full scans, file creation and RMAN smart Backup and restore.

To see if an instance has waited on these events, how often and for how long one may use queries which exploit the following Exadata Cell v$views:


To see if any sessions are currently waiting, and for which cell:

SELECT w.event, c.cell_path,, w.p3
e.wait_class_id = w.wait_class_id AND
w.p1 = c.cell_hashval AND w.p2 = d.hash_value
order by 1;

To see the waits for the instance since startup use the following:

SELECT, c.cell_path, w.event,
w.total_waits, w.time_waited, w.average_wait
WHERE w.event LIKE ‘%cell%’ AND
d.path like ‘o/%’
order by 1,3,4 desc;

To see the IO occurring from the cell log in to the “cellcli” utility on a cell and use the “list activerequest” command to see active IOs on the cell as follows:

CellCLI> list activerequest detail
name:                   0
asmDiskGroupNumber:     0
asmFileIncarnation:     0
asmFileNumber:          0
consumerGroupID:        11202
dbID:                   3931557617
dbRequestID:            0
id:                     0
instanceNumber:         1
ioBytes:                1048576
ioBytesSofar:           0
ioGridDisk:             /box/predicate3
ioOffset:               0
ioType:                 “Predicate Pushing”
objectNumber:           -1
parentID:               0
requestState:           “Queued for Predicate Disk”
sessionID:              128
sessionSerNumber:       17
sqlID:                  7wnj4uwqw1yb2
tableSpaceNumber:       0

Another way to examine performance is to list the Cell metrics that are gathered automatically byt the cell using “cellcli“. Each performance metric has a definition which may be listed using  the “list METRICDEFINITION detail” command. An example of some metrics are:

name:                   CD_IO_BY_R_LG
description:            “Number of megabytes read in large blocks from a cell disk”
metricType:             Cumulative
objectType:             CELLDISK
unit:                   MB

name:                   CD_IO_BY_R_LG_SEC
description:            “Number of megabytes read in large blocks per second from a cell disk”
metricType:             Rate
objectType:             CELLDISK
unit:                   MB/sec

To see the metrics for example for a specific grid disk do:

CellCLI> list metriccurrent where metricObjectName = ‘data_CD_disk01_cell1’
GD_IO_BY_R_LG   data_CD_disk01_cell1    55.4 MB
GD_IO_BY_R_LG_SEC       data_CD_disk01_cell1    0.0 MB/sec
GD_IO_BY_R_SM   data_CD_disk01_cell1    0.7 MB
GD_IO_BY_R_SM_SEC       data_CD_disk01_cell1    0.0 MB/sec
GD_IO_BY_W_LG   data_CD_disk01_cell1    11.9 MB
GD_IO_BY_W_LG_SEC       data_CD_disk01_cell1    0.0 MB/sec
GD_IO_BY_W_SM   data_CD_disk01_cell1    0.1 MB
GD_IO_BY_W_SM_SEC       data_CD_disk01_cell1    0.0 MB/sec
GD_IO_RQ_R_LG   data_CD_disk01_cell1    56 IO requests
GD_IO_RQ_R_LG_SEC       data_CD_disk01_cell1    0.0 IO/sec
GD_IO_RQ_R_SM   data_CD_disk01_cell1    81 IO requests
GD_IO_RQ_R_SM_SEC       data_CD_disk01_cell1    0.0 IO/sec
GD_IO_RQ_W_LG   data_CD_disk01_cell1    24 IO requests
GD_IO_RQ_W_LG_SEC       data_CD_disk01_cell1    0.0 IO/sec
GD_IO_RQ_W_SM   data_CD_disk01_cell1    9 IO requests
GD_IO_RQ_W_SM_SEC       data_CD_disk01_cell1    0.0 IO/sec

There are many other tools in “cellcli” which are beyond the scope of this discussion but if and when you work with exadata this is a good way to get started in performance measurement.


3 Responses to “Measuring Exadata Offload Efficiency”

  1. said

    By Any Chance, do you have more topics like this particular one named,
    Measuring Exadata Offload Efficiency The Blog from the DBA Classroom?
    I really would like to read even alot more regarding it.

  2. […] by Joel Goodman on 05/11/2010 My last in depth post on Exadata discussed Measuring Exadata Offload Efficiency , explained how to measure the amount of “offload” processing using system stats and […]

  3. whaccal said

    hi. great article!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: