Oracle Database Performance and Scalability

PREFACE xxv ACKNOWLEDGMENTS xxxiii INTRODUCTION 1 PART 1 GETTING STARTED WITH ORACLE 7 1 Basic Concepts 9 2 Installing Oracle Software 14 3 Options for Accessing an Oracle Server 34 4 A Quick Tour of an Oracle Server 52 PART 2 ORACLE ARCHITECTURE FROM PERFORMANCE AND SCALABILITY PERSPECTIVES 75 5 Understanding Oracle Architecture 79 6 Oracle 10g Memory Management 101 7 Oracle 11g Memory Management 111 8 Oracle Storage Structure 116 9 Oracle Wait Interface (OWI) 127 10 Oracle Data Consistency and Concurrency 139 11 Anatomy of an Oracle Automatic Workload Repository (AWR) Report 161 12 Oracle Advanced Features and Options 227 13 Top 10 Oracle Performance and Scalability Features 257 14 Oracle-Based Application Performance and Scalability by Design 266 15 Project: Soba--A Secure Online Banking Application on Oracle 326 PART 3 OPTIMIZING ORACLE PERFORMANCE AND SCALABILITY 415 16 Logistics of the Oracle Cost-Based Optimizer (CBO) 417 17 Oracle SQL Tuning 431 18 Oracle Indexing 449 19 Auto_Tune Features 459 PART 4 CASE STUDIES: ORACLE MEETING REAL WORLD PERFORMANCE AND SCALABILITY CHALLENGES 473 20 Case Study: Achieving High Throughput with Array Processing 477 21 Case Study: Performance Comparison of Heap-Organized versus Index-Organized Tables 485 22 Case Study: SQL Tuning: "IN" versus "OR" versus Global Temporary Table 492 23 Case Study: Data Access Paths (Double Buffering) 504 24 Case Study: Covering Index 531 25 Case Study: CURSOR_SHARING 547 26 Case Study: Bulk Transactions 571 27 Case Study: Missing Statistics 594 28 Case Study: Misconfigured SAN Storage 620 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 SQLPLUS WITH ORACLE 636 B.1 Installation 636 B.2 SQLPlus and tnsnames.ora File 637 B.3 Basics of SQLPlus 638 B.4 Common SQLPlus Commands 638 B.5 Using SQLPlus to Execute SQL Statements 639 B.6 Using SQLPlus to Execute PL/SQL Blocks 640 B.7 Using SQLPlus Autotrace to Obtain EXECUTION PLANs and Optimizer Statistics 640 B.8 Using SQLPlus Timing Command 641 B.9 Exporting/Importing Oracle Databases with SQLPlus 642 B.10 Creating AWR Reports with SQLPlus 643 B.11 Checking Tablespace Usage with SQLPlus 644 B.12 Creating EM DBConsole with SQLPlus 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