The innovative performance and scalability features with each newer edition of the Oracle database system can present challenges for users. This book teaches software developers and students how to effectively deal with Oracle performance and scalability issues throughout the entire life cycle of developing Oracle-based applications. Using real-world case studies to deliver key theories and concepts, the book introduces highly dependable and ready-to-apply performance and scalability optimization techniques, augmented with Top 10 Oracle Performance and Scalability Features as well as a supplementary support website.

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 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 Da…