Troubleshooting Oracle Performance (eBook)

Since 1995, Christian Antognini has been focusing on understanding how the Oracle database engine works. His main interests range from logical and physical database design, to the integration of databases with Java applications, the query optimizer, and basically everything else related to performance management and tuning. He is working as a senior consultant and trainer at Trivadis AG in Z rich, Switzerland. If he is not helping one of his customers to get the most out of Oracle, he is somewhere lecturing on optimization or new Oracle database features for developers. He is member of the Trivadis Performance Team and of the OakTable Network).

Contents at a Glance 5
Contents 7
Forewords 15
About the Author 18
About the Technical Reviewers 19
Acknowledgments 21
Introduction 23
Structure of This Book 23
Intended Audience 24
Which Versions Are Covered? 24
Online Resources 24
About the OakTable Network 25
The Members 25
The Politics 26
The Books 26
Foundations 28
Performance Problems 29
Do You Need to Plan Performance? 29
Do You Have Performance Problems? 34
How Do You Approach Performance Problems? 36
On to Chapter 2 38
Key Concepts 39
Selectivity and Cardinality 39
Life Cycle of a Cursor 41
How Parsing Works 44
Reading and Writing Blocks 56
On to Chapter 3 58
Identification 59
Identifying Performance Problems 60
Divide and Conquer 60
Analysis Road Map 62
Instrumentation vs. Profiling Analysis 65
Instrumentation 66
Profiling Application Code 73
Tracing Database Calls 84
Profiling PL/ SQL Code 125
On to Chapter 4 131
Query Optimizer 132
System and Object Statistics 134
Overview of the Package dbms_ stats 134
System Statistics 136
Object Statistics 144
Common Services 189
On to Chapter 5 192
Configuring the Query Optimizer 193
To Configure or Not to Configure . . . 194
Configuration Road Map 194
Set the Right Parameter! 196
On to Chapter 6 217
Execution Plans 218
Obtaining Execution Plans 218
Interpreting Execution Plans 244
Recognizing Inefficient Execution Plans 264
On to Chapter 7 268
SQL Tuning Techniques 269
Altering the Access Structures 270
Altering the SQL Statement 272
Hints 274
Altering the Execution Environment 283
SQL Profiles 287
Stored Outlines 302
SQL Plan Baselines 313
On to Chapter 8 326
Optimization 328
Parsing 329
Identifying Parsing Problems 329
Solving Parsing Problems 337
Working Around Parsing Problems 344
Using Application Programming Interfaces 348
On to Chapter 9 358
Optimizing Data Access 359
Identifying Suboptimal Access Paths 359
SQL Statements with Weak Selectivity 370
SQL Statements with Strong Selectivity 392
On to Chapter 10 428
Optimizing Joins 429
Definitions 429
Nested Loop Joins 438
Merge Joins 444
Hash Joins 454
Outer Joins 459
Choosing the Join Method 461
Partition- wise Joins 462
Transformations 467
On to Chapter 11 477
Beyond Data Access and Join Optimization 478
Materialized View 478
Result Caching 500
Parallel Processing 508
Direct- Path Insert 532
Row Prefetching 536
Array Interface 541
On to Chapter 12 544
Optimizing the Physical Design 546
Optimal Column Order 546
Optimal Datatype 548
Row Migration and Row Chaining 554
Block Contention 558
Data Compression 565
Appendixes 568
Downloadable Files 569
Test Environment 569
Files Available for Download 569
Bibliography 581
Index 585