Oracle Detección Slow Database

Oracle Performance Diagnostic Guide Slow Database
Version 3.1.0 January 13, 2009

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 isfinished but portions of the content under the Hang/Locking tab is still under development. Your feedback is very valuable to us - please email your comments to: Vickie.Carbonneau@oracle.com

Contents
Slow Database > Identify the Issue > Overview Recognize a Slow Database Issue Clarify the Issue Verify the Issue Next Step - Data Collection Slow Database > Identify the Issue > Data Collection GatherDatabase Performance Data Gather Operating System (OS) Performance Data Gather RDA Report Gather Application Logs (Optional) Next Step - Analyze Slow Database > Identify the Issue > Analysis Verify Oracle OS Resource Usage Verify The Database is Slow Next Step - Determine a Cause Would You Like to Stop and Log a Service Request? Slow Database > Determine a Cause >Overview Slow Database >Determine a Cause >Data Collection Gather an Extended SQL Trace Next Step - Analyze Slow Database > Determine a Cause >Analysis Determine the Type of Performance Problem Choose a Tuning Strategy Open a Service Request with Oracle Support Services Give Us Your Feedback Slow Database > Reference

Causes and Solutions Tools

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).

Slow Database > Identify the Issue > Overview
To properly identify the issue we want to resolve, we must do three things:
q q q

Recognize the issue Clarify the details surrounding the issue Verify that the issue is indeedthe problem r This will be done in the Data Collection and Analysis steps that follow

Recognize a Slow Database Issue
What is a slow database issue? A slow database issue can manifest itself as:
q q

Documentation
q

Performance Tuning Overview

q

A large number of sessions that run slower than usual The database permits logons and seems to be working (not hung) but takes muchlonger than usual to show results Many different types of activity all slow down at around the same time

Notes
q

Database Performance FAQ

You might have identified this behavior from:
q q q q q

benchmarking/testing user complaints statspack or AWR reports showing less throughput (e.g., transactions/sec) statspack, AWR, or ASH reports showing much higher wait and/or CPU times than normalOS data that shows more CPU consumption or I/O by Oracle processes than is normal

White Papers q Yet Another Performance Profiling Method q The COE Performance Method Case Studies

These problems might appear after:

q q q q q

schema changes changes in statistics gathering changes in data volumes changes in application database upgrades

Resolving High CPU usage in Oracle Servers q TheMysterious Performance Drop q Intense and Random Buffer Busy Wait Performance q Diagnosing Another Buffer Busy Waits Issue q Using Real-Time Diagnostic Tools to Diagnose Intermittent Database Hangs
q

Clarify the Issue
A clear problem statement is critical. You need to be clear on exactly what the problem is. It may be that in subsequent phases of working through the issue, the real problembecomes clearer and you have to revisit and re-clarify the issue. To clarify the issue, you must know as much as possible of the following:
q q q q q q q q

Notes
q

ODM Reference: Identify the Issue

When the system was slow and when it was OK. Any related changes that coincide with the bad performance The sequence of events leading up to the problem Where/how was it noticed The...