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

COUNT(*)
----------
2840
Execution Plan
----------------------------------------------------------
Plan hash value: 2973321430
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU) | Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL | FND_USER | 2840 | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
As mentioned in the solution of this recipe, the fact that a SQL plan operation shows a STORAGE FULL operation
does not imply that the operation was actually offloaded and was satisfied via Smart Scan; rather, it simply implies
that it could be depending on whether a direct read operation was performed. This behavior can be confusing for
an Exadata DMA and one of the reasons why additional diagnostics measures are required to confirm that smart
scans are actually taking place for your queries. Additional recipes in this chapter will arm you with these diagnostics
techniques.
In the solution of this recipe, we presented the concept of a “SQL operating being offloadable.” Oracle enables
cell offload for full table scans, fast full index scans, fast full IOT scans, and a subset of SQL functions. These functions
are visible in the V$SQLFN_METADATA view, and V$SQLFN_METADATA.OFFLOADABLE contains a YES or NO value indicating
whether the function is offloadable on the storage cells. You can query V$SQLFN_METADATA to find all offloadable and
non-offloadable functions. In Oracle 11gR2, there are 393 offloadable operations and the following query lists some
of these:
SQL> select name,offloadable
from v$sqlfn_metadata
where offloadable='YES'
/
NAME OFF
--------------------- ---
> YES
< YES
>= YES
<= YES
= YES
... Output omitted for brevity
If you are not seeing offload operations in your SQL plan, check the Predicate Information from your plan and
look for functions that are not offloadable, as outlined in bold:
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("ORG_ID"=TO_NUMBER( SYS_CONTEXT ('multi_org2','current_org_id')))
filter("ORG_ID"=TO_NUMBER( SYS_CONTEXT ('multi_org2','current_org_id')))