Oracle 9i. Database Performance Guide and Reference

Contents......Page 3
Send Us Your Comments......Page 19
Preface......Page 21
Organization......Page 22
Related Documentation......Page 26
Conventions......Page 27
Documentation Accessibility......Page 29
What’s New in Oracle Performance?......Page 31
Part I Writing and Tuning SQL......Page 35
1 Introduction to the Optimizer......Page 37
Overview of SQL Processing Architecture......Page 38
Overview of the Optimizer......Page 39
Steps in Optimizer Operations......Page 40
Understanding Execution Plans......Page 41
Choosing an Optimizer Approach and Goal......Page 46
How the CBO Optimizes SQL Statements for Fast Response......Page 49
Features that Require the CBO......Page 50
Understanding the Cost-Based Optimizer (CBO)......Page 51
Architecture of the CBO......Page 52
Understanding Access Paths for the CBO......Page 59
Full Table Scans......Page 60
Sample Table Scans......Page 63
ROWID Scans......Page 64
Index Scans......Page 65
How the CBO Chooses an Access Path......Page 75
How the CBO Executes Join Statements......Page 78
How the CBO Chooses the Join Method......Page 79
Nested Loop Joins......Page 80
Nested Loop Outer Joins......Page 84
Hash Joins......Page 85
Hash Join Outer Joins......Page 87
Sort Merge Joins......Page 89
Cartesian Joins......Page 93
Full Outer Joins......Page 98
How the CBO Chooses Execution Plans for Joins......Page 99
How the CBO Executes Semi-Joins......Page 100
How the CBO Executes Star Queries......Page 101
OPTIMIZER_FEATURES_ENABLE Parameter......Page 102
Other CBO Parameters......Page 104
Understanding User-Defined Statistics......Page 108
Understanding User-Defined Costs......Page 109
2 Optimizer Operations......Page 111
How the CBO Evaluates IN-List Iterators......Page 112
How the CBO Evaluates Concatenation......Page 116
How the CBO Evaluates Remote Operations......Page 119
How the CBO Executes Distributed Statements......Page 122
How the CBO Executes Sort Operations......Page 123
How the CBO Executes Views......Page 126
How the CBO Evaluates Constants......Page 128
How the CBO Evaluates the UNION/UNION ALL Operators......Page 129
How the CBO Evaluates the LIKE Operator......Page 130
How the CBO Evaluates the ANY or SOME Operator......Page 131
How the CBO Evaluates the BETWEEN Operator......Page 132
How the CBO Evaluates Transitivity......Page 133
How the CBO Optimizes Common Subexpressions......Page 134
How the CBO Evaluates DETERMINISTIC Functions......Page 136
How the Optimizer Transforms SQL Statements......Page 137
How the CBO Transforms ORs into Compound Queries......Page 138
How the CBO Unnests Subqueries......Page 141
How the CBO Merges Views......Page 143
How the CBO Pushes Predicates......Page 146
How the CBO Executes Compound Queries......Page 156
3 Gathering Optimizer Statistics......Page 161
Understanding Statistics......Page 162
Generating Statistics......Page 163
Using the DBMS_STATS Package......Page 165
Using the ANALYZE Statement......Page 172
Missing Statistics......Page 173
Managing Statistics......Page 174
Verifying Table Statistics......Page 176
Verifying Index Statistics......Page 177
Verifying Column Statistics......Page 178
Using Histograms......Page 180
Creating Histograms......Page 181
Types of Histograms......Page 182
Verifying Histogram Statistics......Page 184
4 Understanding Indexes and Clusters......Page 187
Tuning the Logical Structure......Page 188
Choosing Columns and Expressions to Index......Page 189
Choosing Composite Indexes......Page 190
Re-creating Indexes......Page 192
Compacting Indexes......Page 193
Using Enabled Novalidated Constraints......Page 194
Using Function-based Indexes......Page 195
Using Bitmap Indexes......Page 197
When to Use Bitmap Indexes......Page 198
Using Bitmap Indexes with Good Performance......Page 200
Initialization Parameters for Bitmap Indexing......Page 202
Using Bitmap Access Plans on Regular B-tree Indexes......Page 203
Using Domain Indexes......Page 204
Using Clusters......Page 205
Using Hash Clusters......Page 206
5 Optimizer Hints......Page 209
Specifying Hints......Page 210
Hints for Optimization Approaches and Goals......Page 214
Hints for Access Paths......Page 217
Hints for Query Transformations......Page 225
Hints for Join Orders......Page 229
Hints for Join Operations......Page 231
Hints for Parallel Execution......Page 236
Additional Hints......Page 241
Using Hints with Views......Page 246
6 Optimizing SQL Statements......Page 251
Parallelize the Workload......Page 252
Identifying Resource-Intensive SQL......Page 253
Gathering Data on the SQL Identified......Page 254
Overview of SQL Statement Tuning......Page 255
Verifying Optimizer Statistics......Page 256
Restructuring the SQL Statements......Page 257
Controlling the Access Path and Join Order with Hints......Page 266
Restructuring the Data......Page 270
Visiting Data as Few Times as Possible......Page 271
7 Using Plan Stability......Page 275
Using Hints with Plan Stability......Page 276
Creating Outlines......Page 278
Using and Editing Stored Outlines......Page 280
Moving Outline Tables......Page 283
Using Outlines to Move to the Cost-Based Optimizer......Page 285
Upgrading and the Cost-Based Optimizer......Page 286
8 Using the Rule-Based Optimizer......Page 289
Understanding Access Paths for the RBO......Page 290
Choosing Execution Plans for Joins with the RBO......Page 303
Transforming ORs into Compound Queries with the RBO......Page 305
Using Alternative SQL Syntax......Page 308
Part II SQL-Related Performance Tools......Page 311
9 Using EXPLAIN PLAN......Page 313
How Execution Plans Can Change......Page 314
Looking Beyond Execution Plans......Page 315
Identifying Statements for EXPLAIN PLAN......Page 316
Displaying PLAN_TABLE Output......Page 317
Reading EXPLAIN PLAN Output......Page 318
Viewing Bitmap Indexes with EXPLAIN PLAN......Page 322
Examples of Displaying Range and Hash Partitioning with EXPLAIN PLAN......Page 323
Examples of Pruning Information with Composite Partitioned Objects......Page 325
Examples of Partial Partition-wise Joins......Page 328
Examples of Full Partition-wise Joins......Page 329
Examples of INLIST ITERATOR and EXPLAIN PLAN......Page 330
Example of Domain Indexes and EXPLAIN PLAN......Page 331
PLAN_TABLE Columns......Page 332
10 Using SQL Trace and TKPROF......Page 341
Understanding the SQL Trace Facility......Page 342
Using the SQL Trace Facility and TKPROF......Page 343
Step 1: Setting Initialization Parameters for Trace File Management......Page 344
Step 2: Enabling the SQL Trace Facility......Page 345
Step 3: Formatting Trace Files with TKPROF......Page 346
Step 4: Interpreting TKPROF Output......Page 351
Step 5: Storing SQL Trace Facility Statistics......Page 356
Avoiding the Read Consistency Trap......Page 359
Avoiding the Schema Trap......Page 360
Avoiding the Time Trap......Page 361
Sample TKPROF Output......Page 362
Sample TKPROF Body......Page 363
Sample TKPROF Summary......Page 369
11 Using Autotrace in SQL*Plus......Page 371
Execution Plan......Page 372
Statistics......Page 373
Tracing Parallel and Distributed Queries......Page 376
Monitoring Disk Reads and Buffer Gets......Page 378
12 Using Oracle Trace......Page 381
Event Sets......Page 382
Using the Oracle Trace Command-Line Interface......Page 383
Using Initialization Parameters to Control Oracle Trace......Page 387
Controlling Oracle Trace Collections from PL/SQL......Page 390
Accessing Oracle Trace Collection Results......Page 392
Formatting Oracle Trace Data to Oracle Tables......Page 393
Running the Oracle Trace Reporting Utility......Page 394
Oracle Server Events......Page 395
Data Items Collected for Events......Page 396
Items Associated with Each Event......Page 402
Oracle Trace Configuration......Page 412
Formatter Tables......Page 416
Part III Creating a Database for Good Performance......Page 419
13 Building a Database for Performance......Page 421
Parameters Necessary for Initial Database Creation......Page 422
The CREATE DATABASE Statement......Page 423
Running Data Dictionary Scripts......Page 424
Sizing Redo Log Files......Page 425
Creating Tables for Good Performance......Page 426
Efficient Index Creation......Page 428
Initial Instance Configuration......Page 429
Configuring Rollback Segments......Page 431
Setting up OS, Database, and Network Monitoring......Page 432
14 Memory Configuration and Use......Page 433
Dynamically Changing Cache Sizes......Page 434
Operating System Memory Use......Page 435
Iteration During Configuration......Page 436
Sizing the Buffer Cache......Page 437
Interpreting and Using the Buffer Cache Statistics......Page 441
Considering Multiple Buffer Pools......Page 443
Determining Which Segments Have Many Buffers in the Pool......Page 445
Keep Pool......Page 447
Recycle Pool......Page 448
Configuring and Using the Shared Pool and Large Pool......Page 449
Shared Pool Concepts......Page 450
Using the Shared Pool Effectively......Page 454
Sizing the Shared Pool......Page 458
Interpreting Shared Pool Statistics......Page 464
Consider using the Large Pool......Page 465
Consider Using CURSOR_SPACE_FOR_TIME......Page 469
Consider Configuring the Reserved Pool......Page 470
Consider Keeping Large Objects to Prevent Aging......Page 473
Consider CURSOR_SHARING for Existing Applications......Page 474
Configuring and Using the Redo Log Buffer......Page 476
Log Buffer Statistics......Page 477
Configuring the PGA Working Memory......Page 478
Automatic PGA Memory Management......Page 480
Configuring SORT_AREA_SIZE......Page 486
Reducing Total Memory Usage......Page 492
15 I/O Configuration and Design......Page 493
Understanding I/O......Page 494
Basic I/O Configuration......Page 497
Determining Application I/O Characteristics......Page 498
Know your I/O System......Page 501
Match I/O Requirements with the I/O System......Page 502
Layout the Files using Operating System or Hardware Striping......Page 503
Manually Distributing I/O......Page 506
When to Separate Files......Page 507
Three Sample Configurations......Page 509
Oracle-Managed Files......Page 510
Choosing Data Block Size......Page 511
16 Understanding Operating System Resources......Page 515
Evaluating Raw Devices......Page 516
Using Operating System Resource Managers......Page 517
Performance Hints on UNIX-Based Systems......Page 519
Performance Hints on Mainframe Computers......Page 520
Understanding CPU......Page 521
Finding System CPU Utilization......Page 523
17 Configuring Instance Recovery Performance......Page 527
Checkpointing and Cache Recovery......Page 528
Reducing Checkpoint Frequency to Optimize Runtime Performance......Page 529
Use Fast-Start Checkpointing to Limit Instance Recovery Time......Page 530
Set LOG_CHECKPOINT_INTERVAL to Influence the Number of Redo Logs......Page 532
Initialization Parameters that Influence Cache Recovery Time......Page 533
Monitoring Cache Recovery......Page 534
Tuning Transaction Recovery......Page 541
18 Configuring Undo and Temporary Segments......Page 545
Configuring Rollback Segments......Page 546
Configuring Temporary Tablespaces......Page 548
19 Configuring Shared Servers......Page 551
Identifying Contention Using the Dispatcher-Specific Views......Page 552
Reducing Contention for Dispatcher Processes......Page 553
Reducing Contention for Shared Servers......Page 555
Determining the Optimal Number of Dispatchers and Shared Servers......Page 559
Part IV System-Related Performance Tools......Page 561
20 Oracle Tools to Gather Database Statistics......Page 563
Principles of Data Gathering......Page 564
Interpreting Statistics......Page 565
Oracle Enterprise Manager Diagnostics Pack......Page 566
Statspack......Page 567
V$ Performance Views......Page 568
21 Using Statspack......Page 571
Statspack vs. BSTAT/ESTAT......Page 572
Database Space Requirements for Statspack......Page 573
Interactive Statspack Installation......Page 574
Taking a Statspack Snapshot......Page 576
Running a Statspack Performance Report......Page 578
Configuring the Amount of Data Captured in Statspack......Page 584
Time Units Used for Wait Events......Page 588
Managing and Sharing Statspack Performance Data......Page 589
Oracle Real Application Clusters Considerations with Statspack......Page 592
Removing Statspack......Page 593
Scripts for Upgrading Statspack......Page 594
Statspack Limitations......Page 595
Part V Optimizing Instance Performance......Page 597
22 Instance Tuning......Page 599
Baselines......Page 600
When to Tune......Page 601
Performance Tuning Steps......Page 602
Define the Problem......Page 603
Examine the Host System......Page 604
Examine the Oracle Statistics......Page 607
Implement and Measure Change......Page 609
Examine Load......Page 610
Using Wait Event Statistics to Drill Down to Bottlenecks......Page 611
Table of Wait Events and Potential Causes......Page 613
Additional Statistics......Page 614
Wait Events......Page 617
SQL*Net......Page 618
buffer busy waits......Page 620
db file scattered read......Page 623
db file sequential read......Page 625
direct path read......Page 626
direct path write......Page 628
enqueue......Page 629
free buffer waits......Page 632
latch free......Page 634
log file switch......Page 639
log file sync......Page 640
Idle Wait Events......Page 641
23 Tuning Networks......Page 643
Understanding Connection Models......Page 644
Detecting Network Problems......Page 647
Understanding Latency and Bandwidth......Page 648
Finding Network Bottlenecks......Page 650
Dissecting Network Bottlenecks......Page 652
Using Array Interfaces......Page 655
Using Connection Manager......Page 656
Part VI Performance-Related Reference Information......Page 657
24 Dynamic Performance Views for Tuning......Page 659
Counter/Accumulator Views......Page 660
Information Views......Page 661
V$DB_OBJECT_CACHE......Page 662
V$FILESTAT......Page 664
V$LATCH......Page 667
V$LATCH_CHILDREN......Page 670
V$LATCH_HOLDER......Page 671
V$LIBRARYCACHE......Page 673
V$LOCK......Page 675
V$MYSTAT......Page 680
V$OPEN_CURSOR......Page 681
V$PARAMETER and V$SYSTEM_PARAMETER......Page 683
V$PROCESS......Page 685
V$ROLLSTAT......Page 687
V$ROWCACHE......Page 688
V$SESSION......Page 690
V$SESSION_EVENT......Page 694
V$SESSION_WAIT......Page 696
V$SESSTAT......Page 700
V$SQL......Page 704
V$SQL_PLAN......Page 705
V$SQLAREA......Page 711
V$SQLTEXT......Page 714
V$SYSSTAT......Page 715
V$SYSTEM_EVENT......Page 721
V$UNDOSTAT......Page 724
V$WAITSTAT......Page 726
A Schemas Used in Performance Examples......Page 727
Glossary......Page 733
Index......Page 741