Oracle 10g Database Tuning
Objective:
This course is aimed at the DBAs who often have to make the first attempt at solving performance problems prior to, and as they develop. We cover all aspects of internal database tuning on an Oracle 10g platform in this three-day course.
The enhancements and changes that have been introduced in 10g will be discussed during this course for the benefit of DBA's responsible for earlier RDBMS versions for the purposes of planning and upgrade tuning.
course outline:
Tuning Overview ( Tuning Questions - Tuning Goals - Steps )
Oracle Alert Trace Files & Events
- Diagnostic Information
- The Alert Log File
- User Trace Files
- Statistics Event Views
Dynamic Performance Views & Utilities
- Collecting System-Wide Statistics
- Collecting Session Related Statistics
- Statspack
Oracle Wait Events
- I/O related events
- Lock related events
- Latency related events
- Gathering wait event data
Automatic Workload Repository
- Automated snapshot collection
- Active Session History
Automatic Database Diagnostics Monitor
- Enterprise Manager Advisors
- ADDM reports
- Time / Wait model statistics
Tuning the Shared Pool
- The Shared Global Area
- Tuning the Library Cache
- The Data Dictionary Cache
- User Global Area and Shared Server
Tuning the Buffer Cache … Evaluating the Cache Hit Ratio
- Adding Buffers
- Using Multiple Buffer Pools
- Defining Multiple Buffer Pools
- Caching Tables
Tuning The Redo Log Buffer
- Sizing The Redo Log Buffer
- Reducing Redo Operations
SQL Tuning
- Optimizer Modes
- Diagnostic Tools
- V$SQL_PLAN
- Monitoring table and index useage
DBMS_STATS
- Gathering statistics
- Changing the data dictionary statistics
- Automating statistics collection
Optimizer plan stability
- Creating stored outlines
- Using stored outlines
- Viewing the execution paths
The OUTLN user Materialized Views .. Creating
- Registering existing summary tables
- Refresh options
Database Configuration and I/O Issues
- Tablespace Usage
- Partitioning Tables and Indexes
- Distributing Files Across Devices
- Tuning Checkpoints
- Automatic Storage Management
Using Oracle Blocks Efficiently
- Database Storage Hierarchy
- Database Block Size
- Multiple Block Sizes
- PCTFREE and PCTUSED
- Automatic Space Segment Management
- Detecting Chaining
- Monitoring and Rebuilding Indexes
Tuning Rollback Segments
- Rollback Segment Usage
- Tools for Tuning Rollback Segments
- Using Less Rollback
- Using Automatic Undo Management
Tuning the Operating System
- CPU Tuning Guidelines
- Tuning Memory
- Tuning I/O
Optimizing Sorts
- Sort Area and Parameters
- Tuning Sorts
- Avoiding Sorts
- Configuring Temporary Tablespaces
- Database Default Temporary Tablespace
- Automatic PGA Management
Tuning Different Applications
- Comparing B-Tree and Bitmap Indexes
- Creating Reverse Key Indexes
- Index-Organized Tables
- Clusters / Cluster Types
- Histograms
Database Resource Manager
- Creating Consumer groups and adding users
- Allocating resources to groups
- Activating resource plans
Lock Contention
- Locking Mechanisms
- Tools for Monitoring Locking Activity
- Resolving Contention
- Deadlocks
Contention Issues
- Diagnosing Latch Problems
- Resolving Latch contention
- Diagnosing Free List Contention
- Resolving Free List Contention