InformIT Home About InformIT Oracle Workforce Development Program Cisco Networking Academy Program Microsoft IT Training & Certification Other Intensive Training International IT Certifications
  10g DBA Track  
  10g iDS Track  
  10g Java Develop. Track  
  10g App. Server Track  
  9i DBA Track  
  9i iDS Track  
  9i J2EE Java Develop. Track  
  9i AS Web Adm. Track  
Oracle Data Warehouse

































































Oracle 9i Database Performance Tuning

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. 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.

This course helps Participants prepare to successfully pass the Oracle certification exam #1z0-033 to become an Oracle Database Administrator Certified Professional (OCP-DBA).


Course schedule

Duration: 30 hours
To view the schedule for this class, click here.

Who should attend

Administrators and other IT Professionals involved in the management of Oracle9i databases.


•  Introduction to Oracle9i: SQL
•  Oracle9i Database: Fundamental I
•  Oracle9i Database: Fundamental II
. or equivalent knowledge. .

Course Outline

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 Oracle 9i
  • 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 Oracle 9i
  • 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




INFORMIT - Skill Development Center
Jl. Purnawarman 9c, Bandung 40117- Phone (022) 4224 168 / 4223 168
Fax. (022) 4231 168 - E-mail:

  Home | About Us | Oracle | Cisco | Microsoft | Other Training | Certification | Training Calendar  
© 2006, InformIT Ganesa Pratama. All Right Reserved