Cover image for Oracle performance tuning
Oracle performance tuning
Whalen, Edward.
Personal Author:
Publication Information:
Boston : Addison-Wesley, [2002]

Physical Description:
xv, 414 pages : illustrations ; 24 cm
General Note:
Includes index.
Title Subject:
Added Author:
Format :


Call Number
Material Type
Home Location
Item Holds
QA76.9.D3 W477 2002 Adult Non-Fiction Central Closed Stacks

On Order



Oracle performance tuning is a part of the management and adminstration of successful database systems. One of the biggest responsiblities of a database adminstrator (DBA) is to ensure that the Oracle database is tuned properly. A poorly performing database can cost a company thousands or millions of pounds in lost business or lost income.

Author Notes

Edward Whalen is vice president and founder of Performance Tuning Corporation ( ), a consulting company that specializes in database performance, administration, and backup/recovery solutions. He has extensive experience in database system design and tuning for optimal performance. His career has consisted of hardware, operating system, and database development projects for many different companies. Edward has published two other books on the Oracle RDBMS. He has also written four books on Microsoft SQL Server. In addition to writing, Edward has worked on numerous benchmarks and performance-tuning projects with both Oracle and MS SQL Server.

Mitchell Schroeter is a senior consultant with Performance Tuning Corporation ( ) and has worked in the field of database performance tuning for five years. Prior to Performance Tuning Corporation, Mitchell worked at Dell Computer Corporation as a systems analyst in the systems performance analysis group, specializing in database technologies and high-performance storage systems. He has extensive experience with developing client-server and multitiered applications on both Oracle and Microsoft SQL Server in a variety of programming languages. Mitchell specializes in tuning database systems and application code, storage area networks, and Oracle 9i Real Application Clusters.




Each year, computer systems become faster. At the same time, memory and disk storage are becoming more plentiful and less expensive. Because of this, the means and the budget are now available to store vast amounts of information at relatively inexpensive prices. This has allowed users to increase the amount of data stored as well as increase the amount of processing that is done with that data. Because of this, society cannot simply solve performance problems by throwing more hardware at the problem. It's necessary to analyze, size, and properly configure systems to perform optimally. For us, performance engineers, one of the problems that we see in the industry today is the introduction of large disk drives. At one time, it would have been necessary to configure 10 disk drives to hold the data that you have generated; you can now store the same data in 1 disk drive. What many people don't know is that this 1 disk drive is 10 times slower than 10 smaller disk drives. In this book, concepts such as this will be explained and explored. Our philosophy in writing this book is that tuning must be done holistically. You cannot focus on one part of the system and achieve optimal performance. It is necessary to look at the application, the Oracle instance, the operating system, and the hardware and I/O subsystem to achieve optimal performance. In this book, we explore all areas of the system from SQL statements down to the operation of the disk drive. We hope that you will use this book not only as a guide for learning Oracle performance tuning, but as a reference as well. How to Use This Book To keep the book interesting, we have added some personal anecdotes relevant to the subject matter. We hope we have conveyed some of the excitement that comes when you push systems to their limits. Those of us who work in the database performance field constantly push the envelope of technology to achieve new levels of performance previously thought impossible. This kind of experimentation can be satisfying when everything works well, but frustrating when it doesn't. Our hope is that, having read this book, you will have a basic understanding of how the components of the system work together to form the whole. If you have this foundation, you should be able to tackle a performance problem, know what to look for, and know how to fix it. Not all performance problems are alike, and solutions aren't either. It is important that you have a basic understanding of what to look for and what the possible solutions are. If we have done our jobs correctly, you should finish this book with the ability to analyze the problem, hypothesize a solution, test that solution, and understand the result. We hope this book gives novices an idea of what performance engineering is all about. Seasoned professionals should receive new insight and ideas. By applying this newly acquired knowledge, you will find that tackling even the most complex performance problems can be broken down into simple, manageable tasks. 0672321467P04222002 Excerpted from Oracle Performance Tuning by Edward Whalen, Mitchell Schroeter All rights reserved by the original copyright owners. Excerpts are provided for display purposes only and may not be reproduced, reprinted or distributed without the written permission of the publisher.

Table of Contents

