Oracle8i Server Performance Tuning

This class focuses on performance tuning tools and techniques for optimizing the efficiency of an Oracle server. An emphasis will be placed on the major focus areas of the Oracle server. Students will learn how to generate tuning statistics, evaluate the information, and then tune the Oracle server.

Audience: Oracle database administrators.

Prerequisites: A fundamental understanding of Oracle database administration.

Review: Oracle Server Architecture

Responsibilities of a DBA
What is an Oracle Server?
Oracle8i Architecture
Oracle Instance Components
The Oracle System Global Area
Oracle Background Processes
Database Files
Logical Storage Structures
Blocks, Extents, and Segments
Logical Structural Design
Control and Redo Log Files
System Change Numbers
Getting Familiar with your Server
Operating System Environment
Important Subdirectories in ORACLE_HOME
The Multi-Threaded Server
JServer Scalability and Performance
PL/SQL versus Java
Oracle's Object-Relational Database
Oracle Architectures
DBA Management Pack

Introduction to Performance Tuning

What Needs Tuning?
Who is Responsible for Tuning?
Monitoring a System
Understanding Different Types of Applications
Developing a Tuning Methodology
Areas of Focus
Oracle Performance Tuning Tools
Primary V$ Views used in Tuning
Oracle Diagnostics and Tuning
Putting Together a Tuning Game
Where to look first
Setting BACKGROUND_DUMP_DEST
Managing User Trace Files
Important Initialization Parameters

Designing Efficient Data Storage

Database Storage
Database Block Size
Looking Inside an Oracle Data Block
Sizing the Database Block
Setting Physical Storage Characteristics
Setting PCTFREE and PCTUSED
How PCTUSED and PCTFREE are used
Guidelines for PCTFREE and PCTUSED
Table Statistics Generated with ANALYZE
Index Statistics Generated with ANALYZE
The High-Water Mark
Chaining and Migration
Determining the Number of Chained Rows
Finding Chained and Migrated Rows
Table Storage Statistics
Environmental Impacts on Design
The Physical Design
Optimal Flexible Architecture

Managing Large Tables

Oracle8 Size Ceilings
Managing Large Tables with Oracle7
Oracle8 Data Storage
What is Partitioning?
Range-Partitioning
Partition Features
Advantages of Partitioning
Creating a Range-Partitioned Table
The Partition Key
Partition Syntax
Accessing a Partitioned Table
Data Dictionary Views
Using MAXVALUE
Partitioning Rules to Follow
Partition Extended Table Name Syntax
Restrictions on Partition Extended Name
Multi-Column Partition Keys
Inserting into a Multi-Column Partition Key
Modifying Partition Key Values
Setting Physical Attributes for Partitions
Hash Partitioning
Defining Tablespaces for Hash Partitions
Composite Partitioning
Creating a Composite-partitioned Table
Subpartitioning
Getting Information on Composite Objects
Index-Organized Tables
Restrictions on Index-Organized Tables
Temporary Tables

Managing Large Indexes

Different Types of Indexes
GLOBAL Prefixed Indexes
Create a Global Prefixed Index
Local Prefixed Indexes
Create a Local Prefixed Index
Local Non-Prefixed Indexes
More on Local Indexes
Composite-partitioned Indexes
Reverse Key Indexes
Improvements to Bitmap Indexes
Function-based indexes
Index Rebuilds ONLINE
Rebuilding Indexes Online
Additional Index Features

Tuning The Buffer Cache

The Database Buffer Cache
The LRU List and Checkpoint Queue
Server Processes and the Buffer Cache
DBWR and the Buffer Cache
DBWR Events
Buffer Cache Hit Ratios
Determining the Cache Hit Ratio
Calculating the Data Block Cache Hit Ratio
Multiple DB Cache Buffer Pools
Defining the DB Buffer Pools
Buffer Pool Initialization Parameters
The Buffer Pool Parameter
Defining Storage for Buffer Pools
Calculating the Hit Ratio for Buffer Pools
Calculating Cache Usage of Schema Objects
Calculating Buffer Pool Sizes
Buffer Pool Considerations
LRU Latches
Caching Tables

