Contents

List of Examples

List of Figures

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

What's New in Oracle Database Performance Tuning Guide?

• Oracle Database 11g Release 2 (11.2.0.2) New Features in Oracle Database Performance
• Oracle Database 11g Release 2 (11.2.0.1) New Features in Oracle Database Performance

Part I Performance Tuning

1 Performance Tuning Overview

• 1.1 Introduction to Performance Tuning
  • 1.1.1 Performance Planning
  • 1.1.2 Instance Tuning
    • 1.1.2.1 Performance Principles
    • 1.1.2.2 Baselines
    • 1.1.2.3 The Symptoms and the Problems
    • 1.1.2.4 When to Tune
  • 1.1.3 SQL Tuning
    • 1.1.3.1 Query Optimizer and Execution Plans
• 1.2 Introduction to Performance Tuning Features and Tools
  • 1.2.1 Automatic Performance Tuning Features
  • 1.2.2 Additional Oracle Database Tools
    • 1.2.2.1 V$ Performance Views

Part II Performance Planning

2 Designing and Developing for Performance

• 2.1 Oracle Methodology
• 2.2 Understanding Investment Options
• 2.3 Understanding Scalability
  • 2.3.1 What is Scalability?
  • 2.3.2 System Scalability
  • 2.3.3 Factors Preventing Scalability
• 2.4 System Architecture
  • 2.4.1 Hardware and Software Components
    • 2.4.1.1 Hardware Components
    • 2.4.1.2 Software Components
  • 2.4.2 Configuring the Right System Architecture for Your Requirements
• 2.5 Application Design Principles
  • 2.5.1 Simplicity In Application Design
  • 2.5.2 Data Modeling
  • 2.5.3 Table and Index Design
    • 2.5.3.1 Appending Columns to an Index or Using Index-Organized Tables
    • 2.5.3.2 Using a Different Index Type
    • 2.5.3.3 Finding the Cost of an Index
    • 2.5.3.4 Serializing within Indexes
    • 2.5.3.5 Ordering Columns in an Index
  • 2.5.4 Using Views
  • 2.5.5 SQL Execution Efficiency
  • 2.5.6 Implementing the Application
  • 2.5.7 Trends in Application Development
• 2.6 Workload Testing, Modeling, and Implementation
  • 2.6.1 Sizing Data
  • 2.6.2 Estimating Workloads
    • 2.6.2.1 Extrapolating From a Similar System
    • 2.6.2.2 Benchmarking
  • 2.6.3 Application Modeling
  • 2.6.4 Testing, Debugging, and Validating a Design
• 2.7 Deploying New Applications
  • 2.7.1 Rollout Strategies
  • 2.7.2 Performance Checklist

3 Performance Improvement Methods

• 3.1 The Oracle Performance Improvement Method
  • 3.1.1 Steps in The Oracle Performance Improvement Method
  • 3.1.2 A Sample Decision Process for Performance Conceptual Modeling
  • 3.1.3 Top Ten Mistakes Found in Oracle Systems
• 3.2 Emergency Performance Methods
  • 3.2.1 Steps in the Emergency Performance Method

Part III Optimizing Instance Performance

4 Configuring a Database for Performance

• 4.1 Performance Considerations for Initial Instance Configuration
  • 4.1.1 Initialization Parameters
  • 4.1.2 Configuring Undo Space
  • 4.1.3 Sizing Redo Log Files
  • 4.1.4 Creating Subsequent Tablespaces
    • 4.1.4.1 Creating Permanent Tablespaces - Automatic Segment-Space Management
    • 4.1.4.2 Creating Temporary Tablespaces
• 4.2 Creating and Maintaining Tables for Optimal Performance
  • 4.2.1 Table Compression
    • 4.2.1.1 Estimating the Compression factor
    • 4.2.1.2 Tuning to Achieve a Better Compression Ratio
  • 4.2.2 Reclaiming Unused Space
  • 4.2.3 Indexing Data
    • 4.2.3.1 Specifying Memory for Sorting Data
• 4.3 Performance Considerations for Shared Servers
  • 4.3.1 Identifying Contention Using the Dispatcher-Specific Views
    • 4.3.1.1 Reducing Contention for Dispatcher Processes
  • 4.3.2 Identifying Contention for Shared Servers

5 Automatic Performance Statistics

• 5.1 Overview of Data Gathering
  • 5.1.1 Database Statistics
    • 5.1.1.1 Wait Events
    • 5.1.1.2 Time Model Statistics
    • 5.1.1.3 Active Session History
    • 5.1.1.4 System and Session Statistics
  • 5.1.2 Operating System Statistics
    • 5.1.2.1 CPU Statistics
    • 5.1.2.2 Virtual Memory Statistics
    • 5.1.2.3 Disk I/O Statistics
    • 5.1.2.4 Network Statistics
    • 5.1.2.5 Operating System Data Gathering Tools
  • 5.1.3 Interpreting Statistics
• 5.2 Overview of the Automatic Workload Repository
  • 5.2.1 Snapshots
  • 5.2.2 Baselines
    • 5.2.2.1 Fixed Baselines
    • 5.2.2.2 Moving Window Baseline
    • 5.2.2.3 Baseline Templates
  • 5.2.3 Adaptive Thresholds
  • 5.2.4 Space Consumption
