Oracle Database Performance and Scalability: A Quantitative Approach -- Contents -- PREFACE -- Why This Book -- Who This Book is For -- How This Book is Organized -- Software and Hardware -- How to Use This Book -- How to Reach The Author -- ACKNOWLEDGMENTS -- INTRODUCTION -- Features of Oracle -- Objectives -- Performance versus Scalability -- PART 1: GETTING STARTED WITH ORACLE -- 1 Basic Concepts -- 1.1 Standard versus Flavored SQLS -- 1.2 Relational versus Object-Oriented Databases -- 1.3 An Instance versus a Database -- 1.4 Summary -- Recommended Reading -- Exercises -- 2 Installing Oracle Software -- 2.1 Installing Oracle 11g Server Software -- 2.2 Configuring a Listener -- 2.3 Creating an Oracle Database -- 2.4 Installing Oracle 11g Client Software -- 2.5 Oracle Grid Control versus DB Control -- 2.6 Summary -- Recommended Reading -- Exercises -- 3 Options for Accessing an Oracle Server -- 3.1 A Command Line Interface (CLI) versus a GUI-Based Console -- 3.2 The Oracle Enterprise Manager Java Console (OEMJC) -- 3.3 Using the SQL *Plus Tool -- 3.4 Oracle Enterprise Manager DBConsole -- 3.5 Other Tools for Developers -- 3.6 Case Study: Creating ER Diagrams with Visio via ODBC -- 3.7 Case Study: Accessing Oracle in Java via JDBC -- 3.8 Summary -- Recommended Reading -- Exercises -- 4 A Quick Tour of an Oracle Server -- 4.1 New Oracle Schemas Beyond "Scott" -- 4.2 Oracle Users versus Schemas -- 4.3 Tablespaces, Segments, Extents, and Data Blocks -- 4.4 Tables, Indexes and Index Types for Structured Data -- 4.5 Domain and LOB Index Types for Unstructured Data -- 4.6 Views, Materialized Views, and Synonyms -- 4.7 Stored Procedures, Functions, and Triggers -- 4.8 Referential Integrity with Foreign Keys -- 4.9 Summary -- Recommended Reading -- Exercises -- PART 2: ORACLE ARCHITECTURE FROM PERFORMANCE AND SCALABILITY PERSPECTIVES.

5 Understanding Oracle Architecture -- 5.1 The Version History of Oracle -- 5.2 Oracle Processes -- 5.3 Oracle Memory Areas -- 5.4 Dedicated versus Shared Oracle Server Architecture -- 5.5 Performance Sensitive Initialization Parameters -- 5.6 Oracle Static Data Dictionary Views -- 5.7 Oracle Dynamic Performance (V) Views -- 5.8 Summary -- Recommended Reading -- Exercises -- 6 Oracle 10g Memory Management -- 6.1 SGA Sub-Areas -- 6.2 SGA Sizing: Automatic Shared Memory Management (ASMM) -- 6.3 PGA Sizing: PGA_AGGREGATE_TARGET -- 6.4 Summary -- Recommended Reading -- Exercises -- 7 Oracle 11g Memory Management -- 7.1 Automatic Memory Management (AMM) -- 7.2 Memory Sizing Options Configurable at Database Creation Time -- 7.3 Checking Memory Management and Usage Distribution at Run Time -- 7.4 Summary -- Recommended Reading -- Exercises -- 8 Oracle Storage Structure -- 8.1 Overview -- 8.2 Managing Tablespaces -- 8.3 Managing Data Files -- 8.4 Managing Redo Logs -- 8.5 Summary -- Recommended Reading -- Exercises -- 9 Oracle Wait Interface (OWI) -- 9.1 Ratio-based versus OWI-based Oracle Performance Tuning Methodologies -- 9.2 Wait Event-The Core Concept of OWI -- 9.3 Classification of Wait Events from OWI -- 9.4 The Other Part (CPU Time) of the Equation Elapsed Time = CPU Time + Wait Time -- 9.5 AWR as a Compass to Tuning Oracle Performance and Scalability -- 9.6 Summary -- Recommended Reading -- Exercises -- 10 Oracle Data Consistency and Concurrency -- 10.1 Select . . . for Update Statement -- 10.2 ACID Properties of Transactions -- 10.3 Read Phenomena and Data Inconsistencies -- 10.4 Oracle Isolation Levels -- 10.5 Multi-Version Concurrency Control (MVCC) and Read Consistency -- 10.6 Oracle Locks -- 10.7 Lock Escalations versus Conversions -- 10.8 Oracle Latches -- 10.9 Oracle Enqueues -- 10.10 Deadlocks.

