Manual

Oracle9i Database Performance Tuning
Volume 1 - Student Guide

D11299GC21 Edition 2.1 June 2003 D38321

Authors Peter Kilpatrick Shankar Raman Jim Womack Technical Contributors and Reviewers Mirza Ahmad David Austin Ruth Baylis Howard Bradley Pietro Colombo Michele Cyran Benoit Dagerville Connie Dialeris Joel Goodman Scott Gossett Lilian Hobbs Alexander Hunold Sushil Kumar Roderick ManalacHoward Ostrow Darren Pelacchi Sander Rekveld Maria Senise Ranbir Singh Janet Stern Wayne Stokes Tracy Stollberg Harald Van Breederode John Watson Publisher Joseph Fernandez

Copyright © Oracle Corporation, 2003. All rights reserved. This documentation contains proprietary information of Oracle Corporation. It is provided under a license agreement containing restrictions on use and disclosure andis also protected by copyright law. Reverse engineering of the software is prohibited. If this documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable: Restricted Rights Legend Use, duplication or disclosure by the Government is subject to restrictions for commercial computer softwareand shall be deemed to be Restricted Rights software under Federal law, as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in Technical Data and Computer Software (October 1988). This material or any portion of it may not be copied in any form or by any means without the express prior written permission of Oracle Corporation. Any other copying is a violation of copyright law andmay result in civil and/or criminal penalties. If this documentation is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with “Restricted Rights,” as defined in FAR 52.227-14, Rights in Data-General, including Alternate III (June 1987). The information in this document is subject to change without notice. If you find any problems in thedocumentation, please report them in writing to Education Products, Oracle Corporation, 500 Oracle Parkway, Box SB-6, Redwood Shores, CA 94065. Oracle Corporation does not warrant that this document is error-free. Oracle and all references to Oracle and Oracle products are trademarks or registered trademarks of Oracle Corporation. All other products or company names are used for identification purposes only,and may be trademarks of their respective owners.

Contents
1 Overview of Oracle9i Database Performance Tuning Objectives 1-2 Tuning Questions 1-3 Tuning Phases 1-5 Tuning Goals 1-6 Common Performance Problems 1-7 Tuning Steps During Development 1-8 Collect a Baseline Set of Statistics 1-9 Tuning Steps for a Production Database 1-10 Database Server Tuning Methodology 1-12 Performance VersusSafety Trade-Offs 1-14 Summary 1-15 Diagnostic and Tuning Tools Objectives 2-2 Maintenance of the Alert Log File 2-3 Tuning Components of the Alert Log File 2-4 Background Processes Trace Files 2-5 User Trace Files 2-6 Views, Utilities, and Tools 2-7 Oracle Enterprise Manager Console 2-9 Performance Manager 2-10 Overview of Oracle Expert Tuning Methodology 2-12 Tuning Using Oracle Expert 2-13Statspack 2-15 Statspack Output 2-17 Dictionary and Special Views 2-20 Displaying Systemwide Statistics 2-21 Displaying Session-Related Statistics 2-24 Oracle Wait Events 2-26 The v$event_name View 2-27 Statistics Event Views 2-28 The v$session_event View 2-29 The v$session_wait View 2-30 The v$system_event View 2-32 Dynamic Troubleshooting and Performance Views 2-33 Troubleshooting and Tuning Views 2-34utlbstat and utlestat Scripts 2-36 DBA-Developed Tools 2-37 Level of Statistics Collection 2-38 Summary 2-40
iii

2

3 Database Configuration and I/O Issues Objectives 3-2 Oracle Processes and Files 3-3 Performance Guidelines 3-4 Distributing Files Across Devices 3-5 IO Topology Support 3-6 Tablespace Usage 3-7 Locally Managed system Tablespace 3-8 Diagnostic Tools for Checking I/O...