Oracle Performance Tuning for 10gR2 (eBook)

Front Cover 1
Oracle ® Performance Tuning for 10gR2 4
Copyright Page 5
Contents 8
Preface 24
Introduction 30
Part I: Data Model Tuning 52
Chapter 1. The Relational Database Model 54
1.1 The Formal Definition of Normalization 54
1.2 A Layperson’s Approach to Normalization 60
1.3 Referential Integrity 76
Chapter 2. Tuning the Relational Database Model 78
2.1 Normalization and Tuning 78
2.2 Referential Integrity and Tuning 79
2.3 Optimizing with Alternate Indexes 95
2.4 Undoing Normalization 98
Chapter 3. Different Forms of the Relational Database Model 124
3.1 The Purist’s Relational Database Model 124
3.2 Object Applications and the Relational Database Model 126
Chapter 4. A Brief History of Data Modeling 132
4.1 The History of Data Modeling 132
4.2 The History of Relational Databases 136
4.3 The History of the Oracle Database 137
4.4 The Roots of SQL 138
Part II: SQL Code Tuning 140
Chapter 5. What Is SQL? 142
5.1 DML and DDL 142
5.2 Transaction Control 161
5.3 Parallel Queries 164
Chapter 6. The Basics of Efficient SQL 166
6.1 The SELECT Statement 168
6.2 Using Functions 196
6.3 Pseudocolumns 206
6.4 Comparison Conditions 209
Chapter 7. Advanced Concepts of Efficient SQL 218
7.1 Joins 218
7.2 Using Subqueries for Efficiency 230
7.3 Using Synonyms 242
7.4 Using Views 242
7.5 Temporary Tables 249
7.6 Resorting to PL/SQL 250
7.7 Object and Relational Conflicts 257
7.8 Replacing DELETE with TRUNCATE 259
Chapter 8. Common-Sense Indexing 260
8.1 What and How to Index 260
8.2 Types of Indexes 264
8.3 Types of Indexes in Oracle Database 265
8.4 Tuning BTree Indexes 288
8.5 Summarizing Indexes 304
Chapter 9. Oracle SQL Optimization and Statistics 306
9.1 What Is the Parser? 307
9.2 What Is the Purpose of the Optimizer? 308
9.3 Rule-Based versus Cost-Based Optimization 312
Chapter 10. How Oracle SQL Optimization Works 332
10.1 Data Access Methods 332
10.2 Sorting 384
10.3 Special Cases 388
Chapter 11. Overriding Optimizer Behavior Using Hints 398
11.1 How to Use Hints 398
11.2 Hints: Suggestion or Force? 401
11.3 Classifying Hints 403
11.4 Influencing the Optimizer in General 404
11.5 10g Naming Query Blocks for Hints 414
Chapter 12. How to Find Problem Queries 418
12.1 Tools to Detect Problems 418
12.2 EXPLAIN PLAN 419
12.3 SQL Trace and TKPROF 428
12.4 TRCSESS 443
12.5 Autotrace 445
12.6 Oracle Database Performance Views for Tuning SQL 447
Chapter 13. Automated SQL Tuning 460
13.1 Automatic Gathering of Statistics 461
13.2 The AWR and the ADDM 462
13.3 Automating SQL Tuning 472
Part III: Physical and Configuration Tuning 478
Chapter 14. Tuning Oracle Database File Structures 480
14.1 Oracle Database Architecture and the Physical Layer 480
14.2 Tuning and the Logical Layer 491
14.3 Automating Database File Structures 507
Chapter 15. Object Tuning 510
15.1 Tables 510
15.2 Indexes 518
15.3 Index-Organized Tables and Clusters 522
15.4 Sequences 523
15.5 Synonyms and Views 523
15.6 The Recycle Bin 524
Chapter 16. Low-Level Physical Tuning 526
16.1 What Is the High-Water Mark? 526
16.2 Space Used in a Database 527
16.3 What Are Row Chaining and Row Migration? 528
16.4 Different Types of Objects 529
16.5 How Much Block and Extent Tuning? 530
16.6 Choosing Database Block Size 530
16.7 Physical Block Structure 532
16.8 Extent Level Storage Parameters 544
Chapter 17. Hardware Resource Usage Tuning 548
17.1 Tuning Oracle CPU Usage 548
17.2 How Oracle Database Uses Memory 560
17.3 Tuning Oracle I/O Usage 591
Chapter 18. Tuning Network Usage 600
18.1 The Listener 600
18.2 Network Naming Methods 604
18.3 Connection Profiles 608
18.4 Shared Servers 611
Chapter 19. Oracle Partitioning and Parallelism 620
19.1 What Is Oracle Partitioning? 620
19.2 Tricks with Partitions 637
19.3 Endnotes 638
Part IV: Tuning Everything at Once 640
Chapter 20. Ratios: Possible Symptoms of Problems 642
20.1 Database Buffer Cache Hit Ratio 643
20.2 Table Access Ratios 654
20.3 Index Use Ratio 657
20.4 Dictionary Cache Hit Ratio 658
20.5 Library Cache Hit Ratios 658
20.6 Disk Sort Ratio 659
20.7 Chained Rows Ratio 660
20.8 Parse Ratios 661
20.9 Latch Hit Ratio 662
20.10 Ratios in the Database Control 663
Chapter 21. Wait Events 666
21.1 Idle Events 667
21.2 Significant Events 671
21.3 Wait Events in the Database Control 718
Chapter 22. Latches 720
22.1 What Is a Latch? 720
22.2 The Most Significant Latches 727
Chapter 23. Tools and Utilities 736
23.1 Oracle Enterprise Manager 736
23.2 The Database Control 760
23.3 Spotlight 780
23.4 Operating System Tools 780
23.5 Other Utilities and Tools 784
Chapter 24. The Wait Event Interface 790
24.1 What Is a Bottleneck? 790
24.2 Detecting Potential Bottlenecks 791
24.3 What Is the Wait Event Interface? 792
24.4 Oracle Database Wait Event Interface Improvements 811
24.5 Oracle Enterprise Manager and the Wait Event Interface 813
24.6 The Database Control and the Wait Event Interface 816
Chapter 25. Tuning with STATSPACK 822
25.1 Using STATSPACK 822
Appendices 850
A. Sample Databases 850
B. Sample Scripts 882
C. Syntax Conventions 890
D. Installing Oracle 9i Database 892
E. Sources of Information 930
Index 932