• 5.3 Managing the Automatic Workload Repository
  • 5.3.1 Managing Snapshots
    • 5.3.1.1 Creating Snapshots
    • 5.3.1.2 Dropping Snapshots
    • 5.3.1.3 Modifying Snapshot Settings
  • 5.3.2 Managing Baselines
    • 5.3.2.1 Creating a Baseline
    • 5.3.2.2 Dropping a Baseline
    • 5.3.2.3 Renaming a Baseline
    • 5.3.2.4 Displaying Baseline Metrics
    • 5.3.2.5 Modifying the Window Size of the Default Moving Window Baseline
  • 5.3.3 Managing Baseline Templates
    • 5.3.3.1 Creating a Single Baseline Template
    • 5.3.3.2 Creating a Repeating Baseline Template
    • 5.3.3.3 Dropping a Baseline Template
  • 5.3.4 Transporting Automatic Workload Repository Data
    • 5.3.4.1 Extracting AWR Data
    • 5.3.4.2 Loading AWR Data
  • 5.3.5 Using Automatic Workload Repository Views
  • 5.3.6 Generating Automatic Workload Repository Reports
    • 5.3.6.1 Generating an AWR Report
    • 5.3.6.2 Generating an Oracle RAC AWR Report
    • 5.3.6.3 Generating an AWR Report on a Specific Database Instance
    • 5.3.6.4 Generating an Oracle RAC AWR Report on Specific Database Instances
    • 5.3.6.5 Generating an AWR Report for a SQL Statement
    • 5.3.6.6 Generating an AWR Report for a SQL Statement on a Specific Database Instance
  • 5.3.7 Generating Automatic Workload Repository Compare Periods Reports
    • 5.3.7.1 Generating an AWR Compare Periods Report
    • 5.3.7.2 Generating an Oracle RAC AWR Compare Periods Report
    • 5.3.7.3 Generating an AWR Compare Periods Report on a Specific Database Instance
    • 5.3.7.4 Generating an Oracle RAC AWR Compare Periods Report on Specific Database Instances
  • 5.3.8 Generating Active Session History Reports
    • 5.3.8.1 Generating an ASH Report
    • 5.3.8.2 Generating an ASH Report on a Specific Database Instance
    • 5.3.8.3 Generating an Oracle RAC ASH Report
  • 5.3.9 Using Active Session History Reports
    • 5.3.9.1 Top Events
    • 5.3.9.2 Load Profile
    • 5.3.9.3 Top SQL
    • 5.3.9.4 Top PL/SQL
    • 5.3.9.5 Top Java
    • 5.3.9.6 Top Sessions
    • 5.3.9.7 Top Objects/Files/Latches
    • 5.3.9.8 Activity Over Time

6 Automatic Performance Diagnostics

• 6.1 Overview of the Automatic Database Diagnostic Monitor
  • 6.1.1 ADDM Analysis
  • 6.1.2 Using ADDM with Oracle Real Application Clusters
  • 6.1.3 ADDM Analysis Results
  • 6.1.4 Reviewing ADDM Analysis Results: Example
• 6.2 Setting Up ADDM
• 6.3 Diagnosing Database Performance Problems with ADDM
  • 6.3.1 Running ADDM in Database Mode
  • 6.3.2 Running ADDM in Instance Mode
  • 6.3.3 Running ADDM in Partial Mode
  • 6.3.4 Displaying an ADDM Report
• 6.4 Views with ADDM Information

7 Configuring and Using Memory

• 7.1 Understanding Memory Allocation Issues
  • 7.1.1 Oracle Memory Caches
  • 7.1.2 Automatic Memory Management
  • 7.1.3 Automatic Shared Memory Management
  • 7.1.4 Dynamically Changing Cache Sizes
    • 7.1.4.1 Viewing Information About Dynamic Resize Operations
  • 7.1.5 Application Considerations
  • 7.1.6 Operating System Memory Use
    • 7.1.6.1 Reduce Paging
    • 7.1.6.2 Fit the SGA into Main Memory
    • 7.1.6.3 Allow Adequate Memory to Individual Users
  • 7.1.7 Iteration During Configuration
• 7.2 Configuring and Using the Buffer Cache
  • 7.2.1 Using the Buffer Cache Effectively
  • 7.2.2 Sizing the Buffer Cache
    • 7.2.2.1 Buffer Cache Advisory Statistics
    • 7.2.2.2 Using V$DB_CACHE_ADVICE
    • 7.2.2.3 Calculating the Buffer Cache Hit Ratio
  • 7.2.3 Interpreting and Using the Buffer Cache Advisory Statistics
    • 7.2.3.1 Increasing Memory Allocated to the Buffer Cache
    • 7.2.3.2 Reducing Memory Allocated to the Buffer Cache
  • 7.2.4 Considering Multiple Buffer Pools
    • 7.2.4.1 Random Access to Large Segments
    • 7.2.4.2 Oracle Real Application Clusters Instances
    • 7.2.4.3 Using Multiple Buffer Pools
  • 7.2.5 Buffer Pool Data in V$DB_CACHE_ADVICE
  • 7.2.6 Buffer Pool Hit Ratios
  • 7.2.7 Determining Which Segments Have Many Buffers in the Pool
  • 7.2.8 KEEP Pool
  • 7.2.9 RECYCLE Pool
