Overview of Database Performance Tuning
The Oracle RDBMS as a data repository
The Oracle RDBMS as an SQL engine
How Oracle determines an execution plan
The work involved in determining an execution plan
The work involved in executing an execution plan
Trade-offs in relation design
Tuning During Design
Tuning During Development
Tuning a Production Database
Tuning Methodology
Performance Versus Safety Trade-Offs
Common Performance Problems
Gathering Performance Information
Background Processes and Trace Files
User Trace Files
Statistics and Wait Events
Viewing statistics and wait events
Dictionary and Special Views
Displaying System wide Statistics
Displaying Session-Related Statistics
Oracle Wait Events
Displaying System-wide Wait Events
Displaying Session Wait Events
Reporting on statistics and wait events
Statspack reports
Installing Statspack
Creating Statspack snapshots
Generating Statspack reports
AWR Reports
Generating AWR reports
Generating AWR SQL reports
Generating AWR comparative reports
ASH Reports
Generating ASH reports
Level of Statistics Collection
Troubleshooting and Tuning Views
Index Access and Full Table Scans
Properties of Heap tables
Monitoring Full Table Scan Operations
Table Scan Statistics
The cost of Full Table Scans
B-Tree Indexes
Reverse Key Index
Creating Reverse Key Indexes
Rebuilding Indexes
Compressed Indexes
Bitmap Indexes
Creating and Maintaining Bitmap Indexes
B-Tree Indexes and Bitmap Indexes
Index Reorganization
Monitoring Index Space
The ANALYSE Statement
Deciding Whether to Rebuild or Coalesce an Index
When to use an Index
Which columns to Index
Monitoring Index Usage
Identifying Unused Indexes
Choosing an optimal Table Structure
Data Storage Structures
Selecting the Physical Structure
Data Access Methods
The Default Heap Table
Index-Organized Tables
Index-Organized Tables and Heap Tables
Creating Index-Organized Tables
IOT Row Overflow
Querying dba_ views for IOT Information
Using a Mapping Table
Clusters
Cluster Types
Situations Where Clusters Are Useful
Partitioning Methods
Range Partitioning
Hash Partitioning
List Partitioning
Default Partition for List Partitioning
Composite Partitioned Table
Partitioned Indexes for Scalable Access
Partition Pruning
Partition-Wise Join
Statistics Collection for Partitioned Objects
Optimizing Oracle Block Utilization
Allocation of Extents
Locally Managed Extents
Automatic segment space management
Pros and Cons of Large Extents
Empty space with a segment
The High-Water Mark
Table Statistics
Recovering Space
The Shrink Command
Database Block Size
The DB_BLOCK_SIZE Parameter
Small Block Size: Pros and Cons
Large Block Size: Pros and Cons
PCTFREE and PCTUSED
Guidelines for PCTFREE and PCTUSED
Migration and Chaining
Detecting Migration and Chaining
Identifying Migrated Rows
Eliminating Migrated Rows
Optimizing the use of I/O resources
Oracle Processes and Files
Tablespace Usage
Automatic file extension
Distributing Files Across Devices
Multi-block read count
Performance Guidelines
Using Raw devices
Direct I/O
Synchronous and Asynchronous I/O
Choosing a file system
Disks verses Spindles
Using S.A.M.E
Choosing a R.A.I.D configuration
Using Automatic Storage Management (ASM)
Diagnostic Tools for Checking I/O Statistics
The v$filestat view
The V$segment_statistics view
I/O System statistics and I/O waits
Gathering Optimizer Statistics
Managing Statistics
Table Statistics
Collecting Segment-Level Statistics
Querying Segment-Level Statistics
Using Dynamic Sampling
Enabling Dynamic Sampling
Index Statistics
Column Statistics
Histograms
Generating Histogram Statistics
Gathering Statistic Estimates
Automatic Statistic Collecting
Using System Statistics
Gathering System Statistics
Automatic Gathering of System Statistics
Manual Gathering of System Statistics
Import System Statistics Example
Copying Statistics Between Databases
Diagnosing and Resolving Contention
Oracle's Locking Mechanisms
Latches, Mutexes and Locks
What is a Latch?
What is a Mutex?
Accessing Memory Structures
Parent and child Latches
Immediate mode and willing to wait mode
Latch/Mutex related views, events and statistics
Resolving Latch/Mutex contention
What are Locks?
Data Concurrency
Locking Mechanism
Types of Locks
DML Locks
Enqueue Mechanism
Table Lock Modes
Manually Locking a Table
DML Locks in Blocks
DDL Locks
Possible Causes of Lock Contention
Diagnostic Tools for Monitoring Locking Activity
Guidelines for Resolving Contention
Deadlocks
Application design and contention points
Minimizing Connection Management Load
Performance and resource issues
Listener load Issues
Configuring Multiple Listeners
Distributing connections across Listeners
Configuring Listener Fail over
Monitoring Process Usage
Configuring Pre-spawned servers
Configuring Shared Servers
Configuring Connection Multiplexing
Configuring Connection Manager
Configuring Connection Pooling
Monitoring Shared Servers
Monitoring Dispatchers
Shared Servers and Memory Usage
Troubleshooting
Managing the Shared Pool
The Shared Pool
The Library Cache
Important Shared Pool Latches
Shared Pool and Library Cache Latches
Tuning the Library Cache
Terminology
Diagnostic Tools for Tuning the Library Cache
Sharing Cursors
Library Cache Guidelines
Invalidations
Sizing the Library Cache
Shared Pool Advisory
Cached Execution Plans
Views to Support Cached Execution Plans
Global Space Allocation
Large Memory Requirements
Tuning the Shared Pool Reserved Space
Keeping Large Objects
Anonymous PL/SQL Blocks
Other Parameters Affecting the Library Cache
Tuning the Data Dictionary Cache
Diagnostic Tools for the Data Dictionary Cache
Measuring the Dictionary Cache Statistics
Tuning the Data Dictionary Cache
Guidelines: Dictionary Cache
Sizing the shared pool
Optimizing the use of the Buffer Cache
Buffer Cache Characteristics
Buffer Cache Sizing Parameters
Buffer Cache Advisory
Managing the Database Buffer Cache
Tuning Goals and Techniques
Diagnostic Tools
Buffer Cache Performance Indicators
Measuring the Cache Hit Ratio
What does a Cache Hit Ratio really tell us?
Guidelines to Increase the Cache Size
Using Multiple Buffer Pools
Defining Multiple Buffer Pools
Enabling Multiple Buffer Pools
KEEP Buffer Pool Guidelines
RECYCLE Buffer Pool Guidelines
Calculating the Hit Ratio for Multiple Pools
Identifying Candidate Pool Segments
Dictionary Views with Buffer Pool Information
Caching Tables
Free Lists
Diagnosing Free List Contention
Resolving Free List Contention
Automatic Segment Space Management
Auto-Management of Free Space
Multiple DBWn Processes
Multiple I/O Slaves
Optimizing The Redo Chain
The Redo Log Buffer
Sizing the Redo Log Buffer
Diagnosing Redo Log Buffer Inefficiency
Redo Log Buffer Tuning Guidelines
Redo Log Groups and Members
Online Redo Log File Configuration
Sizing Online Redo Log files
Diagnosing Redo Log File Inefficiency
Reducing Redo Operations
Tuning Instance Recovery
Setting Fast Start MTTR target
Monitor performance impact of MTTR target
MTTR and Checkpointing
Log file Size and Checkpointing
Diagnose checkpoint and redo issues
Tuning Archiving performance
Dynamic and Automatic Memory Management
Dynamic SGA Features
Automatic Shared Memory
Enable Automatic Shared Memory
Manually setting auto-tuned memory parameters
Setting the manually tuned SGA parameters
Allocation units and Dynamic SGA
Using the SGA advisor
Tuning the SQL Work Area
The SQL work area
SQL memory usage
Temporary Tablespace usage
Automatic SQL Memory Management
The pga_aggregate_target Parameter
Work Area Groups
PGA Cache Hit Percentages
Determining PGA Workload
The PGA Advisor
Views for SQL Work Areas
Manual Parameters effecting PGA usage
Sort Area Parameters
The Sorting Process
Tuning Sorts
The Sorting Process and Temporary Space
Temporary Space Segments
Operations Requiring Sorts
Avoiding Sorts
Diagnostic Tools
Diagnostics and Guidelines
Monitoring Temporary Tablespaces
Temporary Tablespace Configuration
Temporary Tablespace Groups
Tuning the Operating System
Operating System Tuning
System Architectures
Virtual and Physical Memory
Paging and Swapping
Tuning Memory
Tuning I/O
CPU Tuning
O/S Monitoring tools (Linux)
Using top
Using ps and pstree
Using vmstat
Using ipcs and ipcrm
Using iostat
Using mpstat
Using sar
Using netstat