On-line training course catalogue

Oracle9i Database: Performance Tuning

Type of course: Classroom

Course details

Fee	£2100
Days	5
Course code	ORO9IPT

We currently do not have public dates scheduled for this course.
If you have a requirement for this course, please contact us for the latest course information.

Print this page | Download as Word document |

Overview

Please note that Oracle courses running at London training centres are subject to a 5% surcharge.

This class is taught on Oracle9i Release 1. Database performance tuning is critical to any effective information system. Reducing wait times, increasing users productivity and maximising 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.

Leads to certification

• - Oracle9i Database Administrator Certified Professional

Prerequisites

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

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 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
• Paging and Swapping
• CPU Tuning Guidelines
• Process Vs Thread
• Resource Manager Concepts

Related Certifications

This course forms part of the following certification track(s):

• Oracle9i Database Administrator Certified Professional