10.11 Taking Advantage of Oracle's Scalable Concurrency Model -- 10.12 Case Study: A JDBC Example -- 10.13 Summary -- Recommended Reading -- Exercises -- 11 Anatomy of an Oracle Automatic Workload Repository (AWR) Report -- 11.1 Importance of Performance Statistics -- 11.2 AWR Report Header -- 11.3 Report Summary -- 11.3.1 Cache Sizes -- 11.3.2 Load Profile -- 11.3.3 Instance Efficiency Percentages (Target 100%) -- 11.3.4 Shared Pool Statistics -- 11.3.5 Top Five Timed Events -- 11.4 Main Report -- 11.5 Wait Events Statistics -- 11.5.1 Time Model Statistics -- 11.5.2 Wait Class -- 11.5.3 Wait Events -- 11.5.4 Background Wait Events -- 11.5.5 Operating System Statistics -- 11.5.6 Service Statistics -- 11.5.7 Service Wait Class Stats -- 11.6 SQL Statistics -- 11.6.1 SQL ordered by Elapsed Time -- 11.6.2 SQL ordered by CPU Time -- 11.6.3 SQL ordered by Gets -- 11.6.4 SQL ordered by Reads -- 11.6.5 SQL ordered by Executions -- 11.6.6 SQL ordered by Parse Calls -- 11.6.7 SQL ordered by Sharable Memory -- 11.6.8 SQL ordered by Version Count -- 11.6.9 Complete List of SQL Text -- 11.7 Instance Activity Statistics -- 11.7.1 Instance Activity Stats -- 11.7.2 Instance Activity Stats-Absolute Values -- 11.7.3 Instance Activity Stats-Thread Activity -- 11.8 IO Stats -- 11.8.1 Tablespace IO Stats -- 11.8.2 File IO Stats -- 11.9 Buffer Pool Statistics -- 11.10 Advisory Statistics -- 11.10.1 Instance Recovery Stats -- 11.10.2 Buffer Pool Advisory -- 11.10.3 PGA Aggr Summary -- 11.10.4 PGA Aggr Target Stats -- 11.10.5 PGA Aggr Target Histogram -- 11.10.6 PGA Memory Advisory -- 11.10.7 Shared Pool Advisory -- 11.10.8 SGA Target Advisory -- 11.10.9 Streams Pool Advisory -- 11.10.10 Java Pool Advisory -- 11.11 Wait Statistics -- 11.12 Undo Statistics -- 11.13 Latch Statistics -- 11.13.1 Latch Activity -- 11.13.2 Latch Sleep Breakdown -- 11.13.3 Latch Miss Sources.

