Oracle Database 11g R2 Performance Tuning Cookbook电子书

Oracle Database 11gR2 Performance Tuning Cookbook

Table of Contents

Oracle Database 11gR2 Performance Tuning Cookbook

Credits

About the Author

Acknowledgement

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers and more

Why Subscribe?

Free Access for Packt account holders

Instant Updates on New Packt Books

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code

Errata

Piracy

Questions

1. Starting with Performance Tuning

Introduction

Incorrect session management

Poorly designed cursor management

Inadequate relational design

Improper use of storage structures

Reviewing the performance tuning process

How to do it...

How it works...

There's more…

See also

Exploring the example database

Getting ready

How to do it...

How it works...

There's more...

Acquiring data using a data dictionary and dynamic performance views

Getting ready

How to do it...

How it works...

There's more...

See also

Analyzing data using Statspack reports

Getting ready

How to do it...

How it works...

There's more...

Collecting different amounts of data

Producing a report on a specific SQL

Automating snapshot generation

Statspack maintenance

Diagnosing performance issues using the alert log

Getting ready

How to do it...

How it works...

There's more...

See also

Analyzing data using Automatic Workload Repository (AWR)

Getting ready

How to do it...

How it works...

There's more...

Analyzing data using Automatic Database Diagnostic Monitor (ADDM)

Getting ready

How to do it...

How it works...

There's more...

See also

A working example

Getting ready

How to do it...

How it works...

There's more...

See also

2. Optimizing Application Design

Introduction

Optimizing connection management

Getting ready

How to do it...

How it works...

There's more...

Dedicated server versus shared server

Web applications

Client-server Online Transaction Processing

Batch processing

See also

Improving performance sharing reusable code

Getting ready

How to do it...

How it works...

There's more...

PL/SQL and parsing

Diagnosing soft and hard parsing

See also

Reducing the number of requests to the database using stored procedures

How to do it...

How it works...

There's more...

See also

Reducing the number of requests to the database using sequences

How to do it...

How it works...

There's more...

Correct definition of a sequence

See also

Reducing the number of requests to the database using materialized views

How to do it...

How it works...

There's more...

Materialized views in depth

Materialized views and grants

Database parameters to use query rewrite

Can I use materialized views in an OLTP environment?

Optimizing performance with schema denormalization

Getting ready

How to do it...

How it works...

There's more...

Not 1NF structures

Avoiding dynamic SQL

How to do it...

How it works...

There's more...

See also

3. Optimizing Storage Structures

Introduction

Avoiding row chaining

Getting ready

How to do it...

How it works...

There's more...

See also

Avoiding row migration

How to do it...

How it works...

There's more...

Estimating table size with different PCTFREE parameter

Using LOBs

Getting ready

How to do it...

How it works...

There's more...

Using index clusters

How to do it...

How it works...

There's more...

Cluster size

Cluster index

Clustering and truncating

Using hash clusters

How to do it...

How it works...

There's more...

Sorted hash clusters

Custom hash function

Single-table hash clusters

Indexing the correct way

How to do it...

How it works...

There's more...

What is the "small percentage" of the data which assures we can improve performances using B-tree indexes?

See also

Rebuilding index

Getting ready

How to do it...

How it works...

There's more...

Index rebuild and statistics

See also

Compressing indexes

How to do it…

How it works...

There's more...

Using reverse key indexes

How to do it...

How it works...

There's more...

Using bitmap indexes

How to do it...

How it works...

There's more...

Bitmap join index

See also

Migrating to index organized tables

How to do it...

How it works...

There's more...

INCLUDING, OVERFLOW, PCTTHRESHOLD

Logical ROWID

See also

Using partitioning

How to do it...

How it works...

There's more...

List partitioning

Hash partitioning

Composite partitioning

4. Optimizing SQL Code

Introduction

Using bind variables

Getting ready

How to do it...

How it works...

There's more...

Concurrency and scalability

Security issues

See also

Avoiding full table scans

How to do it...

How it works...

There's more...

The High-Water Mark

PctFree, PctUsed, and FREELISTs

See also

Exploring index lookup

