PacktLib: Oracle Database 11gR2 Performance Tuning Cookbook

Oracle Database 11gR2 Performance Tuning Cookbook

Credits

About the Author

Acknowledgement

About the Reviewers

www.PacktPub.com

Preface

Starting with Performance Tuning

Introduction

Reviewing the performance tuning process

Exploring the example database

Acquiring data using a data dictionary and dynamic performance views

Analyzing data using Statspack reports

Diagnosing performance issues using the alert log

Analyzing data using Automatic Workload Repository (AWR)

Analyzing data using Automatic Database Diagnostic Monitor (ADDM)

A working example

Optimizing Application Design

Introduction

Optimizing connection management

Improving performance sharing reusable code

Reducing the number of requests to the database using stored procedures

Reducing the number of requests to the database using sequences

Reducing the number of requests to the database using materialized views

Optimizing performance with schema denormalization

Avoiding dynamic SQL

Optimizing Storage Structures

Introduction

Avoiding row chaining

Avoiding row migration

Using LOBs

Using index clusters

Using hash clusters

Indexing the correct way

Rebuilding index

Compressing indexes

Using reverse key indexes

Using bitmap indexes

Migrating to index organized tables

Using partitioning

Optimizing SQL Code

Introduction

Using bind variables

Avoiding full table scans

Exploring index lookup

Exploring index skip-scan and index range-scan

Introducing arrays and bulk operations

Optimizing joins

Using subqueries

Tracing SQL activity with SQL Trace and TKPROF

Optimizing Sort Operations

Introduction

Sorting—in-memory and on-disk

Sorting and indexing

Writing top n queries and ranking

Using count, min/max, and group-by

Avoiding sorting in set operations: union, minus, and intersect

Troubleshooting temporary tablespaces

Optimizing PL/SQL Code

Introduction

Using bind variables and parsing

Array processing and bulk-collect

Passing values with NOCOPY (or not)

Using short-circuit IF statements

Avoiding recursion

Using native compilation

Taking advantage of function result cache

Inlining PL/SQL code

Using triggers and virtual columns

Improving the Oracle Optimizer

Introduction

Exploring optimizer hints

Collecting statistics

Using histograms

Managing stored outlines

Introducing Adaptive Cursor Sharing for bind variable peeking

Creating SQL Tuning Sets

Using the SQL Tuning Advisor

Configuring and using SQL Baselines

Other Optimizations

Introduction

Caching results with the client-side result cache

Enabling parallel SQL

Direct path inserting

Using create table as select

Inspecting indexes and triggers overhead

Loading data with SQL*Loader and Data Pump

Tuning Memory

Introduction

Tuning memory to avoid Operating System paging

Tuning the Library Cache

Tuning the Shared Pool

Tuning the Program Global Area and the User Global Area

Tuning the Buffer Cache

Tuning I/O

Introduction

Tuning at the disk level and strategies to distribute Oracle files

Striping objects across multiple disks

Choosing different RAID levels for different Oracle files

Using asynchronous I/O

Tuning checkpoints

Tuning redo logs

Tuning Contention

Introduction

Detecting and preventing lock contention

Investigating transactions and concurrency

Tuning latches

Tuning resources to minimize latch contention

Minimizing latches using bind variables

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

Dynamic Performance Views

A Summary of Oracle Packages Used for Performance Tuning

A Summary of Oracle Packages Used for Performance Tuning

A Summary of Oracle Packages Used for Performance Tuning

A Summary of Oracle Packages Used for Performance Tuning

A Summary of Oracle Packages Used for Performance Tuning

A Summary of Oracle Packages Used for Performance Tuning

A Summary of Oracle Packages Used for Performance Tuning

A Summary of Oracle Packages Used for Performance Tuning

A Summary of Oracle Packages Used for Performance Tuning

A Summary of Oracle Packages Used for Performance Tuning

A Summary of Oracle Packages Used for Performance Tuning

A Summary of Oracle Packages Used for Performance Tuning

A Summary of Oracle Packages Used for Performance Tuning

A Summary of Oracle Packages Used for Performance Tuning

A Summary of Oracle Packages Used for Performance Tuning

A Summary of Oracle Packages Used for Performance Tuning

Index