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.

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

Objectives:
        Design and configure an Oracle database with an emphasis on good performance
        Perform day-to-day monitoring on an operational database using Oracle Enterprise
              Manager or Statspack.
        List the important steps in a tuning methodology .
      Employ Oracle tools to diagnose performance problems.
        Perform tuning tasks with Oracle Enterprise Manager.
        Perform tuning tasks using the command line interface .
        Identify and resolve I/O, data storage and database configuration problems.
        Optimize sort operations .
        Configure Oracle Shared Server.
        Configure Resource Manager to control resource usage.


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
      Selecting the physical structure 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