1 Performance Tuning Overview

Introduction to Performance Tuning
Performance Planning
Instance Tuning
Performance Principles.
Baselines.
The Symptoms and the Problems.
When to Tune .
Proactive Monitoring
Bottleneck Elimination
SQL Tuning
Query Optimizer and Execution Plans
Introduction to Performance Tuning Features and Tools
Automatic Performance Tuning Features.
Additional Oracle Database Tools
V$ Performance Views

2 Designing and Developing for Performance

Oracle Methodology
Understanding Investment Options
Understanding Scalability
What is Scalability?
System Scalability
Factors Preventing Scalability
System Architecture
Hardware and Software Components
Hardware Components.
CPU.
Memory
I/O Subsystem.
Network
Software Components.
Managing the User Interface
Implementing Business Logic
Managing User Requests and Resource Allocation
Managing Data and Transactions
Configuring the Right System Architecture for Your Requirements.
Application Design Principles
Simplicity In Application Design.
Data Modeling
Table and Index Design.
Appending Columns to an Index or Using Index-Organized Tab
Using a Different Index Type.
B-Tree Indexes .
Bitmap Indexes .
Function-based Indexes
Partitioned Indexes
Reverse Key Indexes.
Finding the Cost of an Index.
Serializing within Indexes
Ordering Columns in an Index
Using Views
SQL Execution Efficiency.
Implementing the Application
Trends in Application Development
Workload Testing, Modeling, and Implementation
Sizing Data.
Estimating Workloads.
Extrapolating From a Similar System
Benchmarking.
Application Modeling
Testing, Debugging, and Validating a Design
Deploying New Applications
Rollout Strategies.
Performance Checklist.

3 Performance Improvement Methods

The Oracle Performance Improvement Method
Steps in the Oracle Performance Improvement Method
A Sample Decision Process for Performance Conceptual Modeling.
Top Ten Mistakes Found in Oracle Systems.
Emergency Performance Methods
Steps in the Emergency Performance Method.

4 Configuring a Database for Performance

Performance Considerations for Initial Instance Configuration
Initialization Parameters.
Configuring Undo Space
Sizing Redo Log Files
Creating Subsequent Tablespaces
Creating Permanent Tablespaces – Automatic Segment-Space
Creating Temporary Tablespaces.
Creating and Maintaining Tables for Optimal Performance
Table Compression
Estimating the Compression Factor
Tuning to Achieve a Better Compression Ratio
Using Attribute-Clustered Tables
Reclaiming Unused Space.
Indexing Data.
Specifying Memory for Sorting Data.
Performance Considerations for Shared Servers
Identifying Contention Using the Dispatcher-Specific Views
Reducing Contention for Dispatcher Processes.
Identifying Contention for Shared Servers.

5 Measuring Database Performance

About Database Statistics
Time Model Statistics.
Active Session History Statistics
Wait Events Statistics.
Session and System Statistics.
Interpreting Database Statistics
Using Hit Ratios
Using Wait Events with Timed Statistics
Using Wait Events without Timed Statistics.
Using Idle Wait Events.
Comparing Database Statistics with Other Factors
Using Computed Statistics.

6 Gathering Database Statistics

About Gathering Database Statistics
Automatic Workload Repository.
Snapshots
Baselines
Fixed Baselines.
Moving Window Baselines
Baseline Templates.
Single Baseline Templates
Repeating Baseline Templates
Space Consumption.
Adaptive Thresholds.
Percentage of Maximum Thresholds
Significance Level Thresholds
Managing the Automatic Workload Repository
Enabling the Automatic Workload Repository
Managing Snapshots
User Interfaces for Managing Snapshots
Creating Snapshots.
Creating Snapshots Using the Command-Line Interface.
Dropping Snapshots
Dropping Snapshots Using the Command-Line Interface
Modifying Snapshot Settings.
Modifying Snapshot Settings Using the Command-Line Int
Managing Baselines.
User Interface for Managing Baselines.
Creating a Baseline.
Creating a Baseline Using the Command-Line Interface.
Dropping a Baseline
Dropping a Baseline Using the Command-Line Interface
Renaming a Baseline
Renaming a Baseline Using the Command-Line Interface
Displaying Baseline Metrics
Displaying Baseline Metrics Using the Command-Line Inte
Resizing the Default Moving Window Baseline.
Resizing the Default Moving Window Using the Command
Managing Baseline Templates.
User Interfaces for Managing Baseline Templates.
Creating a Single Baseline Template
Creating a Single Baseline Template Using the Command
Creating a Repeating Baseline Template.
Creating a Repeating Baseline Template Using the Comm
Dropping a Baseline Template
Dropping a Baseline Template Using the Command-Line I
Transporting Automatic Workload Repository Data.
Extracting AWR Data
Loading AWR Data.
Using Automatic Workload Repository Views
Generating Automatic Workload Repository Reports
User Interface for Generating an AWR Report.
Generating an AWR Report Using the Command-Line Interface.
Generating an AWR Report for the Local Database.
Generating an AWR Report for a Specific Database
Generating an Oracle RAC AWR Report for the Local Databas
Generating an Oracle RAC AWR Report for a Specific Databas
Generating an AWR Report for a SQL Statement on the Local
Generating an AWR Report for a SQL Statement on a Specific
Generating Performance Hub Active Report
Overview of Performance Hub Active Report.
About Performance Hub Active Report Tabs
About Performance Hub Active Report Types
Command-Line User Interface for Generating a Performance Hub A
Generating a Performance Hub Active Report Using a SQL Script

