Exam # 1Z0-033: Oracle9i Performance Tuning

Oracle9i: Performance Tuning (Exam #1Z0-033) is a core requirement for the Oracle 9i OCP (Oracle Certified Professional) certifications. It is designed for intermediate to advanced level database personnel. In addition, we cover the following topics:

Chapter 1: Oracle9i Server Architecture

Oracle9i Environments

Oracle8i Architecture (Review)

Oracle9i Architecture

The Oracle System Global Area

Oracle Background Processes

Storage Management

Getting Familiar With Your Server

The Shared Server Architecture

The Oracle9i Java Environment

PL/SQL versus Java

Oracle's Object-Relational Database

Oracle Architectures

DBA Management Pack

Chapter 2: Introduction to Performance Tuning

What Needs Tuning?

Who is Responsible for Tuning?

Understanding Different Types of Applications

Oracle Performance Tuning Tools

Oracle Diagnostics and Tuning Packs

Putting Together a Tuning Game Plan

Where to Look First

Setting BACKGROUND_DUMP_DEST for

Chapter 3: Designing Efficient Data Storage

Database Storage

Looking Inside an Oracle Data Block

Traditional Storage Management with Freelists

Setting Physical Storage Characteristics

Setting PCTFREE and PCTUSED

Guidelines for PCTFREE and PCTUSED

Generating Table Statistics

Generating Index Statistics

Environmental Impacts on Design

The Physical Design

Optimal Flexible Architecture

Chapter 4: Managing Large Tables

Managing Large Tables with Oracle

Oracle Data Storage

Range-Partitioning

Creating a Range-Partitioned Table

Accessing a Partitioned Table

Data Dictionary Views

Using MAXVALUE

Inserting into a Multi-Column Partition Key

Setting Physical Attributes for Partitions

Getting Information about Composite Objects

List Partitioning in Oracle9i

Oracle9iR2 Enhancements to Partitioning

Composite Partition Templates

Chapter 5: Managing Large Indexes

Different Types of Indexes

GLOBAL Prefixed Indexes

Create a Global Prefixed Index

Local Prefixed Indexes

Composite-Partitioned Indexes

Additional Index Features

Bitmap Join Indexes

Chapter 6: Space Management

The DBMS_SPACE Package

The SPACE_USAGE Parameters

Example using SPACE_USAGE

Space Deallocation Statements

Viewing Space Usage Information

Data Segment Compression

Chapter 7: Tuning The Standard Buffer Cache

The Database Buffer Cache

Calculating the Data Buffer Cache Hit Ratio

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

Caching Tables

Chapter 8: Management

Setting up SQL Work Areas

Displaying Workarea Information

Multiple Database Buffer Caches

Database Buffer Cache Parameters

Changing the Database Buffer Cache

Advisory Parameter

Chapter 9: Tuning the Shared and Large Pool Areas

Memory Pool Areas

Configuring The Program Global Area

Evaluating Library Cache Utilization

Changing the Shared Pool Size

Shared Pool Advisory Statistics

Checking for SQL Statement Contention

Evaluating Sharable Memory

Performance Tuning EM Enhancements

The Large Pool Area

Setting the Large Pool

Tuning Parallel Query

Chapter 10: Tuning SQL Statements

SQL Tuning

The Rule-Based Optimizer

The Cost-Based Optimizer

Generating Statistics

Rule-Based versus Cost-Based Optimization

Setting the Optimizer Mode

The PLAN_TABLE Columns

Setting up SQL Trace

Running TKPROF

TKPROF Data

Chapter 11: Configuring Database I/O

Physical Design Guidelines

I/O Tuning Goals

Tuning Full Table Scans

Determining Disk I/O Distribution

Reducing Fragmentation

MTTR and Checkpoint intervals

Methods for Controlling Check pointing

Minimal I/O Recovery

Managing Checkpoint Intervals

Chapter 12: Additional Tuning Considerations

Data Dictionary Views

Tuning Users and Applications

Tuning for Specific Time Frames

Guidelines to Follow

One Step at a Time

The Oracle Event Management System

Tuning for Waits and Contention

Ratio Tuning versus Wait Tuning

Unix Tuning Tools

Windows Tuning Tools

Chapter 13: Statspack

What is Statspack?

Installing Statspack

Database Space Requirements

Generating Statspack Snapshots

Exporting Statspack Data

Statistics Gathering

Scheduling Statspack Jobs

Removing Statspack

Would you like to learn Oracle9i Performance Tuning? We provide home tutoring service.

Please contact us for any additional information.

---