• 7.3 Configuring and Using the Shared Pool and Large Pool
  • 7.3.1 Shared Pool Concepts
    • 7.3.1.1 Dictionary Cache Concepts
    • 7.3.1.2 Library Cache Concepts
    • 7.3.1.3 SQL Sharing Criteria
  • 7.3.2 Using the Shared Pool Effectively
    • 7.3.2.1 Shared Cursors
    • 7.3.2.2 Single-User Logon and Qualified Table Reference
    • 7.3.2.3 Use of PL/SQL
    • 7.3.2.4 Avoid Performing DDL
    • 7.3.2.5 Cache Sequence Numbers
    • 7.3.2.6 Cursor Access and Management
  • 7.3.3 Sizing the Shared Pool
    • 7.3.3.1 Shared Pool: Library Cache Statistics
    • 7.3.3.2 V$LIBRARYCACHE
    • 7.3.3.3 Shared Pool Advisory Statistics
    • 7.3.3.4 Shared Pool: Dictionary Cache Statistics
  • 7.3.4 Interpreting Shared Pool Statistics
    • 7.3.4.1 Increasing Memory Allocation
    • 7.3.4.2 Reducing Memory Allocation
  • 7.3.5 Using the Large Pool
    • 7.3.5.1 Tuning the Large Pool and Shared Pool for the Shared Server Architecture
  • 7.3.6 Using CURSOR_SPACE_FOR_TIME
  • 7.3.7 Caching Session Cursors
    • 7.3.7.1 How the Session Cursor Cache Works
    • 7.3.7.2 Enabling the Session Cursor Cache
    • 7.3.7.3 Tuning the Session Cursor Cache
  • 7.3.8 Configuring the Reserved Pool
    • 7.3.8.1 Using SHARED_POOL_RESERVED_SIZE
    • 7.3.8.2 When SHARED_POOL_RESERVED_SIZE Is Too Small
    • 7.3.8.3 When SHARED_POOL_RESERVED_SIZE Is Too Large
    • 7.3.8.4 When SHARED_POOL_SIZE is Too Small
  • 7.3.9 Keeping Large Objects to Prevent Aging
  • 7.3.10 Sharing Cursors for Existing Applications
    • 7.3.10.1 How Similar Statements Can Share SQL Areas
    • 7.3.10.2 When to Set CURSOR_SHARING to a Nondefault Value
  • 7.3.11 Maintaining Connections
• 7.4 Configuring and Using the Redo Log Buffer
  • 7.4.1 Sizing the Log Buffer
  • 7.4.2 Log Buffer Statistics
• 7.5 PGA Memory Management
  • 7.5.1 Configuring Automatic PGA Memory
    • 7.5.1.1 Setting PGA_AGGREGATE_TARGET Initially
    • 7.5.1.2 Monitoring the Performance of the Automatic PGA Memory Management
    • 7.5.1.3 Tuning PGA_AGGREGATE_TARGET
    • 7.5.1.4 V$SYSSTAT and V$SESSTAT
  • 7.5.2 Configuring OLAP_PAGE_POOL_SIZE
• 7.6 Managing the Server and Client Result Caches
  • 7.6.1 Managing the Server Result Cache
    • 7.6.1.1 How the Server Result Cache Works
    • 7.6.1.2 Server Result Cache Initialization Parameters
    • 7.6.1.3 Managing Memory for the Server Result Cache
  • 7.6.2 Managing the Client Result Cache
    • 7.6.2.1 How the Client Result Cache Works
    • 7.6.2.2 Client Result Cache Initialization Parameters
  • 7.6.3 Specifying Queries for Result Caching
    • 7.6.3.1 About the Result Cache Mode
    • 7.6.3.2 Using SQL Result Cache Hints
    • 7.6.3.3 Using Result Cache Table Annotations
  • 7.6.4 Requirements for the Result Cache
    • 7.6.4.1 Read Consistency Requirements for the Result Cache
    • 7.6.4.2 Additional Requirements for the Result Cache
    • 7.6.4.3 Query Parameter Requirements for the Result Cache
  • 7.6.5 Accessing Result Cache Information

8 I/O Configuration and Design

• 8.1 About I/O
• 8.2 I/O Configuration
  • 8.2.1 Lay Out the Files Using Operating System or Hardware Striping
    • 8.2.1.1 Requested I/O Size
    • 8.2.1.2 Concurrency of I/O Requests
    • 8.2.1.3 Alignment of Physical Stripe Boundaries with Block Size Boundaries
    • 8.2.1.4 Manageability of the Proposed System
  • 8.2.2 Manually Distributing I/O
  • 8.2.3 When to Separate Files
    • 8.2.3.1 Tables, Indexes, and TEMP Tablespaces
    • 8.2.3.2 Redo Log Files
    • 8.2.3.3 Archived Redo Logs
  • 8.2.4 Three Sample Configurations
    • 8.2.4.1 Stripe Everything Across Every Disk
    • 8.2.4.2 Move Archive Logs to Different Disks
    • 8.2.4.3 Move Redo Logs to Separate Disks
  • 8.2.5 Oracle Managed Files
  • 8.2.6 Choosing Data Block Size
    • 8.2.6.1 Reads
    • 8.2.6.2 Writes
    • 8.2.6.3 Block Size Advantages and Disadvantages
• 8.3 I/O Calibration Inside the Database
  • 8.3.1 Prerequisites for I/O Calibration
  • 8.3.2 Running I/O Calibration
• 8.4 I/O Calibration with the Oracle Orion Calibration Tool
  • 8.4.1 Introduction to the Oracle Orion Calibration Tool
    • 8.4.1.1 Orion Test Targets
    • 8.4.1.2 Orion for Oracle Administrators
  • 8.4.2 Getting Started with Orion
  • 8.4.3 Orion Input Files
  • 8.4.4 Orion Parameters
    • 8.4.4.1 Orion Required Parameter
    • 8.4.4.2 Orion Optional Parameters
    • 8.4.4.3 Orion Command Line Samples
  • 8.4.5 Orion Output Files
    • 8.4.5.1 Orion Sample Output Files
  • 8.4.6 Orion Troubleshooting

9 Managing Operating System Resources

• 9.1 Understanding Operating System Performance Issues
  • 9.1.1 Using Operating System Caches
    • 9.1.1.1 Asynchronous I/O
    • 9.1.1.2 FILESYSTEMIO_OPTIONS Initialization Parameter
    • 9.1.1.3 Limiting Asynchronous I/O in NFS Server Environments
  • 9.1.2 Memory Usage
    • 9.1.2.1 Buffer Cache Limits
    • 9.1.2.2 Parameters Affecting Memory Usage
  • 9.1.3 Using Operating System Resource Managers
• 9.2 Resolving Operating System Issues
  • 9.2.1 Performance Hints on UNIX-Based Systems
  • 9.2.2 Performance Hints on Windows Systems
  • 9.2.3 Performance Hints on HP OpenVMS Systems
