Oracle Silver Bullets: Real-World Oracle Performance Secrets

Description

Every Oracle professional should feel the exhilaration of adding a single function-based index and seeing the execution speed cut-in-half for 500 SQL statements. It's these silver bullets that can make you a hero in your shop and amaze your friends and associates. 

All Oracle tuning professionals know that they must start by optimizing the database as-a-whole before tuning individual SQL statements. Only after you have tuned the external hardware (disk RAID, network, OS kernel parms) and the instance (indexes, CBO statistics, optimal parameter settings), is it appropriate to tune individual SQL statements and application code.

A single change to the optimizer can improve the behavior of hundreds of SQL statements, and database-wide tuning approaches such as adding a missing index, creating a materialized view or adjusting CBO statistics must be done before detailed SQL tuning can happen. 

These silver-bullet recommendations have been codified inside the Oracle 10g Automatic Database Diagnostic Monitor (ADDM), and every Oracle professional must recognize the benefits of these important tuning techniques.

Oracle consultant Don Burleson shares his in-the trenches Oracle tuning secrets in this concise and practical book.  Packed with actual techniques and tips, Burleson shows you how to perform system-level tuning on all Oracle databases, from small OLTP systems to giant data warehouses.

This is the definitive collection of professional system-level tuning secrets for every Oracle professional DBA. 

Table of Contents

CHAPTER 1- Oracle Silver bullets

 

CHAPTER 2 - The Oracle DBA of the 21st Century

 

CHAPTER 3 - Proactive Tuning and Silver Bullets

 

CHAPTER 4 - The Silver Bullet Hierarchy

 

CHAPTER 5 - Very Large Database Silver Bullets

 

CHAPTER 6 - Indexing Silver Bullets

 

CHAPTER 7 - Instance Parameter Silver Bullets

 

CHAPTER 8 - Table Structure Silver Bullets

 

CHAPTER 9 - Segment Structure Silver Bullets

 

CHAPTER 10 - Oracle SQL Silver Bullets

 

CHAPTER 11 - Oracle Hardware Silver Bullets

 

The top-down approach to Oracle tuning

The one-hour Oracle triage technique
Bottleneck analysis
Server stress analysis
Instance analysis
Network analysis
Disk I/O analysis
Tuning when you can't touch the code

   Fixing sub-optimal code with faster hardware

 

CHAPTER 2 - Disk sub-system Tuning

 

Identify I/O bottlenecks

Applying solid-state disk

Adjusting disk RAID

 

CHAPTER 3 - Oracle Parameter Tuning

 

Change the CBO optimizer parameters

   optimizer_index_cost_adj

   optimizer_index_caching

   optimizer_mode

Initialize missing Oracle instance parameters

 

CHAPTER 4 - Oracle RAM Tuning

 

Adjust the sizes of the SGA pools

Implement the KEEP pool for small-table full-scanned tables

Utilize multiple blocksizes and multiple data caches

 

CHAPTER 5 - Oracle SQL Optimizer Tuning

 

Fix missing CBO statistics
Replace an obsolete statistics gathering method

Create bitmap indexes

Add missing indexes

Employ materialized views

 

CHAPTER 6 - Tuning the Shared Pool

 

Evaluating the library cache

Using cache cursors

Implement cursor_sharing

Removing excessive SQL parsing

 

CHAPTER 7 - Server silver bullets

 

Identify incorrect kernel parameters

Oracle Windows issues (screen savers, virus scanning, file server configuration)

Configuring direct I/O

 

CHAPTER 8 - Network silver bullets

 

Using network sniffers

Adjusting SDU and TDU

Implementing tcp.nodelay

 

CHAPTER 9 - Table & Index silver bullets

 

Adjust freelists and freelist groups

Adjust maxtrans

Implement bitmap freelists (automatic segment space management)

Using locally-managed tablespaces

Table reorganization

Index rebuilding