|
|
|
ORA02DBA-9i-03-SG
Oracle 9i Database Performance Tuning (5 days)
Description
This course presents techniques for finding performance or configuration problems within the Oracle database and solving those problems. Manual techniques as well as those employing the specialty Packs within Oracle Enterprise Manager are considered. Workshop exercises allow participants the opportunity to examine the internal operation of an Oracle instance, isolate problems, test possible solutions and implement effective remedies. This course was formerly called “Oracle 9i Database Tuning & Troubleshooting”.
Audience
Oracle database administrators.
Mandatory Prerequisites
• The Sideris course Introduction To Oracle9i: SQL
• The Sideris course Introduction To Oracle9i: PL/SQL Language
• The Sideris course Introduction To Oracle9i: Advanced SQL
• The Sideris course Oracle 9i Database Administration - DBA I
• The Sideris course Oracle 9i Database Backup & Recovery - DBA II
Suggested Next Courses
• The Sideris course Oracle 9i New & Advanced Features For DBAs
Objectives
• Avoid locking and contention problems
• Tune and troubleshoot SGA buffers
• Tune and troubleshoot the shared pool
• Tune and troubleshoot data file configuration problems
• Use utlBstat and utlEstat
Course Outline
CONFIGURING & TUNING AUTOMATIC UNDO SPACE MANAGEMENT • ABOUT UNDO SPACE • CONFIGURING AUTOMATIC UNDO SPACE MANAGEMENT • TUNING AUTOMATIC UNDO SPACE MANAGEMENT
TUNING MANUAL ROLLBACK SEGMENTS
USING THE SQL TUNING UTILITIES TKPROF & AUTOTRACE • ABOUT THE SQL EXECUTION ENVIRONMENT • ABOUT AUTOTRACE
USING THE DATABASE TUNING UTILITIES • ABOUT COLLECTING STATISTICS • ABOUT THE UTLBSTAT & UTLESTAT UTILITIES • ABOUT THE STATSPACK UTILITY • THE OEM PERFORMANCE MANAGER
MEMORY TUNING (BUFFER CACHE) • ABOUT THE MEMORY STRUCTURES • ABOUT THE BUFFER CACHE • MONITORING THE BUFFER CACHE • TUNING THE BUFFER CACHE • SPECIALTY TUNING TECHNIQUES
MEMORY TUNING (SHARED POOL LIBRARY CACHE) • MONITORING THE LIBRARY CACHE • TUNING THE LIBRARY CACHE • MONITORING ADJUSTMENTS TO THE SGA • V$SGA_CURRENT_RESIZE_OPS • V$SGA_DYNAMIC_COMPONENTS • V$SGA_RESIZE_OPS • V$SGA_DYNAMIC_FREE_MEMORY
ADDITIONAL MEMORY TUNING • MONITORING & TUNING THE DICTIONARY CACHE • UTILIZING THE LARGE POOL • MONITORING & TUNING THE PGA/UGA • V$SESSTAT, V$STATNAME • V$PROCESS • V$SYSSTAT • V$SQL_WORKAREA_HISTOGRAM • V$SQL_WORKAREA_ACTIVE • V$PGASTAT • V$PGA_TARGET_ADVICE • V$PGA_TARGET_ADVICE_HISTOGRAM • MONITORING & TUNING THE REDO LOG BUFFER
FILE I/O TUNING • MONITORING DATABASE I/O • TUNING DATABASE I/O
MONITORING LATCHES, LOCKS & CONTENTION • V$LOCK • V$LOCK Columns • V$LOCKED_OBJECT • V$SESSION • V$SESSION_WAIT • DBA_BLOCKERS & DBA_WAITERS • DBA_DML_LOCKS & DBA_DDL_LOCKS • DML_LOCKS
TROUBLESHOOTING USING THE DBMS_REPAIR() PACKAGE • USING THE DBMS_REPAIR() PROCEDURES • ADMIN_TABLES() • CHECK_OBJECT() • FIX_CORRUPT_BLOCKS() • SKIP_CORRUPT_BLOCKS() • DUMP_ORPHAN_KEYS() • REBUILD_FREELISTS() • SEGMENT_FIX_STATUS()
RESUMABLE SPACE ALLOCATION • MANAGING RESUMABLE SPACE ALLOCATION • IMPLEMENTING SPECIAL FEATURES
UTILIZING LOGMINER • USING THE LOGMINER PACKAGE • DBMS_LOGMNR_D.BUILD() • DBMS_LOGMNR() • DBMS_LOGMNR.ADD_LOGFILE() • DBMS_LOGMNR.START_LOGMNR() • DBMS_LOGMNR.END_LOGMNR() • USING THE RESULTS OF LOGMINER ANALYSIS • USING THE LOGMINER VIEWER
DATABASE RESOURCE MANAGER • PLANS, GROUPS AND DIRECTIVES • CPU • PROCEDURES AND OPERATIONS • A SIMPLE RESOURCE PLAN • CREATE_SIMPLE_PLAN Procedure • SYSTEM_PLAN • ADMINISTERING COMPLEX RESOURCE PLANS
|
|
|
|
| Round out your understanding of performance tuning by adding these tuning skills to your repertoire. |
|
 |
|
|
|
|
|
|
