Oracle Performance Survival Guide: A Systematic Approach to Database Optimization

✍ Scribed by Harrison, Guy

• Publisher: Prentice Hall
• Year: 2009
• Tongue: English
• Leaves: 760
• Edition: 1st edition
• Category: Library

✦ Synopsis

Oracle Performance Survival Guide A Systematic Approach to Database Optimization The fast, complete, start-to-finish guide to optimizing Oracle performanceOracle Performance Survival Guideoffers 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.netinformit.com/ph

✦ Table of Contents

Cover......Page 1
Table of Contents......Page 10
Preface......Page 20
Chapter 1. Oracle Performance Tuning: A Methodical Approach......Page 28
A Brief History of Oracle Performance Tuning......Page 29
Moving Beyond a Symptomatic Approach......Page 30
Stage 1: Minimizing the Application Workload......Page 33
Stage 2: Reducing Contention and Bottlenecks......Page 34
Stage 3: Reducing Physical IO......Page 35
Stage 4: Optimizing Disk IO......Page 36
Summary......Page 38
The Oracle APIs......Page 40
The Oracle Query Optimizer......Page 47
Oracle Server Architecture......Page 51
Segments and Files......Page 55
Background Processes......Page 58
Real Application Clusters......Page 59
Summary......Page 60
Explaining SQL Statements......Page 62
Tracing Oracle Execution......Page 80
Formatting Traces with tkprof......Page 86
Using AUTOTRACE in SQLPLUS......Page 93
Monitoring the Oracle Server......Page 95
Summary......Page 104
Logical Data Modeling......Page 106
Logical to Physical......Page 109
Denormalization......Page 116
Star Schema Design......Page 120
Physical Storage Options......Page 126
Oracle Partitioning......Page 131
Summary......Page 135
Overview of Oracle Indexing and Clustering......Page 138
B-Tree Indexes......Page 139
Bitmap Indexes......Page 151
Index Overhead......Page 157
Clustering......Page 166
Nested Tables......Page 175
Choosing the Best Indexing Strategy......Page 177
Summary......Page 178
Chapter 6. Application Design and Implementation......Page 180
SQL Statement Management......Page 181
The Array Interface......Page 190
Transaction Design......Page 194
Using Stored Procedures to Reduce Network Traffic......Page 202
Summary......Page 206
The Oracle Optimizer......Page 208
Using DBMS_STAT......Page 226
Summary......Page 237
Chapter 8. Execution Plan Management......Page 238
Hints......Page 239
Stored Outlines......Page 243
SQL Tuning Sets......Page 248
SQL Profiles and the SQL Tuning Advisor......Page 252
SQL Baselines......Page 260
Summary......Page 268
Single Value Lookups......Page 270
Avoiding "Accidental" Table Scans......Page 279
Multicolumn Lookups......Page 290
Searching for Ranges......Page 293
Using the LIKE Operator......Page 300
Multvalue Single-Column Lookups......Page 301
Optimizing Necessary Full Table Scans......Page 302
Summary......Page 313
Types of Joins......Page 316
Join Methods......Page 317
Choosing the Right Join Method......Page 320
Optimizing Joins......Page 325
Avoiding Joins......Page 330
Join Order......Page 334
Special Joins......Page 336
Subqueries......Page 346
Summary......Page 354
Sort Operations......Page 356
Grouping and Aggregates......Page 364
SET Operations......Page 373
Summary......Page 379
Performance Advantages of PL/SQL......Page 380
Measuring PL/SQL Performance......Page 382
Data Access Optimization......Page 389
PL/SQL Code Optimization......Page 395
Other Optimizations......Page 408
DML Trigger Performance......Page 417
Summary......Page 419
Chapter 13. Parallel SQL......Page 422
Understanding Parallel SQL......Page 423
Deciding When to Use Parallel Processing......Page 429
Configuring Parallel Processing......Page 431
Monitoring Parallel SQL......Page 435
Optimizing Parallel Performance......Page 442
Other Parallel Topics......Page 449
Summary......Page 457
DML Performance Fundamentals......Page 460
INSERT Specific Optimizations......Page 465
DELETE Operations......Page 472
UPDATE and MERGE Operations......Page 474
COMMIT Optimization......Page 479
Summary......Page 484
Chapter 15. Lock Contention......Page 486
Lock Types and Modes......Page 487
Waiting for Locks......Page 490
Monitoring and Analyzing Locks......Page 492
Application Locking Strategies......Page 506
When Row Level Locking Fails......Page 508
System Locks......Page 512
Summary......Page 515
Overview of Latch and Mutex Architecture......Page 518
Measuring and Diagnosing Latch/Mutex Contention......Page 520
Specific Latch/Mutex Scenarios......Page 525
Is Latch Contention Inevitable?......Page 534
Summary......Page 538
Buffer Cache Architecture......Page 540
Free Buffer Waits......Page 542
Recovery Writer (RVWR) Waits......Page 547
Buffer Busy Waits......Page 552
Redo Log Buffer Waits......Page 557
Summary......Page 559
Chapter 18. Buffer Cache Tuning......Page 562
Buffer Cache Principles......Page 563
Buffer Cache Configuration and Tuning......Page 566
Automatic Shared Memory Management (ASMM)......Page 577
Summary......Page 584
IO and PGA Memory......Page 586
PGA Memory Management......Page 588
Measuring PGA Usage and Efficiency......Page 591
Sizing the PGA with V$PGA_TARGET_ADVICE......Page 598
Over-Riding PGA Aggregate Target......Page 600
Summary......Page 603
Optimizing Overall Oracle Memory......Page 604
Result Set Cache......Page 623
Other Memory Optimizations......Page 631
Summary......Page 635
Chapter 21. Disk IO Tuning Fundamentals......Page 636
Disk IO Concepts......Page 637
Oracle IO Architecture......Page 639
Measuring and Monitoring Oracle IO......Page 644
Optimizing Datafile IO......Page 652
Redo and Archive Optimization......Page 659
Flashback Logs......Page 666
Summary......Page 667
Automatic Storage Management (ASM)......Page 668
Solid State Disk (SSD)......Page 683
The Exadata Storage Server......Page 686
Database Block Size......Page 687
Summary......Page 689
RAC Overview......Page 690
Measuring Cluster Overhead......Page 695
Reducing Global Cache Latency......Page 699
Optimizing the Interconnect......Page 705
Cluster Balance......Page 710
Minimizing Global Cache Requests......Page 719
Summary......Page 723
The Oracle Documentation Set......Page 726
Books......Page 727
Internet Sites and Blogs......Page 728
A......Page 730
B......Page 732
C......Page 733
D......Page 735
E......Page 737
G......Page 738
I......Page 739
L......Page 741
M......Page 742
N......Page 744
O......Page 745
P......Page 747
Q......Page 748
R......Page 749
S......Page 750
T......Page 754
V......Page 756
Y......Page 757