Table of Contents
- Title and Copyright Information
- Preface
- Changes in This Release for Oracle Database Performance Tuning Guide
-
Part I Database Performance Fundamentals
- 1 Performance Tuning Overview
-
2
Designing and Developing for Performance
- 2.1 Oracle Methodology
- 2.2 Understanding Investment Options
- 2.3 Understanding Scalability
- 2.4 System Architecture
- 2.5 Application Design Principles
- 2.6 Workload Testing, Modeling, and Implementation
- 2.7 Deploying New Applications
- 3 Performance Improvement Methods
- 4 Configuring a Database for Performance
-
Part II Diagnosing and Tuning Database Performance
- 5 Measuring Database Performance
-
6
Gathering Database Statistics
- 6.1 About Gathering Database Statistics
-
6.2
Managing the Automatic Workload Repository
- 6.2.1 Enabling the Automatic Workload Repository
- 6.2.2 Managing Snapshots
- 6.2.3 Managing Baselines
- 6.2.4 Managing Baseline Templates
- 6.2.5 Transporting Automatic Workload Repository Data to Another System
- 6.2.6 Using Automatic Workload Repository Views
- 6.2.7 Managing Automatic Workload Repository in a Multitenant Environment
- 6.2.8 Managing Automatic Workload Repository in Active Data Guard Standby Databases
-
6.3
Generating Automatic Workload Repository Reports
- 6.3.1 User Interface for Generating an AWR Report
-
6.3.2
Generating an AWR Report Using the Command-Line Interface
- 6.3.2.1 Generating an AWR Report for the Local Database
- 6.3.2.2 Generating an AWR Report for a Specific Database
- 6.3.2.3 Generating an AWR Report for the Local Database in Oracle RAC
- 6.3.2.4 Generating an AWR Report for a Specific Database in Oracle RAC
- 6.3.2.5 Generating an AWR Report for a SQL Statement on the Local Database
- 6.3.2.6 Generating an AWR Report for a SQL Statement on a Specific Database
- 6.4 Generating Performance Hub Active Report
-
7
Automatic Performance Diagnostics
- 7.1 Overview of the Automatic Database Diagnostic Monitor
- 7.2 Setting Up ADDM
- 7.3 Diagnosing Database Performance Problems with ADDM
- 7.4 ADDM Views
-
8
Comparing Database Performance Over Time
- 8.1 About Automatic Workload Repository Compare Periods Reports
-
8.2
Generating Automatic Workload Repository Compare Periods Reports
- 8.2.1 User Interfaces for Generating AWR Compare Periods Reports
-
8.2.2
Generating an AWR Compare Periods Report Using the Command-Line Interface
- 8.2.2.1 Generating an AWR Compare Periods Report for the Local Database
- 8.2.2.2 Generating an AWR Compare Periods Report for a Specific Database
- 8.2.2.3 Generating an Oracle RAC AWR Compare Periods Report for the Local Database
- 8.2.2.4 Generating an Oracle RAC AWR Compare Periods Report for a Specific Database
-
8.3
Interpreting Automatic Workload Repository Compare Periods Reports
- 8.3.1 Summary of the AWR Compare Periods Report
-
8.3.2
Details of the AWR Compare Periods Report
- 8.3.2.1 Time Model Statistics
- 8.3.2.2 Operating System Statistics
- 8.3.2.3 Wait Events
- 8.3.2.4 Service Statistics
-
8.3.2.5
SQL Statistics
- 8.3.2.5.1 Top 10 SQL Comparison by Execution Time
- 8.3.2.5.2 Top 10 SQL Comparison by CPU Time
- 8.3.2.5.3 Top 10 SQL Comparison by Buffer Gets
- 8.3.2.5.4 Top 10 SQL Comparison by Physical Reads
- 8.3.2.5.5 Top 10 SQL Comparison by Executions
- 8.3.2.5.6 Top 10 SQL Comparison by Parse Calls
- 8.3.2.5.7 Complete List of SQL Text
- 8.3.2.6 Instance Activity Statistics
- 8.3.2.7 I/O Statistics
- 8.3.2.8 Advisory Statistics
- 8.3.2.9 Wait Statistics
- 8.3.2.10 Undo Segment Summary
- 8.3.2.11 Latch Statistics
- 8.3.2.12 Segment Statistics
- 8.3.2.13 In-Memory Segment Statistics
- 8.3.2.14 Dictionary Cache Statistics
- 8.3.2.15 Library Cache Statistics
- 8.3.2.16 Memory Statistics
- 8.3.2.17 Advanced Queuing Statistics
- 8.3.3 Supplemental Information in the AWR Compare Periods Report
- 9 Analyzing Sampled Data
-
10
Instance Tuning Using Performance Views
- 10.1 Instance Tuning Steps
- 10.2 Interpreting Oracle Database Statistics
-
10.3
Wait Events Statistics
- 10.3.1 Changes to Wait Event Statistics from Past Releases
- 10.3.2 buffer busy waits
- 10.3.3 db file scattered read
- 10.3.4 db file sequential read
- 10.3.5 direct path read and direct path read temp
- 10.3.6 direct path write and direct path write temp
- 10.3.7 enqueue (enq:) waits
- 10.3.8 events in wait class other
- 10.3.9 free buffer waits
- 10.3.10 Idle Wait Events
- 10.3.11 latch events
- 10.3.12 log file parallel write
- 10.3.13 library cache pin
- 10.3.14 library cache lock
- 10.3.15 log buffer space
- 10.3.16 log file switch
- 10.3.17 log file sync
- 10.3.18 rdbms ipc reply
- 10.3.19 SQL*Net Events
-
10.4
Tuning Instance Recovery Performance: Fast-Start Fault Recovery
- 10.4.1 About Instance Recovery
- 10.4.2 Configuring the Duration of Cache Recovery: FAST_START_MTTR_TARGET
- 10.4.3 Tuning FAST_START_MTTR_TARGET and Using MTTR Advisor
-
Part III Tuning Database Memory
- 11 Database Memory Allocation
-
12
Tuning the System Global Area
- 12.1 Using Automatic Shared Memory Management
- 12.2 Sizing the SGA Components Manually
- 12.3 Monitoring Shared Memory Management
- 12.4 Improving Query Performance with the In-Memory Column Store
- 12.5 Enabling High Performance Data Streaming with the Memoptimized Rowstore
-
13
Tuning the Database Buffer Cache
- 13.1 About the Database Buffer Cache
- 13.2 Configuring the Database Buffer Cache
-
13.3
Configuring Multiple Buffer Pools
- 13.3.1 Considerations for Using Multiple Buffer Pools
- 13.3.2 Using Multiple Buffer Pools
- 13.3.3 Using the V$DB_CACHE_ADVICE View for Individual Buffer Pools
- 13.3.4 Calculating the Buffer Pool Hit Ratio for Individual Buffer Pools
- 13.3.5 Examining the Buffer Cache Usage Pattern
- 13.3.6 Configuring the KEEP Pool
- 13.3.7 Configuring the RECYCLE Pool
- 13.4 Configuring the Redo Log Buffer
- 13.5 Configuring the Database Caching Mode
-
14
Tuning the Shared Pool and the Large Pool
- 14.1 About the Shared Pool
- 14.2 Using the Shared Pool
-
14.3
Configuring the Shared Pool
- 14.3.1 Sizing the Shared Pool
- 14.3.2 Deallocating Cursors
- 14.3.3 Caching Session Cursors
- 14.3.4 Sharing Cursors
- 14.3.5 Keeping Large Objects to Prevent Aging
- 14.3.6 Configuring the Reserved Pool
- 14.4 Configuring the Large Pool
-
15
Tuning the Result Cache
- 15.1 About the Result Cache
- 15.2 Configuring the Result Cache
- 15.3 Specifying Queries for Result Caching
- 15.4 Monitoring the Result Cache
-
16
Tuning the Program Global Area
- 16.1 About the Program Global Area
- 16.2 Sizing the Program Global Area Using Automatic Memory Management
- 16.3 Sizing the Program Global Area by Specifying an Absolute Limit
-
Part IV Managing System Resources
-
17
I/O Configuration and Design
- 17.1 About I/O
- 17.2 I/O Configuration
- 17.3 I/O Calibration Inside the Database
- 17.4 I/O Calibration with the Oracle Orion Calibration Tool
-
18
Managing Operating System Resources
- 18.1 Understanding Operating System Performance Issues
- 18.2 Resolving Operating System Issues
- 18.3 Understanding CPU
-
18.4
Resolving CPU Issues
- 18.4.1 Finding and Tuning CPU Utilization
- 18.4.2 Managing CPU Resources Using Oracle Database Resource Manager
- 18.4.3 Managing CPU Resources Using Instance Caging
-
17
I/O Configuration and Design
- Index