Oracle Database Performance Tuning Guide E41573Performance
5_PDF_11g_PerformanceTuningGuide_E41573-04
PERFTUNE_01_PDF_112_PerformanceTuningGuide_E41573-04
User Manual:
Open the PDF directly: View PDF
Page Count: 560 [warning: Documents this large are best viewed by clicking the View PDF Link!]
- Contents
- Preface
- What's New in Oracle Database Performance Tuning Guide?
- Part I Performance Tuning
- 1 Performance Tuning Overview
- Part II Performance Planning
- 2 Designing and Developing for Performance
- Oracle Methodology
- Understanding Investment Options
- Understanding Scalability
- System Architecture
- Application Design Principles
- Workload Testing, Modeling, and Implementation
- Deploying New Applications
- 3 Performance Improvement Methods
- Part III Optimizing Instance Performance
- 4 Configuring a Database for Performance
- 5 Automatic Performance Statistics
- Overview of Data Gathering
- Overview of the Automatic Workload Repository
- Managing the Automatic Workload Repository
- Managing Snapshots
- Managing Baselines
- Managing Baseline Templates
- Transporting Automatic Workload Repository Data
- Using Automatic Workload Repository Views
- Generating Automatic Workload Repository Reports
- Generating an AWR Report
- Generating an Oracle RAC AWR Report
- Generating an AWR Report on a Specific Database Instance
- Generating an Oracle RAC AWR Report on Specific Database Instances
- Generating an AWR Report for a SQL Statement
- Generating an AWR Report for a SQL Statement on a Specific Database Instance
- Generating Automatic Workload Repository Compare Periods Reports
- Generating Active Session History Reports
- Using Active Session History Reports
- 6 Automatic Performance Diagnostics
- 7 Configuring and Using Memory
- Understanding Memory Allocation Issues
- Configuring and Using the Buffer Cache
- 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
- PGA Memory Management
- Managing the Server and Client Result Caches
- 8 I/O Configuration and Design
- 9 Managing Operating System Resources
- 10 Instance Tuning Using Performance Views
- Instance Tuning Steps
- Interpreting Oracle Database 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
- log file sync
- rdbms ipc reply
- SQL*Net Events
- Real-Time SQL Monitoring
- Tuning Instance Recovery Performance: Fast-Start Fault Recovery
- About Instance Recovery
- Configuring the Duration of Cache Recovery: FAST_START_MTTR_TARGET
- Tuning FAST_START_MTTR_TARGET and Using MTTR Advisor
- Part IV Optimizing SQL Statements
- 11 The Query Optimizer
- Overview of the Query Optimizer
- Overview of Optimizer Access Paths
- Overview of Joins
- Reading and Understanding Execution Plans
- Controlling Optimizer Behavior
- 12 Using EXPLAIN PLAN
- Understanding EXPLAIN PLAN
- The PLAN_TABLE Output Table
- Running EXPLAIN PLAN
- Displaying PLAN_TABLE Output
- Reading EXPLAIN PLAN Output
- Viewing Parallel Execution with EXPLAIN PLAN
- Viewing Bitmap Indexes with EXPLAIN PLAN
- Viewing Result Cache with EXPLAIN PLAN
- Viewing Partitioned Objects with EXPLAIN PLAN
- PLAN_TABLE Columns
- 13 Managing Optimizer Statistics
- Overview of Optimizer Statistics
- Managing Automatic Optimizer Statistics Collection
- Gathering Statistics Manually
- System Statistics
- Managing Statistics
- Controlling Dynamic Statistics
- Viewing Statistics
- 14 Using Indexes and Clusters
- Understanding Index Performance
- Tuning the Logical Structure
- Index Tuning using the SQLAccess Advisor
- Choosing Columns and Expressions to Index
- Choosing Composite Indexes
- Writing Statements That Use Indexes
- Writing Statements That Avoid Using Indexes
- Re-creating Indexes
- Compacting Indexes
- Using Nonunique Indexes to Enforce Uniqueness
- Using Enabled Novalidated Constraints
- Using Function-based Indexes for Performance
- Using Partitioned Indexes for Performance
- Using Index-Organized Tables for Performance
- Using Bitmap Indexes for Performance
- Using Bitmap Join Indexes for Performance
- Using Domain Indexes for Performance
- Using Table Clusters for Performance
- Using Hash Clusters for Performance
- Understanding Index Performance
- 15 Using SQL Plan Management
- Overview of SQL Plan Baselines
- Managing SQL Plan Baselines
- Using SQL Plan Baselines with SQL Tuning Advisor
- Using Fixed SQL Plan Baselines
- Displaying SQL Plan Baselines
- SQL Management Base
- Importing and Exporting SQL Plan Baselines
- Migrating Stored Outlines to SQL Plan Baselines
- 16 SQL Tuning Overview
- Introduction to SQL Tuning
- Goals for Tuning
- Identifying High-Load SQL
- Automatic SQL Tuning Features
- Developing Efficient SQL Statements
- Verifying Optimizer Statistics
- Reviewing the Execution Plan
- Restructuring the SQL Statements
- Controlling the Access Path and Join Order with Hints
- Restructuring the Indexes
- Modifying or Disabling Triggers and Constraints
- Restructuring the Data
- Maintaining Execution Plans Over Time
- Visiting Data as Few Times as Possible
- Building SQL Test Cases
- 17 Automatic SQL Tuning
- 18 SQL Access Advisor
- Overview of SQL Access Advisor
- Using SQL Access Advisor
- Steps for Using SQL Access Advisor
- Privileges Needed to Use SQL Access Advisor
- Setting Up Tasks and Templates
- SQL Access Advisor Workloads
- Working with Recommendations
- Recommendations and Actions
- Recommendation Options
- Evaluation Mode
- View Intermediate Results During Recommendation Analysis
- Generating Recommendations
- Viewing Recommendations
- Stopping the Recommendation Process
- Marking Recommendations
- Modifying Recommendations
- Generating SQL Scripts
- Special Considerations when Script Includes Partitioning Recommendations
- When Recommendations are no Longer Required
- Performing a Quick Tune
- Managing Tasks
- Using SQL Access Advisor Constants
- Examples of Using SQL Access Advisor
- Tuning Materialized Views for Fast Refresh and Query Rewrite
- 19 Using Optimizer Hints
- Overview of Optimizer Hints
- Specifying Hints
- Using Hints with Views
- 20 Using Plan Stability
- 21 Using Application Tracing Tools
- End-to-End Application Tracing
- Using the trcsess Utility
- Understanding SQL Trace and TKPROF
- Using the SQL Trace Facility and TKPROF
- Step 1: Setting Initialization Parameters for Trace File Management
- Step 2: Enabling the SQL Trace Facility
- Step 3: Formatting Trace Files with TKPROF
- Step 4: Interpreting TKPROF Output
- Tabular Statistics in TKPROF
- Row Source Operations
- Wait Event Information
- Interpreting the Resolution of Statistics
- Understanding Recursive Calls
- Library Cache Misses in TKPROF
- Statement Truncation in SQL Trace
- Identification of User Issuing the SQL Statement in TKPROF
- Execution Plan in TKPROF
- Deciding Which Statements to Tune
- Step 5: Storing SQL Trace Facility Statistics
- Avoiding Pitfalls in TKPROF Interpretation
- Sample TKPROF Output
- Glossary
- Index