Call us on 0870 7777 388 for availability on this course.
Have questions or need a better city/date?Ask now.
Course Overview
This class is taught on Oracle9i. 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.
Prerequisites
Oracle9i Database Administration Fundamentals II
Oracle9i Database Administration Fundamentals I
Introduction to Oracle9i: SQL
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 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
CourseMonster books thousands of public training courses, classes and boot camps both in London and throughout the UK including: Berkshire, Birmingham, Bristol, Bournemouth, Bucks, Cambridge, Derby, Devon, Edinburgh, Glasgow, Hampshire, Ipswich, Leeds, Leicester, Luton, Manchester, Middlesex, Milton Keynes, Norfolk, Nottingham, Reading, Surrey, Sussex, Tyne and Wear, Midlands and Yorkshire. Topics range from software to administration and development.
