In which three situations will a Smart Scan occur on a table for which Smart Scan is possible, assuming that
nothing in the environment prevents a Smart Scan?
A.
The ASM diskgroup containing the table’s tablespace has a 4 MByte AU size.
B.
Executing queries in parallel.
C.
setting serial_direct_read= true in the session issuing the SQL statements and executing a serial query.
D.
Having direct path reads used at execution time.
E.
An update to the table is made identifying the row to be modified by primary key.
F.
An index range scan occurs for the table.
Explanation:
Whether you get a direct path read & smart scan, depends on the current buffer cache size, how big segment you’re about to scan and how much of that segment is actually cached at the moment. This dynamic decision unfortunately can cause unexpected surprises and variance in your report/batch job runtimes.
To work around these problems and force a direct path read/smart scan, you can either:
(B) Run your query in parallel as parallel full segment scans will use direct path reads, unless your parallel_degree_policy = AUTO, then you may still get buffered reads thanks to the dynamic in-memory parallel execution decision of Oracle 11.2
(C) Run your query in serial, but force the serial direct path reads by setting _serial_direct_read = TRUE (or
ALWAYS in 11.2.0.2+)
D: One of the most common Exadata performance problems is that the direct path reads (and thus also Smart Scans) don’t sometimes kick in when running full scans in serial sessions.
Note: Smart Scan is a subset of Cell Offload Processing and is perhaps the feature that is most highly associated with Exadata. Smart Scan allows Oracle databases to push query information down to the storage level, specifically:
Filtration (predicate information)
Column Projection
Join FilteringIncorrect Answers:
F: Objects such as clusters and index organized tables cannot be Smart Scanned.
References: http://blog.tanelpoder.com/2013/05/29/forcing-smart-scans-on-exadata-is-_serial_direct_readparameter-safe-to-use-in-production/