Oracle Performance Survival Guide: A Systematic Approach to Database Optimization

Paperback (Print)
Used and New from Other Sellers
Used and New from Other Sellers
from $35.99
Usually ships in 1-2 business days
(Save 44%)
Other sellers (Paperback)
  • All (8) from $35.99   
  • New (6) from $35.99   
  • Used (2) from $78.98   

Overview

Oracle Performance Survival Guide

A Systematic Approach to Database Optimization

The fast, complete, start-to-finish guide to optimizing Oracle performance

Oracle Performance Survival Guide offers a structured, systematic, start-to-finish methodology for optimizing Oracle performance as efficiently as possible. Leading Oracle expert Guy Harrison shows how to maximize your tuning investment by focusing on causes rather than symptoms, and by quickly identifying the areas that deliver the greatest “bang for the buck.”

Writing for DBAs and developers with all levels of experience, Harrison covers every area of Oracle performance management, from application design through SQL tuning, contention management through memory and physical IO management. He also presents up-to-the-minute guidance for optimizing the performance of the Oracle 11g Release 2.

You’ll start by mastering Oracle structured performance tuning principles and tools, including techniques for tracing and monitoring Oracle execution. Harrison illuminates the interaction between applications and databases, guides you through choosing tuning tools, and introduces upfront design techniques that lead to higher-performance applications. He also presents a collection of downloadable scripts for reporting on all aspects of database performance.

Coverage includes

• “Tuning by layers,” the most effective, highest-value approach to Oracle performance optimization

• Making the most of Oracle’s core tools for tracing, monitoring, and diagnosing performance

• Highly efficient database logical and physical design, indexing, transaction design, and API use

• SQL and PL/SQL tuning, including the use of parallel SQL techniques

• Minimizing contention for locks, latches, shared memory, and other database resources

• Optimizing memory and physical disk IO

• Tuning Real Application Cluster (RAC) databases

guyharrison.net

informit.com/ph

Read More Show Less

Product Details

  • ISBN-13: 9780137011957
  • Publisher: Prentice Hall
  • Publication date: 10/28/2009
  • Series: Prentice Hall Professional Oracle Series
  • Pages: 768
  • Sales rank: 996,799
  • Product dimensions: 6.90 (w) x 9.00 (h) x 1.60 (d)

Meet the Author

Guy Harrisonhas worked with Oracle databases as a developer, administrator, and performance expert for more than two decades. He is the author of many articles and several books on database technology, including Oracle SQL High Performance Tuning(Prentice Hall) and MySql Stored Procedure Programming(with Steven Feuerstein, O’Reilly). Guy is currently a director of development at Quest Software and is the chief architect of Quest’s popular Spotlight product family. He lives in Melbourne, Australia, with his wife Jenni and children Chris, Kate, Mike, and William. You can find Guy on the Web at http://www.guyharrison.net

Read More Show Less

Table of Contents

Preface

Part I: Methods, Concepts, and Tools

Chapter 1. Oracle Performance Tuning: A Methodical Approach

A Brief History of Oracle Performance Tuning

Moving Beyond a Symptomatic Approach

Stage 1: Minimizing the Application Workload

Stage 2: Reducing Contention and Bottlenecks

Stage 3: Reducing Physical IO

Stage 4: Optimizing Disk IO

Summary

Chapter 2. Oracle Architecture and Concepts

The Oracle APIs

Creating the Cursor

Checking for Cached SQL Statements

Parsing the SQL

Associating Bind Variables

Executing the SQL

Fetching Rows

Using Array Fetch

Processing Result Sets

Closing the Cursor

Optimizing Oracle API Calls

The Oracle Query Optimizer

Cost Based Optimization

Optimizer Goal

Optimizer Statistics

Bind Variable Peeking and Adaptive Cursor Sharing

Hints

Outlines, Profiles, and Baselines

Transactions and Locking

Oracle Server Architecture

Instances and Databases

The System Global Area

