Oracle9i Database: Advanced Instance Tuning

This seminar introduces a proven performance tuning methodology. Focusing on the internal implementation of areas of concern when tuning a database server, students learn how to tune enqueues, latches, and different memory areas used by the Oracle server: the buffer cache, the library cache, PGA, UGA, and CGA.

This course counts towards the Hands-on course requirement for the Oracle 9i Database Administrator and Oracle Database 10g Administrator Certification.

A Live Virtual Class (LVC) is exclusively for registered students; unregistered individuals may not view an LVC at any time. Registered students must view the class from the country listed in the registration form. Unauthorized recording, copying, or transmission of LVC content may not be made.

Audience

• Support Engineer
• Data Warehouse Administrator
• Database Administrators

Course Topics Performance Tuning Methodology

• Understanding Workload Characteristics
• A Six Steps Methodology
• The Database Server Stack

Enqueue Structures

• Enqueue Identifiers
• Enqueue Modes and Compatibility
• Hashing and Latching
• Lock Acquisition and Conversion
• Enqueue Wait Events
• Deadlock Detection
• Enqueue Diagnostics

Lock Contention

• Data Dictionary Locks
• Row Cache Locks and Library Cache Locks
• DML Table and Row Level Locks
• Transaction Locks
• Buffer Locks
• Contention
• Coalescing Free Space
• Temporary Segment Cleanup

Latch Internals

• Latch Characteristics and Implementation
• Latch Acquisition and Release
• The Latch Free Wait Event
• Latch Contention and Diagnostics

Memory Management

• The SGA Components
• Heaps and Subheaps
• The Shared Pool
• The Reserved Pool
• Keeping Objects in Memory
• The Large Pool
• The Java Pool
• PGA, UGA, and CGA

The Buffer Cache

• Hash Buckets and Chains
• LRU Lists and Working Sets
• Buffer Management
• Checkpoints
• Buffer Cache Latches and Wait Events
• Buffer Cache and DBWR Statistics
• Tuning the Buffer Cache

The Library Cache

• Shared Cursors
• Hash Buckets and Object Handles
• Locks and Pins
• Library Cache Latches
• Library Cache Contention
• Library Cache Diagnostics

Redo and Archiving

• Log Force at Commit
• The Redo Log Buffer
• The LGWR Algorithm
• Redo Wait Events
• Redo Latch Contention
• NOLOGGING Operations
• Tuning Archiving

Course Objectives

• Determine whether the Oracle server requires tuning
• Identify which components within the Oracle server need attention
• Describe the internal architecture of enqueues, latches, and the SGA memory areas
• Explain the internal algorithms of background processes such as LGWR and DBWR