FAQ: SQL Query Performance - Frequently Asked Questions
(Doc ID 398838.1)
Last updated on JULY 13, 2024
Applies to:
Oracle Database - Personal Edition - Version 7.1.4.0 and laterOracle Database - Standard Edition - Version 7.0.16.0 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Information in this document applies to any platform.
Purpose
This document records a number of Frequently Asked Questions pertaining to the tuning of SQL statements.
Best Practices
Pro-Active Problem Avoidance and Diagnostic Collection
Although some problems may be unforeseen, in many cases problems may be avoidable if signs are detected early enough. Additionally, if an issue does occur, it is no use collecting information about that issue after the event. For information on proactive preparations and diagnostics, see:
<Document 1482811.1> Best Practices: Proactively Avoiding Database and Query Performance Issues
<Document 1477599.1> Best Practices Around Data Collection For Performance Issues
<Document 1477599.1> Best Practices Around Data Collection For Performance Issues
Questions and Answers
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
Purpose |
Best Practices |
Pro-Active Problem Avoidance and Diagnostic Collection |
Questions and Answers |
Where is there an Overview of the Main Query Tuning Articles? |
Where is there Information About Performance Related Features |
Where can I find Performance Information Centres? |
Where can I find Performance Troubleshooting Information? |
Common Issues/Problem Avoidance |
What information is there on Optimizer Statistics? |
Community: Database Tuning |
Where can Performance Documentation be found? |
Where can Technical Briefs and Blog Entries Related to the Oracle Optimizer be found? |
Why is an index not used? |
Why is a particular query slow? |
Why is a particular query slower than it used to be? |
Why is a particular query slower on one machine than another? |
Why is a particular query slower since upgrading? |
Why does a particular query's runtime vary? |
Why does the execution plan for a particular query change? |
Why does a particular query's runtime vary with bind variables rather than literals? |
What should I do if a Query Returns Unexpected Results? |
Which optimizer is a query using? |
Where is the information regarding how to use hints? |
Can Optimizer Parameters be hinted? |
Why is partition elimination not occurring? |
What is the importance of Global Statistics? |
How to Store and Apply Query Outlines |
Diagnostics |
Action Plans for Common Scenarios |
How to Gather Information for Query Tuning Problems |
How to Gather Access Path Information for Queries |
How to Gather Trace for Query Tuning Issues |
How to Transfer Optimizer Statistics to Support |
References |