Data Caching

The Program Global Area

Memory Management

Segments and Files

Tables

Indexes

Blocks, Extents, Segments, and Partitions

Tablespaces and Data Files

Undo Segments

Redo Logs and Archive Logs

Flashback Logs

Server Processes

Background Processes

Real Application Clusters

Summary

Chapter 3. Tools of the Trade

Explaining SQL Statements

The Plan Table

Exploiting Cached SQL

DBMS_XPLAN

Interpreting the Execution Plan

Virtual Indexing

Tracing Oracle Execution

Tracing from Within Your Session

Identifying Your Trace File

Getting Tracing Status

Invoking Trace in Another Session

Tracing by MODULE, ACTION, or SERVICE

Starting a Trace Using a Login Trigger

Finding the Trace File

Other Specialized Traces

Formatting Traces with tkprof

The tkprof Sort Options

Other tkprof Options

Merging Multiple SQL Trace Files

Interpreting Tkprof Output

Execution Plans in tkprof

Wait Statistics and tkprof

Alternatives to tkprof

Using AUTOTRACE in SQL*PLUS

Monitoring the Oracle Server

The V$ table interface

Wait Interface

The Time Model

Integrating the Time Model and Wait Interface

Oracle Enterprise Manager

Spotlight on Oracle

Summary

Part II: Application and Database Design

Chapter 4. Logical and Physical Database Design

Logical Data Modeling

Normalization and Third Normal Form

Data Type Choices

Artificial Keys

Data Warehouse Design

Logical to Physical

Mapping Entities or Classes to Tables

Choosing a Table Type

Data Types and Precisions

Optional Attributes and NULL Values

Column Order

Exploiting Oracle Object Types

Denormalization

Replicating Column Values to Avoid Joins

Summary Tables

Vertical Partitioning

Implementing Denormalization

Star Schema Design

Star Schema Basics

Snowflakes Schemas

Dimension Hierarchies

Aggregations and Materialized Views

Materialized View Best Practices

Physical Storage Options

Manual and Automatic Segment Storage Management

Concurrent Inserts and Freelists

PCTFREE and PCTUSED

Compression

LOB Storage

Oracle Partitioning

Types of Partitions

Composite Partitions

Choosing a Partitioning Strategy

Enterprise Manager Partitioning Advisor

Summary

Chapter 5. Indexing and Clustering

Overview of Oracle Indexing and Clustering

B*-Tree Indexes

Index Selectivity

Unique Indexes

Implicit Indexes

Concatenated Indexes

Index Skip Scans

Guidelines for Concatenated Indexes

Index Merges

Null Values in Indexes

Reverse Key Indexes

Index Compression

Functional Indexes

Foreign Keys and Locking

Indexes and Partitioning

Bitmap Indexes

Features of Bitmap Indexes

Drawbacks of Bitmap Indexes

Bitmap Indexes and Cardinality

Bitmap Index Merge

Bitmap Join Indexes

Index Overhead

Index Organized Tables

Configuring the Overflow Segment

Periodic Rebuild of Index Only Tables

Clustering

Index Clusters

Hash Clusters

Nested Tables

Choosing the Best Indexing Strategy

Summary

Chapter 6. Application Design and Implementation

SQL Statement Management

Optimizing Parsing

Avoiding Unnecessary SQL Executions

The Array Interface

Implementing Array Fetch

Array Insert

Transaction Design

Isolation Levels

Transactions and Locks

Row Level Locking in Oracle

Application Locking Strategies

Using Stored Procedures to Reduce Network Traffic

Summary

Part III: SQL and PL/SQL Tuning

Chapter 7. Optimizing the Optimizer

The Oracle Optimizer

What Is Cost?

Optimizer Goal

Selectivity and Cardinality

Query Transformation

Cost Calculations

Object Statistics

Histograms

Bind Variable Peeking

Adaptive Cursor Sharing

Database Parameters

System Statistics

Collecting Statistics