7 Automatic Performance Diagnostics

Overview of the Automatic Database Diagnostic Monitor
ADDM Analysis
Using ADDM with Oracle Real Application Clusters
Real-Time ADDM Analysis.
Real-Time ADDM Connection Modes.
Real-Time ADDM Triggers
Real-Time ADDM Trigger Controls.
ADDM Analysis Results.
Reviewing ADDM Analysis Results: Example
Setting Up ADDM.
Diagnosing Database Performance Problems with ADDM
Running ADDM in Database Mode
Running ADDM in Instance Mode
Running ADDM in Partial Mode.
Displaying an ADDM Report
Views with ADDM Information

8 Comparing Database Performance Over Time

About Automatic Workload Repository Compare Periods Reports
Generating Automatic Workload Repository Compare Periods Reports
User Interfaces for Generating AWR Compare Periods Reports
Generating an AWR Compare Periods Report Using the Command
Generating an AWR Compare Periods Report for the Local Database
Generating an AWR Compare Periods Report for a Specific Database
Generating an Oracle RAC AWR Compare Periods Report
Generating an Oracle RAC AWR Compare Periods Report for a
Interpreting Automatic Workload Repository Compare Periods Reports
Summary of the AWR Compare Periods Report
Snapshot Sets.
Host Configuration Comparison
System Configuration Comparison.
Load Profile
Top 5 Timed Events
Details of the AWR Compare Periods Report
Time Model Statistics
Operating System Statistics
Wait Events.
Service Statistics.
SQL Statistics
Top 10 SQL Comparison by Execution Time.
Top 10 SQL Comparison by CPU Time.
Top 10 SQL Comparison by Buffer Gets.
Top 10 SQL Comparison by Physical Reads.
Top 10 SQL Comparison by Executions
Top 10 SQL Comparison by Parse Calls.
Complete List of SQL Text.
Instance Activity Statistics.
Key Instance Activity Statistics
Other Instance Activity Statistics
I/O Statistics
Tablespace I/O Statistics.
Top 10 File Comparison by I/O
Top 10 File Comparison by Read Time.
Top 10 File Comparison by Buffer Waits.
Advisory Statistics.
PGA Aggregate Summary.
PGA Aggregate Target Statistics.
Wait Statistics
Buffer Wait Statistics
Enqueue Activity
Undo Segment Summary
Latch Statistics
Segment Statistics
Top 5 Segments Comparison by Logical Reads
Top 5 Segments Comparison by Physical Reads
Top 5 Segments Comparison by Row Lock Waits.
Top 5 Segments Comparison by ITL Waits.
Top 5 Segments Comparison by Buffer Busy Waits
In-Memory Segment Statistics
Dictionary Cache Statistics.
Library Cache Statistics
Memory Statistics
Process Memory Summary
SGA Memory Summary.
SGA Breakdown Difference.
Streams Statistics.
Supplemental Information in the AWR Compare Periods Report.
init.ora Parameters
Complete List of SQL Text

9 Analyzing Sampled Data

About Active Session History9-1.
Generating Active Session History Reports
User Interfaces for Generating ASH Reports.
Generating an ASH Report Using the Command-Line Interface
Generating an ASH Report on the Local Database Instance
Generating an ASH Report on a Specific Database Instance.
Generating an ASH Report for Oracle RAC
Interpreting Results from Active Session History Reports
Top Events.
Top User Events.
Top Background Events
Top Event P1/P2/P3
Load Profile
Top Service/Module
Top Client IDs.
Top SQL Command Types.
Top Phases of Execution
Top SQL.
Top SQL with Top Events.
Top SQL with Top Row Sources.
Top SQL Using Literals.
Top Parsing Module/Action.
Complete List of SQL Text
Top PL/SQL
Top Java.
Top Sessions
Top Sessions
Top Blocking Sessions
Top Sessions Running PQs
Top Objects/Files/Latches
Top DB Objects.
Top DB Files.
Top Latches
Activity Over Time

