Oracle Database Performance

A data-driven, fact-based, quantitative text on Oracle performance and scalability

With database concepts and theories clearly explained in Oracle's context, readers quickly learn how to fully leverage Oracle's performance and scalability capabilities at every stage of designing and developing an Oracle-based enterprise application. The book is based on the author's more than ten years of experience working with Oracle, and is filled with dependable, tested, and proven performance optimization techniques.

Oracle Database Performance and Scalability is divided into four parts that enable readers to progressively learn and apply new skills:
* Part 1, Getting Started with Oracle, describes how to set up a working Oracle environment, offering a quick tour to illustrate major database concepts
* Part 2, Oracle Architecture from Performance and Scalability Perspectives, explains all the major database concepts and architectural features that readers need to know to optimize Oracle performance and scalability
* Part 3, Optimizing Oracle Performance and Scalability, offers hands-on instructions to optimize and fine-tune performance and scalability for all database applications
* Part 4, Case Studies: Oracle Meeting Real-World Performance and Scalability Challenges, sets forth quantitative case studies based on the author's first-hand experience that demonstrate how readers can apply proven techniques to dramatically improve the performance and scalability of actual database products

In addition to case studies, this book offers a detailed secure online banking application as an example, making it possible for readers to fully explore Oracle's performance and scalability features on their own. Oracle Database Performance and Scalability is an ideal text for pre-conditioning college students and training software developers.

