Oracle Exadata Smart Scan conditions

Exadata Smart Scan gives a possiblity to the Oracle Database to offload SQL processing to the Exadata Storage Servers. In order to make usage of Smart Scan features of Exadata, the initialization parameter cell_offload_processing must be set to TRUE.

SQL> show parameter cell_offload_pr

cell_offload_processing boolean TRUE

The Smart Scan magic SQL processing happens at the storage tier, instead of the database tier, to improve query performance. It reduces the volume of data sent to the database tier thereby reducing the CPU usage on database nodes.

source: oracle.com

We have 4 basic conditions for Smart Scan to be triggered

- There must be a full scan of an object.

- Oracle’s Direct Path Read mechanis must be used, and path reads are generally used when we read   

  into PGA memory (not the buffer cache).

- Each segment being scanned must be on a Cell disk group (usually with ASSM it is).

- We need the CELL_OFFLOAD_PROCESSING initialization parameter to be enabled.

If we get the STORAGE keyword in the explanation plan, does not guarantee that the offload will occur. Sometimes Exadata Storage Server could decide that Smart Scan will not produce any performance.