Oracle Performance Diagnostic Guide (Version 3.20)

Query Tuning

9/11/2012

Welcome to the Oracle Performance Diagnostic Guide This guide is intended to help you resolve query tuning, hang/locking, and slow database issues. The

guide is not an automated tool but rather seeks to show methodologies, techniques, common causes, and solutions to performance problems.

Most of the guide is finished but portions of the content under the Hang/Locking tab is still under development.

Query Tuning > Identify the Issue > Overview

Recognize a Query Tuning Issue

Clarify the Issue

Verify the Issue

Special Considerations

Next Step - Data Collection

Query Tuning > Identify the Issue >Data Collection

Gather an Extended SQL Trace

Next Step - Analyze

Query Tuning > Identify the Issue > Analysis

Gather Comprehensive Information about the Query (Mandatory)

Gather Historical Information about the Query

Construct a Test Script

Next Step - Analyze

Query Tuning > Determine a Cause >Analysis

Always Check: Optimizer Mode, Statistics, and Parameters

Choose a Tuning Strategy

Open a Service Request with Oracle Support Services

Query Tuning > Reference

Optimizer Mode, Statistics, and Initialization Parameters

Join Order and Type

Miscellaneous Causes and Solutions

Feedback

We look forward to your feedback. Please email any comments, suggestion to help improve this guide, or any issues that you have encountered with the tool

usage to Vickie.Carbonneau@oracle.com, Technical Advisor, Center of Expertise (CoE).