Preface xxv
Why This Book xxv
Who This Book is for xxvi
How This Book is Organized xxvii
Software and Hardware xxviii
How to Use This Book xxix
How to Reach the Author xxxi
Acknowledgments xxxiii
Introduction 1
Features of Oracle 2
Objectives 4
Conventions 5
Performance versus Scalability 6
Part 1 Getting Started with Oracle 7
1 Basic Concepts 9
1. 1 Standard versus Flavored SQLS 10
1. 2 Relational versus Object-Oriented Databases 11
1. 3 An Instance versus a Database 11
1. 4 Summary 12
Recommended Reading 12
Exercises 12
2 Installing Oracle Software 14
2. 1 Installing Oracle 11g Server Software 15
2. 2 Configuring a Listener 18
2. 3 Creating an Oracle Database 18
2. 4 Installing Oracle 11g Client Software 28
2. 5 Oracle Grid Control versus DB Control 31
2. 6 Summary 33
Recommended Reading 33
Exercises 33
3 Options for Accessing an Oracle Server 34
3. 1 A Command Line Interface (CLI) versus a GUI-Based Console 35
3. 2 The Oracle Enterprise Manager Java Console (OEMJC) 37
3. 3 Using the SQL*Plus Tool 40
3. 4 Oracle Enterprise Manager DBConsole 42
3. 5 Other Tools for Developers 43
3. 6 Case Study: Creating ER Diagrams with Visio via ODBC 44
3. 7 Case Study: Accessing Oracle in Java via JDBC 47
3. 8 Summary 49
Recommended Reading 50
Exercises 50
4 A Quick Tour of an Oracle Server 52
4. 1 New Oracle Schemas Beyond "Scott" 53
4. 2 Oracle Users versus Schemas 54
4. 3 Tablespaces, Segments, Extents, and Data Blocks 56
4. 4 Tables, Indexes and Index Types for Structured Data 57
4. 5 Domain and LOB Index Types for Unstructured Data 65
4. 6 Views, Materialized Views, and Synonyms 68
4. 7 Stored Procedures, Functions, and Triggers 68
4. 8 Referential Integrity with Foreign Keys 71
4. 9 Summary 73
Recommended Reading 73
Exercises 74
Part 2 Oracle Architecture from Performance and Scalability Perspectives 75
5 Understanding Oracle Architecture 79
5. 1 The Version History of Oracle 80
5. 2 Oracle Processes 82
5. 3 Oracle Memory Areas 87
5. 4 Dedicated versus Shared Oracle Server Architecture 89
5. 5 Performance Sensitive Initialization Parameters 91
5. 6 Oracle Static Data Dictionary Views 94
5. 7 Oracle Dynamic Performance (V$) Views 95
5. 8 Summary 98
Recommended Reading 98
Exercises 99
6 Oracle 10g Memory Management 101
6. 1 SGA Sub-Areas 102
6. 2 SGA Sizing: Automatic Shared Memory Management (ASMM) 104
6. 3 PGA Sizing: PGA_AGGREGATE_TARGET 106
6. 4 Summary 108
Recommended Reading 109
Exercises 110
7 Oracle 11g Memory Management 111
7. 1 Automatic Memory Management (AMM) 112
7. 2 Memory Sizing Options Configurable at Database Creation Time 112
7. 3 Checking Memory Management and Usage Distribution at Run Time 113
7. 4 Summary 115
Recommended Reading 115
Exercises 115
8 Oracle Storage Structure 116
8. 1 Overview 117
8. 2 Managing Tablespaces 119
8. 3 Managing Data Files 122
8. 4 Managing Redo Logs 124
8. 5 Summary 125
Recommended Reading 125
Exercises 126
9 Oracle Wait Interface (OWI) 127
9. 1 Ratio-based versus OWI-based Oracle Performance Tuning Methodologies 128
9. 2 Wait Event-The Core Concept of OWI 130
9. 3 Classification of Wait Events from OWI 131
9. 4 The Other Part (CPU Time) of the Equation Elapsed Time = CPU Time + Wait Time 134
9. 5 AWR as a Compass to Tuning Oracle Performance and Scalability 136
9. 6 Summary 137
Recommended Reading 137
Exercises 138
10 Oracle Data Consistency and Concurrency 139
10. 1 Select . . . for Update Statement 140
10. 2 ACID Properties of Transactions 141
10. 3 Read Phenomena and Data Inconsistencies 143
10. 4 Oracle Isolation Levels 145
10. 5 Multi-Version Concurrency Control (MVCC) and Read Consistency 145
10. 6 Oracle Locks 146
10. 7 Lock Escalations versus Conversions 149
10. 8 Oracle Latches 149
10. 9 Oracle Enqueues 150
10. 10 Deadlocks 150
10. 11 Taking Advantage of Oracle's Scalable Concurrency Model 151
10. 12 Case Study: A JDBC Example 152
10. 13 Summary 158
Recommended Reading 159
Exercises 159
11 Anatomy of an Oracle Automatic Workload Repository (AWR) Report 161
11. 1 Importance of Performance Statistics 162
11. 2 AWR Report Header 165
11. 3 Report Summary 166
11. 4 Main Report 171
11. 5 Wait Events Statistics 172
11. 6 SQL Statistics 178
11. 7 Instance Activity Statistics 185
11. 8 IO Stats 197
11. 9 Buffer Pool Statistics 199
11. 10 Advisory Statistics 199
11. 11 Wait Statistics 206
11. 12 Undo Statistics 207
11. 13 Latch Statistics 208
11. 14 Segment Statistics 215
11. 15 Dictionary Cache Stats 218
11. 16 Library Cache Activity 219
11. 17 Memory Statistics 219
11. 18 Streams Statistics 222
11. 19 Resource Limit Stats 224
11. 20 init. ora Parameters 224
11. 21 Summary 225
Recommended Reading 225
Exercises 226
12 Oracle Advanced Features and Options 227
12. 1 Oracle 8i New Features 227
12. 2 Oracle 9i New Features 233
12. 3 Oracle 10g New Features 241
12. 4 Oracle 11g New Features 248
12. 5 Summary 255
Recommended Reading 255
Exercises 255
13 Top 10 Oracle Performance and Scalability Features 257
13. 1 Real Application Clustering (RAC) 258
13. 2 Dedicated versus Shared Server Models 260
13. 3 Proven Transaction and Concurrency Models 260
13. 4 A Highly Efficient SQL Optimization Engine 261
13. 5 Efficient Parallel Processing with Modern Multi-Core CPUs 261
13. 6 Partitioning 262
13. 7 An All-Encompassing, Powerful Performance, and Scalability Troubleshooting Tool-AWR 262
13. 8 The Most Comprehensive Set of Internal Performance Metrics 263
13. 9 Database Resident Connection Pool 263
13. 10 In-Memory Database Cache (IMDB) 263
13. 11 Summary 263
Recommended Reading 264
Exercises 264
14 Oracle-Based Application Performance and Scalability by Design 266
14. 1 Rapid Development Methodologies 268
14. 2 Planning 269
14. 3 Requirements Gathering 272
14. 4 Conceptual Design via Data Modeling 275
14. 5 Logical Design via Normalization 280
14. 6 Physical Design 295
14. 7 Implementation 315
14. 8 Release to Market (RTM) 322
14. 9 Continuous Improvements 322
14. 10 Summary 323
Recommended Reading 324
Exercises 325
15 Project: Soba-A Secure Online Banking Application on Oracle 326
15. 1 Getting SOBA Up and Running 328
15. 2 Overview of Spring Framework 333
15. 3 MVC Architecture 337
15. 4 Spring MVC Framework Applied to SOBA 342
15. 6 RESTful Web Services Applied to SOBA 376
15. 7 Spring Security Applied to SOBA 386
15. 8 Spring ACL Applied to SOBA 394
15. 9 Summary 413
Recommended Reading 414
Exercises 414
Part 3 Optimizing Oracle Performance and Scalability 415
16 Logistics of the Oracle Cost-Based Optimizer (CBO) 417
16. 1 Life of a SQL Statement in Oracle 418
16. 2 Oracle SQL Optimizer: Rule-Based versus Cost-Based 420
16. 3 CBO Statistics 421
16. 4 Pivot Role of Gathering Database Statistics to CBO 422
16. 5 Methods of Gathering CBO Statistics 424
16. 6 Locking and Unlocking CBO Statistics 425
16. 7 Explain Plan-A Handle to CBO 425
16. 8 Data Access Methods-CBO's Footprints 426
16. 9 Looking Up CBO's Plan Hidden in V$SQL*PLAN 427
16. 10 When CBO may Generate Suboptimum Execution Plans 428
16. 11 Summary 429
Recommended Reading 429
Exercises 430
17 Oracle SQL Tuning 431
17. 1 Tuning Joins 432
17. 2 Tuning Subqueries 437
17. 3 Case Study: Performance of SUBQUERY versus JOIN 439
17. 4 Case Study: Performance of IN versus EXISTS 443
17. 5 Case Study: A SQL Tuning Yielded a 12x Performance Gain 444
17. 6 Summary 447
Recommended Reading 447
Exercises 448
18 Oracle Indexing 449
18. 1 Rules of Thumb on Indexing 450
18. 2 Creating and Using Ubiquitous b-Tree Indexes 451
18. 3 Advanced Indexing Scheme I: Covering Indexes versus Index-Organized Tables 452
18. 4 Advanced Indexing Scheme II: Function-Based Indexes (FBIs) 453
18. 5 Unusual Indexing Scheme I: BITMAP Indexes 454
18. 6 Unusual Indexing Scheme II: Reverse Key Indexes 455
18. 7 Unusual Indexing Scheme III: Compressed Composite Indexes 455
18. 8 How to Create Oracle Indexes 456
18. 9 Summary 457
Recommended Reading 458
Exercises 458
19 Auto_Tune Features 459
19. 1 Oracle Automatic Database Diagnostic Monitor (ADDM) 460
19. 2 Automatic Undo Management 462
19. 3 Data Recovery Advisor 462
19. 4 Memory Advisors 462
19. 5 MTTR Advisor 466
19. 6 Segment Advisor 466
19. 7 SQL Advisors 467
19. 8 SQL Performance Analyzer 469
19. 9 Summary 470
Recommended Reading 471
Exercises 471
Part 4 Case Studies: Oracle Meeting Real World Performance and Scalability Challenges 473
20 Case Study: Achieving High Throughput with Array Processing 477
20. 1 Context 478
20. 2 Performance Model 479
20. 3 Tests 480
20. 4 Solution 480
20. 5 Effects of Array Processing 482
20. 6 Summary 484
Recommended Reading 484
Exercises 484
21 Case Study: Performance Comparison of Heap-Organized versus Index-Organized Tables 485
21. 1 Context 486
21. 2 Conversion from Heap-Organized to Index-Organized 487
21. 3 Creating Indexes 487
21. 4 Creating Constraints 488
21. 5 EXPLAIN PLANs 488
21. 6 Oracle SQL Traces 489
21. 7 Summary 490
Recommended Reading 491
Exercises 491
22 Case Study: SQL Tuning: "IN" versus "OR" versus Global Temporary Table 492
22. 1 Context 493
22. 2 Test Program 494
22. 3 Observation 1: IN_CreateStatement is the Best Performer 495
22. 4 Observation 2: Batch Insert Saves Time 497
22. 5 Temptable Performed Better without an Index Hint than with an Index Hint 498
22. 6 Effects of APPEND Hint for Populating Temptable 499
22. 7 Effects of Number of Iterations 499
22. 8 OR and IN without the Index Hint 499
22. 9 Limitation on the Number of Literal Values and the Size of OR Statement 501
22. 10 Dealing with More Than 1000 Literal Values for an IN Based SQL Query 501
22. 11 A Recommendation for Dealing with 1000 Literal Value Limit in an IN Statement 501
22. 12 Summary 502
Recommended Reading 503
Exercises 503
23 Case Study: Data Access Paths (Double Buffering) 504
23. 1 Data Access Paths in General 505
23. 2 Test Environments 511
23. 3 Test Results with Solaris on Veritas 514
23. 4 Test Results with Solaris on UFS 522
23. 5 Test Results with Windows on NTFS 526
23. 6 Moral of the Case Study 528
Recommended Reading 529
Exercises 530
24 Case Study: Covering Index 531
24. 1 Getting to Know the Application Architecture 533
24. 2 Quantifying the Problems 533
24. 3 Analyzing Bottlenecks 533
24. 4 Applying Optimizations/Tunings 535
24. 5 Verifying the Fixes 535
24. 6 Moral of the Case Study 545
Recommended Reading 546
Exercises 546
25 Case Study: CURSOR_SHARING 547
25. 1 The Concept of a Bind Variable 548
25. 2 Oracle CURSOR_SHARING Parameter 549
25. 3 Getting to Know the Application Architecture 550
25. 4 Quantifying Problems 550
25. 5 Analyzing Bottlenecks 551
25. 6 Applying Tuning: CURSOR_SHARING = FORCE 560
25. 7 Applying Tuning: CURSOR_SHARING = SIMILAR 564
25. 8 Moral of the Case Study 569
Recommended Reading 569
Exercises 570
26 Case Study: Bulk Transactions 571
26. 1 Application Architecture 572
26. 2 Quantifying Problems 572
26. 3 Identifying Performance and Scalability Optimization Opportunities 573
26. 4 Effects of Bulk Transactions on Performance 581
26. 5 Moral of the Case Study 592
Recommended Reading 593
Exercises 593
27 Case Study: Missing Statistics 594
27. 1 Decaying Performance due to Missing Statistics 595
27. 2 First Run with no Statistics 597
27. 3 Second Run with Missing Statistics 604
27. 4 Third Run with Updated Statistics 611
27. 5 Moral of the Case Study 618
Recommended Reading 618
Exercises 618
28 Case Study: Misconfigured SAN Storage 620
28. 1 Architecture of the Apple's Xserve RAID 621
28. 2 Problem Analysis 622
28. 3 Reconfiguring the RAID and Verifying 626
28. 4 Moral of the Case Study 629
Recommended Reading 630
Exercises 630
Appendix A Oracle Product Documentations 633
A. 1 Oracle Database Concepts 633
A. 2 Oracle Database Administrator's Guide 633
A. 3 Oracle Database Reference 634
A. 4 Oracle Database Performance Tuning Guide 634
A. 5 Oracle Database 2 Day + Performance Tuning Guide 634
A. 6 Oracle Database 2 Day DBA 634
A. 7 Oracle Database SQL Language Reference 634
A. 8 Oracle Database Sample Schemas 635
A. 9 Oracle Database PL/SQL Packages and Types Reference 635
A. 10 Oracle Database PL/SQL Language Reference 635
A. 11 Oracle Database JDBC Developer's Guide and References 635
Appendix B Using SQL*Plus with Oracle 636
B. 1 Installation 636
B. 2 SQL*Plus and tnsnames. ora File 637
B. 3 Basics of SQL*Plus 638
B. 4 Common SQL*Plus Commands 638
B. 5 Using SQL*Plus to Execute SQL Statements 639
B. 6 Using SQL*Plus to Execute PL/SQL Blocks 640
B. 7 Using SQL*Plus Autotrace to Obtain EXECUTION PLANs and Optimizer Statistics 640
B. 8 Using SQL*Plus Timing Command 641
B. 9 Exporting/Importing Oracle Databases with SQL*Plus 642
B. 10 Creating AWR Reports with SQL*Plus 643
B. 11 Checking Tablespace Usage with SQL*Plus 644
B. 12 Creating EM DBConsole with SQL*Plus 646
Appendix C A Complete List of All Wait Events in Oracle 11g 648
Appendix D A Complete List of All Metrics with the V$Statname View 656
Appendix E A Complete List of All Statistics with the V$Sysstat View 667
Index 681