Using DBMS_STAT

DBMS_STATS Procedures and Parameters

Setting DBMS_STATS Defaults

Creating Histograms with METHOD_OPT

Sampling

Partition Statistics

Extended Statistics

Locking Statistics

System Statistics

Exporting and Importing Statistics

Manipulating Statistics

Summary

Chapter 8. Execution Plan Management

Hints

Using Hints to Change the Access Path

Using Hints to Change the Join Order

Errors in Hint Specifications

Stored Outlines

Creating an Outline to Stabilize a Plan

Hacking an Outline

SQL Tuning Sets

Manually Creating a Tuning Set

Creating Tuning Sets in

Enterprise Manager

SQL Profiles and the SQL Tuning Advisor

Using DBMS_SQLTUNE

Indexing Advice

SQL Tuning in Enterprise Manager

Cross-SQL Tuning with the SQL Access Advisor

SQL Baselines

Creating the Baseline

Evolving the Baseline

Automating and Configuring Baselines

Fixed Baselines

Baseline Management in Oracle

Enterprise Manager

Summary

Chapter 9. Tuning Table Access

Single Value Lookups

Choosing Between Table and Index Scan

Bitmap Indexes and Single Value Lookups

Hash Clusters and Single Value Lookups

Avoiding “Accidental” Table Scans

NOT EQUALS Conditions

Searching for Nulls

Searching for Values That Are NOT NULL

Creating Indexes on NULLable Columns

Unintentionally Disabling an Index with a Function

Functional Indexes

Functional Indexes and Statistics

Virtual Columns

Multicolumn Lookups

Using Concatenated Indexes

Index Merges

Uniqueness and Over-Indexing

Searching for Ranges

Unbounded Range Scan

Bounded Range Scans

Range Lookups

Using the LIKE Operator

Multvalue Single-Column Lookups

Optimizing Necessary Full Table Scans

Lowering the High Water Mark

Optimizing PCTFREE and PCTUSED

Reducing the Row Length

Compressing the Table

Making Database IO More Efficient

Using the SAMPLE Option

Parallel Query

The Fast Full Index Scan

Partitioning

Summary

Chapter 10. Joins and Subqueries

Types of Joins

Join Methods

Nested Loops Join

Sort-Merge Join

Hash Join

Choosing the Right Join Method

Sort-Merge/Hash Versus Nested Loops

Sort-Merge Versus Hash Joins

Optimizing Joins

Optimizing Nested Loops Join

Optimizing Sort-Merge and Hash Joins

Avoiding Joins

Denormalization

Index Clusters

Materialized Views

Bitmap Join Index

Join Order

Special Joins

Outer Joins

Star Joins

Hierarchical Joins

Subqueries

Simple Subqueries

Correlated Subqueries

Anti-Join Subqueries

Semi-Join Subqueries

Summary

Chapter 11. Sorting, Grouping, and Set Operations

Sort Operations

Optimal, One-Pass and Multi-Pass Sorts

Measuring Sort Activity

Tracing Sort Activity

Using an Index to Avoid a Sort

Grouping and Aggregates

Aggregate Operations

Maximums and Minimums

The “Top N” Query

Counting the Rows in a Table

GROUP BY Operations

HAVING Versus WHERE

SET Operations

UNION Versus UNION ALL

INTERSECT

MINUS

SET Operations and Their Alternatives

Summary

Chapter 12. Using and Tuning PL/SQL

Performance Advantages of PL/SQL

A Procedural Approach

Reduction in Network Overhead

Divide and Conquer Massive SQLs

Measuring PL/SQL Performance

Measuring PL/SQL Overhead

Using DBMS_PROFILER

The 11g Hierarchical Profiler

Data Access Optimization

Array Processing and BULK COLLECT

Array Processing for INSERT Statements

Bind Variables and Dynamic SQL

PL/SQL Code Optimization

Tune the SQL First

PLSQL_OPTIMIZE_LEVEL

