Oracle 9i Performance Tuning for DBAs
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
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 DutiesSteps for Tuning
Tuning goals for different types of applications
The Performance tuning Team
Tuning Tools
Tune the Logical Structure of the Database
OverviewTable/Tablespace Considerations
Free Space Management
Chained and Migrated Rows
Index Types
Materialised Views/Snapshots
Query Rewrite
Tune Database Operations
SQL Parsing and ExecutionShared 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 OptimisationTable 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.OraV$ Views
UTLBSTAT / UTLESTAT
STATSPACK
Tuning Memory
Tune OS MemoryTune 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
RAIDOracle File 1/O
Partitioning Tables and Indexes
Checkpointing
Tuning Contention and Understanding the Resource Manager
Shared Server ContentionTune 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 environmentDiagnose and resolve performance problems
Configure shared servers to optimise performance
Detecting and tuning lock contention
Define locking levelsDescribe 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_INFODBMS_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