• 9.3 Understanding CPU
• 9.4 Resolving CPU Issues
  • 9.4.1 Finding and Tuning CPU Utilization
    • 9.4.1.1 Checking Memory Management
    • 9.4.1.2 Checking I/O Management
    • 9.4.1.3 Checking Network Management
    • 9.4.1.4 Checking Process Management
  • 9.4.2 Managing CPU Resources Using Oracle Database Resource Manager
  • 9.4.3 Managing CPU Resources Using Instance Caging

10 Instance Tuning Using Performance Views

• 10.1 Instance Tuning Steps
  • 10.1.1 Define the Problem
  • 10.1.2 Examine the Host System
    • 10.1.2.1 CPU Usage
    • 10.1.2.2 Identifying I/O Problems
    • 10.1.2.3 Identifying Network Issues
  • 10.1.3 Examine the Oracle Database Statistics
    • 10.1.3.1 Setting the Level of Statistics Collection
    • 10.1.3.2 Wait Events
    • 10.1.3.3 Dynamic Performance Views Containing Wait Event Statistics
    • 10.1.3.4 System Statistics
    • 10.1.3.5 Segment-Level Statistics
  • 10.1.4 Implement and Measure Change
• 10.2 Interpreting Oracle Database Statistics
  • 10.2.1 Examine Load
    • 10.2.1.1 Changing Load
    • 10.2.1.2 High Rates of Activity
  • 10.2.2 Using Wait Event Statistics to Drill Down to Bottlenecks
  • 10.2.3 Table of Wait Events and Potential Causes
  • 10.2.4 Additional Statistics
    • 10.2.4.1 Redo Log Space Requests Statistic
    • 10.2.4.2 Read Consistency
    • 10.2.4.3 Table Fetch by Continued Row
    • 10.2.4.4 Parse-Related Statistics
• 10.3 Wait Events Statistics
  • 10.3.1 buffer busy waits
    • 10.3.1.1 Causes
    • 10.3.1.2 Actions
  • 10.3.2 db file scattered read
    • 10.3.2.1 Actions
    • 10.3.2.2 Managing Excessive I/O
    • 10.3.2.3 Inadequate I/O Distribution
    • 10.3.2.4 Finding the SQL Statement executed by Sessions Waiting for I/O
    • 10.3.2.5 Finding the Object Requiring I/O
  • 10.3.3 db file sequential read
    • 10.3.3.1 Actions
  • 10.3.4 direct path read and direct path read temp
    • 10.3.4.1 Causes
    • 10.3.4.2 Actions
  • 10.3.5 direct path write and direct path write temp
    • 10.3.5.1 Causes
    • 10.3.5.2 Actions
  • 10.3.6 enqueue (enq:) waits
    • 10.3.6.1 Finding Locks and Lock Holders
    • 10.3.6.2 Actions
  • 10.3.7 events in wait class other
  • 10.3.8 free buffer waits
    • 10.3.8.1 Causes
    • 10.3.8.2 Actions
    • 10.3.8.3 Consider Multiple Database Writer (DBWR) Processes or I/O Slaves
  • 10.3.9 Idle Wait Events
  • 10.3.10 latch events
    • 10.3.10.1 Actions
    • 10.3.10.2 Example: Find Latches Currently Waited For
    • 10.3.10.3 Shared Pool and Library Cache Latch Contention
  • 10.3.11 log file parallel write
  • 10.3.12 library cache pin
  • 10.3.13 library cache lock
  • 10.3.14 log buffer space
  • 10.3.15 log file switch
    • 10.3.15.1 Actions
  • 10.3.16 log file sync
  • 10.3.17 rdbms ipc reply
  • 10.3.18 SQL*Net Events
    • 10.3.18.1 SQL*Net message from client
    • 10.3.18.2 SQL*Net message from dblink
    • 10.3.18.3 SQL*Net more data to client
• 10.4 Real-Time SQL Monitoring
  • 10.4.1 SQL Plan Monitoring
  • 10.4.2 Parallel Execution Monitoring
  • 10.4.3 Generating the SQL Monitor Report
  • 10.4.4 Enabling and Disabling SQL Monitoring
• 10.5 Tuning Instance Recovery Performance: Fast-Start Fault Recovery
  • 10.5.1 About Instance Recovery
    • 10.5.1.1 Cache Recovery (Rolling Forward)
    • 10.5.1.2 Transaction Recovery (Rolling Back)
    • 10.5.1.3 Checkpoints and Cache Recovery
  • 10.5.2 Configuring the Duration of Cache Recovery: FAST_START_MTTR_TARGET
    • 10.5.2.1 Practical Values for FAST_START_MTTR_TARGET
    • 10.5.2.2 Reducing Checkpoint Frequency to Optimize Run-Time Performance
    • 10.5.2.3 Monitoring Cache Recovery with V$INSTANCE_RECOVERY
  • 10.5.3 Tuning FAST_START_MTTR_TARGET and Using MTTR Advisor
    • 10.5.3.1 Calibrate the FAST_START_MTTR_TARGET
    • 10.5.3.2 Determine the Practical Range for FAST_START_MTTR_TARGET
    • 10.5.3.3 Evaluate Different Target Values with MTTR Advisor
    • 10.5.3.4 Determine Optimal Size for Redo Logs

Part IV Optimizing SQL Statements

11 The Query Optimizer

• 11.1 Overview of the Query Optimizer
  • 11.1.1 Optimizer Operations
  • 11.1.2 Components of the Query Optimizer
    • 11.1.2.1 Query Transformation
    • 11.1.2.2 Estimation
    • 11.1.2.3 Plan Generation
  • 11.1.3 Bind Variable Peeking
    • 11.1.3.1 Adaptive Cursor Sharing
    • 11.1.3.2 Viewing Bind-Related Performance Data
