OCP Oracle9i Database:Performance Tuning Exam Guide(Exam 1Z0-033)

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