10 Instance Tuning Using Performance Views

Instance Tuning Steps
Define the Problem.
Examine the Host System
CPU Usage.
Non-Oracle Processes.
Oracle Processes.
Oracle Database CPU Statistics
Interpreting CPU Statistics.
Identifying I/O Problems
Identifying I/O Problems Using V$ Views
Identifying I/O Problems Using Operating System Monitori
Identifying Network Issues
Examine the Oracle Database Statistics
Setting the Level of Statistics Collection.
V$STATISTICS_LEVEL.
Wait Events.
Dynamic Performance Views Containing Wait Event Statistics.
System Statistics.
V$ACTIVE_SESSION_HISTORY
V$SYSSTAT
V$FILESTAT
V$ROLLSTAT
V$ENQUEUE_STAT
V$LATCH.
Segment-Level Statistics.
Implement and Measure Change.
Interpreting Oracle Database Statistics
Examine Load
Changing Load
High Rates of Activity
Using Wait Event Statistics to Drill Down to Bottlenecks.
Table of Wait Events and Potential Causes.
Additional Statistics
Redo Log Space Requests Statistic
Read Consistency
Table Fetch by Continued Row.
Parse-Related Statistics
Wait Events Statistics
Changes to Wait Event Statistics from Past Releases.
buffer busy waits
Causes
Actions
segment header.
data block.
undo header.
undo block
db file scattered read
Actions
Managing Excessive I/O.
Inadequate I/O Distribution.
Finding the SQL Statement executed by Sessions Waiting for I/O
Finding the Object Requiring I/O.
db file sequential read
Actions
direct path read and direct path read temp
Causes
Actions
Sorts to Disk
Full Table Scans
Hash Area Size
direct path write and direct path write temp
Causes
Actions
enqueue (enq:) waits
Finding Locks and Lock Holders.
Actions
ST enqueue.
HW enqueue
TM enqueue.
TX enqueue.
events in wait class other.
free buffer waits
Causes
Actions
Writes.
Cache is Too Small
Cache Is Too Big for One DBWR
Consider Multiple Database Writer (DBWR) Processes or I/O S
DB_WRITER_PROCESSES.
DBWR_IO_SLAVES
Choosing Between Multiple DBWR Processes and I/O Slav
Idle Wait Events.
latch events
Actions
Example: Find Latches Currently Waited For.
Shared Pool and Library Cache Latch Contention
Unshared SQL.
Reparsed Sharable SQL
By Session.
cache buffers lru chain.
cache buffers chains
row cache objects
log file parallel write
library cache pin.
library cache lock
log buffer space
log file switch
Actions
log file sync.
rdbms ipc reply.
SQL*Net Events.
SQL*Net message from client.
Network Bottleneck
Resource Bottleneck on the Client Process.
SQL*Net message from dblink
SQL*Net more data to client.
Tuning Instance Recovery Performance: Fast-Start Fault Recovery
About Instance Recovery.
Cache Recovery (Rolling Forward).
Transaction Recovery (Rolling Back)
Checkpoints and Cache Recovery
How Checkpoints Affect Performance.
Fast Cache Recovery Tradeoffs
Configuring the Duration of Cache Recovery: FAST_START_MTTR_TARGET
Practical Values for FAST_START_MTTR_TARGET.
Reducing Checkpoint Frequency to Optimize Run-Time Performance
Monitoring Cache Recovery with V$INSTANCE_RECOVERY
Tuning FAST_START_MTTR_TARGET and Using MTTR Advisor.
Calibrate the FAST_START_MTTR_TARGET.
Determine the Practical Range for FAST_START_MTTR_TARGET
Determining Lower Bound for FAST_START_MTTR_TARGET
Determining Upper Bound for FAST_START_MTTR_TARGET
Selecting Preliminary Value for FAST_START_MTTR_TARGET
Evaluate Different Target Values with MTTR Advisor
Enabling MTTR Advisor.
Using MTTR Advisor
Viewing MTTR Advisor Results: V$MTTR_TARGET_ADVISORY
Determine the Optimal Size for Redo Logs

11 Database Memory Allocation

