Troubleshooting Oracle Performance (eBook)

Christian Antognini (Autor)

eBook Download: PDF

2008 | 1st ed.
XXIX, 586 Seiten
Apress (Verlag)
978-1-4302-0498-5 (ISBN)

Infos

When your database application isn't running fast enough, troubleshooting is usually your first move. Finding the slow part of an application is often easy, but discovering a solution can prove much more difficult. Troubleshooting Oracle Performance helps by providing a systematic approach to addressing the underlying causes of poor database application performance. Written for developers by an application developer who has learned by doing, this book shows you how to plan for performance as you would for any other application requirement.

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).

---

The Oracle database engine has become a huge piece of software. This not only means that a single human can no longer be proficient in using all the features provided in recent versions, but it also means that some of them will rarely be used. Actually, in most situations, it is enough to know and take advantage of a limited number of core features in order to use the Oracle da- base engine efficiently and successfully. This is precisely why in this book I will cover only the features that, based on my experience, are necessary to troubleshoot most of the databa- related performance problems you will encounter. Structure of This Book This book is divided into five parts: Part 1 covers some basics that are required to read the rest of the book. Chapter 1, "e;Perf- mance Problems,"e; explains not only why it is essential to approach performance problems at the right moment and in a methodological way but also why understanding business needs and problems is essential. Chapter 2, "e;Key Concepts,"e; describes the operations carried out by the database engine when parsing and executing SQL statements. It also introduces some terms that are frequently used in the book. Part 2 explains how to approach performance problems in an environment that is based on the Oracle database engine. Chapter 3, "e;Identifying Performance Problems,"e; provides a detailed analysis road map for identifying performance problems. Several tools and te- niques that can be used with it are also described.

Inhalt

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

Produkt-Details

• Erscheint lt. Verlag: 20.8.2008
• Zusatzinfo: XXIX, 586 p.
• Verlagsort: Berkeley
• Sprache: englisch
• Themenwelt: Informatik ► Datenbanken ► Oracle
• Schlagworte: Database • Oracle • Performance • Software • SQL
• ISBN-10: 1-4302-0498-2 / 1430204982
• ISBN-13: 978-1-4302-0498-5 / 9781430204985