• 11.2 Overview of Optimizer Access Paths
  • 11.2.1 Full Table Scans
    • 11.2.1.1 Why a Full Table Scan Is Faster for Accessing Large Amounts of Data
    • 11.2.1.2 When the Optimizer Uses Full Table Scans
    • 11.2.1.3 Full Table Scan Hints
    • 11.2.1.4 Parallel Query Execution
  • 11.2.2 Rowid Scans
    • 11.2.2.1 When the Optimizer Uses Rowids
  • 11.2.3 Index Scans
    • 11.2.3.1 Assessing I/O for Blocks, not Rows
    • 11.2.3.2 Index Unique Scans
    • 11.2.3.3 Index Range Scans
    • 11.2.3.4 Index Range Scans Descending
    • 11.2.3.5 Index Skip Scans
    • 11.2.3.6 Full Scans
    • 11.2.3.7 Fast Full Index Scans
    • 11.2.3.8 Index Joins
    • 11.2.3.9 Bitmap Indexes
  • 11.2.4 Cluster Access
  • 11.2.5 Hash Access
  • 11.2.6 Sample Table Scans
  • 11.2.7 How the Query Optimizer Chooses an Access Path
• 11.3 Overview of Joins
  • 11.3.1 How the Query Optimizer Executes Join Statements
  • 11.3.2 How the Query Optimizer Chooses Execution Plans for Joins
  • 11.3.3 Nested Loop Joins
    • 11.3.3.1 Original and New Implementation for Nested Loop Joins
    • 11.3.3.2 When the Optimizer Uses Nested Loop Joins
    • 11.3.3.3 Nested Loop Join Hints
    • 11.3.3.4 Nesting Nested Loops
  • 11.3.4 Hash Joins
    • 11.3.4.1 When the Optimizer Uses Hash Joins
    • 11.3.4.2 Hash Join Hints
  • 11.3.5 Sort Merge Joins
    • 11.3.5.1 When the Optimizer Uses Sort Merge Joins
    • 11.3.5.2 Sort Merge Join Hints
  • 11.3.6 Cartesian Joins
    • 11.3.6.1 When the Optimizer Uses Cartesian Joins
    • 11.3.6.2 Cartesian Join Hints
  • 11.3.7 Outer Joins
    • 11.3.7.1 Nested Loop Outer Joins
    • 11.3.7.2 Hash Join Outer Joins
    • 11.3.7.3 Sort Merge Outer Joins
    • 11.3.7.4 Full Outer Joins
• 11.4 Reading and Understanding Execution Plans
  • 11.4.1 Overview of EXPLAIN PLAN
  • 11.4.2 Steps in the Execution Plan
• 11.5 Controlling Optimizer Behavior
  • 11.5.1 Enabling Query Optimizer Features
  • 11.5.2 Choosing an Optimizer Goal
    • 11.5.2.1 Setting the OPTIMIZER_MODE Initialization Parameter
    • 11.5.2.2 Using Hints to Change the Optimizer Goal
    • 11.5.2.3 Optimizer Statistics in the Data Dictionary

12 Using EXPLAIN PLAN

• 12.1 Understanding EXPLAIN PLAN
  • 12.1.1 How Execution Plans Can Change
    • 12.1.1.1 Different Schemas
    • 12.1.1.2 Different Costs
  • 12.1.2 Minimizing Throw-Away
  • 12.1.3 Looking Beyond Execution Plans
    • 12.1.3.1 Using V$SQL_PLAN Views
  • 12.1.4 EXPLAIN PLAN Restrictions
• 12.2 The PLAN_TABLE Output Table
• 12.3 Running EXPLAIN PLAN
  • 12.3.1 Identifying Statements for EXPLAIN PLAN
  • 12.3.2 Specifying Different Tables for EXPLAIN PLAN
• 12.4 Displaying PLAN_TABLE Output
  • 12.4.1 Customizing PLAN_TABLE Output
• 12.5 Reading EXPLAIN PLAN Output
• 12.6 Viewing Parallel Execution with EXPLAIN PLAN
  • 12.6.1 Viewing Parallel Queries with EXPLAIN PLAN
• 12.7 Viewing Bitmap Indexes with EXPLAIN PLAN
• 12.8 Viewing Result Cache with EXPLAIN PLAN
• 12.9 Viewing Partitioned Objects with EXPLAIN PLAN
  • 12.9.1 Examples of Displaying Range and Hash Partitioning with EXPLAIN PLAN
    • 12.9.1.1 Plans for Hash Partitioning
  • 12.9.2 Examples of Pruning Information with Composite Partitioned Objects
  • 12.9.3 Examples of Partial Partition-Wise Joins
  • 12.9.4 Examples of Full Partition-wise Joins
  • 12.9.5 Examples of INLIST ITERATOR and EXPLAIN PLAN
    • 12.9.5.1 When the IN-List Column is an Index Column
    • 12.9.5.2 When the IN-List Column is an Index and a Partition Column
    • 12.9.5.3 When the IN-List Column is a Partition Column
  • 12.9.6 Example of Domain Indexes and EXPLAIN PLAN
• 12.10 PLAN_TABLE Columns

13 Managing Optimizer Statistics

• 13.1 Overview of Optimizer Statistics
• 13.2 Managing Automatic Optimizer Statistics Collection
  • 13.2.1 Enabling and Disabling Automatic Optimizer Statistics Collection
  • 13.2.2 Considerations When Gathering Statistics
    • 13.2.2.1 When to Use Manual Statistics
    • 13.2.2.2 Restoring Previous Versions of Statistics
    • 13.2.2.3 Locking Statistics
