Back to Search Results

Training Course Summary:

Everyone involved with the Oracle system; system architects, designers, developers and database administrators (DBA's), should consider performance tuning while carrying out their work.

This course is aimed at the DBA's 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.

Pre-Requisites:

Oracle 10g SQL (O011)
10g DBA Fundamentals I (O1010)
10g DBA Fundamentals II (O1020)

Training Course Overview/Content:

Tuning Overview
Tuning Questions/Goals/Steps
back to top
Alerts, Trace Files & Events
Diagnostic Information
The Alert Log File
User Trace Files
Statistics Event Views
back to top
Dynamic Performance Views
Dynamic Performance Views
Collecting System-Wide Statistics
Collecting Session Related Statistics
Statspack
back to top
Oracle Wait Events
I/O related events
Lock related events
Latency related events
Gathering wait event data
back to top
Automatic Workload Repository
Automated snapshot collection
Active Session History
back to top
Automatic Database Diagnostics
Enterprise Manager Advisors
ADDM reports
Time model statistics
Wait model statistics
back to top
Tuning the Shared Pool
The Shared Global Area
Tuning the Library Cache
The Data Dictionary Cache
User Global Area and Shared Server
back to top
Tuning the Buffer Cache
Evaluating the Cache Hit Ratio
Adding Buffers
Using Multiple Buffer Pools
Defining Multiple Buffer Pools
Caching Tables
back to top
Tuning The Redo Log Buffer
Sizing The Redo Log Buffer
Reducing Redo Operations
back to top
SQL Tuning
Optimizer Modes
Diagnostic Tools
V$SQL_PLAN
Monitoring table and index useage
back to top
DBMS_STATS
Gathering statistics
Changing the data dictionary statistics
Automating statistics collection
back to top
Optimiser Plan Stability
Creating stored outlines
Using stored outlines
Viewing the execution paths
The OUTLN user
back to top
Materialised Views
Creating
Registering existing summary tables
Refresh options
back to top
Database Config. & I/O Issues
Tablespace Usage
Partitioning Tables and Indexes
Distributing Files Across Devices
Tuning Checkpoints
Automatic Storage Management
back to top
Oracle Blocks Efficiency
Database Storage Hierarchy
Database Block Size
Multiple Block Sizes
PCTFREE and PCTUSED
Automaic Space Segment Management
Detecting Chaining
Monitoring and Rebuilding Indexes
back to top
Tuning Rollback Segments
Rollback Segment Usage
Tools for Tuning Rollback Segments
Using Less Rollback
Using Automatic Undo Management
back to top
Tuning the Operating System
CPU Tuning Guidelines
Tuning Memory
Tuning I/O
back to top
Optimizing Sorts
Sort Area and Parameters
Tuning Sorts
Avoiding Sorts
Configuring Temporary Tablespaces
Database Default Temporary Tablespace
Automatic PGA Management
back to top
Tuning Different Applications
Comparing B-Tree and Bitmap Indexes
Creating Reverse Key Indexes
Index-Organized Tables
Clusters / Cluster Types
Histograms
back to top
Database Resource Manager
Creating Consumer groups and adding users
Allocating resources to groups
Activating resource plans
back to top
Lock Contention
Locking Mechanisms
Tools for Monitoring Locking Activity
Resolving Contention
Deadlocks
back to top
Contention Issues
Diagnosing Latch Problems
Resolving Latch contention
Diagnosing Free List Contention
Resolving Free List Contention