Let start with listing Smart Scan requirements here:
- Full table scan / Full Index Scan
- Direct path read
As you can see that “direct path read” is required for Smart Scan. Parallel queries automatically use direct path read and serial queries may use direct path read. So if you don’t see Smart Scan happening with “Full Table / Index Scan”, its probably serial query not doing “Direct Path Read”.
Direct Path Read
For serial queries Oracle dynamically decides to use “Direct Path Read” base on following parameters
- _serial_direct_read = auto
- _direct_read_decision_statistics_driven = TRUE
- _small_table_threshold = XXX
As you can see that we can force “Direct Path Read” by setting “_serial_direct_read” to “ALWAYS”. It’s a hidden parameter and if you are not is comfortable using it in production, I will suggest making your queries run in parallel and keep db cache size small.