LOOP Optimization

“Short Circuiting” Expressions

Order of Expressions in IF and CASE Statements

Recursion

The NOCOPY Clause

Associative Arrays

Other Optimizations

Native Compilation

PL/SQL In-Lining

Data Types

Using Java for Computation

Function Caching

DML Trigger Performance

UPDATE OF and WHEN Clauses

Before and After Row Triggers

Summary

Chapter 13. Parallel SQL

Understanding Parallel SQL

Parallel Processes and the Degree of Parallelism

Parallel Slave Pool

Parallel Query IO

Parallel Performance Gains

Deciding When to Use Parallel Processing

Your Server Computer Has Multiple CPUs

The Data to Be Accessed Is on Multiple Disk Drives

The SQL to Be Parallelized is Long Running or Resource-Intensive

The SQL Performs at Least One Full Table, Index, or Partition Scan

There Is Spare Capacity on Your Host

The SQL is Well Tuned

Configuring Parallel Processing

Determining the Degree of Parallelism

Parallel Hints

Parallel Configuration Parameters

Monitoring Parallel SQL

Parallel Explain Plans

Tracing Parallel Execution

The V$PQ_TQSTAT View

Other Statistics

Optimizing Parallel Performance

Start with a SQL That Is Optimized for Serial Execution

Ensure That the SQL Is a Suitable SQL for Parallel Execution

Ensure That the System Is Suitably Configured for Parallel Execution

Make Sure that All Parts of the Execution Plan Are Parallelized

Ensure That the Requested DOP Is Realistic

Monitor the Actual DOP

Check for Skew in Data and Skew in Workload Between Processes

Other Parallel Topics

Parallel Execution in RAC

Parallel Index Lookups

Parallel DML

Parallel DDL

Summary

Chapter 14. DML Tuning

DML Performance Fundamentals

WHERE Clause Optimization

Index Overhead

Trigger Overhead

Referential Integrity

INSERT Specific Optimizations

Array Processing

Direct Path Inserts

Multi-Table Insert

Manual Segment Storage Management (MSSM) and Freelists

Parallel DML

DELETE Operations

TRUNCATE

Partitions

Create Table as Select

UPDATE and MERGE Operations

Correlated UPDATEs

Optimizing MERGE

COMMIT Optimization

COMMIT Frequency

Batch and NOWAIT Commit

NOLOGGING

Summary

Part IV: Minimizing Contention

Chapter 15. Lock Contention

Lock Types and Modes

Waiting for Locks

Monitoring and Analyzing Locks

Lock Wait Statistics

Finding the Responsible SQL

Measuring Lock Contention for Specific Transactions

Tracing Lock Activity

Blockers and Waiters

Application Locking Strategies

When Row Level Locking Fails

Unindexed Foreign Keys

ITL Waits

Bitmap Indexes

Direct Path Inserts

System Locks

The High Water Mark (HW) Enqueue

The Space Transaction (ST) Enqueue

The Sequence Cache (SQ) Enqueue

The User Lock (UL) Enqueue

Other System Locks

Summary

Chapter 16. Latch and Mutex Contention

Overview of Latch and Mutex Architecture

Gets, Spins, and Sleeps

Mutexes

Measuring and Diagnosing Latch/Mutex Contention

Identifying Individual Latches

Finding SQLs and Segments Associated with Latch Waits

Specific Latch/Mutex Scenarios

Library Cache Mutex Waits

Library Cache Pin

Shared Pool Latch

Cache Buffers Chains Latch

Row Cache Objects Latch

Other Latch Scenarios

Is Latch Contention Inevitable?

What About Changing _SPIN_COUNT?

Spin Count, Latch Contention, and Throughput

Setting Spin Count for Individual Latches

Summary

Chapter 17. Shared Memory Contention

Buffer Cache Architecture

Free Buffer Waits

DBWR Direct and Asynchronous IO

Other Remedies for Free Buffer Waits

