My Oracle Support Banner

FAQ: SQL Query Performance - Frequently Asked Questions (Doc ID 398838.1)

Last updated on FEBRUARY 03, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 6.0.0.0 and later
Oracle Database - Personal Edition - Version 7.1.4.0 and later
Oracle Database - Standard Edition - Version 7.0.16.0 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

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 White Papers 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

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