Title and Copyright Information
Preface
- Audience
- Documentation Accessibility
- Related Documents
- Conventions
Changes in This Release for Oracle Database Performance Tuning Guide
- Changes in Oracle Database 12c Release 1 (12.1.0.2)
- New Features
- Changes in Oracle Database 12c Release 1 (12.1.0.1)
- New Features
- Other Changes
Part I Database Performance Fundamentals
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
- Software Components
- Configuring the Right System Architecture for Your Requirements
- Hardware and Software Components
- Application Design Principles
- Simplicity In Application Design
- Data Modeling
- Table and Index Design
- Appending Columns to an Index or Using Index-Organized Tables
- Using a Different Index Type
- 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
- 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
- Undo Space
- Redo Log Files
- Tablespaces
- Creating and Maintaining Tables for Optimal Performance
- Table Compression
- Reclaiming Unused Space
- Indexing Data
- Performance
Considerations for Shared Servers
- Identifying and Reducing Contention Using the Dispatcher-Specific Views
- Identifying Contention for Shared Servers
Part II Diagnosing and Tuning Database Performance
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
- Dropping Snapshots
- Modifying Snapshot Settings
- Managing Baselines
- User Interface for Managing Baselines
- Creating a Baseline
- Dropping a Baseline
- Renaming a Baseline
- Displaying Baseline Metrics
- Resizing the Default Moving Window Baseline
- Managing Baseline Templates
- User Interfaces for Managing Baseline Templates
- Creating a Single Baseline Template
- Creating a Repeating Baseline Template
- Dropping a Baseline Template
- 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 Database
- Generating an Oracle RAC AWR Report for a Specific Database
- Generating an AWR Report for a SQL Statement on the Local Database
- Generating an AWR Report for a SQL Statement on a Specific Database
- 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 Active Report
- Generating a Performance Hub Active Report Using a SQL Script
- Overview of Performance Hub Active Report
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-Line Interface
- 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 for the Local Database
- Generating an Oracle RAC AWR Compare Periods Report for a Specific Database
- 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
- 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
- Summary of the AWR Compare Periods Report
9 Analyzing Sampled Data
- About Active Session History
- 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
- Top Events
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 Monitoring Tools
- Identifying Network Issues
- CPU Usage
- Examine the Oracle Database Statistics
- Setting the Level of Statistics Collection
- Wait Events
- Dynamic Performance Views Containing Wait Event Statistics
- System Statistics
- Segment-Level Statistics
- Implement and Measure Change
- Interpreting Oracle Database Statistics
- Examine Load
- Using Wait Event Statistics to Drill Down to Bottlenecks
- Table of Wait Events and Potential Causes
- Additional Statistics
- Wait Events Statistics
- Changes to Wait Event Statistics from Past Releases
- 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
- Tuning Instance Recovery
Performance: Fast-Start Fault Recovery
- About Instance Recovery
- Cache Recovery (Rolling Forward)
- Transaction Recovery (Rolling Back)
- Checkpoints and Cache Recovery
- 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: Scenario
- 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_ADVICE
- Determine the Optimal Size for Redo Logs
- About Instance Recovery
Part III Tuning Database Memory
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
- User Interfaces for Setting the SGA_TARGET Parameter
- 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 Segment
- Configuring the KEEP Pool
- Configuring the RECYCLE Pool
- Considerations for Using Multiple Buffer Pools
- 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_CACHE_MEMORY
- Using Dictionary Cache Statistics
- Increasing Memory Allocated to the Shared Pool
- Reducing Memory Allocated to the Shared Pool
- Using Library Cache Statistics
- 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
- Sizing the
Shared 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
- Server Result Cache Concepts
- 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_CACHE
- Viewing Memory Usage Statistics for the Server Result Cache
- 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
- Configuring the Server 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
- Using SQL Result Cache Hints
- 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 Views
- 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
Part IV Managing System Resources
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 Boundaries
- 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
- Lay Out the Files Using Operating System or Hardware
Striping
- 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
- Introduction to the Oracle Orion Calibration Tool
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
- Using Operating System Caches
- 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
- Checking Memory Management
- Managing CPU Resources Using Oracle Database Resource Manager
- Managing CPU Resources Using Instance Caging
- Finding and Tuning CPU Utilization