What you will learn:
This class will cover Oracle9i and Oracle9i Release 2 features. Database performance tuning is critical to any effective information system. Reducing wait times, increasing users' productivity and maximizing the performance of your Oracle database are critical to success. Learn from the experts the value of intelligent database design and methods for tuning an Oracle database for maximum performance. This course will introduce participants to the importance of good initial database design, and the method used to tune a production Oracle9i database. The focus is on Database and Instance tuning rather than specific operating system performance issues. Participants will gain practical experience tuning an Oracle database. Using the available Oracle tools, such as Oracle Enterprise Manager (with the Diagnostics and Tuning Packs) and STATSPACK participants also learn how to recognize, troubleshoot and resolve common performance related problems in administering an Oracle database.

Audience:
Database Administrators

Prerequisites:
Introduction to Oracle9i: SQL
Oracle9i Database Administration Fundamentals I
Oracle9i Database Administration Fundamentals II

Course Objectives:
Configure Resource Manager to control resource usage
Configure an Oracle Database with an emphasis on good performance.
Detect and resolve latch and lock contention problems within the Oracle database environment
Diagnose and resolve performance issues associated with Shared Server
Use Oracle tools to diagnose performance problems
Utilize Enterprise Manager to perform Tuning tasks
Perform day-to-day monitoring on an operational database using STATSPACK
Identify and resolve I/O, data storage, and database configuration problems
Configure memory and disk resources to optimize sort operations
Reconfigure file structures for performance and other considerations
List the important steps for outlining a tuning methodology

Course Topics:

Overview of Oracle 9i Performance Tuning
Job Roles in Tuning
Tuning phases
Tuning goals and Service Level Agreements
Common performance problems
Tuning Methodology

Diagnostic and Tuning Tools
Alert log file
Background process trace files
User trace files
Dictionary views providing statistics
Dynamic performance views
TIMED_STATISTICS parameter to collect statistics
Statistics Package
STATSPACK procedures

Sizing the Shared Pool
Overview of the shared pool
Library cache tuning
Reuse statements
Using Reserved Space
Keeping Large Objects
Related tuning issues
Data Dictionary Cache (DDC) Tuning

Sizing the Buffer Cache
Overview of tuning the buffer cache
Buffer Cache Sizing Parameters in Oracle9i
Depreciated Buffer Cache Parameters
Buffer Cache Advisory Parameter
Dynamically resizing SGA components
Granules of Allocation
Increase the size of a SGA component
Resolving Techniques

Sizing other SGA Structures
Sizing the redo log buffer
Detecting contention
Resolving contention
Sizing the Java Pool
Monitoring Java Pool Memory
Sizing the SGA for Java
Sizing Java Pool Memory
Limiting Java Session Memory Usage

Database Configuration and I/O Issues
Distributing files across devices
Tablespace usage
Detecting improper tablespace usage
Moving datafiles
Oracle File Striping
Tuning Full Table Scans
Tuning checkpoints
Redo log file configuration

Optimize Sort Operations
What is the sorting process
Sort Area Parameters (New & Old)
Tuning sort operations (DB Configuration)
Tuning Temporary segments
Reducing sort operations (application tuning)
Identify sort operations
Avoiding sort operations
Diagnostic Tools

Diagnosing Contention For Latches
Oracle's Implementation of latches
Latch request types - willing-to-wait, immediate
Reducing Latch Contention
Significant Latches
Shared Pool and Library Cache Latches

Tuning Rollback (or UNDO) Segments
Describing Rollback segment usage
Usage and configuration
Detecting Problems with Rollback Segments
Resolving Problems with Rollback Segments
Creating Rollback Segments and bringing them online
Allocating transactions to a rollback segment
Resolving RBS problems
Describing System Managed Undo

Monitoring and Detecting Lock Contention
Concepts of Locking
Overview of Locking issues
Types of DML locks
Levels of locks
Types of DDL lock modes
Detecting Blocking Locks
Monitoring locking activity
Deadlocks

Tuning Oracle Shared Server
Introducing Oracle Shared Server
When are Shared Servers required in Oracle9i
Monitoring dispatcher processes
Monitoring shared server processes
Increasing or decreasing the number of Dispatchers
Increasing or decreasing the number of Shared Servers
Monitoring Process Usage
Monitoring Memory Usage

Application Tuning
Role of the DBA
Oracle Data Structures
Selecting the physical structure
Data storage structures
Clusters
Indexes
B-Tree
Compressed

Using Oracle Blocks Efficiently
Database Storage hierarchy
Allocating extents
Monitoring Space Usage in Data Segments
Recovering space from sparsely populated segments
Database Blocks
Chaining and migration
Detecting row chaining and migration
Resolving row chaining and migration

SQL Statement Tuning
Cost-based, Rule-based Optimiser Modes
New Cost Based Optimiser option
Plan Stability
Plan Equivalence
Stored Outlines
Creating Stored Outlines
Maintaining Stored Outlines
Hints

Tuning the Operating System and Using Resource Manager
System Architecture
Virtual and Physical Memory
Paging and Swapping
CPU Tuning Guidelines
Process Vs Thread
Resource Manager Concepts

Workshop