Saturday, March 2, 2013

ORACLE - EXADATA - Smart Scan/Offload

Exadata Smart scan per-requisites:
1.Must be a full scan of an object
2.Must use Oracle Direct path Read mechanism
3.Must be Exadata storage server

With Oracle Exadata, full table scans with selected functions and operators such as =, >, and so on in predicates and index fast full scans can use smart scans.

To see functions that can benefit from Smart Scan, you can issue the query  : 
select name from v$sqlfn_metadata

How to check that Smart Scan is happening ?
1.Explain plan:
TABLE ACCESS STORAGE FULL or INDEX STORAGE FAST FULL SCAN
and
storage ("COL1"  > 0) predicate
Above character indicate that smart scan is possible.

2.Offload eligible byte;
V$SQL (colume:IO_CELL_OFFLOAD_ELIGIBLE_BYTES) - which show he number of bytes are eligible for offloading.

3.10046 Trace:
If same scan was used, there will be CELL SMART TABLE SCAN or CELL SMART INDEX SCAN event in the trace file.

4. Check v$sesstat or SQL PLAN MONITORING Report (EM12c or Grid Control)

Smart Scan Limitation:
1.Cannot be used on cluster tables or IOT
2.Cannot be used on table with ROWDEPENDENCIES

No comments:

Post a Comment