11.13.4 Parent and Child Latch Statistics -- 11.14 Segment Statistics -- 11.14.1 Segments by Logical Reads -- 11.14.2 Segments by Physical Reads -- 11.14.3 Segments by Row Lock Waits -- 11.14.4 Segments by ITLWaits -- 11.14.5 Segments by Buffer Busy Waits -- 11.15 Dictionary Cache Stats -- 11.16 Library Cache Activity -- 11.17 Memory Statistics -- 11.17.1 Process Memory Summary -- 11.17.2 SGA Memory Summary -- 11.17.3 SGA Breakdown Difference -- 11.18 Streams Statistics -- 11.19 Resource Limit Stats -- 11.20 init.ora Parameters -- 11.21 Summary -- Recommended Reading -- Exercises -- 12 Oracle Advanced Features and Options -- 12.1 Oracle 8i New Features -- 12.1.1 Java -- 12.1.2 Oracle interMedia, Spatial, Time Series, andVisual Image Retrieval -- 12.1.3 Oracle Parallel Server -- 12.1.4 Optimizer Plan Stability -- 12.1.5 Locally Managed Tablespaces -- 12.1.6 Online Index Creation and Rebuild -- 12.1.7 Online Read-Only Tablespaces -- 12.1.8 Temporary Tables -- 12.1.9 Non-Blocking OCI (Oracle Call Interface) -- 12.1.10 Function-Based Indexes -- 12.1.11 Logical ROWIDs -- 12.1.12 Enhanced Partitioning -- 12.1.13 Connection Load Balancing -- 12.1.14 Client Load Balancing -- 12.1.15 Oracle Enterprise Manager -- 12.2 Oracle 9i New Features -- 12.2.1 Real Application Clusters (RAC) -- 12.2.2 Data Guard -- 12.2.3 Performance Tuning Intelligent Advisors -- 12.2.4 Actual Operation-Level Query Statistics -- 12.2.5 Dynamic Sampling of Optimizer Statistics -- 12.2.6 Cloning Production Database with Oracle Enterprise Manager -- 12.2.7 Renaming Columns and Constraints -- 12.2.8 Dynamic Memory Pools -- 12.2.9 Flashback Query -- 12.2.10 List Partitioning -- 12.3 Oracle 10g New Features -- 12.3.1 Automatic Storage Management (ASM) -- 12.3.2 Asynchronous Commit -- 12.3.3 Database Replay -- 12.3.4 Read Performance Statistics Directly from the SGA.

12.3.5 Automatic Workload Repository (AWR) -- 12.3.6 Automatic Database Diagnostic Monitor (ADDM) -- 12.3.7 Automatic Shared Memory Tuning -- 12.3.8 Automatic Optimizer Statistics Gathering -- 12.3.9 SQL Tuning Features -- 12.3.10 Grid Computing -- 12.4 Oracle 11g New Features -- 12.4.1 Automatic Memory Management -- 12.4.2 Intelligent Cursor Sharing -- 12.4.3 Database Resident Connection Pool (DRCP) -- 12.4.4 Server Result Cache -- 12.4.5 Database Smart Flash Cache -- 12.4.6 Database Replay SQL Performance Analyzer (SPA) Integration -- 12.4.7 I/O Calibration -- 12.4.8 Partitioning Enhancements -- 12.4.9 SQL Plan Management -- 12.4.10 Zero-Size Unusable Indexes and Index Partitions -- 12.4.11 Invisible Indexes -- 12.4.12 Virtual Columns -- 12.5 Summary -- Recommended Reading -- Exercises -- 13 Top 10 Oracle Performance and Scalability Features -- 13.1 Real Application Clustering (RAC) -- 13.2 Dedicated versus Shared Server Models -- 13.3 Proven Transaction and Concurrency Models -- 13.4 A Highly Efficient SQL Optimization Engine -- 13.5 Efficient Parallel Processing with Modern Multi-Core CPUs -- 13.6 Partitioning -- 13.7 An All-Encompassing, Powerful Performance, and Scalability Troubleshooting Tool-AWR -- 13.8 The Most Comprehensive Set of Internal Performance Metrics -- 13.9 Database Resident Connection Pool -- 13.10 In-Memory Database Cache (IMDB) -- 13.11 Summary -- Recommended Reading -- Exercises -- 14 Oracle-Based Application Performance and Scalability by Design -- 14.1 Rapid Development Methodologies -- 14.2 Planning -- 14.2.1 Vision -- 14.2.2 Objectives -- 14.2.3 ROI Analysis -- 14.2.4 Feasibility Study -- 14.2.5 Project Team Formation -- 14.3 Requirements Gathering -- 14.3.1 Use Cases -- 14.3.2 User Views -- 14.3.3 Business Processes, Entities, and Business Rules -- 14.4 Conceptual Design via Data Modeling.

14.4.1 Entity-Relationship Diagramming.