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 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 n1.name = ‘cell physical IO interconnect bytes’
and n2.name = ‘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, d.name, w.p3
FROM V$SESSION_WAIT w, V$EVENT_NAME e,
V$ASM_DISK d, V$CELL c
WHERE e.name LIKE ‘cell%’ AND
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 d.name, c.cell_path, w.event,
w.total_waits, w.time_waited, w.average_wait
FROM V$SYSTEM_EVENT w, V$ASM_DISK d, V$CELL c
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
ioType: “Predicate Pushing”
requestState: “Queued for Predicate Disk”
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:
CellCLI> list METRICDEFINITION detail
description: “Number of megabytes read in large blocks from a cell disk”
description: “Number of megabytes read in large blocks per second from a cell disk”
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.