My Oracle Support Banner

Tuning SQLs to Optimize Smart Scan Performance (Doc ID 2608210.1)

Last updated on MAY 04, 2023

Applies to:

Oracle Exadata Storage Server Software - Version 11.1.3.1.0 to 23.1.1.0.0
Information in this document applies to any platform.

Goal

 The goal of this document is to share tips on how to optimize your SQLs for the best smart scan performance on Exadata particularly for large reads.

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Goal
Solution
 Tuning SQLs to Maximize Smart Scan Throughput
 Standard SQL Tuning Rules Apply
 Exadata Smart Scan SQL Best Practices
 Data Collection for Troubleshooting Smart Scan SQL Issues
 Diagnosing smart scan issues
 No Smart Scans (cell multiblock physical read)
 Reduced Storage Index Savings
 Reduced Columnar Cache Reasons
 Increased Passthru Bytes
 Use of direct path reads
 Check for IORM Queuing
 Tuning PARALLEL_SERVERS_TARGET
 Identifying Problematic SQLs
 AWR report
 Additional/Advanced diagnostics
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.