Transcription
Oracle DatabaseDatabase Performance Tuning Guide19cE96347-04April 2020
Oracle Database Database Performance Tuning Guide, 19cE96347-04Copyright 2007, 2020, Oracle and/or its affiliates.Contributing Authors: Glenn MaxeyPrimary Authors: Rajesh Bhatiya, Immanuel Chan, Lance AshdownContributors: Hermann Baer, Deba Chatterjee, Maria Colgan, Mikael Fries, Prabhaker Gongloor, KevinJernigan, Sue K. Lee, William Lee, David McDermid, Uri Shaft, Oscar Suro, Trung Tran, Sriram Vrinda, YujunWangThis 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.Reverse engineering, disassembly, or decompilation of this software, unless required by law forinteroperability, is prohibited.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 it onbehalf of the U.S. Government, then the following notice is applicable:U.S. GOVERNMENT END USERS: Oracle programs (including any operating system, integrated software,any programs embedded, installed or activated on delivered hardware, and modifications of such programs)and Oracle computer documentation or other Oracle data delivered to or accessed by U.S. Government endusers are "commercial computer software" or “commercial computer software documentation” pursuant to theapplicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use,reproduction, duplication, release, display, disclosure, modification, preparation of derivative works, and/oradaptation of i) Oracle programs (including any operating system, integrated software, any programsembedded, installed or activated on delivered hardware, and modifications of such programs), ii) Oraclecomputer documentation and/or iii) other Oracle data, is subject to the rights and limitations specified in thelicense contained in the applicable contract. The terms governing the U.S. Government’s use of Oracle cloudservices are defined by the applicable contract for such services. No other rights are granted to the U.S.Government.This software or hardware is developed for general use in a variety of information management applications.It is not developed or intended for use in any inherently dangerous applications, including applications thatmay create a risk of personal injury. If you use this software or hardware in dangerous applications, then youshall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure itssafe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of thissoftware 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 Inside are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks areused under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Epyc,and the AMD logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registeredtrademark of The Open Group.This software or hardware and documentation may provide access to or information about 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 unless otherwiseset forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not beresponsible for any loss, costs, or damages incurred due to your access to or use of third-party content,products, or services, except as set forth in an applicable agreement between you and Oracle.
ContentsPrefaceAudiencexviiiDocumentation AccessibilityxviiiRelated DocumentsxixConventionsxixChanges in This Release for Oracle Database Performance TuningGuideChanges in Oracle Database Release 19c, Version 19.1xxChanges in Oracle Database Release 18c, Version 18.1xxiChanges in Oracle Database 12c Release 2 (12.2)xxiChanges in Oracle Database 12c Release 1 (12.1.0.2)xxiiChanges in Oracle Database 12c Release 1 (12.1.0.1)xxiiiPart I1Database Performance FundamentalsPerformance Tuning OverviewIntroduction to Performance Tuning1-1Performance Planning1-1Instance Tuning1-1Performance Principles1-2Baselines1-2The Symptoms and the Problems1-2When to Tune1-3SQL TuningQuery Optimizer and Execution PlansIntroduction to Performance Tuning Features and Tools1-41-41-5Automatic Performance Tuning Features1-5Additional Oracle Database Tools1-6iii
V Performance Views2Designing and Developing for PerformanceOracle Methodology2-1Understanding Investment Options2-1Understanding Scalability2-2What is Scalability?2-2System Scalability2-3Factors Preventing Scalability2-4System ArchitectureHardware and Software Components2-52-5Hardware Components2-5Software Components2-6Configuring the Right System Architecture for Your RequirementsApplication Design Principles2-72-10Simplicity In Application Design2-10Data Modeling2-10Table and Index Design2-11Appending Columns to an Index or Using Index-Organized Tables2-11Using a Different Index Type2-11Finding the Cost of an Index2-12Serializing within Indexes2-13Ordering Columns in an Index2-13Using Views2-13SQL Execution Efficiency2-14Implementing the Application2-15Trends in Application Development2-17Workload Testing, Modeling, and Implementation2-17Sizing Data2-18Estimating Workloads2-18Application Modeling2-19Testing, Debugging, and Validating a Design2-19Deploying New Applications31-62-20Rollout Strategies2-20Performance Checklist2-21Performance Improvement MethodsThe Oracle Performance Improvement MethodSteps in the Oracle Performance Improvement Method3-13-2iv
A Sample Decision Process for Performance Conceptual Modeling3-3Top Ten Mistakes Found in Oracle Systems3-4Emergency Performance MethodsSteps in the Emergency Performance Method44-2Undo Space4-3Redo Log Files4-4Tablespaces4-44-6Table Compression4-6Reclaiming Unused Space4-8Indexing Data4-8Performance Considerations for Shared Servers4-9Identifying and Reducing Contention Using the Dispatcher-Specific Views4-10Identifying Contention for Shared Servers4-11Improved Client Connection Performance Due to Prespawned ProcessesPart II4-1Initialization ParametersCreating and Maintaining Tables for Optimal Performance4-12Diagnosing and Tuning Database PerformanceMeasuring Database PerformanceAbout Database Statistics5-1Time Model Statistics5-1Active Session History Statistics5-2Wait Events Statistics5-3Session and System Statistics5-4Interpreting Database Statistics63-6Configuring a Database for PerformancePerformance Considerations for Initial Instance Configuration53-65-5Using Hit Ratios5-5Using Wait Events with Timed Statistics5-5Using Wait Events without Timed Statistics5-6Using Idle Wait Events5-6Comparing Database Statistics with Other Factors5-6Using Computed Statistics5-7Gathering Database StatisticsAbout Gathering Database Statistics6-1v
Automatic Workload Repository6-2Snapshots6-2Baselines6-3Fixed Baselines6-3Moving Window Baselines6-3Baseline Templates6-4Space Consumption6-4Adaptive Thresholds6-6Percentage of Maximum Thresholds6-6Significance Level Thresholds6-6Managing the Automatic Workload Repository6-7Enabling the Automatic Workload Repository6-8Managing Snapshots6-8User Interfaces for Managing Snapshots6-9Creating Snapshots6-9Dropping Snapshots6-10Modifying Snapshot Settings6-11Managing Baselines6-12User Interface for Managing Baselines6-12Creating a Baseline6-13Dropping a Baseline6-14Renaming a Baseline6-15Displaying Baseline Metrics6-15Resizing the Default Moving Window Baseline6-15Managing Baseline Templates6-16User Interfaces for Managing Baseline Templates6-17Creating a Single Baseline Template6-17Creating a Repeating Baseline Template6-18Dropping a Baseline Template6-18Transporting Automatic Workload Repository Data to Another System6-19Exporting AWR Data6-19Importing AWR Data6-20Using Automatic Workload Repository Views6-21Managing Automatic Workload Repository in a Multitenant Environment6-23Categorization of AWR Data in a Multitenant Environment6-23AWR Data Storage and Retrieval in a Multitenant Environment6-24Viewing AWR Data in a Multitenant Environment6-26Managing Automatic Workload Repository in Active Data Guard StandbyDatabases6-28Configuring the Remote Management Framework (RMF)6-29Managing Snapshots for Active Data Guard Standby Databases6-34vi
Viewing AWR Data in Active Data Guard Standby DatabasesGenerating Automatic Workload Repository Reports6-37Generating an AWR Report Using the Command-Line Interface6-37Generating an AWR Report for the Local Database6-38Generating an AWR Report for a Specific Database6-39Generating an AWR Report for the Local Database in Oracle RAC6-40Generating an AWR Report for a Specific Database in Oracle RAC6-41Generating an AWR Report for a SQL Statement on the Local Database6-42Generating an AWR Report for a SQL Statement on a Specific Database6-43Overview of Performance Hub Active Report6-446-44About Performance Hub Active Report Tabs6-45About Performance Hub Active Report Types6-46Command-Line User Interface for Generating a Performance Hub Active Report6-46Generating a Performance Hub Active Report Using a SQL Script6-46Automatic Performance DiagnosticsOverview of the Automatic Database Diagnostic Monitor7-1ADDM Analysis7-2Using ADDM with Oracle Real Application Clusters7-4Using ADDM in a Multitenant Environment7-4Enabling ADDM in a Pluggable DatabaseReal-Time ADDM Analysis7-67-7Real-Time ADDM Connection Modes7-7Real-Time ADDM Triggers7-7Real-Time ADDM Trigger Controls7-8ADDM Analysis ResultsReviewing ADDM Analysis Results: Example7-97-10Setting Up ADDM7-11Diagnosing Database Performance Problems with ADDM7-11Running ADDM in Database Mode7-12Running ADDM in Instance Mode7-13Running ADDM in Partial Mode7-13Displaying an ADDM Report7-14ADDM Views86-37User Interface for Generating an AWR ReportGenerating Performance Hub Active Report76-367-14Comparing Database Performance Over TimeAbout Automatic Workload Repository Compare Periods Reports8-1vii
Generating Automatic Workload Repository Compare Periods Reports8-2User Interfaces for Generating AWR Compare Periods Reports8-2Generating an AWR Compare Periods Report Using the Command-LineInterface8-3Generating an AWR Compare Periods Report for the Local Database8-3Generating an AWR Compare Periods Report for a Specific Database8-4Generating an Oracle RAC AWR Compare Periods Report for the LocalDatabase8-5Generating an Oracle RAC AWR Compare Periods Report for a SpecificDatabase8-6Interpreting Automatic Workload Repository Compare Periods ReportsSummary of the AWR Compare Periods Report8-9Snapshot Sets8-9Host Configuration Comparison8-9System Configuration Comparison8-9Load Profile8-9Top 5 Timed Events8-9Details of the AWR Compare Periods Report8-9Time Model Statistics8-10Operating System Statistics8-10Wait Events8-10Service Statistics8-11SQL Statistics8-11Instance Activity Statistics8-13I/O Statistics8-13Advisory Statistics8-14Wait Statistics8-14Undo Segment Summary8-15Latch Statistics8-15Segment Statistics8-15In-Memory Segment Statistics8-16Dictionary Cache Statistics8-16Library Cache Statistics8-17Memory Statistics8-17Advanced Queuing Statistics8-18Supplemental Information in the AWR Compare Periods Report98-88-18init.ora Parameters8-18Complete List of SQL Text8-18Analyzing Sampled DataAbout Active Session History9-1viii
Generating Active Session History ReportsUser Interfaces for Generating ASH Reports9-3Generating an ASH Report Using the Command-Line Interface9-3Generating an ASH Report on the Local Database Instance9-3Generating an ASH Report on a Specific Database Instance9-4Generating an ASH Report for Oracle RAC9-5Interpreting Results from Active Session History ReportsTop Events9-79-7Top User Events9-8Top Background Events9-8Top Event P1/P2/P39-8Load Profile9-8Top Service/Module9-8Top Client IDs9-8Top SQL Command Types9-8Top Phases of Execution9-9Top SQL9-9Top SQL with Top Events9-9Top SQL with Top Row Sources9-9Top SQL Using Literals9-9Top Parsing Module/Action9-9Complete List of SQL Text9-9Top PL/SQL9-10Top Java9-10Top Sessions9-10Top Sessions9-10Top Blocking Sessions9-10Top Sessions Running PQs9-10Top Objects/Files/Latches9-10Top DB Objects9-11Top DB Files9-11Top Latches9-11Activity Over Time109-29-11Instance Tuning Using Performance ViewsInstance Tuning Steps10-1Define the Problem10-2Examine the Host System10-2CPU Usage10-3Identifying I/O Problems10-4ix
Identifying Network IssuesExamine the Oracle Database Statistics10-610-7Setting the Level of Statistics Collection10-7Wait Events10-8Dynamic Performance Views Containing Wait Event Statistics10-9System Statistics10-10Segment-Level Statistics10-11Implement and Measure Change10-12Interpreting Oracle Database Statistics10-12Examine Load10-12Using Wait Event Statistics to Drill Down to Bottlenecks10-13Table of Wait Events and Potential Causes10-15Additional Statistics10-16Wait Events Statistics10-18Changes to Wait Event Statistics from Past Releases10-19buffer busy waits10-21db file scattered read10-23db file sequential read10-24direct path read and direct path read temp10-26direct path write and direct path write temp10-27enqueue (enq:) waits10-28events in wait class other10-30free buffer waits10-31Idle Wait Events10-33latch events10-33log file parallel write10-38library cache pin10-38library cache lock10-38log buffer space10-38log file switch10-38log file sync10-39rdbms ipc reply10-40SQL*Net Events10-40Tuning Instance Recovery Performance: Fast-Start Fault RecoveryAbout Instance Recovery10-4210-42Cache Recovery (Rolling Forward)10-42Transaction Recovery (Rolling Back)10-42Checkpoints and Cache Recovery10-42Configuring the Duration of Cache Recovery: FAST START MTTR TARGET10-43Practical Values for FAST START MTTR TARGET10-44Reducing Checkpoint Frequency to Optimize Run-Time Performance10-44x
Part III1112Monitoring Cache Recovery with V INSTANCE RECOVERY10-45Tuning FAST START MTTR TARGET and Using MTTR Advisor10-45Calibrate the FAST START MTTR TARGET10-46Determine the Practical Range for FAST START MTTR TARGET10-46Evaluate Different Target Values with MTTR Advisor10-48Determine the Optimal Size for Redo Logs10-49Tuning Database MemoryDatabase Memory AllocationAbout Database Memory Caches and Other Memory Structures11-1Database Memory Management Methods11-2Automatic Memory Management11-3Automatic Shared Memory Management11-3Manual Shared Memory Management11-3Automatic PGA Memory Management11-3Manual PGA Memory Management11-3Using Automatic Memory Management11-4Monitoring Memory Management11-4Tuning the System Global AreaUsing Automatic Shared Memory ManagementUser Interfaces for Setting the SGA TARGET Parameter12-112-2Setting the SGA TARGET Parameter in Oracle Enterprise Manager CloudControl12-2Setting the SGA TARGET Parameter in the Command-Line Interface12-2Setting the SGA TARGET Parameter12-2Enabling Automatic Shared Memory Management12-3Disabling Automatic Shared Memory Management12-3Sizing the SGA Components Manually12-3SGA Sizing Unit12-4Maximum Size of the SGA12-4Application Considerations12-5Operating System Memory Use12-5Reduce Paging12-5Fit the SGA into Main Memory12-6Allow Adequate Memory to Individual Users12-6Iteration During ConfigurationMonitoring Shared Memory Management12-612-7xi
Improving Query Performance with the In-Memory Column Store12-7Enabling High Performance Data Streaming with the Memoptimized Rowstore12-8About the Memoptimized Rowstore12-8Using Fast Ingest12-9Enabling a Table for Fast Ingest12-13Specifying a Hint for Using Fast Ingest for Data Inserts12-13Disabling a Table for Fast Ingest12-14Managing Fast Ingest Data in the Large Pool12-14Using Fast Lookup1312-15Enabling the Memoptimize Pool12-17Enabling a Table for Fast Lookup12-18Disabling a Table for Fast Lookup12-19Managing Fast Lookup Data in the Memoptimize Pool12-19Tuning the Database Buffer CacheAbout the Database Buffer Cache13-1Configuring the Database Buffer Cache13-1Using the V DB CACHE ADVICE View13-2Calculating the Buffer Cache Hit Ratio13-4Interpreting the Buffer Cache Hit Ratio13-5Increasing Memory Allocated to the Database Buffer Cache13-6Reducing Memory Allocated to the Database Buffer Cache13-7Configuring Multiple Buffer PoolsConsiderations for Using Multiple Buffer Pools13-713-8Random Access to Large Segments13-8Oracle Real Application Cluster Instances13-8Using Multiple Buffer Pools13-8Using the V DB CACHE ADVICE View for Individual Buffer Pools13-9Calculating the Buffer Pool Hit Ratio for Individual Buffer Pools13-9Examining the Buffer Cache Usage Pattern13-10Examining the Buffer Cache Usage Pattern for All Segments13-10Examining the Buffer Cache Usage Pattern for a Specific Segment13-11Configuring the KEEP Pool13-11Configuring the RECYCLE Pool13-12Configuring the Redo Log Buffer13-13Sizing the Redo Log Buffer13-14Using Redo Log Buffer Statistics13-14Configuring the Database Caching Mode13-15Default Database Caching Mode13-15Force Full Database Caching Mode13-16xii
14Determining When to Use Force Full Database Caching Mode13-16Verifying the Database Caching Mode13-17Tuning the Shared Pool and the Large PoolAbout the Shared Pool14-1Benefits of Using the Shared Pool14-1Shared Pool Concepts14-1Library Cache Concepts14-2Data Dictionary Cache Concepts14-3SQL Sharing Criteria14-3Using the Shared Pool14-4Use Shared Cursors14-5Use Single-User Logon and Qualified Table Reference14-6Use PL/SQL14-6Avoid Performing DDL Operations14-6Cache Sequence Numbers14-7Control Cursor Access14-7Controlling Cursor Access Using OCI14-7Controlling Cursor Access Using Oracle Precompilers14-8Controlling Cursor Access Using SQLJ14-8Controlling Cursor Access Using JDBC14-8Controlling Cursor Access Using Oracle Forms14-9Maintain Persistent ConnectionsConfiguring the Shared PoolSizing the Shared Pool14-914-914-9Using Library Cache Statistics14-10Using Shared Pool Advisory Statistics14-12Using Dictionary Cache Statistics14-14Increasing Memory Allocated to the Shared Pool14-15Reducing Memory Allocated to the Shared Pool14-16Deallocating Cursors14-16Caching Session Cursors14-17About the Session Cursor Cache14-17Enabling the Session Cursor Cache14-18Sizing the Session Cursor Cache14-18Sharing Cursors14-19About Cursor Sharing14-20Forcing Cursor Sharing14-20Keeping Large Objects to Prevent Aging14-21Configuring the Reserved Pool14-22xiii
Sizing the Reserved Pool14-23Increasing Memory Allocated to the Reserved Pool14-24Reducing Memory Allocated to the Reserved Pool14-24Configuring the Large Pool1514-24Configuring the Large Pool for Shared Server Architecture14-25Configuring the Large Pool for Parallel Query14-26Sizing the Large Pool14-26Limiting Memory Use for User Sessions14-28Reducing Memory Use Using Three-Tier Connections14-28Tuning the Result CacheAbout the Result CacheServer Result Cache Concepts15-115-1Benefits of Using the Server Result Cache15-1Understanding How the Server Result Cache Works15-2Client Result Cache Concepts15-3Benefits of Using the Client Result Cache15-4Understanding How the Client Result Cache Works15-4Configuring the Result CacheConfiguring the Server Result Cache15-515-5Sizing the Server Result Cache Using Initialization Parameters15-6Managing the Server Result Cache Using DBMS RESULT CACHE15-6Configuring the Client Result Cache15-8Setting the Result Cache Mode15-9Requ
Generating an AWR Report for the Local Database 6-38 Generating an AWR Report for a Specific Database 6-39 Generating an AWR Report for the Local Database in Oracle RAC 6-40 Generating an AWR Report for a Specific Database in Oracle RAC 6-41 Generating an AWR Report for a SQL Statement on t