• 13.3 Gathering Statistics Manually
  • 13.3.1 Gathering Statistics with DBMS_STATS Procedures
    • 13.3.1.1 Statistics Gathering Using Sampling
    • 13.3.1.2 Parallel Statistics Gathering
    • 13.3.1.3 Statistics on Partitioned Objects
    • 13.3.1.4 Column Statistics and Histograms
    • 13.3.1.5 Extended Statistics
    • 13.3.1.6 MultiColumn Statistics
    • 13.3.1.7 Expression Statistics
    • 13.3.1.8 Determining Stale Statistics
    • 13.3.1.9 User-Defined Statistics
  • 13.3.2 Setting Preferences for Manual Statistics Gathering
  • 13.3.3 When to Gather Statistics
  • 13.3.4 Comparing Statistics with DBMS_STATS Functions
• 13.4 System Statistics
  • 13.4.1 Workload Statistics
    • 13.4.1.1 Gathering Workload Statistics
    • 13.4.1.2 Multiblock Read Count
  • 13.4.2 Noworkload Statistics
    • 13.4.2.1 Gathering Noworkload Statistics
• 13.5 Managing Statistics
  • 13.5.1 Pending Statistics
  • 13.5.2 Restoring Previous Versions of Statistics
  • 13.5.3 Exporting and Importing Statistics
  • 13.5.4 Restoring Statistics Versus Importing or Exporting Statistics
  • 13.5.5 Locking Statistics for a Table or Schema
  • 13.5.6 Setting Statistics
  • 13.5.7 Handling Missing Statistics
• 13.6 Estimating Statistics with Dynamic Sampling
  • 13.6.1 Purpose of Dynamic Sampling
  • 13.6.2 Dynamic Sampling Concepts
    • 13.6.2.1 Dynamic Sampling Levels
    • 13.6.2.2 When the Optimizer Uses Dynamic Sampling
  • 13.6.3 Setting Dynamic Sampling Levels
• 13.7 Viewing Statistics
  • 13.7.1 Statistics on Tables, Indexes and Columns
  • 13.7.2 Viewing Histograms
    • 13.7.2.1 Height-Balanced Histograms
    • 13.7.2.2 Frequency Histograms

14 Using Indexes and Clusters

• 14.1 Understanding Index Performance
  • 14.1.1 Tuning the Logical Structure
  • 14.1.2 Index Tuning using the SQLAccess Advisor
  • 14.1.3 Choosing Columns and Expressions to Index
  • 14.1.4 Choosing Composite Indexes
    • 14.1.4.1 Choosing Keys for Composite Indexes
    • 14.1.4.2 Ordering Keys for Composite Indexes
  • 14.1.5 Writing Statements That Use Indexes
  • 14.1.6 Writing Statements That Avoid Using Indexes
  • 14.1.7 Re-creating Indexes
  • 14.1.8 Compacting Indexes
  • 14.1.9 Using Nonunique Indexes to Enforce Uniqueness
  • 14.1.10 Using Enabled Novalidated Constraints
• 14.2 Using Function-based Indexes for Performance
• 14.3 Using Partitioned Indexes for Performance
• 14.4 Using Index-Organized Tables for Performance
• 14.5 Using Bitmap Indexes for Performance
• 14.6 Using Bitmap Join Indexes for Performance
• 14.7 Using Domain Indexes for Performance
• 14.8 Using Table Clusters for Performance
• 14.9 Using Hash Clusters for Performance

15 Using SQL Plan Management

• 15.1 Overview of SQL Plan Baselines
  • 15.1.1 Purpose of SQL Plan Baselines
  • 15.1.2 Architecture of SQL Plan Baselines
• 15.2 Managing SQL Plan Baselines
  • 15.2.1 Capturing SQL Plan Baselines
    • 15.2.1.1 Capturing Plans Automatically
    • 15.2.1.2 Creating Baselines from Existing Plans
  • 15.2.2 Selecting SQL Plan Baselines
  • 15.2.3 Evolving SQL Plan Baselines
    • 15.2.3.1 Evolving Plans with Manual Plan Loading
    • 15.2.3.2 Evolving Plans with DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE
• 15.3 Using SQL Plan Baselines with SQL Tuning Advisor
• 15.4 Using Fixed SQL Plan Baselines
• 15.5 Displaying SQL Plan Baselines
• 15.6 SQL Management Base
  • 15.6.1 Disk Space Usage
  • 15.6.2 Purging Policy
  • 15.6.3 SQL Management Base Configuration Parameters
• 15.7 Importing and Exporting SQL Plan Baselines
• 15.8 Migrating Stored Outlines to SQL Plan Baselines
  • 15.8.1 Overview of Stored Outline Migration
    • 15.8.1.1 Purpose of Stored Outline Migration
    • 15.8.1.2 How Stored Outline Migration Works
    • 15.8.1.3 User Interface for Stored Outline Migration
    • 15.8.1.4 Basic Steps in Stored Outline Migration
  • 15.8.2 Preparing for Stored Outline Migration
  • 15.8.3 Migrating Outlines to Utilize SQL Plan Management Features
  • 15.8.4 Migrating Outlines to Preserve Stored Outline Behavior
  • 15.8.5 Performing Follow-Up Tasks After Stored Outline Migration

16 SQL Tuning Overview

• 16.1 Introduction to SQL Tuning
• 16.2 Goals for Tuning
  • 16.2.1 Reduce the Workload
  • 16.2.2 Balance the Workload
  • 16.2.3 Parallelize the Workload
• 16.3 Identifying High-Load SQL
  • 16.3.1 Identifying Resource-Intensive SQL
    • 16.3.1.1 Tuning a Specific Program
    • 16.3.1.2 Tuning an Application / Reducing Load
  • 16.3.2 Gathering Data on the SQL Identified
    • 16.3.2.1 Information to Gather During Tuning
• 16.4 Automatic SQL Tuning Features
  • 16.4.1 ADDM
  • 16.4.2 SQL Tuning Advisor
  • 16.4.3 SQL Tuning Sets
  • 16.4.4 SQL Access Advisor
