Oracle 9i Performance Tuning for DBAs

Request more details:

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
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: