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

How It Works
Oracle tracks I/O statistics specific to the Exadata storage interconnect three main statistics, as explained in
Table 15-2 .

Table 15-2. Smart Scan Statistic Descriptions

Statistic Meaning

cell physical IO interconnect
bytes

Represents the number of bytes transmitted over the storage interconnect.
Oracle increments this counter for all physical I/Os, regardless of whether
satisfied via smart scans or block I/O requests. As such, this statistic
provides a mechanism to measure the amount of data being serviced over
your InfiniBand interconnect and ingested on the compute nodes by the
database instances.

cell physical IO bytes eligible
for predicate offload

Represents the number of bytes eligible for Smart Scan. For queries that
qualify for Smart Scan, this statistic provides an indication of how much
interconnect I/O you will save as a result of cell offload.

cell physical IO interconnect
bytes returned by Smart Scan

Represents the number of bytes returned by Smart Scan. For queries in
which the majority of I/O is serviced via Smart Scan, this statistic is often
very close in size to the cell physical IO interconnect bytes statistic.

Using these statistics and their meanings, you can design scripts to measure Smart Scan behavior and quantify
the volume of I/O bytes transmitted over the storage interconnect. One of the ways that an Exadata DMA can quantify
the savings provided by Smart Scan is to compare the ratio of bytes eligible for predicate offload to the number of
actual bytes transmitted over the storage interconnect. This is commonly referred to as the Exadata storage cell
offload efficiency and is the subject of Recipe 15-5.

15-4. Measuring Offload Statistics for Individual SQL Cursors
Problem
You wish to gather I/O interconnect and cell offload statistics on a per SQL statement basis in order to understand
whether the statement benefited from Exadata Smart Scan.

Solution
In this recipe, you will learn how to extract cell I/O interconnect and offload statistics from V$SQL and SQL
Monitor views.

Querying SQL Cursor Cell I/O Statistics from V$SQL
To report cell I/O and offload statistics for individual SQL statements, run the script in Listing 15-5. This script accepts
a single sql_id as an input parameter.