Oracle High Performance Tuning for 9i and 10g (eBook)

Front Cover 1
Oracle® High Performance Tuning for 9i and 1og 4
Copyright Page 5
Contents 6
Contents at a glance 12
Preface 30
Introduction 36
A Tuning Environment 36
When to Tune 39
What to Tune in Production 40
When to Stop Tuning in Production 41
Tuning from Development to Production 46
How is this Book Organized? 52
Some Final Points 57
What is Oracle Database 10g? 57
Part I: Data Model Tuning 60
Chapter 1. The Relational Database Model 62
1.1 A Layman’s Approach to Normalization 62
1.2 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 94
2.4 Undoing Normalization 96
Chapter 3. Different Forms of the Relational Database Model 116
3.1 The Purist’s Relational Database Model 116
3.2 Object Applications and the Relational Database Model 118
Chapter 4. A Brief History of Data Modeling 124
4.1 The History of Data Modeling 124
4.2 The History of Relational Databases 128
4.3 The History of the Oracle Database 129
4.4 The Roots of SQL 131
Part II: SQL Code Tuning 132
Chapter 5. What is SQL? 134
5.1 DML and DDL 134
5.2 DML Command Syntax 135
5.3 Transaction Control 152
5.4 Parallel Queries 154
Chapter 6. The Basics of Efficient SQL 156
6.1 The SELECT Statement 157
6.2 Using Functions 178
6.3 Pseudocolumns 187
6.4 Comparison Conditions 189
6.5 Joins 196
6.6 Using Subqueries for Efficiency 207
6.7 Using Synonyms 217
6.8 Using Views 217
6.9 Temporary Tables 223
6.10 Resorting to PL/SQL 223
6.11 Replacing DELETE with TRUNCATE 230
6.12 Object and Relational Conflicts 230
Chapter 7. Common Sense Indexing 234
7.1 What and How to Index 234
7.2 Types of Indexes 238
7.3 Types of Indexes in Oracle Database 239
7.4 Tuning BTree Indexes 255
7.5 Summarizing Indexes 270
Chapter 8. Making SQL Efficient in Oracle Database 272
8.1 What is the Parser? 273
8.2 What is the Optimizer? 274
8.3 How Best to Access Data in the Database 275
8.4 Rule- versus Cost-Based Optimization 276
8.5 Data Access Methods 291
8.6 Sorting 336
8.7 Special Cases 340
8.8 Overriding the Optimizer using Hints 348
Chapter 9. How to Find Problem Queries 362
9.1 Tools to Detect Problems 362
9.2 EXPLAIN PLAN 363
9.3 SQL Trace and TKPROF 371
9.4 10g TRCSESS 384
9.5 Autotrace 384
9.6 Oracle Database Performance Views for Tuning SQL 385
Chapter 10. Tuning SQL with Oracle Enterprise Manager 400
10.1 The Tuning Pack 400
Part III: Physical and Configuration Tuning 420
Chapter 11. Installing Oracle and Creating a Database 422
11.1 Installing Oracle Database 422
11.2 Basic Configuration 424
11.3 Creating a Database 440
Chapter 12. Tuning Oracle Database File Structures 462
12.1 Oracle Database Architecture and the Physical Layer 462
12.2 Tuning and the Logical Layer 473
Chapter 13. Object Tuning 488
13.1 Tables 488
13.2 Indexes 494
13.3 Index-Organized Tables and Clusters 497
13.4 Sequences 498
13.5 Synonyms and Views 499
13.6 10g The Recycle Bin 500
Chapter 14. Low-Level Physical Tuning 502
14.1 What is the High Water Mark? 502
14.2 Space Used in a Database 503
14.3 What are Row Chaining and Row Migration? 504
14.4 Different Types of Objects 505
14.5 How Much Block and Extent Tuning? 506
14.6 Choosing Database Block Size 506
14.7 Physical Block Structure 508
14.8 Extent-Level Storage Parameters 520
Chapter 15. Hardware Resource Usage Tuning 524
15.1 Tuning Oracle CPU Usage 524
15.2 How Oracle Database Uses Memory 534
15.3 Tuning Oracle I/O Usage 555
Chapter 16. Tuning Network Usage 562
16.1 The Listener 562
16.2 Network Naming Methods 566
16.3 Connection Profiles 570
16.4 Shared Servers 572
Chapter 17. Oracle Partitioning and Parallelism 580
17.1 What is Oracle Partitioning? 580
17.2 Tricks with Partitions 591
Chapter 18. Ratios: Possible Symptoms of Problems 594
18.1 Database Buffer Cache Hit Ratio 596
18.2 Table Access Ratios 600
18.3 Index Use Ratio 603
18.4 Dictionary Cache Hit Ratio 604
18.5 Library Cache Hit Ratios 604
18.6 Disk Sort Ratio 604
18.7 Chained Rows Ratio 605
18.8 Parse Ratios 606
18.9 Latch Hit Ratio 607
Chapter 19. Wait Events 610
19.1 Idle Events 611
19.2 Significant Events 613
Chapter 20. Latches 646
20.1 What is a Latch? 646
20.2 The Most Significant Latches 651
Chapter 21. Tools and Utilities 660
21.1 Oracle Enterprise Manager 660
21.2 Spotlight 669
21.3 Operating System Tools 671
21.4 Other Utilities and Tools 674
Chapter 22. Tuning with the Wait Event Interface and STATSPACK 682
22.1 What is a Bottleneck? 682
22.2 Detecting Potential Bottlenecks 683
22.3 What is the Wait Event Interface? 684
22.4 10g Oracle Database Wait Event Interface Improvements 699
22.5 Oracle Enterprise Manager and the Wait Event Interface 700
22.6 Using STATSPACK 704
Appendix A. Sample Databases 730
Database Schemas 730
Active Concurrent Database Scripting 734
Updating Summary Fields 749
Partitioning Tablespace Creation 752
Appendix B. Sample Scripts 754
Interpreting EXPLAIN PLAN 754
Statistics Generation 755
Count Rows 756
Constraints 756
Indexes 756
Space in the Database 757
TKPROF Trace Files Interpretation Scripts 758
The TKPROF Interpretation Script 760
Appendix C. Sources of Information 762
Index 764