About Database Memory Caches and Other Memory Structures
Database Memory Management Methods
Automatic Memory Management.
Automatic Shared Memory Management.
Manual Shared Memory Management.
Automatic PGA Memory Management.
Manual PGA Memory Management.
Using Automatic Memory Management
Monitoring Memory Management

12 Tuning the System Global Area 

Using Automatic Shared Memory Management
User Interfaces for Setting the SGA_TARGET Parameter
Setting the SGA_TARGET Parameter in Oracle Enterprise Manager
Setting the SGA_TARGET Parameter in the Command-Line Interface
Setting the SGA_TARGET Parameter.
Enabling Automatic Shared Memory Management.
Disabling Automatic Shared Memory Management
Sizing the SGA Components Manually
SGA Sizing Unit
Maximum Size of the SGA.
Application Considerations.
Operating System Memory Use.
Reduce Paging
Fit the SGA into Main Memory
Viewing SGA Memory Allocation.
Locking the SGA into Physical Memory.
Allow Adequate Memory to Individual Users.
Iteration During Configuration
Monitoring Shared Memory Management
Configuring the In-Memory Column Store
About the In-Memory Column Store.
Performance Benefits of Using the In-Memory Column Store.
Estimating the Required Size of the In-Memory Column Store.
Sizing the In-Memory Column Store

13 Tuning the Database Buffer Cache

About the Database Buffer Cache
Configuring the Database Buffer Cache
Using the V$DB_CACHE_ADVICE View.
Calculating the Buffer Cache Hit Ratio.
Interpreting the Buffer Cache Hit Ratio
Increasing Memory Allocated to the Database Buffer Cache
Reducing Memory Allocated to the Database Buffer Cache.
Configuring Multiple Buffer Pools
Considerations for Using Multiple Buffer Pools
Random Access to Large Segments.
Oracle Real Application Cluster Instances
Using Multiple Buffer Pools
Using the V$DB_CACHE_ADVICE View for Individual Buffer Pools.
Calculating the Buffer Pool Hit Ratio for Individual Buffer Pools.
Examining the Buffer Cache Usage Pattern.
Examining the Buffer Cache Usage Pattern for All Segments
Examining the Buffer Cache Usage Pattern for a Specific Segments
Configuring the KEEP Pool
Configuring the RECYCLE Pool.
Configuring the Redo Log Buffer
Sizing the Redo Log Buffer
Using Redo Log Buffer Statistics
Configuring the Database Caching Mode
Default Database Caching Mode.
Force Full Database Caching Mode.
Determining When to Use Force Full Database Caching Mode
Verifying the Database Caching Mode.

14 Tuning the Shared Pool and the Large Pool

About the Shared Pool
Benefits of Using the Shared Pool.
Shared Pool Concepts.
Library Cache Concepts
Data Dictionary Cache Concepts
SQL Sharing Criteria.
Using the Shared Pool
Use Shared Cursors.
Use Single-User Logon and Qualified Table Reference
Use PL/SQL.
Avoid Performing DDL Operations.
Cache Sequence Numbers
Control Cursor Access.
Controlling Cursor Access Using OCI.
Controlling Cursor Access Using Oracle Precompilers
Controlling Cursor Access Using SQLJ
Controlling Cursor Access Using JDBC
Controlling Cursor Access Using Oracle Forms
Maintain Persistent Connections
Configuring the Shared Pool
Sizing the Shared Pool.
Using Library Cache Statistics
Using the V$LIBRARYCACHE View
Calculating the Library Cache Hit Ratio.
Viewing the Amount of Free Memory in the Shared Pool.
Using Shared Pool Advisory Statistics
About the V$SHARED_POOL_ADVICE View.
About the V$LIBRARY_CACHE_MEMORY View.
About V$JAVA_POOL_ADVICE and V$JAVA_LIBRARY_C
Using Dictionary Cache Statistics.
Increasing Memory Allocated to the Shared Pool.
Reducing Memory Allocated to the Shared Pool
Deallocating Cursors
Caching Session Cursors.
About the Session Cursor Cache
Enabling the Session Cursor Cache.
Sizing the Session Cursor Cache.
Sharing Cursors.
About Cursor Sharing
Forcing Cursor Sharing.
Keeping Large Objects to Prevent Aging.
Configuring the Reserved Pool
Sizing the Reserved Pool
Increasing Memory Allocated to the Reserved Pool.
Reducing Memory Allocated to the Reserved Pool
Configuring the Large Pool
Configuring the Large Pool for Shared Server Architecture.
Configuring the Large Pool for Parallel Query
Sizing the Large Pool.
Limiting Memory Use for User Sessions
Reducing Memory Use Using Three-Tier Connections