• 16.5 Developing Efficient SQL Statements
  • 16.5.1 Verifying Optimizer Statistics
  • 16.5.2 Reviewing the Execution Plan
  • 16.5.3 Restructuring the SQL Statements
    • 16.5.3.1 Compose Predicates Using AND and =
    • 16.5.3.2 Avoid Transformed Columns in the WHERE Clause
    • 16.5.3.3 Write Separate SQL Statements for Specific Tasks
  • 16.5.4 Controlling the Access Path and Join Order with Hints
    • 16.5.4.1 Use Caution When Managing Views
    • 16.5.4.2 Store Intermediate Results
  • 16.5.5 Restructuring the Indexes
  • 16.5.6 Modifying or Disabling Triggers and Constraints
  • 16.5.7 Restructuring the Data
  • 16.5.8 Maintaining Execution Plans Over Time
  • 16.5.9 Visiting Data as Few Times as Possible
    • 16.5.9.1 Combine Multiples Scans Using CASE Expressions
    • 16.5.9.2 Use DML with RETURNING Clause
    • 16.5.9.3 Modify All the Data Needed in One Statement
• 16.6 Building SQL Test Cases
  • 16.6.1 Creating a Test Case
    • 16.6.1.1 Accessing SQL Test Case Builder from Enterprise Manager
    • 16.6.1.2 Accessing SQL Test Case Builder Using DBMS_SQLDIAG

17 Automatic SQL Tuning

• 17.1 Overview of the Automatic Tuning Optimizer
  • 17.1.1 Statistics Analysis
  • 17.1.2 SQL Profiling
  • 17.1.3 Access Path Analysis
  • 17.1.4 SQL Structure Analysis
  • 17.1.5 Alternative Plan Analysis
• 17.2 Managing the Automatic SQL Tuning Advisor
  • 17.2.1 How Automatic SQL Tuning Works
  • 17.2.2 Enabling and Disabling Automatic SQL Tuning
  • 17.2.3 Configuring Automatic SQL Tuning
  • 17.2.4 Viewing Automatic SQL Tuning Reports
• 17.3 Tuning Reactively with SQL Tuning Advisor
  • 17.3.1 Input Sources
  • 17.3.2 Tuning Options
  • 17.3.3 Advisor Output
  • 17.3.4 Running SQL Tuning Advisor
    • 17.3.4.1 Creating a SQL Tuning Task
    • 17.3.4.2 Configuring a SQL Tuning Task
    • 17.3.4.3 Executing a SQL Tuning Task
    • 17.3.4.4 Checking the Status of a SQL Tuning Task
    • 17.3.4.5 Checking the Progress of SQL Tuning Advisor
    • 17.3.4.6 Displaying the Results of a SQL Tuning Task
    • 17.3.4.7 Additional Operations on a SQL Tuning Task
• 17.4 Managing SQL Tuning Sets
  • 17.4.1 Creating a SQL Tuning Set
  • 17.4.2 Loading a SQL Tuning Set
  • 17.4.3 Displaying the Contents of a SQL Tuning Set
  • 17.4.4 Modifying a SQL Tuning Set
  • 17.4.5 Transporting a SQL Tuning Set
  • 17.4.6 Dropping a SQL Tuning Set
  • 17.4.7 Additional Operations on SQL Tuning Sets
• 17.5 Managing SQL Profiles
  • 17.5.1 Overview of SQL Profiles
    • 17.5.1.1 SQL Profile Recommendations
    • 17.5.1.2 SQL Profile Creation
    • 17.5.1.3 SQL Profile APIs
  • 17.5.2 Accepting a SQL Profile
  • 17.5.3 Altering a SQL Profile
  • 17.5.4 Dropping a SQL Profile
  • 17.5.5 Transporting a SQL Profile
• 17.6 SQL Tuning Views

18 SQL Access Advisor

• 18.1 Overview of SQL Access Advisor
  • 18.1.1 Overview of Using SQL Access Advisor
    • 18.1.1.1 SQL Access Advisor Repository
• 18.2 Using SQL Access Advisor
  • 18.2.1 Steps for Using SQL Access Advisor
  • 18.2.2 Privileges Needed to Use SQL Access Advisor
  • 18.2.3 Setting Up Tasks and Templates
    • 18.2.3.1 Creating Tasks
    • 18.2.3.2 Using Templates
    • 18.2.3.3 Creating Templates
  • 18.2.4 SQL Access Advisor Workloads
    • 18.2.4.1 SQL Tuning Set Workloads
    • 18.2.4.2 Using SQL Tuning Sets
    • 18.2.4.3 Linking Tasks and Workloads
  • 18.2.5 Working with Recommendations
    • 18.2.5.1 Recommendations and Actions
    • 18.2.5.2 Recommendation Options
    • 18.2.5.3 Evaluation Mode
    • 18.2.5.4 View Intermediate Results During Recommendation Analysis
    • 18.2.5.5 Generating Recommendations
    • 18.2.5.6 Viewing Recommendations
    • 18.2.5.7 Stopping the Recommendation Process
    • 18.2.5.8 Marking Recommendations
    • 18.2.5.9 Modifying Recommendations
    • 18.2.5.10 Generating SQL Scripts
    • 18.2.5.11 Special Considerations when Script Includes Partitioning Recommendations
    • 18.2.5.12 When Recommendations are no Longer Required
  • 18.2.6 Performing a Quick Tune
  • 18.2.7 Managing Tasks
    • 18.2.7.1 Updating Task Attributes
    • 18.2.7.2 Deleting Tasks
    • 18.2.7.3 Setting the DAYS_TO_EXPIRE Parameter
  • 18.2.8 Using SQL Access Advisor Constants
  • 18.2.9 Examples of Using SQL Access Advisor
    • 18.2.9.1 Recommendations From a User-Defined Workload
    • 18.2.9.2 Generate Recommendations Using a Task Template
    • 18.2.9.3 Evaluate Current Usage of Indexes and Materialized Views
