ACKNOWLEOGMENTS

INTROOUCTION
PART I

OCP Tuning Exam Guide

1 Database Tuning Overview

How This Book Is Organized
Topics Covered in the OCP OBA Tuning Exam
The Oracle Performance Tuning Methodoiogy
Diagnostics and Tuning Tools
Sizing the Shared Pool
Sizing the Buffer Cacbe
Sizing Other SGA Structures
Database Configuration and I/O lssues
Optimizing Sort Operdtions
Didgnosing Contention for Latches
Tuning Rollback or UNDO Segments
Monitoring and Detecting Lock Contention
Tuning Oracle Sbared Server
Application Tuning
Using Oracle Blocks Efficiently
SQL Statement Tuning
OS Considerations
Tuning Overview
Why Do We Performance Tune?
What Is Performance Tuning?
Where Do I Tune7
How Do I Performance Tune?
When Do I Tune?
How Do I Know If My Performance Tuning Efforts Are
Actually Working?
To Advance Your Tuning Knowledge

2 Oracle Performance Tuning Methodology

Oracle Performance Tuning Method
Describe the Roles Associated with the Database Tuning Process
Describe the Dependency Between Tuning in Different
Deve1opment Phases
Describe SLAs
Describe the Tuning Goals
Describe the Most Common Tuning Problems
Describe Tuning Goals Associated with Different Types
of Applications
Performance and Safety Trade-Offs
Chapter Summary
Two-Minute Drill
Chapter Questions
Answers to Chapter Questions

3 Diagnostic and Tuning Tools

Diagnostic and Tuning Tools
The Data Dictionary and Dynamic Performance Views Related to Tuning
Data Dictionary Views
How the ANALYZE Command Is Used with Data
Dictionary Views
Dynamic Performance Views
Current State Views
Accumulator Views
Informational Views
Statistics in the Dynamic Performance Views
Instance/Database Performance Views
Memory
Disk
Contention
Session-Related
What Is Statspack?
Installing and Configuring Statspack
Interactive or Batch Installations
Statspack Maintenance
Configuring Statspack Statistics Gathering
Collect Statistics Using Statspack
Take a Snapshot
Automating Snapshots
Producing a Performance Report
Contents of the Statspack Performance Report
Other Tools That Can Be Used for Tuning
UTLBSTATUTLESTAT
OEM Oracle Expert
Custom Tools
The A1ert Log and Trace Files
Instance Alert Log
Trace User SQL
Chapter Summary
Two-Minute Drill
Chapter Questions
Answers to Chapter Questions

4 Sizing the Shared Pool

Sizing the Shared Pool
Shared Pool Concepts
Measure and Tune the Library Cache Hit Ratio
Size and Pin Objects in the Shared Pool
Tune the Shared Pool Reserve Space
Measure and Tune the Dictionary Cache Hit Ratio
Describe UGA and Session Memory Considerations
Setting the Large Pool
Chapter Summary
Two-Minute Drill
Chapter Questions
Answers to Chapter Questions

5 Sizing the Buffer Cache

Sizing the Buffer Cache
Describe How the Buffer Cache Is Used by Different
Dracle Processes
Describe the Tuning lssues Related to the Buffer Cache
Cetting Advice about the Buffer Cache Size
Implement Dynamic SGA Allocation
Monitor the Use of the Buffer Cache and the Different
Poo1s Within the Buffer Cache
Create and Size Multiple Buffer Pools
Make Appropriate Use of Table Caching
Diagnose LRU Latch Contention
Diagnose Freelist Contention
Deprecated Buffer Cache Parameters
Chapter Summary
Two-Minute Drill
Chapter Questions
Answers to Chapter Questions

6 Sizing Other SGA Structures

Sizing Other SGA Structures
Monitor and Size the Redo Log Buffer
Monitor and Size the Java Pool
Limit the Amount of Java Session Memory Used by a Session
Configuring I/O Slaves
Configuring Multiple DBW Processes
Chapter Summary
Two-Minute Drill
Chapter Questions
Answers to Chapter Questions

7 Database Configuration and I/O lssues

Database Configuration and I/O Issues
Describe Reasons for Distributing Different Oracle File Types
Diagnose Inappropriate Use of Tablespaces
Tune Full Table Scan Operations
Describe How Checkpoints Work
Monitor and Tune Checkpoints
Monitor and Tune Redo Logs
Chapter Summary
Two-Minute Drill
Chapter Questions
Answers to Chapter Questions