15 Tuning the Result Cache

About the Result Cache
Server Result Cache Concepts
Benefits of Using the Server Result Cache.
Understanding How the Server Result Cache Works.
How Results are Retrieved in a Query.
How Results are Retrieved in a View.
Client Result Cache Concepts.
Benefits of Using the Client Result Cache
Understanding How the Client Result Cache Works.
Configuring the Result Cache
Configuring the Server Result Cache.
Sizing the Server Result Cache Using Initialization Parameters
Managing the Server Result Cache Using DBMS_RESULT_CA
Viewing Memory Usage Statistics for the Server Result Ca
Flushing the Server Result Cache.
Configuring the Client Result Cache
Setting the Result Cache Mode
Requirements for the Result Cache.
Read Consistency Requirements
Query Parameter Requirements.
Restrictions for the Result Cache
Specifying Queries for Result Caching
Using SQL Result Cache Hints
Using the RESULT_CACHE Hint
Using the NO_RESULT_CACHE Hint
Using the RESULT_CACHE Hint in Views.
Using Result Cache Table Annotations
Using the DEFAULT Table Annotation.
Using the FORCE Table Annotation.
Monitoring the Result Cache

16 Tuning the Program Global Area

About the Program Global Area
Work Area Sizes.
Sizing the Program Global Area
Configuring Automatic PGA Memory Management.
Setting the Initial Value for PGA_AGGREGATE_TARGET.
Monitoring Automatic PGA Memory Management.
Using the V$PGASTAT View
Using the V$PROCESS View.
Using the V$PROCESS_MEMORY View.
Using the V$SQL_WORKAREA_HISTOGRAM View.
Using the V$WORKAREA_ACTIVE View.
Using the V$SQL_WORKAREA View.
Tuning PGA_AGGREGATE_TARGET
Enabling Automatic Generation of PGA Performance Advisory
Using the V$PGA_TARGET_ADVICE View.
Using the V$PGA_TARGET_ADVICE_HISTOGRAM View
Using the V$SYSSTAT and V$SESSTAT Views
Tutorial: How to Tune PGA_AGGREGATE_TARGET
Limiting the Size of the Program Global Area
About PGA_AGGREGATE_LIMIT.
Setting PGA_AGGREGATE_LIMIT

17 I/O Configuration and Design

About I/O
I/O Configuration
Lay Out the Files Using Operating System or Hardware Striping.
Requested I/O Size.
Concurrency of I/O Requests
Alignment of Physical Stripe Boundaries with Block Size Bound
Manageability of the Proposed System.
Manually Distributing I/O
When to Separate Files
Tables, Indexes, and TEMP Tablespaces
Redo Log Files
Archived Redo Logs
Three Sample Configurations.
Stripe Everything Across Every Disk.
Move Archive Logs to Different Disks.
Move Redo Logs to Separate Disks
Oracle Managed Files
Choosing Data Block Size
Reads
Writes
Block Size Advantages and Disadvantages
I/O Calibration Inside the Database
Prerequisites for I/O Calibration.
Running I/O Calibration
I/O Calibration with the Oracle Orion Calibration Tool
Introduction to the Oracle Orion Calibration Tool.
Orion Test Targets
Orion for Oracle Administrators.
Getting Started with Orion
Orion Input Files.
Orion Parameters.
Orion Required Parameter
Orion Optional Parameters.
Orion Command Line Samples.
Orion Output Files
Orion Sample Output Files
Orion Troubleshooting

18 Managing Operating System Resources

Understanding Operating System Performance Issues
Using Operating System Caches.
Asynchronous I/O
FILESYSTEMIO_OPTIONS Initialization Parameter
Limiting Asynchronous I/O in NFS Server Environments.
Memory Usage.
Buffer Cache Limits.
Parameters Affecting Memory Usage.
Using Operating System Resource Managers.
Resolving Operating System Issues
Performance Hints on UNIX-Based Systems.
Performance Hints on Windows Systems
Performance Hints on HP OpenVMS Systems
Understanding CPU
Resolving CPU Issues
Finding and Tuning CPU Utilization.
Checking Memory Management.
Paging and Swapping .
Oversize Page Tables .
Checking I/O Management.
Checking Network Management.
Checking Process Management.
Scheduling and Switching .
Context Switching .
Starting New Operating System Processes.
Managing CPU Resources Using Oracle Database Resource Manager
Managing CPU Resources Using Instance Caging