WIXLIB

Oracle DatabasePerformance Tuning Guide11g Release 2 (11.2)E41573-04June 2014

Oracle Database Performance Tuning Guide, 11g Release 2 (11.2)E41573-04Copyright 2000, 2014, Oracle and/or its affiliates. All rights reserved.Primary Authors: Immanuel Chan, Lance AshdownContributors: Aditya Agrawal, Hermann Baer, Vladimir Barriere, Mehul Bastawala, Eric Belden, PeteBelknap, Supiti Buranawatanachoke, Sunil Chakkappen, Maria Colgan, Benoit Dageville, Dinesh Das, KarlDias, Kurt Engeleiter, Marcus Fallen, Mike Feng, Leonidas Galanis, Ray Glasstone, Prabhaker Gongloor,Kiran Goyal, Cecilia Grant, Connie Dialeris Green, Shivani Gupta, Karl Haas, Bill Hodak, AndrewHoldsworth, Hakan Jacobsson, Shantanu Joshi, Ameet Kini, Sergey Koltakov, Vivekanada Kolla, Paul Lane,Sue K. Lee, Herve Lejeune, Ilya Listvinsky, Bryn Llewellyn, George Lumpkin, Mughees Minhas, Gary Ngai,Mark Ramacher, Yair Sarig, Uri Shaft, Vishwanath Sreeraman, Vinay Srihari, Randy Urbano, Amir Valiani,Venkateshwaran Venkataramani, Yujun Wang, Graham Wood, Khaled Yagoub, Mohamed Zait, MohamedZiauddinThis software and related documentation are provided under a license agreement containing restrictions onuse and disclosure and are protected by intellectual property laws. Except as expressly permitted in yourlicense agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license,transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverseengineering, disassembly, or decompilation of this software, unless required by law for interoperability, isprohibited.The information contained herein is subject to change without notice and is not warranted to be error-free. Ifyou find any errors, please report them to us in writing.If this is software or related documentation that is delivered to the U.S. Government or anyone licensing iton behalf of the U.S. Government, the following notice is applicable:U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software,any programs installed on the hardware, and/or documentation, delivered to U.S. Government end usersare "commercial computer software" pursuant to the applicable Federal Acquisition Regulation andagency-specific supplemental regulations. As such, use, duplication, disclosure, modification, andadaptation of the programs, including any operating system, integrated software, any programs installed onthe hardware, and/or documentation, shall be subject to license terms and license restrictions applicable tothe programs. No other rights are granted to the U.S. Government.This software or hardware is developed for general use in a variety of information managementapplications. It is not developed or intended for use in any inherently dangerous applications, includingapplications that may create a risk of personal injury. If you use this software or hardware in dangerousapplications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and othermeasures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damagescaused by use of this software or hardware in dangerous applications.Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks oftheir respective owners.Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarksare used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD,Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of AdvancedMicro Devices. UNIX is a registered trademark of The Open Group.This software or hardware and documentation may provide access to or information on content, products,and services from third parties. Oracle Corporation and its affiliates are not responsible for and expresslydisclaim all warranties of any kind with respect to third-party content, products, and services. OracleCorporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to youraccess to or use of third-party content, products, or services.

ContentsPreface . xvAudience.Documentation Accessibility .Related Documents .Conventions .xvxvxvxviWhat's New in Oracle Database Performance Tuning Guide? . xviiOracle Database 11g Release 2 (11.2.0.4) New Features in Oracle Database Performance. xviiOracle Database 11g Release 2 (11.2.0.2) New Features in Oracle Database Performance. xviiOracle Database 11g Release 2 (11.2.0.1) New Features in Oracle Database Performance. xviiiPart I1Performance TuningPerformance Tuning OverviewIntroduction to Performance Tuning.Performance Planning .Instance Tuning .SQL Tuning .Introduction to Performance Tuning Features and Tools .Automatic Performance Tuning Features .Additional Oracle Database Tools .Part II21-11-11-11-41-41-51-6Performance PlanningDesigning and Developing for PerformanceOracle Methodology .Understanding Investment Options.Understanding Scalability.What is Scalability? .System Scalability.Factors Preventing Scalability .System Architecture.Hardware and Software Components .Configuring the Right System Architecture for Your Requirements .2-12-12-22-22-32-42-52-52-7iii

Application Design Principles. 2-9Simplicity In Application Design. 2-10Data Modeling . 2-10Table and Index Design. 2-10Using Views . 2-12SQL Execution Efficiency . 2-13Implementing the Application . 2-14Trends in Application Development. 2-16Workload Testing, Modeling, and Implementation . 2-16Sizing Data . 2-17Estimating Workloads . 2-17Application Modeling . 2-18Testing, Debugging, and Validating a Design . 2-18Deploying New Applications . 2-19Rollout Strategies . 2-19Performance Checklist. 2-203Performance Improvement MethodsThe Oracle Performance Improvement Method .Steps in The Oracle Performance Improvement Method.A Sample Decision Process for Performance Conceptual Modeling.Top Ten Mistakes Found in Oracle Systems .Emergency Performance Methods .Steps in the Emergency Performance Method.Part III43-13-23-33-43-63-6Optimizing Instance PerformanceConfiguring a Database for PerformancePerformance Considerations for Initial Instance Configuration .Initialization Parameters .Configuring Undo Space.Sizing Redo Log Files .Creating Subsequent Tablespaces.Creating and Maintaining Tables for Optimal Performance .Table Compression .Reclaiming Unused Space.Indexing Data .Performance Considerations for Shared Servers .Identifying Contention Using the Dispatcher-Specific Views .Identifying Contention for Shared Servers.4-14-14-34-34-44-54-54-64-74-74-84-95 Automatic Performance StatisticsOverview of Data Gathering.Database Statistics .Operating System Statistics .Interpreting Statistics.iv5-15-25-45-7

