Oracle Performance Tuning for DBAs

Audience

This course is designed for: Database administrators, Developers, Data warehouse support team members.

Prerequisites

Attendees should be knowledgeable in Oracle Database Administration at version 10G minimum to get the best out of this course.

Duration

3 days. Hands on.

Course Objectives

The Oracle RDBMS is typically deployed throughout a business, from the corporate mainframe, to enterprise UNIX/Linux servers, down to departmental level Intel platforms. For a database of any significant size, performance immediately becomes an issue. Users may resent a query taking 3.5h, for instance. Or on the other hand, an update locks a table for 30 minutes while it modifies data, which denies application access to the table(s) concerned. There will always be a platform ceiling set by hardware and o/s kernel performance but wouldn’t it be nice to use the gap between what we are getting today, and what we could get from our platform investment. Learn to find out where this performance ceiling is and at what level the current system is running. Essentially there are two main avenues for RDBMS optimisation. Strategies deployed by the DBA, such as cache management and DBWR and LGWR optimisation, is one of them. The other approach is to investigate how the SQL is running, to change either the SQL or its execution environment and see if that made a difference. The subject of this course is the administration of the database. The classical bottlenecks on an Oracle platform are memory, i/o, network and CPU. On a loaded system, one of these areas will cause a bottleneck. Which one is it? This is what we aim to show you how to find out. Remove the bottleneck and we hit another one. Learn how to measure the systems performance in several areas, how to stress test it and examine the effect of changes you may make.

Course Content

Overview of Oracle 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
Database events

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
Buffer Cache Advisory Parameter
Dynamically resizing SGA components
Granules of Allocation
Increase the size of a SGA component

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
Sources of I/O
Disk performance
Assessing physical reads
Segment statistics
Segment wait events
Longops facility
Disk I/O
DBWR slaves

Optimize Sort Operations
Fundamentals of sorts
Recognisng sorts have occurred
Automated PGA memory management
PGA views
Tuning considerations

Diagnosing contention
Latches and internal locks
Freelist contention
Explicit (manual) data locking

Tuning Oracle Shared Server
Shared server concepts
Setting up the shared server
Monitoring shared servers

Application Tuning
Query optimization
EXPLAIN PLAN
ANALYZE
AUTOTRACE
TKPROF
Histograms
Stored outlines Cached execution plans
Automatically gathering stats on tables
Materialized views
Query rewrite
Unused indexes

Data storage
Tables and indexes
Identifying unused indexes
Partitioning
Clustering
Temporary tables
Large objects (LOBS)
Fragmentation of extents
Row management
Structure of a block
Chained rows problem

Appendix 1
Managing the lab environment

Appendix 2
Guidelines for monitoring the Unix o/s
CPU Monitoring
Memory Monitoring
Swapping Statistics
Process Queuing Statistics
Disk Capacity Statistics
Disk Performance Statistics