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:
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.
Summary
1050 Wilshire Drive,
Suite 170,
Troy, MI 48084
Phone: (248) 465-9533
Toll free: 1-877-868-1753
Email: [email protected]
© Centroid, Inc. All rights reserved. Contact Privacy Policy Terms of Use CCPA Policy
Centroid is a cloud services and technology company that provides Oracle enterprise workload consulting and managed services across Oracle, Azure, Amazon, Google, and private cloud. From applications to technology to infrastructure, Centroid’s depth of Oracle expertise and breadth of cloud capabilities helps clients modernize, transform, and grow their business to the next level.
© Centroid, Inc. All rights reserved. Contact Privacy Policy Terms of Use CCPA Policy