Tuning the Shared and Large Pool Areas

Memory Pool Areas
The Shared Pool Area
Tuning the Dictionary Cache

The Program Global Area

Evaluating the Size of the UGA
Library Cache
Sizing the Shared Pool Area
Evaluating Library Cache Utilization
Pinning Large Objects
Monitoring Library Cache Reloads
Evaluating Statements in the Library Cache
Evaluating Sharable Memory
Sharing Cursors
Determining Cursor Memory
The Large Pool Area
Setting the Large Pool
Tuning Parallel Query
Parallel DML
Parallel DML Restrictions

Tuning the Redo Log Cache

The Redo Log Buffer
Redo log Cache I/O
Evaluating the Size of the Redo Log Buffer
Tuning the Redo Log Buffer Cache
Tuning with V$SESSION_WAIT
Evaluating Waits using V$SYSSTAT
Redo Log Waits due to Log Switches
Batch Operations and the Redo Log Buffer

Tuning SQL Statements

SQL Tuning
The Rule-Based Optimizer
The Cost-Based Optimizer
Generating Statistics
The ANALYZE Command
DBMS_STATS Routines
Rule-Based versus Cost-Based Optimization
Setting the Optimizer Mode
EXPLAIN PLAN
Running EXPLAIN PLAN
AUTOTRACE
SQL Trace and TKPROF
Setting up SQL Trace
TKPROF
Running TKPROF
TKPROF Data

Tuning Application Code

Working with Object Types and Collections
Bulk Binds
Bulk Collect Into Clause
Use the RETURNING Clause
NOCOPY Hint
Memory and PL/SQL
Pinning PL/SQL Programs
KEEP Procedure
Pinning PL/SQL Programs
Relative Sizes of Code to Pin
Serially Reusable Packages
Optimizing Integer Operations
Avoid NOT NULL Constraints
Rephrase Conditional Control Statements
Additional Considerations
Temporary Tables
Storing Debugging Information

Tuning Rollback Segments

Rollback Segments
More on Rollback Segments
Typical Layout for Rollback Segments
More on Rollback Segments
Rollbacks and Long Transactions
Sizing Rollback Segments
More on Rollback Segments
CREATE and ALTER Rollback Segments
Creating Rollback Segments
Using the SET TRANSACTION Command
Data Dictionary Views for Rollback Segments
Rollback Segment Rules and Guidelines
Rollback Segment Usage
Sizing Rollback Segments for Transactions

Tuning Sort Operations

Managing Sort Operations
Sort Operations
Tuning Sorts
The SORT_AREA_SIZE Parameter
Allocating Sort Memory Space
The SORT_AREA_RETAINED_SIZE
Memory versus Disk Sorts
Temporary Tablespaces
Evaluating Current Sort Storage
CREATE TEMPORARY TABLESPACE

Configuring Database I/O

Physical Design Guidelines
I/O Tuning Goals
Tuning Full Table Scans
Determining Disk I/O Distribution
The Problem with Fragmentation
Managing Checkpoint Intervals
Database Writer I/Os

Additional Tuning Features

Tuning the High Impact Init.ora Parameters
Init.ora Parameter Guidelines
Data Dictionary Views
The V$ Views
Undocumented Initialization Parameters
Tuning Users and Applications
Using X$BH
Tuning Bottlenecks
Tools for Tuning Bottlenecks
Tuning for Specific Time Frames
Running UTLBSTAT and UTLESTAT
UTLBSTAT and UTLESTAT Statistics
Report.txt
Guidelines to Follow
One Step at a Time
Additional Features
The Oracle Event Management System
Unix Tuning Tools
NT Tuning Tools

Statspack

What is Statspack?
Statspack Scripts
Installing Statspack
Database Space Requirements
Installing Statspack
Statspack Parameter Settings
Using Snapshots
Generating Statspack Snapshots
Exporting Statspack Data
UTLBSTAT / UTLESTAT and STATSPACK
Statistics Gathering
Scheduling Statspack Jobs
Removing Statspack
The Report
Looking at a Statspack Report