Overview of the Automatic Workload Repository . 5-8Snapshots. 5-9Baselines . 5-9Adaptive Thresholds . 5-10Space Consumption . 5-12Managing the Automatic Workload Repository . 5-12Managing Snapshots. 5-13Managing Baselines . 5-14Managing Baseline Templates. 5-17Transporting Automatic Workload Repository Data . 5-19Using Automatic Workload Repository Views . 5-21Generating Automatic Workload Repository Reports . 5-22Generating Automatic Workload Repository Compare Periods Reports . 5-28Generating Active Session History Reports . 5-34Using Active Session History Reports . 5-386 Automatic Performance DiagnosticsOverview of the Automatic Database Diagnostic Monitor .ADDM Analysis .Using ADDM with Oracle Real Application Clusters .ADDM Analysis Results .Reviewing ADDM Analysis Results: Example.Setting Up ADDM .Diagnosing Database Performance Problems with ADDM .Running ADDM in Database Mode .Running ADDM in Instance Mode.Running ADDM in Partial Mode.Displaying an ADDM Report.Views with ADDM Information.6-16-26-36-46-56-56-66-76-76-86-86-97 Configuring and Using MemoryUnderstanding Memory Allocation Issues . 7-1Oracle Memory Caches . 7-2Automatic Memory Management . 7-2Automatic Shared Memory Management . 7-2Dynamically Changing Cache Sizes. 7-3Application Considerations. 7-5Operating System Memory Use. 7-5Iteration During Configuration. 7-6Configuring and Using the Buffer Cache. 7-6Using the Buffer Cache Effectively . 7-7Sizing the Buffer Cache . 7-7Interpreting and Using the Buffer Cache Advisory Statistics . 7-10Considering Multiple Buffer Pools. 7-11Buffer Pool Data in V DB CACHE ADVICE . 7-13Buffer Pool Hit Ratios . 7-13v

Determining Which Segments Have Many Buffers in the Pool .KEEP Pool.RECYCLE Pool .Configuring and Using the Shared Pool and Large Pool .Shared Pool Concepts .Using the Shared Pool Effectively .Sizing the Shared Pool.Interpreting Shared Pool Statistics .Using the Large Pool .Using CURSOR SPACE FOR TIME.Caching Session Cursors .Configuring the Reserved Pool .Keeping Large Objects to Prevent Aging .Sharing Cursors for Existing Applications.Maintaining Connections.Configuring and Using the Redo Log Buffer .Sizing the Log Buffer .Log Buffer Statistics .PGA Memory Management .Configuring Automatic PGA Memory .Configuring OLAP PAGE POOL SIZE .Managing the Server and Client Result Caches.Managing the Server Result Cache.Managing the Client Result Cache .Specifying Queries for Result Caching .Requirements for the Result Cache .Accessing Result Cache 37-547-577-597-627-63I/O Configuration and DesignAbout I/O . 8-1I/O Configuration. 8-2Lay Out the Files Using Operating System or Hardware Striping. 8-2Manually Distributing I/O . 8-5When to Separate Files . 8-5Three Sample Configurations. 8-7Oracle Managed Files . 8-8Choosing Data Block Size . 8-9I/O Calibration Inside the Database. 8-10Prerequisites for I/O Calibration. 8-10Running I/O Calibration . 8-11I/O Calibration with the Oracle Orion Calibration Tool . 8-12Introduction to the Oracle Orion Calibration Tool . 8-12Getting Started with Orion . 8-14Orion Input Files . 8-15Orion Parameters . 8-15Orion Output Files . 8-20Orion Troubleshooting . 8-23vi

9Managing Operating System ResourcesUnderstanding Operating System Performance Issues. 9-1Using Operating System Caches. 9-2Memory Usage. 9-3Using Operating System Resource Managers. 9-4Resolving Operating System Issues . 9-5Performance Hints on UNIX-Based Systems . 9-5Performance Hints on Windows Systems . 9-5Performance Hints on HP OpenVMS Systems . 9-6Understanding CPU. 9-6Resolving CPU Issues. 9-7Finding and Tuning CPU Utilization. 9-8Managing CPU Resources Using Oracle Database Resource Manager . 9-10Managing CPU Resources Using Instance Caging . 9-1110Instance Tuning Using Performance ViewsInstance Tuning Steps .Define the Problem .Examine the Host System .Examine the Oracle Database Statistics .Implement and Measure Change.Interpreting Oracle Database Statistics .Examine Load .Using Wait Event Statistics to Drill Down to Bottlenecks.Table of Wait Events and Potential Causes.Additional Statistics.Wait Events Statistics.buffer busy waits.db file scattered read.db file sequential read .direct path read and direct path read temp .direct path write and direct path write temp.enqueue (enq:) waits .events in wait class other .free buffer waits.Idle Wait Events .latch events.log file parallel write.library cache pin .library cache lock.log buffer space.log file switch .

Oracle Database Performance Tuning Guide, 11g Release 2 (11.2) E41573-04 Copyright 2000, 2014, Oracle and/or