Smart Scan and Cell Offload - Oracle Exadata Recipes: A Problem-Solution Approach - page 451

The number of bytes eligible for predicate offload will be non-zero if the SQL statement qualifies for Smart Scan;
if not and if Exadata reverts to a normal block I/O server, this statistic will be zero and the physical read bytes (and
possibly physical write bytes, in the event of a sorting operation) will indicate the amount of physical I/O performed.
In these cases, the cell physical I/O interconnect bytes statistic will closely match the physical read bytes statistic.
When reporting offload efficiencies in this recipe, the higher the efficiency percentage, the greater the I/O savings
as a result of offload operations.
As described in the solution of this recipe, the offload efficiencies can sometimes show negative values or
report efficiencies that are difficult to explain. If you find this to be the case, check whether your SQL statements
are performing physical writes and direct path writes and, if so, multiple the write values by the ASM redundancy
of your ASM disk groups. This behavior sometimes manifests itself in Enterprise Manager and with Real-Time SQL
Monitoring reports. With SQL Monitoring, it is also important to note that the offload efficiency calculations are based
on the physical_read_bytes and physical_write_bytes columns in the V$SQL_MONITOR and V$SQL_PLAN_MONITOR
views; SQL Monitoring does not track the cell physical IO bytes eligible for predicate offload statistic.
One final note about cell offload efficiencies—if you have ever launched CellCLI from a storage cell, you have
undoubtedly noticed the Cell Efficiency Ratio display:

[celladmin@cm01cel01 ~]$ cellcli
CellCLI: Release 11.2.3.1.1 - Production on Sun Nov 11 03:14:57 EST 2012
Copyright (c) 2007, 2011, Oracle. All rights reserved.
Cell Efficiency Ratio : 5
CellCLI>

This efficiency ratio is often difficult to explain, and we recommend you ignore it as it has no practical value for
the Exadata DMA when assessing offload and Smart Scan performance behavior.

15-6. Identifying Smart Scan from 10046 Trace Files
Problem
You have generated an extended trace file using event 10046 and wish to identify Smart Scan operations in the
trace file.

Solution
In this recipe, you will learn how to identify Smart Scan wait events as well as traditional block I/O wait events inside a
10046 trace file generated for a database running on Exadata.

1. Begin by enabling extended SQL trace in your session:

APPS @ visx1> alter session set events '10046 trace name context forever, level 8';
Session altered.
Elapsed: 00:00:00.00
APPS @ visx1>

We have enabled event 10046 with level 8, which enables tracing for our session with wait
events enabled. There are a number of ways to capture wait event information with extended
SQL trace, but additional methods are beyond the scope of this Recipe.

2. Next, execute a series of SQL statements and locate your trace file in your

diagnostic_dest location.