Recovery Writer (RVWR) Waits

Improving Flashback Log IO

Increasing the Size of the

Flashback Log Buffer

Buffer Busy Waits

Measuring Buffer Busy

Traditional Causes of Buffer Busy Waits

Buffer Busy and Hot Blocks

Redo Log Buffer Waits

Summary

Part V: Optimizing Memory

Chapter 18. Buffer Cache Tuning

Buffer Cache Principles

The LRU List

Table Scan Handling

The CACHE Property

Direct Path IO

Buffer Cache Configuration and Tuning

Monitoring the Buffer Cache

The Buffer Cache Hit Rate

Multiple Buffer Caches

Sizing the Buffer Cache

Automatic Shared Memory Management (ASMM)

Implementing ASMM

Monitoring Resize Operations

Tuning ASMM

Nondefault Pools

Memory Thrashing

Summary

Chapter 19. Optimizing PGA Memory

IO and PGA Memory

PGA Memory Management

PGA_AGGREGATE_TARGET

Session PGA Limits

Measuring PGA Usage and Efficiency

Session PGA Utilization

Measuring Temporary IO Wait Time

Measuring Work Area Activity

Sizing the PGA with V$PGA_TARGET_ADVICE

Over-Riding PGA Aggregate Target

Summary

Chapter 20. Other Memory Management Topics

Optimizing Overall Oracle Memory

IO Wait Times and Memory Optimization

Using Advisories to Distribute PGA/Buffer Cache Memory

Oracle 11G Automatic Memory Management (AMM)

Result Set Cache

Enabling and Configuring the Result Set Cache

Result Cache Statistics

Result Cache Dependencies

Result Cache Latches

PL/SQL Function Cache

Other Memory Optimizations

Sizing the Shared Pool

Large Pool Sizing

Redo Log Buffer

Locking the SGA

Summary

Part VI: IO Tuning and Clustering

Chapter 21. Disk IO Tuning Fundamentals

Disk IO Concepts

Service Time and Throughput

Queuing

Disk Drives: Slow and Getting Slower

Disk Capacity and Data Placement

Oracle IO Architecture

Datafile Single Block Read

Multi Block Read

Direct Path Reads

Temporary Direct Path IO

Data File Write IO

Direct Path Writes

Redo Log IO

Archive Log IO

Flashback IO

Control File IO

Measuring and Monitoring Oracle IO

IO Wait Times

Monitoring Datafile IO

Calibrating IO

Optimizing Datafile IO

Minimizing IO Latency

Maximizing IO Throughput

Striping Strategies

RAID Arrays

Isolating Datafile IO

Redo and Archive Optimization

Alternating and Distributing Logs

Redo and Archive Fine-Grained Striping

Just Say NO to RAID5 for Redo!

Redo Log Sizing

Flashback Logs

Summary

Chapter 22. Advanced IO Techniques

Automatic Storage Management (ASM)

ASM Architecture

ASM Monitoring

ASM Tuning

Solid State Disk (SSD)

Flash-Based SSD

DDR RAM-Based SSD

Hybrid SSD

Using SSD for Oracle Databases

The Exadata Storage Server

Database Block Size

Summary

Chapter 23. Optimizing RAC

RAC Overview

Global Cache Requests

RAC Tuning Principles

Single Instance Tuning and RAC

Measuring Cluster Overhead

Reducing Global Cache Latency

Measuring Global Cache Latency

Examining the Interconnect

Signs of Interconnect Problems

Optimizing the Interconnect

Network Hardware and Protocols

Ethernet Jumbo Frames

UDP Buffer Size

LMS Waits

Cluster Balance

Assessing Cluster Balance

Cluster Balance and Services

RAC Load Balancing Facilities

Minimizing Global Cache Requests

Causes of High Global Cache Request Rates

Measuring Global Cache Request Rates

Techniques for Reducing Global Cache Requests

Summary

Bibliography

9780137011957 TOC 9/21/2009

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)