Exadata Smart Scan: What’s Eligible Anyway?

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.