8 Optimize Sort Operations

Optimize Sort Operations
Describe How Sorts Are Performed in Oracle
Set Old and New Sort Parameters
Describe the Operations That Cause Sorting
Differentiate Between Disk and Memory Sorts
Create and Monitor Temporary Tablespaces
Describe Ways to Reduce Total Sorts and Disk Sorts
Chapter Summary
Two-Minute Drill
Chapter Questions
Answers to Chapter Questions

9 Diagnosing Contention for Latches

Diagnosing Contention for Latches
Describe the Purpose of Latches
Describe the Different Types of Latch Requests
Describe How to Diagnose Contention for Latches
Identify the Resources to Be Tuned to Minimize Latch Contention
Chapter Summary
Two-Minute Drill
Chapter Questions
Answers to Chapter Questions

10 Tuning Rollback or Undo Segments

Tuning Rollback/Undo Segments
Use the Dynamic Performance Views to Check Rollback
Segment Performance
Define the Number and Size of Manual Rollback Segments
Appropriately Allocate Rollback Segments to Transactions
Understand and Expiain the Concept of Automatic Undo
Management
Create and Maintain Automatically Managed Undo Tablespace.
Chapter Summary
Two-Minute Drill
Chapter Questions
Answers to Chapter Questions

11 Monitoring and Detecting Lock Contention

Monitoring and Detecting Lock Contention
Define Levels of Locking
Describe PossibIe Causes of Contention
Use Oracle Utilities to Detect Lock Contention
Resolve Contention in an Emergency
Prevent Locking Problems
Recognize Oracle Errors Arising from Deadlocks
Chapter Summary
Two-Minute Drill
Chapter Questions
Answers to Chapter Questions

12 Tuning Oracle Shared Server

Tuning Oracle Shared Server
Identifying Issues Associated with Managing Users in a
Shared Server Environment
Diagnosing and Resolving Performance Issues with
Shared Server Processes
Configure the Shared Server Environment to Optimize
Performance
Chapter Summary
Two-Minute Drill
Chapter Questions
Answers to Chapter Questions

l3 Application Tuning

Application Tuning
Describe the Role of the DBA in Tuning Applicatior
Explain Different Storage Structures
Explain and Describe Clustering Options
Explain the Different Types of Indexes
Explain IOTs
Describe Materialized Views and the Use of Query Rewrites
List Requirements for OLTP, DSS, and Hybrid Systems
Chapter Summary
Two-Minute Drill
Chapter Questions
Answers to Chapter Questions

l4 Using Oracle Blocks Efficiently

Using Oracle Blocks Efficiently
Describe the Correct Usage of Extents and Oracle Blocks
Explain Space Usage and the High Watermark
Determine the High Watermark
Recover Space from Sparsely Populated Segments
Describe the Use of Oracle Block Parameters
Describe and Detect Chaining and Migration of Oracle Blocks
Perform Index Reorganization
Monitor Indexes to Determine Usage
Chapter Summary
Two-Minute Drill
Chapter Questions
Answers to Chapter Questions

15 SQL Statement Tuning

SQL Statement Tuning
Describe How the Optimizer Is Used
Explain the Concept of Plan Stabi1ity.
Use of Stored Outlines
Describe How Hints Are Used
Collect Statistics on Indexes and Tables
Describe the Use of Histograms
Copy Statistics Between Databases
OLTP and OSS Considerations
Use SQL Trace and TKPROF
Chapter Summary
Two-Minute Drill
Chapter Questions
Answers to Chapter Questions

16 OS Considerations and Oracle Resource Manager

OS Considerations
Describe Different System Architectures
Understand Virtual Memory and Paging
Describe the Primary Steps of OS Tuning
Identify Similarities Between OS and DB Tuning
Explain the Difference Between a Process and a Thread
Database Resource Manager
Configuring Resource Management
Administer Resource Manager
Chapter Summary
Two-Minute Drill
Chapter Questions
Answers to Chapter Questions
PART II
Practice Exams

17 Practice Exam I and 2

Practice Exam 1 Questions
Answers to Practice Exam 1 Questions
Practice Exam Two
Practice Exam 2 Questions
Answers to Practice Exam 2 Questions
Glossary

Index