Exadata Smart Scan: What’s Eligible Anyway?

With Oracle Exadata, what SQL statements actually are eligible for offload processing? If you’ve found yourself puzzled by the documentation, test results or not quite putting two and two together, this post attempts to define and prove what types of SQL statements truly are eligible for offload processing, and measure results.

 

A good place to look is V$SQLFN_METADATA.

 

 

The “OFFLOADABLE” column is relatively self-explanatory. Here are some general rules off cell offload eligibility:

  • All statements using aggregate functions (max, count, avg, etc) are off-loadable even though the functions are not marked as such in V$SQLFN_METADATA.
  • SQL statements selecting all columns with no predicate are not off-loadable because there is no filter predicate or column filtering.
  • Statements using index range scans are not off-loadable.

The IO savings for offload processing is proportional to the aggregate function used in some cases.

I’ll provide examples below:

 

The above is offload-able, yielding a dramatic savings in IO.

 

 

Eligible for offload due to column filtering, not-so-dramatic savings.

 

 

Even though we manually selected all columns had had no predicate, Exadata marks this offload eligible. I believe this is due to row reduction caused by “select count(1)” wrapper around it but let’s test this theory:

 

 

This looks to have had the same effect. Let’s try this using PL/SQL so we can actually do a “SELECT *” without having to worry about wading through a ton of output:

 

 

In this example, the SQL was marked offload-eligible but in fact only a very small amount was offloaded. This is contrary to Oracle documentation (and not necessarily a bad thing), but perhaps due to the fact that SOE.ORDERS is partitioned. Let’s try the same test on a 150 million row non-partitioned table:

 

 

And we see the same result – interesting.

  • The Smart Scan efficiency.

Summary

  • Test showed queries selecting all columns with no predicate did in fact qualify for offload processing, but the number of bytes returned was very low.
  • The finer the granularity in column filtering or predicate filtering on cell offload-eligible queries, the greater the Smart Scan efficiency.