Starting with Performance Tuning | Oracle Database 11gR2 Performance Tuning Cookbook

Sign In Start Free Trial

Book Overview & Buying
Table Of Contents

Oracle Database 11gR2 Performance Tuning Cookbook

By : Ciro Fiorillo

4 (4)

Oracle Database 11gR2 Performance Tuning Cookbook

4 (4)

By: Ciro Fiorillo

Overview of this book

Oracle's Database offers great performance, scalability, and many features for DBAs and developers. Due to a wide choice of technologies, successful applications are good candidates to run into performance issues and when a problem arises it's very difficult to identify the cause and the right solution to the problem. The Oracle Database 11g R2 Performance Tuning Cookbook helps DBAs and developers to understand every aspect of Oracle Database that can affect performance. You will be guided through implementing the correct solution in a proactive way before problems arise, and how to diagnose issues on your Oracle database-based solutions. This fast-paced book offers solutions starting from application design and development, through the implementation of well-performing applications, to the details of deployment and delivering best-performance databases. With this book you will quickly learn to apply the right methodology to tune the performance of an Oracle Database, and to optimize application design and SQL and PL/SQL code. By following the real-world examples you will see how to store your data in correct structures and access and manipulate them at a lightning speed. You will learn to speed up sort operations, hack the optimizer and the data loading process, and diagnose and tune memory, I/O, and contention issues. The purpose of this cookbook is to provide concise recipes, which will help you to build and maintain a very high-speed Oracle Database environment.

Oracle Database 11gR2 Performance Tuning Cookbook

Oracle Database 11gR2 Performance Tuning Cookbook

Credits

Credits

About the Author

About the Author

Acknowledgement

Acknowledgement

About the Reviewers

About the Reviewers

www.PacktPub.com

www.PacktPub.com

Preface

Preface

Free Chapter

Starting with Performance Tuning

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

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

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

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

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

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

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

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

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

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

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

ALL_OBJECTS

DBA_BLOCKERS

DBA_DATA_FILES

DBA_EXTENTS

DBA_INDEXES

DBA_SQL_PLAN_BASELINES

DBA_TABLES

DBA_TEMP_FILES

DBA_VIEWS

DBA_WAITERS

INDEX_STATS

DBA_SEQUENCES

DBA_TABLESPACES

DBA_TAB_HISTOGRAMS

V$ADVISOR_PROGRESS

V$BUFFER_POOL_STATISTICS

V$CONTROLFILE

V$DATAFILE

V$DB_CACHE_ADVICE

V$DB_OBJECT_CACHE

V$ENQUEUE_LOCK

V$FILESTAT

V$FIXED_TABLE

V$INSTANCE_RECOVERY

V$LATCH

V$LATCH_CHILDREN

V$LIBRARYCACHE

V$LOCK

V$LOCKED_OBJECT

V$LOG

V$LOG_HISTORY

V$LOGFILE

V$MYSTAT

V$PROCESS

V$ROLLSTAT

V$ROWCACHE

V$SESSION

V$SESSION_EVENT

V$SESSTAT

V$SGA

V$SGAINFO

V$SHARED_POOL_RESERVED

V$SORT_SEGMENT

V$SQL

V$SQL_PLAN

V$SQLAREA

V$STATNAME

V$SYSSTAT

V$SYSTEM_EVENT

V$TEMPFILE

V$TEMPSTAT

V$WAITSTAT

X$BH

A Summary of Oracle Packages Used for Performance Tuning

A Summary of Oracle Packages Used for Performance Tuning

DBMS_ADDM

DBMS_ADVISOR

DBMS_JOB

DBMS_LOB

DBMS_MVIEW

DBMS_OUTLN

DBMS_OUTLN_EDIT

DBMS_SHARED_POOL

DBMS_SPACE

DBMS_SPM

DBMS_SQL

DBMS_SQLTUNE

DBMS_STATS

DBMS_UTILITY

DBMS_WORKLOAD_REPOSITORY

Index

Index

Diagnosing performance issues using the alert log

To diagnose certain performance issues, even the alert log can be used successfully.

Getting ready

There are some parameters to look at in the init.ora file of our database instance.

The parameter BACKGROUND_DUMP_DEST indicates the directory in which the alert log is located. If the parameter LOG_CHECKPOINTS_TO_ALERT is set to TRUE, we will find even checkpoint information in the alert log. By default this parameter is set to FALSE.

Before starting, we can issue the following command:

ALTER SYSTEM SET LOG_CHECKPOINTS_TO_ALERT = TRUE;
SHOW PARAMETER BACKGROUND_DUMP_DEST

This writes checkpoint information to the alert log and shows the directory in which we will find the alert log file (named alert_<instance_name>.log).

How to do it...

The following steps will demonstrate how to use the alert log:

In the alert log, we can find information like the following:

Sun Sep 19 12:25:26 2010
Thread 1 advanced to log sequence 5 (LGWR switch)
Current log# 2 seq# 5 mem# 0: D:\APP\ORACLE\ORADATA\TESTDB\REDO02.LOG

This informs us of a log-switch.

We can then verify the time between log switches.
If we have set the parameter LOG_CHECKPOINTS_TO_ALERT to TRUE, we will also see lines like these in the alert log:
```
Sat Sep 25 20:18:01 2010
Beginning global checkpoint up to RBA [0x16.fd.10], SCN: 1296271
Completed checkpoint up to RBA [0x16.fd.10], SCN: 1296271
```
Then we can calculate checkpoint performance.

How it works...

The database writes information on the alert log about log switches and checkpoints. We can inspect the alert log to diagnose a possible problem with log files.

There's more...

We can force a log switch by using the following command:

ALTER SYSTEM SWITCH LOGFILE;

A checkpoint can be forced by using the following statement:

ALTER SYSTEM CHECKPOINT;

See also

We will see the use of SQL_TRACE and TKPROF to generate trace files and the corresponding report over SQL activity of a particular session in Tracing SQL activity with SQL Trace and TKPROF in Chapter 4, Optimizing SQL Code

CONTINUE READING

83

Tech Concepts

36

Programming languages

73

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

Oracle Database 11gR2 Performance Tuning Cookbook

Search

Your notes and bookmarks