The Blog from the DBA Classroom

By: Joel Goodman

Archive for June, 2009

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 n1.name = ‘cell physical IO interconnect bytes’
and n2.name = ‘cell physical IO bytes eligible for predicate offload';


IO_FILTERING_PERCENTAGE
————————————————–
81.9404485

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:

V$CELL
V$CELL_REQUEST_TOTALS
V$CELL_STATE
V$CELL_THREAD_HISTORY

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
name:                   0
asmDiskGroupNumber:     0
asmFileIncarnation:     0
asmFileNumber:          0
consumerGroupID:        11202
consumerGroupName:
dbID:                   3931557617
dbName:
dbRequestID:            0
fileType:
id:                     0
instanceNumber:         1
ioBytes:                1048576
ioBytesSofar:           0
ioGridDisk:             /box/predicate3
ioOffset:               0
ioReason:
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:

CellCLI> list METRICDEFINITION detail
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.

Posted in Oracle | 3 Comments »

Oracle Certification Benefits

Posted by Joel Goodman on 19/06/2009


I presented a free lunchtime Web Seminar for Oracle EMEA today to discuss the benefits of certification to employers and employees as well as to describe the exam requirements and design process. There were some requests to access the slides so hewre they are:

>> The Benefits of Oracle Certification

I am the global technical leader of the DBA Certification team and also a lead Subject Matter Expert (SME) reviewer for the DBA, SQL, PL/SQL and Oracle on Linux exams and therefore spend a great deal of time helping and mentoring the exam developers.  In the presentation you can see the project development steps taken from the inception of the “blueprint” for an exam until the exam is ready for production.

If you have any queries please contact me here.

Posted in Oracle | Leave a Comment »

Skills and Training Plans for New Oracle DBAs

Posted by Joel Goodman on 10/06/2009


After co-authoring an article on Performing a DBA 1.0 to DBA 2.0 Upgrade with Harald van Breederode last year,  we had many requests to share our views on entry level Oracle DBAs who may be totally new, or who may have moved into the DBA role from Operations, Development or from other technology areas.  We have now written another article called Installing Oracle  DBA 1.0 to share our own experiences in learning Oracle and to provide advice based on our experience in the Oracle DBA Classroom.

Posted in Oracle | 2 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 116 other followers