Oracle 9i Performance Tuning for DBAs

This course has been superseded

We suggest the following instead:
Oracle SQL and PL/SQL Performance Tuning Tips and Techniques

Course Description

This course provides you with the knowledge and hands-on experience of SQL tuning, database tuning and advanced performance design.

View other Oracle Courses available
5 Days
Contact us for pricing
 

Prerequisites

Oracle Foundation Classes, Oracle DBA I or equivalent experience.

Performance Tuning Duties of the DBA

DBA`s Performance Tuning Duties
Steps for Tuning
Tuning goals for different types of applications
The Performance tuning Team
Tuning Tools

Tune the Logical Structure of the Database

Overview
Table/Tablespace Considerations
Free Space Management
Chained and Migrated Rows
Index Types
Materialised Views/Snapshots
Query Rewrite

Tune Database Operations

SQL Parsing and Execution
Shared SQL Statements
Contents of the SQL Area
Segment Space Management
Automatic Undo Management
Understanding Statement Transformations
Optimising SQL Syntax

SQL Tuning Tools

Rule Based and Cost Based Optimisation
Table and Index Statistics
Gathering Statistics
Execution Plans
SQL *Plus and AUTOTRACE
SQL_TRACE and TKPROF
Hints
Store Outlines/Plan Stability

DBA Tuning Tools

SPFILE/Init.Ora
V$ Views
UTLBSTAT / UTLESTAT
STATSPACK

Tuning Memory

Tune OS Memory
Tune Database Buffer Cache
Memory Subcaches
Tune Redo Log Buffer
Tune Private SQL and PL/SQL Areas
Tune the Shared Pool
Tune Sorts
Tuning the Shared Server
Tuning the Java Pool
SGA/PGA Tuning

Tuning I/O

RAID
Oracle File 1/O
Partitioning Tables and Indexes
Checkpointing

Tuning Contention and Understanding the Resource Manager

Shared Server Contention
Tune Lock Contention
Tune Contention for Undo and Temporary Segments, and for Latches
Introduction to the Resource Manager

Tuning the Shared Server

Issues associated with managing users in a Shared Server environment
Diagnose and resolve performance problems
Configure shared servers to optimise performance

Detecting and tuning lock contention

Define locking levels
Describe causes of contention
Use Oracle tools to detect lock contention
Emergency lock contention resolution
Prevent locking problems
Recognise deadlocks

PL/SQL Packages for the Tuner

DBMS_APPLICATION_INFO
DBMS_IOT
DBMS_ORACLE_TRACE_AGENT
DBMS_ORACLE_TRACE_USER
DBMS_PROFILER
DBMS_RESOURCE_MANAGER_PRIVS
DBMS_SESSION
DBMS_SHARED_POOL
DBMS_SPACE
DBMS_STATS
DBMS_TRACE
DBMS_UTILITY

Performance Tuning Checklist & Workshop


OracleOracle 9iOracle Database 9i