Getting ready

How to do it...

How it works...

There's more...

See also

Exploring index skip-scan and index range-scan

Getting ready

How to do it...

How it works...

There's more...

See also

Introducing arrays and bulk operations

How to do it...

How it works...

There's more...

When to use direct path load

See also

Optimizing joins

How to do it...

How it works...

There's more...

See also

Using subqueries

How to do it...

How it works...

There's more...

Tracing SQL activity with SQL Trace and TKPROF

Getting ready

How to do it...

How it works...

There's more...

See also

5. Optimizing Sort Operations

Introduction

Sorting—in-memory and on-disk

Getting ready

How to do it...

How it works...

There's more...

See also

Sorting and indexing

How to do it...

How it works...

There's more...

See also

Writing top n queries and ranking

How to do it...

How it works...

There's more...

See also

Using count, min/max, and group-by

How to do it...

How it works...

There's more...

See also

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

Getting ready

How to do it...

How it works...

There's more...

See also

Troubleshooting temporary tablespaces

How to do it...

How it works...

There's more...

Optimal storage parameters for temporary tablespaces

See also

6. Optimizing PL/SQL Code

Introduction

Using bind variables and parsing

How to do it...

How it works...

There's more...

See also

Array processing and bulk-collect

How to do it...

How it works...

There's more...

See also

Passing values with NOCOPY (or not)

How to do it...

How it works...

There's more...

Using short-circuit IF statements

How to do it...

How it works...

There's more...

Avoiding recursion

How to do it...

How it works...

There's more...

See also

Using native compilation

Getting ready

How to do it...

How it works...

There's more...

See also

Taking advantage of function result cache

How to do it...

How it works...

There's more...

See also

Inlining PL/SQL code

How to do it...

How it works...

There's more...

See also

Using triggers and virtual columns

How to do it...

How it works...

There's more...

Using WHEN and OF in trigger definition

Avoid FOR EACH ROW in triggers, when possible

See also

7. Improving the Oracle Optimizer

Introduction

Exploring optimizer hints

How to do it...

How it works...

There's more...

Errors in hints

See also

Collecting statistics

How to do it...

How it works...

There's more...

Lock table statistics for load or highly volatile tables

Other procedures in DBMS_STATS

See also

Using histograms

How to do it...

How it works...

There's more...

Height-based and value-based (frequency) histograms

See also

Managing stored outlines

Getting ready

How to do it...

How it works...

There's more...

Private and public stored outlines

See also

Introducing Adaptive Cursor Sharing for bind variable peeking

How to do it...

How it works...

There's more...

See also

Creating SQL Tuning Sets

Getting ready

How to do it...

How it works...

There's more...

See also

Using the SQL Tuning Advisor

Getting ready

How to do it...

How it works...

There's more...

See also

Configuring and using SQL Baselines

Getting ready

How to do it...

How it works...

There's more...

See also

8. Other Optimizations

Introduction

Caching results with the client-side result cache

Getting ready

How to do it...

How it works...

There's more...

Configuring the client-side result cache

See also

Enabling parallel SQL

Getting ready

How to do it...

How it works...

There's more...

Parallel query and I/O

When to use parallel SQL

See also

Direct path inserting

How to do it...

How it works...

There's more...

See also

Using create table as select

How to do it...

How it works...

There's more...

See also

Inspecting indexes and triggers overhead

How to do it...

How it works...

There's more...

See also

Loading data with SQL*Loader and Data Pump

Getting ready

How to do it...

How it works...

There's more...

See also

9. Tuning Memory

Introduction

Tuning memory to avoid Operating System paging

How to do it...

How it works...

There's more...

See also

Tuning the Library Cache

How to do it...

How it works...

There's more...

How to minimize misses

See also

Tuning the Shared Pool

How to do it...

How it works...

There's more...

Tuning the Dictionary Cache

See also

Tuning the Program Global Area and the User Global Area

How to do it...

How it works...

There's more...

See also

Tuning the Buffer Cache

How to do it...

How it works...

There's more...

See also

10. Tuning I/O

Introduction