Introductionp. 1
Part I Instance Tuning
1 Tuning Fundamentalsp. 5
Tuning Conceptsp. 6
Tuning Goalsp. 7
Tuning Methodologyp. 10
What Affects Oracle Server Performance?p. 16
Sizing and Capacity Planningp. 19
Summaryp. 21
2 Using the Oracle Configuration Parametersp. 23
Starting the Oracle Instancep. 24
The Initialization Parametersp. 31
Summaryp. 33
3 Tuning the Oracle Instancep. 35
Initialization Parameters Used in This Chapterp. 36
Tuning the SGAp. 39
Managing Rollback Segments and Undo Informationp. 52
Checking for Redo Log Bufferp. 62
Tuning Checkpointsp. 62
Optimizing Sortsp. 63
Minimizing Free List Contentionp. 65
Summaryp. 66
4 Performance-Enhancing Featuresp. 67
Parameters That Are Used in This Chapterp. 68
Indexesp. 69
Parallel Execution in Oraclep. 77
Clustersp. 86
Hash Clustersp. 89
Multiblock Readsp. 91
Partitioningp. 91
Plan Stabilityp. 98
Multithreaded Serverp. 98
Summaryp. 101
5 Tuning Workloadsp. 103
Parameters Used in This Chapterp. 104
Using Resource Consumer Groupsp. 104
Tuning User Resourcesp. 110
Relocating Functions to Different Systemsp. 112
Summaryp. 114
6 The Oracle Performance Viewsp. 115
The V$ Views Versus the G$ Viewsp. 116
Overview of the Dynamic Performance Viewsp. 117
Using the Dynamic Performance Viewsp. 122
Summaryp. 124
7 Using UTLBSTAT and UTLESTATp. 125
Statspackp. 142
Summaryp. 160
Part II Oracle Hardware Topics
8 Oracle and System Hardwarep. 163
Parameters Used in This Chapterp. 164
Overview of the Oracle Instancep. 164
System Architecture Overviewp. 170
Summaryp. 177
9 I/O Conceptsp. 179
The Disk Drivep. 180
RAID Disk Subsystemsp. 187
RAID Controller Performance Featuresp. 199
Internal Versus External RAID Systemsp. 201
SAN Systemsp. 203
Network Attached Storage (NAS) Systemsp. 206
Summaryp. 206
10 Oracle and I/Op. 207
Parameters Used in This Chapterp. 208
Oracle's Dependency on I/Op. 209
Configuring the I/O Subsystem for Oraclep. 212
Tuning I/Op. 215
Reducing Unnecessary I/O Overheadp. 222
Block Sizep. 229
Fragmentationp. 231
Summaryp. 234
Part III Application and SQL Tuning
11 Using EXPLAIN PLAN and SQL Tracep. 237
SQL Tracep. 238
The EXPLAIN PLAN Commandp. 248
Registering Applicationsp. 251
Summaryp. 252
12 Index Tuningp. 255
Parameters Used in This Chapterp. 257
Index Typesp. 257
Using the B*-Tree Indexp. 258
The Index Organized Table (IOT)p. 262
The Bitmap Indexp. 262
Function-Based Indexesp. 264
Using Hintsp. 264
Monitoring and Analyzing Indexesp. 265
Summaryp. 265
13 The Oracle Optimizerp. 267
Understanding the Optimizerp. 268
Using the DBMS_STATS Packagep. 273
Using the ANALYZE Commandp. 277
Transaction Processingp. 282
SQL Statement Processingp. 284
Analyzing SQL Statementsp. 288
Designing New SQL Statementsp. 290
Using Hintsp. 290
Summaryp. 292
14 Tuning SQLp. 293
Optimal SQL Statementsp. 294
How to Identify Poorly Tuned SQL Statementsp. 294
Join Typesp. 295
Tuning SQL Statementsp. 297
Summaryp. 306
15 Using Hintsp. 309
Implementing Hintsp. 311
Hintsp. 313
Summaryp. 328
Part IV Advanced Topics
16 Oracle9i Real Application Clustersp. 331
Overview of RACp. 332
When to Use RACp. 341
Tuning RACp. 341
Summaryp. 342
17 Tuning Backup and Recoveryp. 343
Parameters Used in This Chapterp. 345
Oracle Operational Reviewp. 345
Characteristics of the Oracle Backup Processp. 347
System Design Considerationsp. 350
Tuning Considerations Using User-Managed Backupsp. 355
Tuning Considerations Using RMAN Backupsp. 356
System Enhancements to Improve Backup Performancep. 358
Performance Verificationp. 361
Summaryp. 364
18 Creating a High-Performance Disaster Survival Systemp. 365
Parameters Used in This Chapterp. 366
Why Plan for a Disaster?p. 366
Disaster Survival Conceptsp. 367
Planning for a Disasterp. 371
Recovering from a Disasterp. 373
Tuning Standby Systemsp. 374
Summaryp. 375
19 Oracle Networking Performancep. 377
Network Architecturep. 378
Tuning the Network Componentsp. 382
Software Tuningp. 383
Network Designp. 383
Summaryp. 385
Indexp. 387