Exadata Smart Scan Off-Loadable SQL Analysis

How do you tell whether a SQL statement running on Exadata is or was cell offload-able? The view GV$SQL (or V$SQL) provides the information you need to measure this, and of course this is also available in AWR SQL statistics views as well. The table below provides information regarding offload characteristics of individual SQL statements:

 

View Column Description
V$SQL IO_CELL_OFFLOAD_ELIGIBLE_BYTES Number of eligible bytes for offload processing. If column shows 0 (zero), it means the statement was not off-loadable.
V$SQL IO_INTERCONNECT_BYTES Number of bytes exchanged between compute nodes and Exadata cells
V$SQL IO_CELL_UNCOMPRESSED_BYTES Number of uncompressed bytes that are offloaded to/from Exadata cells
V$SQL IO_CELL_OFFLOAD_RETURNED_BYTES Number of bytes returned by Exadata cells from Smart Scan processing
DBA_HIST_SQLSTAT IO_OFFLOAD_ELIG_BYTES_TOTAL Total number off offload-eligible bytes for SQL_ID across all samples
DBA_HIST_SQLSTAT IO_OFFLOAD_ELIG_BYTES_DELTA Total number off offload-eligible bytes for SQL_ID between current sample and previous sample
DBA_HIST_SQLSTAT IO_INTERCONNECT_BYTES_TOTAL Total number off bytes exchanged between compute nodes and Exadata cells across all AWR samples
DBA_HIST_SQLSTAT IO_INTERCONNECT_BYTES_TOTAL Total number off bytes exchanged between compute nodes and Exadata cells between current sample and previous sample
DBA_HIST_SQLSTAT IO_OFFLOAD_RETURNED_BYTES_TOTAL Number of bytes returned by Exadata cells from Smart Scan processing across all samples
DBA_HIST_SQLSTAT IO_OFFLOAD_RETURNED_BYTES_DELTA Number of bytes returned by Exadata cells from Smart Scan processing for current sample.

Let’s test things with the SOE.ORDERS table from the SwingBench order management load test generator. First, we’ll mark all indices on SOE.ORDERS invisible so we can have our test not eligible for Smart Scan processing:

 

 

Now we’ll run a test with cell_offload_processing set to false via a SQL statement hint as well as a comment in the SQL statement so we can retrieve with sql_offload.sql. After this, we’ll run a test with offload processing enabled.

 

 

 

 

 

The results above show this:

  • SQL_ID 8cnczvwc2r4qp and c070kh9qf7jy6, which are two variations of the non-offload-able statement, show that the SQL statement is not eligible for smart scan processing and returned about 1800Mb.
  • SQL_ID dcc0bpm8wg9g4 was eligible, returned in slightly more than 2 seconds, and we can see a 68.6% savings in IO as a result of Smart Scan processing.

One interesting thing to note has to do with execution plan display when cell_offload_plan_display is set to AUTO or ALWAYS. As stated previously in this document, if the table/segment resides on an Exadata cell, the execution plan will show “ACCESS STORAGE” clauses whether or not it actually was offloaded or not. See below:

 

Summary

To determine whether your SQL statement benefitted from Smart Scan cell offload functionality in Exadata environments, you can check V$SQL, V$SESSTAT, and V$MYSTAT.