Oracle Database Performance and Scalability: A Quantitative Approach

Oracle Database Performance and Scalability: A Quantitative Approach......Page 4
Contents......Page 8
Why This Book......Page 26
Who This Book is For......Page 27
How This Book is Organized......Page 28
Software and Hardware......Page 29
How to Use This Book......Page 30
How to Reach The Author......Page 32
ACKNOWLEDGMENTS......Page 34
INTRODUCTION......Page 36
Features of Oracle......Page 37
Objectives......Page 39
Performance versus Scalability......Page 41
PART 1: GETTING STARTED WITH ORACLE......Page 42
1 Basic Concepts......Page 44
1.1 Standard versus Flavored SQLS......Page 45
1.3 An Instance versus a Database......Page 46
Exercises......Page 47
2 Installing Oracle Software......Page 49
2.1 Installing Oracle 11g Server Software......Page 50
2.3 Creating an Oracle Database......Page 53
2.4 Installing Oracle 11g Client Software......Page 63
2.5 Oracle Grid Control versus DB Control......Page 66
Exercises......Page 68
3 Options for Accessing an Oracle Server......Page 69
3.1 A Command Line Interface (CLI) versus a GUI-Based Console......Page 70
3.2 The Oracle Enterprise Manager Java Console (OEMJC)......Page 72
3.3 Using the SQL *Plus Tool......Page 75
3.4 Oracle Enterprise Manager DBConsole......Page 77
3.5 Other Tools for Developers......Page 78
3.6 Case Study: Creating ER Diagrams with Visio via ODBC......Page 79
3.7 Case Study: Accessing Oracle in Java via JDBC......Page 82
3.8 Summary......Page 84
Exercises......Page 85
4 A Quick Tour of an Oracle Server......Page 87
4.1 New Oracle Schemas Beyond “Scott”......Page 88
4.2 Oracle Users versus Schemas......Page 89
4.3 Tablespaces, Segments, Extents, and Data Blocks......Page 91
4.4 Tables, Indexes and Index Types for Structured Data......Page 92
4.5 Domain and LOB Index Types for Unstructured Data......Page 100
4.7 Stored Procedures, Functions, and Triggers......Page 103
4.8 Referential Integrity with Foreign Keys......Page 106
Recommended Reading......Page 108
Exercises......Page 109
PART 2: ORACLE ARCHITECTURE FROM PERFORMANCE AND SCALABILITY PERSPECTIVES......Page 110
5 Understanding Oracle Architecture......Page 114
5.1 The Version History of Oracle......Page 115
5.2 Oracle Processes......Page 117
5.3 Oracle Memory Areas......Page 122
5.4 Dedicated versus Shared Oracle Server Architecture......Page 124
5.5 Performance Sensitive Initialization Parameters......Page 126
5.6 Oracle Static Data Dictionary Views......Page 129
5.7 Oracle Dynamic Performance (V$) Views......Page 130
Recommended Reading......Page 133
Exercises......Page 134
6 Oracle 10g Memory Management......Page 136
6.1 SGA Sub-Areas......Page 137
6.2 SGA Sizing: Automatic Shared Memory Management (ASMM)......Page 139
6.3 PGA Sizing: PGA_AGGREGATE_TARGET......Page 141
6.4 Summary......Page 143
Recommended Reading......Page 144
Exercises......Page 145
7 Oracle 11g Memory Management......Page 146
7.2 Memory Sizing Options Configurable at Database Creation Time......Page 147
7.3 Checking Memory Management and Usage Distribution at Run Time......Page 148
Exercises......Page 150
8 Oracle Storage Structure......Page 151
8.1 Overview......Page 152
8.2 Managing Tablespaces......Page 154
8.3 Managing Data Files......Page 157
8.4 Managing Redo Logs......Page 159
Recommended Reading......Page 160
Exercises......Page 161
9 Oracle Wait Interface (OWI)......Page 162
9.1 Ratio-based versus OWI-based Oracle Performance Tuning Methodologies......Page 163
9.2 Wait Event—The Core Concept of OWI......Page 165
9.3 Classification of Wait Events from OWI......Page 166
9.4 The Other Part (CPU Time) of the Equation Elapsed Time = CPU Time + Wait Time......Page 169
9.5 AWR as a Compass to Tuning Oracle Performance and Scalability......Page 171
Recommended Reading......Page 172
Exercises......Page 173
10 Oracle Data Consistency and Concurrency......Page 174
10.1 Select . . . for Update Statement......Page 175
10.2 ACID Properties of Transactions......Page 176
10.3 Read Phenomena and Data Inconsistencies......Page 178
10.5 Multi-Version Concurrency Control (MVCC) and Read Consistency......Page 180
10.6 Oracle Locks......Page 181
10.8 Oracle Latches......Page 184
10.10 Deadlocks......Page 185
10.11 Taking Advantage of Oracle’s Scalable Concurrency Model......Page 186
10.12 Case Study: A JDBC Example......Page 187
10.13 Summary......Page 193
Exercises......Page 194
11 Anatomy of an Oracle Automatic Workload Repository (AWR) Report......Page 196
11.1 Importance of Performance Statistics......Page 197
11.2 AWR Report Header......Page 200
11.3.1 Cache Sizes......Page 201
11.3.2 Load Profile......Page 202
11.3.3 Instance Efficiency Percentages (Target 100%)......Page 204
11.3.5 Top Five Timed Events......Page 205
11.4 Main Report......Page 206
11.5 Wait Events Statistics......Page 207
11.5.1 Time Model Statistics......Page 208
11.5.3 Wait Events......Page 209
11.5.5 Operating System Statistics......Page 211
11.5.6 Service Statistics......Page 212
11.6 SQL Statistics......Page 213
11.6.1 SQL ordered by Elapsed Time......Page 214
11.6.3 SQL ordered by Gets......Page 215
11.6.4 SQL ordered by Reads......Page 216
11.6.5 SQL ordered by Executions......Page 217
11.6.8 SQL ordered by Version Count......Page 218
11.6.9 Complete List of SQL Text......Page 219
11.7.1 Instance Activity Stats......Page 220
11.7.2 Instance Activity Stats—Absolute Values......Page 231
11.8 IO Stats......Page 232
11.8.2 File IO Stats......Page 233
11.10 Advisory Statistics......Page 234
11.10.2 Buffer Pool Advisory......Page 235
11.10.3 PGA Aggr Summary......Page 236
11.10.5 PGA Aggr Target Histogram......Page 237
11.10.6 PGA Memory Advisory......Page 238
11.10.8 SGA Target Advisory......Page 239
11.10.10 Java Pool Advisory......Page 240
11.11 Wait Statistics......Page 241
11.12 Undo Statistics......Page 242
11.13.1 Latch Activity......Page 243
11.13.2 Latch Sleep Breakdown......Page 248
11.13.3 Latch Miss Sources......Page 249
11.14.1 Segments by Logical Reads......Page 250
11.14.2 Segments by Physical Reads......Page 251
11.14.5 Segments by Buffer Busy Waits......Page 252
11.15 Dictionary Cache Stats......Page 253
11.17.1 Process Memory Summary......Page 254
11.17.2 SGA Memory Summary......Page 255
11.17.3 SGA Breakdown Difference......Page 256
11.18 Streams Statistics......Page 257
11.20 init.ora Parameters......Page 259
Recommended Reading......Page 260
Exercises......Page 261
12.1 Oracle 8i New Features......Page 262
12.1.1 Java......Page 263
12.1.2 Oracle interMedia, Spatial, Time Series, andVisual Image Retrieval......Page 264
12.1.5 Locally Managed Tablespaces......Page 265
12.1.9 Non-Blocking OCI (Oracle Call Interface)......Page 266
12.1.12 Enhanced Partitioning......Page 267
12.2 Oracle 9i New Features......Page 268
12.2.1 Real Application Clusters (RAC)......Page 269
12.2.2 Data Guard......Page 271
12.2.5 Dynamic Sampling of Optimizer Statistics......Page 274
12.2.6 Cloning Production Database with Oracle Enterprise Manager......Page 275
12.3 Oracle 10g New Features......Page 276
12.3.1 Automatic Storage Management (ASM)......Page 277
12.3.3 Database Replay......Page 279
12.3.8 Automatic Optimizer Statistics Gathering......Page 280
12.3.10 Grid Computing......Page 282
12.4 Oracle 11g New Features......Page 283
12.4.3 Database Resident Connection Pool (DRCP)......Page 284
12.4.4 Server Result Cache......Page 285
12.4.5 Database Smart Flash Cache......Page 286
12.4.8 Partitioning Enhancements......Page 287
12.4.9 SQL Plan Management......Page 288
12.4.12 Virtual Columns......Page 289
Exercises......Page 290
13 Top 10 Oracle Performance and Scalability Features......Page 292
13.1 Real Application Clustering (RAC)......Page 293
13.3 Proven Transaction and Concurrency Models......Page 295
13.5 Efficient Parallel Processing with Modern Multi-Core CPUs......Page 296
13.7 An All-Encompassing, Powerful Performance, and Scalability Troubleshooting Tool—AWR......Page 297
13.11 Summary......Page 298
Exercises......Page 299
14 Oracle-Based Application Performance and Scalability by Design......Page 301
14.1 Rapid Development Methodologies......Page 303
14.2.1 Vision......Page 304
14.2.3 ROI Analysis......Page 305
14.2.5 Project Team Formation......Page 306
14.3 Requirements Gathering......Page 307
14.3.1 Use Cases......Page 308
14.3.3 Business Processes, Entities, and Business Rules......Page 309
14.4 Conceptual Design via Data Modeling......Page 310
14.4.1 Entity-Relationship Diagramming......Page 311
14.4.2 The Information Engineering (IE) Format for ERDs......Page 313
14.4.4 Relational Format for ERDs......Page 314
14.5 Logical Design via Normalization......Page 315
14.5.1 Operational Anomalies......Page 316
14.5.2 Review of Relation Theory......Page 317
14.5.3 Functional Dependencies and Lossless-Join Decompositions......Page 320
14.5.4 First Normal Form (1NF): Avoiding Multi-Valued Columns......Page 322
14.5.6 Third Normal Form (3NF): Eliminating Transitive Dependencies:......Page 323
14.5.7 Boyce-Codd Normal Form (BCNF): Eliminating Key—Non-Key Dependencies......Page 324
14.5.8 Fourth Normal Form (4NF): Trivializing or Keying Multi-Valued Dependencies......Page 325
14.5.9 Fifth Normal Form (5NF): Trivializing or Keying Join Dependencies......Page 327
14.5.11 Denormalization?......Page 329
14.6 Physical Design......Page 330
14.6.1 Naming Conventions......Page 332
14.6.2 Creating Tablespaces......Page 333
14.6.4 Creating Application Schema Objects......Page 334
14.6.5 Changing Schema Objects......Page 343
14.6.6 Enforcing Business Rules and Data Integrity......Page 344
14.6.8 Creating Sequences and Synonyms......Page 347
14.6.9 Adding Indexes......Page 348
14.6.10 Security......Page 349
14.7.1 Choosing an Effective and Efficient Coding Path......Page 350
14.7.2 Leveraging Proven Oracle Database Design Principles......Page 351
14.7.3 Leveraging Proven Application Design Patterns......Page 353
14.7.4 Enforcing with an Effective and Efficient Testing Process......Page 354
14.9 Continuous Improvements......Page 357
14.10 Summary......Page 358
Recommended Reading......Page 359
Exercises......Page 360
15 Project: Soba—A Secure Online Banking Application on Oracle......Page 361
15.1.1 Prerequisite Software......Page 363
15.1.2 Initial Software Stack Setup......Page 364
15.1.4 Installing SOBA on Eclipse IDE......Page 365
15.1.5 Configuring SOBA to Work with Oracle......Page 366
15.2.1 Background......Page 368
15.2.2 Spring for Building Flexible Applications Faster......Page 369
15.2.3 Spring Inversion of Control (IoC) and Dependency Injection......Page 370
15.2.4 Features of Spring 3.0......Page 371
15.3 MVC Architecture......Page 372
15.3.1 MVC Architecture in General......Page 373
15.3.2 Spring MVC in Action with SOBA......Page 375
15.4 Spring MVC Framework Applied to SOBA......Page 377
15.4.1 Spring DispatcherServlet and WebApplicationContext......Page 378
15.4.2 Logic Flow of SOBA Defined in Spring MVC Framework......Page 382
15.4.3 AWeb Entry Point Defined in a Spring MVC Web Form......Page 383
15.4.4 Handler Mapping......Page 385
15.4.5 Implementing Spring Controllers......Page 388
15.4.6 A Typical View Defined in a Spring MVC Web Form......Page 393
15.4.7 A Typical Form Success Controller and its Resultant View......Page 397
15.4.8 POJOs Referenced in the CreateCustomerFormController......Page 399
15.5 Hibernate Object-Relational Mapping (ORM) Applied to SOBA......Page 403
15.5.1 Benefits of Using Hibernate......Page 404
15.5.2 Metadata Mapping with Hibernate......Page 405
15.5.3 Configuring Hibernate to Work with Oracle......Page 406
15.5.4 Hibernate DAO......Page 408
15.6.1 Introduction to RESTful Web Services......Page 411
15.6.2 RESTful Constraints......Page 412
15.6.3 RESTful Interface Design Principles......Page 413
15.6.4 Spring’s Support for RESTful Web Services......Page 414
15.6.5 Server Code......Page 415
15.6.6 Client Code......Page 418
15.7 Spring Security Applied to SOBA......Page 421
15.7.2 Security Configured in web.xml......Page 422
15.7.3 Security Configured in soba-security.xml......Page 423
15.8 Spring ACL Applied to SOBA......Page 429
15.8.2 Configuring Spring ACL......Page 430
15.8.3 Maintaining ACLs for SOBA Domain Objects......Page 433
15.8.4 Applying ACLs to Business Operations......Page 439
15.8.5 Testing ACLs with SOBA......Page 441
Summary......Page 448
Exercises......Page 449
PART 3: OPTIMIZING ORACLE PERFORMANCE AND SCALABILITY......Page 450
16 Logistics of the Oracle Cost-Based Optimizer (CBO)......Page 452
16.1 Life of a SQL Statement in Oracle......Page 453
16.2 Oracle SQL Optimizer: Rule-Based versus Cost-Based......Page 455
16.3 CBO Statistics......Page 456
16.4 Pivot Role of Gathering Database Statistics to CBO......Page 457
16.5 Methods of Gathering CBO Statistics......Page 459
16.7 Explain Plan—A Handle to CBO......Page 460
16.8 Data Access Methods—CBO’s Footprints......Page 461
16.9 Looking Up CBO’s Plan Hidden in V$SQL_PLAN......Page 462
16.10 When CBO may Generate Suboptimum Execution Plans......Page 463
Recommended Reading......Page 464
Exercises......Page 465
17 Oracle SQL Tuning......Page 466
17.1 Tuning Joins......Page 467
17.2 Tuning Subqueries......Page 472
17.3 Case Study: Performance of SUBQUERY versus JOIN......Page 474
17.4 Case Study: Performance of IN versus EXISTS......Page 478
17.5 Case Study: A SQL Tuning Yielded a 12x Performance Gain......Page 479
Recommended Reading......Page 482
Exercises......Page 483
18 Oracle Indexing......Page 484
18.1 Rules of Thumb on Indexing......Page 485
18.2 Creating and Using Ubiquitous b-Tree Indexes......Page 486
18.3 Advanced Indexing Scheme I: Covering Indexes versus Index-Organized Tables......Page 487
18.4 Advanced Indexing Scheme II: Function-Based Indexes (FBIs)......Page 488
18.5 Unusual Indexing Scheme I: BITMAP Indexes......Page 489
18.7 Unusual Indexing Scheme III: Compressed Composite Indexes......Page 490
18.8 How To Create Oracle Indexes......Page 491
18.9 Summary......Page 492
Exercises......Page 493
19 Auto_Tune Features......Page 494
19.1 Oracle Automatic Database Diagnostic Monitor (ADDM)......Page 495
19.4 Memory Advisors......Page 497
19.6 Segment Advisor......Page 501
19.7 SQL Advisors......Page 502
19.8 SQL Performance Analyzer......Page 504
19.9 Summary......Page 505
Exercises......Page 506
PART 4: CASE STUDIES: ORACLE MEETING REAL WORLD PERFORMANCE AND SCALABILITY CHALLENGES......Page 508
20 Case Study: Achieving High Throughput with Array Processing......Page 512
20.1 Context......Page 513
20.2 Performance Model......Page 514
20.4 Solution......Page 515
20.5 Effects of Array Processing......Page 517
Exercises......Page 519
21 Case Study: Performance Comparison of Heap-Organized versus Index-Organized Tables......Page 520
21.1 Context......Page 521
21.3 Creating Indexes......Page 522
21.5 EXPLAIN PLANs......Page 523
21.6 Oracle SQL Traces......Page 524
21.7 Summary......Page 525
Exercises......Page 526
22 Case Study: SQL Tuning: “IN” versus “OR” versus Global Temporary Table......Page 527
22.1 Context......Page 528
22.2 Test Program......Page 529
22.3 Observation 1: IN_CreateStatement is the Best Performer......Page 530
22.4 Observation 2: Batch Insert Saves Time......Page 532
22.5 Temptable Performed Better without an Index Hint than with an Index Hint......Page 533
22.8 OR and IN without the Index Hint......Page 534
22.11 A Recommendation for Dealing with 1000 Literal Value Limit in an IN Statement......Page 536
22.12 Summary......Page 537
Exercises......Page 538
23 Case Study: Data Access Paths (Double Buffering)......Page 539
23.1 Data Access Paths in General......Page 540
23.1.1 Data Buffering......Page 542
23.1.2 Inode Locking......Page 544
23.1.3 Write-Sync Daemon......Page 545
23.2.2 Solaris on UFS......Page 546
23.2.3 Windows on NTFS......Page 547
23.3.1 Test Run #1—145 ms Average Read Time......Page 549
23.3.2 Test Run #2—401 ms Average Read Time......Page 551
23.3.3 Test Run #3—261 ms Average Read Time......Page 553
23.3.4 Test Run #4—0.98 ms Average Read Time......Page 554
23.3.5 Analysis......Page 556
23.4.1 Test Run #1—447 ms Average Read Time......Page 557
23.4.2 Test Run #2—10ms Average Read Time......Page 559
23.4.3 Analysis......Page 560
23.5.1 Test Run—8 ms Average Read Time......Page 561
23.6 Moral of the Case Study......Page 563
Recommended Reading......Page 564
Exercises......Page 565
24 Case Study: Covering Index......Page 566
24.3 Analyzing Bottlenecks......Page 568
24.5 Verifying the Fixes......Page 570
24.5.1 Report Summary......Page 572
24.5.2 Wait Events Statistics......Page 573
24.5.3 SQL Statistics......Page 576
24.5.6 Wait Statistics......Page 579
24.6 Moral of the Case Study......Page 580
Exercises......Page 581
25 Case Study: CURSOR_SHARING......Page 582
25.1 The Concept of a Bind Variable......Page 583
25.2 Oracle CURSOR_SHARING Parameter......Page 584
25.4 Quantifying Problems......Page 585
25.5 Analyzing Bottlenecks......Page 586
25.5.1 Report Summary......Page 587
25.5.2 SQL Statistics......Page 591
25.5.3 IO Stats......Page 592
25.5.5 init.ora Parameters......Page 593
25.6 Applying Tuning: CURSOR_SHARING = FORCE......Page 595
25.6.1 Report Summary......Page 596
25.6.2 Wait Events Statistics......Page 598
25.7.1 Report Summary......Page 599
25.7.2 Wait Events Statistics......Page 601
Recommended Reading......Page 604
Exercises......Page 605
26 Case Study: Bulk Transactions......Page 606
26.2 Quantifying Problems......Page 607
26.3.1 Report Summary......Page 608
26.3.2 Wait Events Statistics......Page 610
26.3.3 SQL Statistics......Page 612
26.3.4 Wait Statistics......Page 614
26.4.1 Report Summary......Page 616
26.4.2 Wait Events Statistics......Page 618
26.4.3 SQL Statistics......Page 620
26.4.4 Wait Statistics......Page 622
26.5 Moral of the Case Study......Page 627
Exercises......Page 628
27 Case Study: Missing Statistics......Page 629
27.1 Decaying Performance due to Missing Statistics......Page 630
27.2 First Run with no Statistics......Page 632
27.2.1 Report Summary......Page 633
27.2.2 Wait Events Statistics......Page 634
27.2.3 SQL Statistics......Page 636
27.2.5 Wait Statistics......Page 637
27.2.6 init.ora Parameters......Page 638
27.3 Second Run with Missing Statistics......Page 639
27.3.1 Report Summary......Page 640
27.3.2 Wait Events Statistics......Page 641
27.3.3 SQL Statistics......Page 642
27.3.5 Wait Statistics......Page 644
27.4.1 Report Summary......Page 646
27.4.2 Wait Events Statistics......Page 648
27.4.4 SQL Statistics......Page 649
27.4.5 Wait Statistics......Page 651
Exercises......Page 653
28 Case Study: Misconfigured SAN Storage......Page 655
28.1 Architecture of the Apple’s Xserve RAID......Page 656
28.2.1 Report Summary......Page 657
28.2.2 Wait Events Statistics......Page 659
28.2.4 init.ora Parameters......Page 660
28.3.1 Report Summary......Page 661
28.3.2 Wait Events Statistics......Page 663
28.4 Moral of the Case Study......Page 664
Exercises......Page 665
A.2 Oracle Database Administrator’s Guide......Page 668
A.7 Oracle Database SQL Language Reference......Page 669
A.11 Oracle Database JDBC Developer’s Guide and References......Page 670
B.1 Installation......Page 671
B.2 SQL *Plus and tnsnames.ora File......Page 672
B.4 Common SQL *Plus Commands......Page 673
B.5 Using SQL *Plus to Execute SQL Statements......Page 674
B.7 Using SQL *Plus Autotrace to Obtain EXECUTION PLANs and Optimizer Statistics......Page 675
B.8 Using SQL *Plus Timing Command......Page 676
B.9 Exporting/Importing Oracle Databases with SQL *Plus......Page 677
B.10 Creating AWR Reports with SQL *Plus......Page 678
B.11 Checking Tablespace Usage with SQL *Plus......Page 679
B.12 Creating EM DBConsole with SQL *Plus......Page 681
APPENDIX C: A COMPLETE LIST OF ALL WAIT EVENTS IN ORACLE 11g......Page 683
APPENDIX D: A COMPLETE LIST OF ALL METRICS WITH THE V$STATNAME VIEW......Page 691
APPENDIX E: A COMPLETE LIST OF ALL STATISTICS WITH THE V$SYSSTAT VIEW......Page 702
INDEX......Page 716