Oracle Database Performance and Scalability

HENRY H. LIU, PHD, is a Software Developer at BMC Software, Inc., with a physicist background from his prior career. His primary responsibilities at BMC include helping build performance and scalability into BMC's cloud computing and enterprise service management software products. Dr. Liu is the author of the highly acclaimed Software Performance and Scalability: A Quantitative Approach (Wiley).

• Preface xxvWhy This Book xxvWho This Book is for xxviHow This Book is Organized xxviiSoftware and Hardware xxviiiHow to Use This Book xxixHow to Reach the Author xxxiAcknowledgments xxxiiiIntroduction 1Features of Oracle 2Objectives 4Conventions 5Performance versus Scalability 6Part 1 Getting Started with Oracle 71 Basic Concepts 91.1 Standard versus Flavored SQLS 101.2 Relational versus Object-Oriented Databases 111.3 An Instance versus a Database 111.4 Summary 12Recommended Reading 12Exercises 122 Installing Oracle Software 142.1 Installing Oracle 11g Server Software 152.2 Configuring a Listener 182.3 Creating an Oracle Database 182.4 Installing Oracle 11g Client Software 282.5 Oracle Grid Control versus DB Control 312.6 Summary 33Recommended Reading 33Exercises 333 Options for Accessing an Oracle Server 343.1 A Command Line Interface (CLI) versus a GUI-Based Console 353.2 The Oracle Enterprise Manager Java Console (OEMJC) 373.3 Using the SQL*Plus Tool 403.4 Oracle Enterprise Manager DBConsole 423.5 Other Tools for Developers 433.6 Case Study: Creating ER Diagrams with Visio via ODBC 443.7 Case Study: Accessing Oracle in Java via JDBC 473.8 Summary 49Recommended Reading 50Exercises 504 A Quick Tour of an Oracle Server 524.1 New Oracle Schemas Beyond “Scott” 534.2 Oracle Users versus Schemas 544.3 Tablespaces, Segments, Extents, and Data Blocks 564.4 Tables, Indexes and Index Types for Structured Data 574.5 Domain and LOB Index Types for Unstructured Data 654.6 Views, Materialized Views, and Synonyms 684.7 Stored Procedures, Functions, and Triggers 684.8 Referential Integrity with Foreign Keys 714.9 Summary 73Recommended Reading 73Exercises 74Part 2 Oracle Architecture from Performance and Scalability Perspectives 755 Understanding Oracle Architecture 795.1 The Version History of Oracle 805.2 Oracle Processes 825.3 Oracle Memory Areas 875.4 Dedicated versus Shared Oracle Server Architecture 895.5 Performance Sensitive Initialization Parameters 915.6 Oracle Static Data Dictionary Views 945.7 Oracle Dynamic Performance (V$) Views 955.8 Summary 98Recommended Reading 98Exercises 996 Oracle 10g Memory Management 1016.1 SGA Sub-Areas 1026.2 SGA Sizing: Automatic Shared Memory Management (ASMM) 1046.3 PGA Sizing: PGA_AGGREGATE_TARGET 1066.4 Summary 108Recommended Reading 109Exercises 1107 Oracle 11g Memory Management 1117.1 Automatic Memory Management (AMM) 1127.2 Memory Sizing Options Configurable at Database Creation Time 1127.3 Checking Memory Management and Usage Distribution at Run Time 1137.4 Summary 115Recommended Reading 115Exercises 1158 Oracle Storage Structure 1168.1 Overview 1178.2 Managing Tablespaces 1198.3 Managing Data Files 1228.4 Managing Redo Logs 1248.5 Summary 125Recommended Reading 125Exercises 1269 Oracle Wait Interface (OWI) 1279.1 Ratio-based versus OWI-based Oracle Performance Tuning Methodologies 1289.2 Wait Event—The Core Concept of OWI 1309.3 Classification of Wait Events from OWI 1319.4 The Other Part (CPU Time) of the Equation Elapsed Time = CPU Time + Wait Time 1349.5 AWR as a Compass to Tuning Oracle Performance and Scalability 1369.6 Summary 137Recommended Reading 137Exercises 13810 Oracle Data Consistency and Concurrency 13910.1 Select . . . for Update Statement 14010.2 ACID Properties of Transactions 14110.3 Read Phenomena and Data Inconsistencies 14310.4 Oracle Isolation Levels 14510.5 Multi-Version Concurrency Control (MVCC) and Read Consistency 14510.6 Oracle Locks 14610.7 Lock Escalations versus Conversions 14910.8 Oracle Latches 14910.9 Oracle Enqueues 15010.10 Deadlocks 15010.11 Taking Advantage of Oracle’s Scalable Concurrency Model 15110.12 Case Study: A JDBC Example 15210.13 Summary 158Recommended Reading 159Exercises 15911 Anatomy of an Oracle Automatic Workload Repository (AWR) Report 16111.1 Importance of Performance Statistics 16211.2 AWR Report Header 16511.3 Report Summary 16611.4 Main Report 17111.5 Wait Events Statistics 17211.6 SQL Statistics 17811.7 Instance Activity Statistics 18511.8 IO Stats 19711.9 Buffer Pool Statistics 19911.10 Advisory Statistics 19911.11 Wait Statistics 20611.12 Undo Statistics 20711.13 Latch Statistics 20811.14 Segment Statistics 21511.15 Dictionary Cache Stats 21811.16 Library Cache Activity 21911.17 Memory Statistics 21911.18 Streams Statistics 22211.19 Resource Limit Stats 22411.20 init.ora Parameters 22411.21 Summary 225Recommended Reading 225Exercises 22612 Oracle Advanced Features and Options 22712.1 Oracle 8i New Features 22712.2 Oracle 9i New Features 23312.3 Oracle 10g New Features 24112.4 Oracle 11g New Features 24812.5 Summary 255Recommended Reading 255Exercises 25513 Top 10 Oracle Performance and Scalability Features 25713.1 Real Application Clustering (RAC) 25813.2 Dedicated versus Shared Server Models 26013.3 Proven Transaction and Concurrency Models 26013.4 A Highly Efficient SQL Optimization Engine 26113.5 Efficient Parallel Processing with Modern Multi-Core CPUs 26113.6 Partitioning 26213.7 An All-Encompassing, Powerful Performance, and Scalability Troubleshooting Tool—AWR 26213.8 The Most Comprehensive Set of Internal Performance Metrics 26313.9 Database Resident Connection Pool 26313.10 In-Memory Database Cache (IMDB) 26313.11 Summary 263Recommended Reading 264Exercises 26414 Oracle-Based Application Performance and Scalability by Design 26614.1 Rapid Development Methodologies 26814.2 Planning 26914.3 Requirements Gathering 27214.4 Conceptual Design via Data Modeling 27514.5 Logical Design via Normalization 28014.6 Physical Design 29514.7 Implementation 31514.8 Release to Market (RTM) 32214.9 Continuous Improvements 32214.10 Summary 323Recommended Reading 324Exercises 32515 Project: Soba—A Secure Online Banking Application on Oracle 32615.1 Getting SOBA Up and Running 32815.2 Overview of Spring Framework 33315.3 MVC Architecture 33715.4 Spring MVC Framework Applied to SOBA 34215.6 RESTful Web Services Applied to SOBA 37615.7 Spring Security Applied to SOBA 38615.8 Spring ACL Applied to SOBA 39415.9 Summary 413Recommended Reading 414Exercises 414Part 3 Optimizing Oracle Performance and Scalability 41516 Logistics of the Oracle Cost-Based Optimizer (CBO) 41716.1 Life of a SQL Statement in Oracle 41816.2 Oracle SQL Optimizer: Rule-Based versus Cost-Based 42016.3 CBO Statistics 42116.4 Pivot Role of Gathering Database Statistics to CBO 42216.5 Methods of Gathering CBO Statistics 42416.6 Locking and Unlocking CBO Statistics 42516.7 Explain Plan—A Handle to CBO 42516.8 Data Access Methods—CBO’s Footprints 42616.9 Looking Up CBO’s Plan Hidden in V$SQL*PLAN 42716.10 When CBO may Generate Suboptimum Execution Plans 42816.11 Summary 429Recommended Reading 429Exercises 43017 Oracle SQL Tuning 43117.1 Tuning Joins 43217.2 Tuning Subqueries 43717.3 Case Study: Performance of SUBQUERY versus JOIN 43917.4 Case Study: Performance of IN versus EXISTS 44317.5 Case Study: A SQL Tuning Yielded a 12x Performance Gain 44417.6 Summary 447Recommended Reading 447Exercises 44818 Oracle Indexing 44918.1 Rules of Thumb on Indexing 45018.2 Creating and Using Ubiquitous b-Tree Indexes 45118.3 Advanced Indexing Scheme I: Covering Indexes versus Index-Organized Tables 45218.4 Advanced Indexing Scheme II: Function-Based Indexes (FBIs) 45318.5 Unusual Indexing Scheme I: BITMAP Indexes 45418.6 Unusual Indexing Scheme II: Reverse Key Indexes 45518.7 Unusual Indexing Scheme III: Compressed Composite Indexes 45518.8 How to Create Oracle Indexes 45618.9 Summary 457Recommended Reading 458Exercises 45819 Auto_Tune Features 45919.1 Oracle Automatic Database Diagnostic Monitor (ADDM) 46019.2 Automatic Undo Management 46219.3 Data Recovery Advisor 46219.4 Memory Advisors 46219.5 MTTR Advisor 46619.6 Segment Advisor 46619.7 SQL Advisors 46719.8 SQL Performance Analyzer 46919.9 Summary 470Recommended Reading 471Exercises 471Part 4 Case Studies: Oracle Meeting Real World Performance and Scalability Challenges 47320 Case Study: Achieving High Throughput with Array Processing 47720.1 Context 47820.2 Performance Model 47920.3 Tests 48020.4 Solution 48020.5 Effects of Array Processing 48220.6 Summary 484Recommended Reading 484Exercises 48421 Case Study: Performance Comparison of Heap-Organized versus Index-Organized Tables 48521.1 Context 48621.2 Conversion from Heap-Organized to Index-Organized 48721.3 Creating Indexes 48721.4 Creating Constraints 48821.5 EXPLAIN PLANs 48821.6 Oracle SQL Traces 48921.7 Summary 490Recommended Reading 491Exercises 49122 Case Study: SQL Tuning: “IN” versus “OR” versus Global Temporary Table 49222.1 Context 49322.2 Test Program 49422.3 Observation 1: IN_CreateStatement is the Best Performer 49522.4 Observation 2: Batch Insert Saves Time 49722.5 Temptable Performed Better without an Index Hint than with an Index Hint 49822.6 Effects of APPEND Hint for Populating Temptable 49922.7 Effects of Number of Iterations 49922.8 OR and IN without the Index Hint 49922.9 Limitation on the Number of Literal Values and the Size of OR Statement 50122.10 Dealing with More Than 1000 Literal Values for an IN Based SQL Query 50122.11 A Recommendation for Dealing with 1000 Literal Value Limit in an IN Statement 50122.12 Summary 502Recommended Reading 503Exercises 50323 Case Study: Data Access Paths (Double Buffering) 50423.1 Data Access Paths in General 50523.2 Test Environments 51123.3 Test Results with Solaris on Veritas 51423.4 Test Results with Solaris on UFS 52223.5 Test Results with Windows on NTFS 52623.6 Moral of the Case Study 528Recommended Reading 529Exercises 53024 Case Study: Covering Index 53124.1 Getting to Know the Application Architecture 53324.2 Quantifying the Problems 53324.3 Analyzing Bottlenecks 53324.4 Applying Optimizations/Tunings 53524.5 Verifying the Fixes 53524.6 Moral of the Case Study 545Recommended Reading 546Exercises 54625 Case Study: CURSOR_SHARING 54725.1 The Concept of a Bind Variable 54825.2 Oracle CURSOR_SHARING Parameter 54925.3 Getting to Know the Application Architecture 55025.4 Quantifying Problems 55025.5 Analyzing Bottlenecks 55125.6 Applying Tuning: CURSOR_SHARING = FORCE 56025.7 Applying Tuning: CURSOR_SHARING = SIMILAR 56425.8 Moral of the Case Study 569Recommended Reading 569Exercises 57026 Case Study: Bulk Transactions 57126.1 Application Architecture 57226.2 Quantifying Problems 57226.3 Identifying Performance and Scalability Optimization Opportunities 57326.4 Effects of Bulk Transactions on Performance 58126.5 Moral of the Case Study 592Recommended Reading 593Exercises 59327 Case Study: Missing Statistics 59427.1 Decaying Performance due to Missing Statistics 59527.2 First Run with no Statistics 59727.3 Second Run with Missing Statistics 60427.4 Third Run with Updated Statistics 61127.5 Moral of the Case Study 618Recommended Reading 618Exercises 61828 Case Study: Misconfigured SAN Storage 62028.1 Architecture of the Apple’s Xserve RAID 62128.2 Problem Analysis 62228.3 Reconfiguring the RAID and Verifying 62628.4 Moral of the Case Study 629Recommended Reading 630Exercises 630Appendix A Oracle Product Documentations 633A.1 Oracle Database Concepts 633A.2 Oracle Database Administrator’s Guide 633A.3 Oracle Database Reference 634A.4 Oracle Database Performance Tuning Guide 634A.5 Oracle Database 2 Day + Performance Tuning Guide 634A.6 Oracle Database 2 Day DBA 634A.7 Oracle Database SQL Language Reference 634A.8 Oracle Database Sample Schemas 635A.9 Oracle Database PL/SQL Packages and Types Reference 635A.10 Oracle Database PL/SQL Language Reference 635A.11 Oracle Database JDBC Developer’s Guide and References 635Appendix B Using SQL*Plus with Oracle 636B.1 Installation 636B.2 SQL*Plus and tnsnames.ora File 637B.3 Basics of SQL*Plus 638B.4 Common SQL*Plus Commands 638B.5 Using SQL*Plus to Execute SQL Statements 639B.6 Using SQL*Plus to Execute PL/SQL Blocks 640B.7 Using SQL*Plus Autotrace to Obtain EXECUTION PLANs and Optimizer Statistics 640B.8 Using SQL*Plus Timing Command 641B.9 Exporting/Importing Oracle Databases with SQL*Plus 642B.10 Creating AWR Reports with SQL*Plus 643B.11 Checking Tablespace Usage with SQL*Plus 644B.12 Creating EM DBConsole with SQL*Plus 646Appendix C A Complete List of All Wait Events in Oracle 11g 648Appendix D A Complete List of All Metrics with the V$Statname View 656Appendix E A Complete List of All Statistics with the V$Sysstat View 667Index 681