• 18.3 Tuning Materialized Views for Fast Refresh and Query Rewrite
  • 18.3.1 DBMS_ADVISOR.TUNE_MVIEW Procedure
    • 18.3.1.1 TUNE_MVIEW Syntax and Operations
    • 18.3.1.2 Accessing TUNE_MVIEW Output Results
    • 18.3.1.3 Fast Refreshable with Optimized Sub-Materialized View

19 Using Optimizer Hints

• 19.1 Overview of Optimizer Hints
  • 19.1.1 Types of Hints
  • 19.1.2 Hints by Category
    • 19.1.2.1 Hints for Optimization Approaches and Goals
    • 19.1.2.2 Hints for Enabling Optimizer Features
    • 19.1.2.3 Hints for Access Paths
    • 19.1.2.4 Hints for Join Orders
    • 19.1.2.5 Hints for Join Operations
    • 19.1.2.6 Hints for Online Application Upgrade
    • 19.1.2.7 Hints for Parallel Execution
    • 19.1.2.8 Hints for Query Transformations
    • 19.1.2.9 Additional Hints
• 19.2 Specifying Hints
  • 19.2.1 Specifying a Full Set of Hints
  • 19.2.2 Specifying a Query Block in a Hint
  • 19.2.3 Specifying Global Table Hints
  • 19.2.4 Specifying Complex Index Hints
• 19.3 Using Hints with Views
  • 19.3.1 Hints and Complex Views
  • 19.3.2 Hints and Mergeable Views
    • 19.3.2.1 Optimization Approaches and Goal Hints in Views
    • 19.3.2.2 Access Path and Join Hints on Views
    • 19.3.2.3 Access Path and Join Hints Inside Views
  • 19.3.3 Hints and Nonmergeable Views

20 Using Plan Stability

• 20.1 Using Plan Stability to Preserve Execution Plans
  • 20.1.1 Using Hints with Plan Stability
    • 20.1.1.1 How Outlines Use Hints
  • 20.1.2 Storing Outlines
  • 20.1.3 Enabling Plan Stability
  • 20.1.4 Using Supplied Packages to Manage Stored Outlines
  • 20.1.5 Creating Outlines
    • 20.1.5.1 Using Category Names for Stored Outlines
  • 20.1.6 Using Stored Outlines
  • 20.1.7 Viewing Outline Data
  • 20.1.8 Moving Outline Tables
• 20.2 Using Plan Stability with Query Optimizer Upgrades
  • 20.2.1 Moving from RBO to the Query Optimizer
  • 20.2.2 Moving to a New Oracle Release under the Query Optimizer
    • 20.2.2.1 Upgrading with a Test System

21 Using Application Tracing Tools

• 21.1 End to End Application Tracing
  • 21.1.1 Enabling and Disabling Statistic Gathering for End to End Tracing
    • 21.1.1.1 Statistic Gathering for Client Identifier
    • 21.1.1.2 Statistic Gathering for Service, Module, and Action
  • 21.1.2 Viewing Gathered Statistics for End to End Application Tracing
  • 21.1.3 Enabling and Disabling for End-to-End Tracing
    • 21.1.3.1 Tracing for Client Identifier
    • 21.1.3.2 Tracing for Service, Module, and Action
    • 21.1.3.3 Tracing for Session
    • 21.1.3.4 Tracing for Entire Instance or Database
  • 21.1.4 Viewing Enabled Traces for End to End Tracing
• 21.2 Using the trcsess Utility
  • 21.2.1 Syntax for trcsess
  • 21.2.2 Sample Output of trcsess
• 21.3 Understanding SQL Trace and TKPROF
  • 21.3.1 Understanding the SQL Trace Facility
  • 21.3.2 Understanding TKPROF
• 21.4 Using the SQL Trace Facility and TKPROF
  • 21.4.1 Step 1: Setting Initialization Parameters for Trace File Management
  • 21.4.2 Step 2: Enabling the SQL Trace Facility
  • 21.4.3 Step 3: Formatting Trace Files with TKPROF
    • 21.4.3.1 Sample TKPROF Output
    • 21.4.3.2 Syntax of TKPROF
    • 21.4.3.3 Examples of TKPROF Statement
  • 21.4.4 Step 4: Interpreting TKPROF Output
    • 21.4.4.1 Tabular Statistics in TKPROF
    • 21.4.4.2 Row Source Operations
    • 21.4.4.3 Wait Event Information
    • 21.4.4.4 Interpreting the Resolution of Statistics
    • 21.4.4.5 Understanding Recursive Calls
    • 21.4.4.6 Library Cache Misses in TKPROF
    • 21.4.4.7 Statement Truncation in SQL Trace
    • 21.4.4.8 Identification of User Issuing the SQL Statement in TKPROF
    • 21.4.4.9 Execution Plan in TKPROF
    • 21.4.4.10 Deciding Which Statements to Tune
  • 21.4.5 Step 5: Storing SQL Trace Facility Statistics
    • 21.4.5.1 Generating the TKPROF Output SQL Script
    • 21.4.5.2 Editing the TKPROF Output SQL Script
    • 21.4.5.3 Querying the Output Table
• 21.5 Avoiding Pitfalls in TKPROF Interpretation
  • 21.5.1 Avoiding the Argument Trap
  • 21.5.2 Avoiding the Read Consistency Trap
  • 21.5.3 Avoiding the Schema Trap
  • 21.5.4 Avoiding the Time Trap
• 21.6 Sample TKPROF Output
  • 21.6.1 Sample TKPROF Header
  • 21.6.2 Sample TKPROF Body
  • 21.6.3 Sample TKPROF Summary

Glossary

Index