Go to main content
1/31
Contents
Title and Copyright Information
Preface
Audience
Documentation Accessibility
Related Documents
Conventions
What's New in Oracle Database Performance Tuning Guide?
Oracle Database 11
g
Release 2 (11.2.0.4) New Features in Oracle Database Performance
Oracle Database 11
g
Release 2 (11.2.0.2) New Features in Oracle Database Performance
Oracle Database 11
g
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
Determining Stale Statistics
13.3.1.6
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
Managing Extended Statistics
13.5.2.1
Managing Column Group Statistics
13.5.2.2
Managing Expression Statistics
13.5.3
Restoring Previous Versions of Statistics
13.5.4
Exporting and Importing Statistics
13.5.5
Restoring Statistics Versus Importing or Exporting Statistics
13.5.6
Locking Statistics for a Table or Schema
13.5.7
Setting Statistics
13.5.8
Handling Missing Statistics
13.6
Controlling Dynamic Statistics
13.6.1
Purpose of Dynamic Statistics
13.6.2
Dynamic Statistics Concepts
13.6.2.1
Dynamic Statistics Levels
13.6.2.2
When the Optimizer Uses Dynamic Statistics
13.6.3
Setting Dynamic Statistics Levels Manually
13.6.4
Disabling Dynamic Statistics
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
Scripting on this page enhances content navigation, but does not change the content in any way.