Antognini Troubleshooting Oracle Performance

1. ed
ISBN: 978-1-4302-0498-5
Verlag: Apress
Format: PDF
Kopierschutz: 1 - PDF Watermark

E-Book, Englisch, 586 Seiten

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

Autoren/Hrsg.

Antognini, Christian

Inhaltsverzeichnis

1;Contents at a Glance;5
2;Contents;7
3;Forewords;15
4;About the Author;18
5;About the Technical Reviewers;19
6;Acknowledgments;21
7;Introduction;23
7.1;Structure of This Book;23
7.2;Intended Audience;24
7.3;Which Versions Are Covered?;24
7.4;Online Resources;24
8;About the OakTable Network;25
8.1;The Members;25
8.2;The Politics;26
8.3;The Books;26
9;Foundations;28
9.1;Performance Problems;29
9.1.1;Do You Need to Plan Performance?;29
9.1.2;Do You Have Performance Problems?;34
9.1.3;How Do You Approach Performance Problems?;36
9.1.4;On to Chapter 2;38
9.2;Key Concepts;39
9.2.1;Selectivity and Cardinality;39
9.2.2;Life Cycle of a Cursor;41
9.2.3;How Parsing Works;44
9.2.4;Reading and Writing Blocks;56
9.2.5;On to Chapter 3;58
10;Identification;59
10.1;Identifying Performance Problems;60
10.1.1;Divide and Conquer;60
10.1.2;Analysis Road Map;62
10.1.3;Instrumentation vs. Profiling Analysis;65
10.1.4;Instrumentation;66
10.1.5;Profiling Application Code;73
10.1.6;Tracing Database Calls;84
10.1.7;Profiling PL/ SQL Code;125
10.1.8;On to Chapter 4;131
11;Query Optimizer;132
11.1;System and Object Statistics;134
11.1.1;Overview of the Package dbms_ stats;134
11.1.2;System Statistics;136
11.1.3;Object Statistics;144
11.1.4;Common Services;189
11.1.5;On to Chapter 5;192
11.2;Configuring the Query Optimizer;193
11.2.1;To Configure or Not to Configure . . .;194
11.2.2;Configuration Road Map;194
11.2.3;Set the Right Parameter!;196
11.2.4;On to Chapter 6;217
11.3;Execution Plans;218
11.3.1;Obtaining Execution Plans;218
11.3.2;Interpreting Execution Plans;244
11.3.3;Recognizing Inefficient Execution Plans;264
11.3.4;On to Chapter 7;268
11.4;SQL Tuning Techniques;269
11.4.1;Altering the Access Structures;270
11.4.2;Altering the SQL Statement;272
11.4.3;Hints;274
11.4.4;Altering the Execution Environment;283
11.4.5;SQL Profiles;287
11.4.6;Stored Outlines;302
11.4.7;SQL Plan Baselines;313
11.4.8;On to Chapter 8;326
12;Optimization;328
12.1;Parsing;329
12.1.1;Identifying Parsing Problems;329
12.1.2;Solving Parsing Problems;337
12.1.3;Working Around Parsing Problems;344
12.1.4;Using Application Programming Interfaces;348
12.1.5;On to Chapter 9;358
12.2;Optimizing Data Access;359
12.2.1;Identifying Suboptimal Access Paths;359
12.2.2;SQL Statements with Weak Selectivity;370
12.2.3;SQL Statements with Strong Selectivity;392
12.2.4;On to Chapter 10;428
12.3;Optimizing Joins;429
12.3.1;Definitions;429
12.3.2;Nested Loop Joins;438
12.3.3;Merge Joins;444
12.3.4;Hash Joins;454
12.3.5;Outer Joins;459
12.3.6;Choosing the Join Method;461
12.3.7;Partition- wise Joins;462
12.3.8;Transformations;467
12.3.9;On to Chapter 11;477
12.4;Beyond Data Access and Join Optimization;478
12.4.1;Materialized View;478
12.4.2;Result Caching;500
12.4.3;Parallel Processing;508
12.4.4;Direct- Path Insert;532
12.4.5;Row Prefetching;536
12.4.6;Array Interface;541
12.4.7;On to Chapter 12;544
12.5;Optimizing the Physical Design;546
12.5.1;Optimal Column Order;546
12.5.2;Optimal Datatype;548
12.5.3;Row Migration and Row Chaining;554
12.5.4;Block Contention;558
12.5.5;Data Compression;565
13;Appendixes;568
13.1;Downloadable Files;569
13.1.1;Test Environment;569
13.1.2;Files Available for Download;569
13.2;Bibliography;581
14;Index;585