• Decrease font size
  • Default font size
  • Increase font size

Oracle9i Database Performance Tuning

 Print E-mail
Course Length - 5 Days
 
Course 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.

 

You Will Learn How To:

At the completion of this course, the student should be able to:

  • 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 Prerequisites

Mandatory Prerequisites:

  • Introduction To Oracle9i: SQL
  • Introduction To Oracle9i: PL/SQL Language
  • Introduction To Oracle9i: Advanced SQL
  • Oracle9i Database Administration - DBA I
  • Oracle9i Database Backup & Recovery - DBA II
 

Who Should Attend?

Target audience for this course is Oracle database administrators.

 

Next Suggested Course

  • Oracle9i New & Advanced Features For DBAs

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