Tuning at the disk level and strategies to distribute Oracle files

Getting ready

How to do it...

How it works...

There's more...

See also

Striping objects across multiple disks

How to do it...

How it works...

There's more...

See also

Choosing different RAID levels for different Oracle files

Getting ready

How to do it...

How it works...

There's more...

RAID level 0

RAID level 1

RAID level 5

RAID level 0+1

See also

Using asynchronous I/O

How to do it...

How it works...

There's more...

See also

Tuning checkpoints

How to do it...

How it works...

There's more...

See also

Tuning redo logs

How to do it...

How it works...

There's more...

See also


11. Tuning Contention

Introduction

Detecting and preventing lock contention

Getting ready

How to do it...

How it works...

There's more...

See also

Investigating transactions and concurrency

Getting ready

How to do it...

How it works...

There's more...

See also

Tuning latches

How to do it...

How it works...

There's more...

See also

Tuning resources to minimize latch contention

Getting ready

How to do it...

How it works...

There's more...

See also

Minimizing latches using bind variables

Getting ready

How to do it...

How it works...

There's more...

See also

A. Dynamic Performance Views

ALL_OBJECTS

Fields

DBA_BLOCKERS

Fields

See also

DBA_DATA_FILES

Fields

See also

DBA_EXTENTS

Fields

See also

DBA_INDEXES

Fields

DBA_SQL_PLAN_BASELINES

Fields

DBA_TABLES

Fields

DBA_TEMP_FILES

Fields

See also

DBA_VIEWS

Fields

DBA_WAITERS

Fields

See also

INDEX_STATS

Fields

See also

DBA_SEQUENCES

Fields

DBA_TABLESPACES

Fields

DBA_TAB_HISTOGRAMS

Fields

V$ADVISOR_PROGRESS

Fields

V$BUFFER_POOL_STATISTICS

Fields

See also

V$CONTROLFILE

Fields

See also

V$DATAFILE

Fields

See also

V$DB_CACHE_ADVICE

Fields

See also

V$DB_OBJECT_CACHE

Fields

See also

V$ENQUEUE_LOCK

Fields

See also

V$FILESTAT

Fields

See also

V$FIXED_TABLE

Fields

V$INSTANCE_RECOVERY

Fields

V$LATCH

Fields

See also

V$LATCH_CHILDREN

Fields

See also

V$LIBRARYCACHE

Fields

V$LOCK

Fields

See also

V$LOCKED_OBJECT

Fields

See also

V$LOG

Fields

See also

V$LOG_HISTORY

Fields

See also

V$LOGFILE

Fields

See also

V$MYSTAT

Fields

See also

V$PROCESS

Fields

See also

V$ROLLSTAT

Fields

V$ROWCACHE

Fields

V$SESSION

Fields

See also

V$SESSION_EVENT

Fields

See also

V$SESSTAT

Fields

See also

V$SGA

Fields

See also

V$SGAINFO

Fields

See also

V$SHARED_POOL_RESERVED

Fields

V$SORT_SEGMENT

Fields

V$SQL

Fields

See also

V$SQL_PLAN

Fields

See also

V$SQLAREA

Fields

See also

V$STATNAME

Fields

See also

V$SYSSTAT

Fields

See also

V$SYSTEM_EVENT

Fields

V$TEMPFILE

Fields

V$TEMPSTAT

Fields

See also

V$WAITSTAT

Fields

See also

X$BH

Fields

B. A Summary of Oracle Packages Used for Performance Tuning

DBMS_ADDM

Procedures

DBMS_ADVISOR

Procedures

DBMS_JOB

Procedures

DBMS_LOB

Procedures

DBMS_MVIEW

Procedures

DBMS_OUTLN

Procedures

DBMS_OUTLN_EDIT

Procedures

DBMS_SHARED_POOL

Procedures

DBMS_SPACE

Procedures

DBMS_SPM

Procedures

DBMS_SQL

Procedures

DBMS_SQLTUNE

Procedures

DBMS_STATS

Procedures

DBMS_UTILITY

Procedures

DBMS_WORKLOAD_REPOSITORY

Procedures

Index