• Utilization of advanced self-tuning and self-management capabilities, including the use of the database Advisors and other components of the Management Framework.
• Performance monitoring, problem troubleshooting, and resolution.
• Detailed scrutiny of database activity using statistics and wait events, as well as a review of the underlying mechanisms and computations involved in the automatic tuning features of the Oracle10g release.
• Manual database tuning using Statspack.
• Tuning of the shared pool, buffer cache and shared memory structures, including little known techniques to address unique circumstances and challenges.
• Tuning recovery structures such as checkpoints and redo data.
• Tuning physical storage structures such as file I/O and database blocks, detecting and reducing fragmentation of physical storage.
• Tuning specialty structures such as the PGA and temporary space.
• Use of specialty performance tuning features, such as metrics, metric baselines, and alerts.

COURSE INTRODUCTION

PERFORMANCE TUNING OVERVIEW • DATABASE PERFORMANCE TUNING CONTEXT • THE DATABASE INSTANCE TUNING METHODOLOGY

STATISTICS & WAIT EVENTS • ABOUT THE DYNAMIC PERFORMANCE VIEWS • EXAMINING THE STATISTICS • INTERPRETING THE STATISTICS

USING STATSPACK • INSTALL THE STATSPACK UTILITY • CREATE STATSPACK SNAPSHOTS • GENERATE STATSPACK REPORTS • IDENTIFY THE REPORT SECTIONS

AUTOMATIC TUNING & AWR • ABOUT THE MANAGEMENT & ADVISORY FRAMEWORK • MANAGING THE AUTOMATIC WORKLOAD REPOSITORY • ABOUT THE ACTIVE SESSION HISTORY (ASH) • AUTOMATIC DATABASE DIAGNOSTIC MONITOR • MANAGING SNAPSHOTS • CREATE_BASELINE() & DROP_BASELINE() • CREATE_SNAPSHOT() & DROP_SNAPSHOT_RANGE()

METRICS, ALERTS & BASELINES • METRICS & ALERT THRESHOLDS • MANAGING METRICS • MANAGING ALERTS • USE EM REAL-TIME MONITORS

TUNING MEMORY: THE SHARED POOL • MORE ABOUT THE SHARED POOL • DIAGNOSE SHARED POOL PROBLEMS • KEEP OBJECTS IN THE SHARED POOL

TUNING MEMORY: THE BUFFER CACHE • MORE ABOUT THE BUFFER CACHE • REMEDY PERFORMANCE ISSUES

TUNING MEMORY: AUTOMATIC SHARED MEMORY • AUTOMATIC SHARED MEMORY MANAGEMENT • USING EM & THE MEMORY ADVISORS

TUNING I/O: CHECKPOINT & REDO TUNING • TUNE REDO WRITING & ARCHIVING • MTTR ADVISOR & TUNING CHECKPOINTS • LOG_CHECKPOINTS_TO_ALERT • ADDITIONAL REDO TUNING OPTIONS

TUNING I/O: DATABASE WRITES

SPACE TUNING: PGA & TEMPORARY SPACE • PGA MEMORY MANAGEMENT • TEMPORARY SPACE MANAGEMENT

SPACE TUNING: BLOCK SPACE USAGE • USING THE SHRINK SPACE OPTION • USING THE SEGMENT ADVISOR