Oracle9i Database Performance Tuning

This 5-day 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.

Target audience

This course has been developed for Oracle database administrators. Students should have attended the following courses or have equivalent knowledge and experience:

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

Course content

Configuring and tuning automatic undo space management About undo space • Configuring automatic undo space management • Tuning automatic undo space management

Tuning manual rollback settings

Using the SQL tuning utilities TKPROF and AUTOTRACE About the SQL execution environment • About AUTOTRACE

Using the database tuning utilities About collecting statistics • About the UTLBSTAT and UTLESTAT utilities • About the STATSPACK utility • The OEM performance buffer

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 and tuning the dictionary cache • Utilizing the large pool • Monitoring and tuning the dictionary cache • V$SESSTAT • V$STATNAME • V$PROCESS • V$SSYSTAT • V$SQL_WORKAREA_HISTOGRAM • V$SQL_WORKAREA_ACTIVE • V$PGASTAT • V$PGA_TARGET_ADVICE • V$PGA_TARGET_ADVICE_HISTOGRAM • Monitoring and tuning the redo log buffer

File I/O Tuning Monitoring database I/O • Tuning database I/O

Monitoring latches, locks and contention V$LOCK • V$LOCK columns • V$LOCKED_OBJECT • V$SESSION • V$SESSION_WAIT • DBA_BLOCKERS • DBA_WAITERS • DBA_DML_LOCKS and 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

About Courseware Company :: Instructor-led training courseware :: Self-study solutions :: Buy online today