This course will introduce participants to a series of tuning steps which can be used to improve the performance of the Oracle8i Server. The focus is on database rather than specific operating system performance issues. Participants will gain practical experience tuning an Oracle database. Using a variety of tools, participants also learn how to recognize, troubleshoot and resolve common performance related problems in administering an Oracle database

Course Objectives:
Detect and resolve latch and lock contention problems within the Oracle database environment
Diagnose and resolve performance issues associated with the multithreaded server
List the options to enhance performance tuning requirements for different types of applications
Use Oracle tools to diagnose performance problems
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:
Oracle Performance Tuning Methodology
Listing the different roles associated with the tuning process
Defining the steps associated with the tuning process
Identifying different tuning goals

Oracle Alert and Trace Files
Identifying the location and usefulness of the alert log file
Identifying the location and usefulness of the background and user process trace files

Utilities and Dynamic Performance Views
Collecting statistics using the dynamic troubleshooting and performance views
Diagnosing statistics using the UTBSTAT/UTLESTAT output report
Identifying the different types of latches
Retrieving and displaying wait events
Setting events through OEM to be alerted about predefined situations
Using appropriate OEM tuning tools

Tuning the Shared Pool
Tuning the library cache and data dictionary cache
Measuring the shared pool hit percentage
Sizing the shared pool appropriately
Pinning objects in the shared pool
Tuning the shared pool reserved space
Listing the UGA and session memory considerations

Tuning the Buffer Cache
Describing the how the buffer cache is managed
Calculating the buffer cache hit ratio
Examining the impact of adding or removing buffers
Creating multiple buffer pools
Sizing multiple buffer pools
Monitoring buffer cache usage
Making appropriate use of table caching
Identifying performance issues associated with buffer cache latches

Tuning the Redo Log Buffer
Determining if processes are waiting for space in the redo log buffer
Sizing the redo log buffer appropriately
Reducing redo operations
Identifying performance issues associated with the redo buffer latches

Database Configuration and I/O Issues
Diagnosing inappropriate use of SYSTEM, RBS, TEMP, DATA and INDEX tablespaces
Detecting I/O problems
Ensuring that files are distributed to minimize I/O contention
Using striping where appropriate
Tuning checkpoints
Tuning background process I/O

Using Oracle Blocks Efficiently
Determining an appropriate block size
Optimizing space usage within blocks
Detecting and resolving row migration
Monitoring and tuning indexes
Appropriately sizing extents

Optimizing Sort Operations
Identifying the SQL operations that require sorts
Ensuring that sorting is done in memory where possible
Allocating temporary space appropriately

Rollback Segment Tuning
Using the dynamic performance views to check rollback segment performance
Reconfiguring and monitoring rollback segments
Defining the number and sizes of rollback segments
Allocating rollback segments to specific transactions

Monitoring and Detecting Lock Contention
Defining the levels of Oracle locking
Listing possible causes of lock contention
Using Oracle utilities to diagnose lock contention
Resolving contention in an emergency
Preventing locking problems
Recognizing Oracle errors arising from deadlocks

SQL Issues and Tuning Considerations for Different Applications
Identifying the role of the DBA in application tuning
Using optimizer modes to enhance SQL statement performance
Managing stored outlines to store execution paths as a series of hints
Using the available data access methods to tune the physical design of the database. Identify the demands of online transaction processing systems (OLTP).
Identify the demands of decision support system.

Managing Mixed Workload
Resource Management Concepts
Steps in Database Resource Management
Dictionary View

Tuning with Oracle Expert
Listing the features of Oracle Expert
Creating a tuning session
Gathering, viewing, and editing the input data
Analyzing the collected data using rules
Reviewing tuning recommendations
Implementing tuning recommendations

Multithreaded Server Tuning Issues (Optional)
Identifying issues associated with managing users in a multithreaded server environment
Diagnosing and resolving performance issues with multithreaded server processes
Configuring the multithreaded server environment to optimize performance

Use a tuning methodology to diagnose and resolve performance tuning issues
Using Oracle tools to diagnose performance problems
Tuning memory structures, file I/O, and contention