Oracle 8i Design Tuning

PDF Directory......Page 0
Send Us Your Comments......Page 17
Preface......Page 19
How This Book is Organized......Page 20
Related Documents......Page 22
Syntax Diagrams and Notation......Page 23
Code Examples......Page 25
Part I Introduction to Tuning......Page 27
1 Understanding Oracle Performance Tuning......Page 29
Trade-offs Between Response Time and Throughput......Page 30
Critical Resources......Page 32
Effects of Excessive Demand......Page 33
Adjustments to Relieve Problems......Page 34
Who Tunes?......Page 35
Setting User Expectations......Page 37
Evaluating Performance......Page 38
2 Performance Tuning Methods......Page 39
Proactive Tuning While Designing and Developing Systems......Page 40
Reactive Tuning to Improve Production Systems......Page 41
Prioritized Tuning Steps......Page 43
Step 1: Tune the Business Rules......Page 45
Step 2: Tune the Data Design......Page 46
Step 5: Tune Database Operations......Page 47
Step 7: Tune Memory Allocation......Page 49
Step 8: Tune I/O and Physical Structure......Page 50
Step 10: Tune the Underlying Platform(s)......Page 51
Create Minimum Repeatable Tests......Page 52
Avoid Common Errors......Page 53
Demonstrate Meeting the Objectives......Page 54
Part II  Application Design Tuning for Designers and Developers......Page 55
3 Application and System Performance Characteristics......Page 57
Online Transaction Processing (OLTP)......Page 58
Decision Support Systems......Page 60
Multipurpose Applications......Page 62
Oracle Configurations......Page 63
Distributed Systems......Page 64
Multi-Tier Systems......Page 65
Oracle Parallel Server......Page 66
Client/Server Configurations......Page 67
4 The Optimizer......Page 69
SQL Processing Architecture......Page 70
EXPLAIN PLAN......Page 71
What Is The Optimizer?......Page 72
Execution Plan......Page 73
Choosing an Optimizer Approach and Goal......Page 76
Statistics in the Data Dictionary......Page 78
Changing the Goal with Hints......Page 79
Cost-Based Optimizer (CBO)......Page 80
Architecture of the CBO......Page 81
Features that Require the CBO......Page 87
Access Paths for the CBO......Page 88
How the CBO Chooses an Access Path......Page 93
CBO Parameters......Page 96
Parameters Affecting CBO Plans......Page 97
Parameters Affecting How the Optimizer Uses Indexes......Page 98
Setting Initialization Parameters......Page 99
Extensible Optimizer......Page 100
User-Defined Costs......Page 101
Access Paths for the RBO......Page 102
Types of SQL Statements......Page 115
Optimizer Operations......Page 116
Optimizing Join Statements......Page 117
Join Operations......Page 118
How the Optimizer Chooses the Join Method......Page 124
Forcing the Join Order......Page 125
Choosing Execution Plans for Join Statements......Page 126
Optimizing Anti-Joins and Semi-Joins......Page 129
Optimizing Star Queries......Page 130
Optimizing Statements that Use Common Subexpressions......Page 131
Constants......Page 133
IN Operator......Page 134
ALL Operator......Page 135
NOT Operator......Page 136
Transitivity......Page 137
DETERMINISTIC Functions......Page 138
Transforming ORs into Compound Queries......Page 139
Transforming Complex Statements into Join Statements......Page 142
Optimizing Statements That Access Views......Page 144
Optimizing Compound Queries......Page 159
Optimizing Distributed Statements......Page 162
5 Using EXPLAIN PLAN......Page 163
Understanding EXPLAIN PLAN......Page 164
Creating the Output Table......Page 165
Output Table Columns......Page 166
Bitmap Indexes and EXPLAIN PLAN......Page 175
Displaying Range and Hash Partitioning with EXPLAIN PLAN......Page 176
Pruning Information with Composite Partitioned Objects......Page 178
Partial Partition-wise Joins......Page 181
Full Partition-wise Joins......Page 182
INLIST ITERATOR and EXPLAIN PLAN......Page 183
Domain Indexes and EXPLAIN PLAN......Page 184
EXPLAIN PLAN Restrictions......Page 185
6 Using SQL Trace and TKPROF......Page 187
Understanding the SQL Trace Facility......Page 188
Using the SQL Trace Facility and TKPROF......Page 189
Step 1: Setting Initialization Parameters for Trace File Management......Page 190
Step 2: Enabling the SQL Trace Facility......Page 191
Step 3: Formatting Trace Files with TKPROF......Page 192
Step 4: Interpreting TKPROF Output......Page 197
Step 5: Storing SQL Trace Facility Statistics......Page 202
The Read Consistency Trap......Page 205
The Schema Trap......Page 206
The Time Trap......Page 207
TKPROF Output Example......Page 208
Body......Page 209
Summary......Page 215
7 Using Optimizer Hints......Page 217
Specifying Hints......Page 218
Hints for Optimization Approaches and Goals......Page 222
Hints for Access Methods......Page 225
Hints for Join Orders......Page 234
Hints for Join Operations......Page 235
Hints for Parallel Execution......Page 240
Additional Hints......Page 246
Using Hints with Views......Page 252
8 Gathering Statistics......Page 257
Understanding Statistics......Page 258
Generating Statistics......Page 259
Using the ANALYZE Statement......Page 260
Using the DBMS_STATS Package......Page 261
Statistics Data......Page 266
Missing Statistics......Page 267
Managing Statistics......Page 268
Verifying Table Statistics......Page 269
Verifying Index Statistics......Page 270
Verifying Column Statistics......Page 271
Using Histograms......Page 273
Creating Histograms......Page 274
Types of Histograms......Page 275
Verifying Histogram Statistics......Page 277
9 Optimizing SQL Statements......Page 279
Restructuring the Statement......Page 280
Restructuring the Data......Page 290
Tuning Goals......Page 291
Tuning Parallel Execution......Page 292
Tuning OLTP Applications......Page 294
Index Cost......Page 295
Analyzing Object Statistics......Page 296
Avoiding Balloon Tactic for Coding SQL......Page 299
SQL Tuning Tips......Page 300
Predicate Collapsing......Page 302
Tuning for the Typical Case......Page 303
Disk Reads and Buffer Gets......Page 305
Using EXISTS versus IN......Page 306
Trouble Shooting......Page 307
Remote and Distributed Queries......Page 308
Transparent Gateways......Page 318
Optimizing Performance of Distributed Queries......Page 319
10 Using Plan Stability......Page 321
Hints and Exact Text Matching......Page 322
Creating Outlines......Page 324
Using Stored Outlines......Page 325
Viewing Outline Data......Page 326
Moving Outline Tables......Page 327
Using Outlines to Move to the Cost-Based Optimizer......Page 328
RDBMS Upgrades and the Cost-Based Optimizer......Page 329
Part III Application Design Tools for Designers and DBAs......Page 333
11 Overview of Diagnostic Tools......Page 335
Data Volumes......Page 336
SQL Trace Facility......Page 337
Program Text......Page 338
Oracle and SNMP Support......Page 339
SQL Trace and TKPROF......Page 340
Supported Scripts......Page 341
Oracle Enterprise Manager, Packs, and Applications......Page 342
Introduction to Oracle Enterprise Manager......Page 343
Oracle Diagnostics Pack......Page 344
Oracle Tuning Pack......Page 346
Independent Tools......Page 348
12 Data Access Methods......Page 349
When to Create Indexes......Page 350
Tuning the Logical Structure......Page 351
Choosing Columns and Expressions to Index......Page 352
Choosing Composite Indexes......Page 353
Writing Statements that Use Indexes......Page 354
Assessing the Value of Indexes......Page 355
Using Fast Full Index Scans......Page 356
Re-creating Indexes......Page 357
Using Nonunique Indexes to Enforce Uniqueness......Page 358
Using Enabled Novalidated Constraints......Page 359
Using Function-based Indexes......Page 360
Using Bitmap Indexes......Page 361
When to Use Bitmap Indexes......Page 362
Creating Bitmap Indexes......Page 364
Initialization Parameters for Bitmap Indexing......Page 367
Using Bitmap Access Plans on Regular B*-tree Indexes......Page 368
Estimating Bitmap Index Size......Page 369
Using Domain Indexes......Page 372
Using Clusters......Page 373
When to Use Hash Clusters......Page 374
Creating Hash Clusters......Page 375
13 Managing Shared SQL and PL/SQL Areas......Page 377
Testing for Identical SQL Statements......Page 378
Reserving Space for Large Allocations......Page 379
Preventing Objects from Aging Out......Page 380
14 Using Oracle Trace......Page 381
Using Oracle Trace Data......Page 382
Managing Collections......Page 384
Accessing Collected Data......Page 385
Oracle Trace Predefined Data Views......Page 386
Viewing Oracle Trace Data......Page 393
Example of Details Property Page......Page 395
Getting More Information on a Selected Query......Page 397
Using the Oracle Trace Command-Line Interface......Page 400
Using Initialization Parameters to Control Oracle Trace......Page 402
Using Stored Procedures to Control Oracle Trace......Page 405
Formatting Oracle Trace Data to Oracle Tables......Page 407
Oracle Trace Statistics Reporting Utility......Page 408
15 Dynamic Performance Views......Page 411
Instance-Level Views for Tuning......Page 412
Current Statistic Values and Rates of Change......Page 413
Finding the Rate of Change of a Statistic......Page 414
16 Diagnosing System Performance Problems......Page 417
Tuning Factors for Well Designed Existing Systems......Page 418
Insufficient Memory......Page 421
Network Constraints......Page 422
Software Constraints......Page 423
17 Transaction Modes......Page 425
Deciding When to Use Discrete Transactions......Page 426
Using Discrete Transactions......Page 427
Example......Page 428
Using Serializable Transactions......Page 430
Part IV Optimizing Instance Performance......Page 431
18 Tuning CPU Resources......Page 433
Understanding CPU Problems......Page 434
System CPU Utilization......Page 436
Oracle CPU Utilization......Page 438
Solving CPU Problems by Changing System Architectures......Page 445
Single Tier to Two-Tier......Page 446
Two-Tier to Three-Tier......Page 447
Oracle Parallel Server......Page 449
19 Tuning Memory Allocation......Page 451
Understanding Memory Allocation Issues......Page 452
Solving Memory Allocation Problems......Page 453
Tuning Operating System Memory Requirements......Page 454
Tuning the Redo Log Buffer......Page 456
Tuning Private SQL and PL/SQL Areas......Page 458
Tuning the Shared Pool......Page 461
Tuning the Buffer Cache......Page 477
Tuning Multiple Buffer Pools......Page 482
Tuning Sort Areas......Page 491
Reducing Total Memory Usage......Page 492
20 Tuning I/O......Page 495
Tuning I/O: Top Down and Bottom Up......Page 496
Analyzing I/O Requirements......Page 497
Planning File Storage......Page 499
Choosing Data Block Size......Page 504
Evaluating Device Bandwidth......Page 505
Checking Oracle I/O Utilization......Page 509
Reducing Disk Contention by Distributing I/O......Page 512
Striping Disks......Page 516
Avoiding Dynamic Space Management......Page 520
Tuning Sorts......Page 529
Tuning Checkpoint Activity......Page 533
Tuning LGWR and DBWR I/O......Page 535
Tuning Backup and Restore Operations......Page 542
Configuring the Large Pool......Page 558
21 Tuning Resource Contention......Page 559
Detecting Contention Problems......Page 560
Reducing Contention for Rollback Segments......Page 561
Reducing Contention for Multi-Threaded Servers......Page 563
Reducing Contention for Parallel Execution Servers......Page 572
Reducing Contention for Redo Log Buffer Latches......Page 574
Reducing Contention for the LRU Latch......Page 577
Reducing Free List Contention......Page 578
22 Tuning Networks......Page 581
Understanding Connection Models......Page 582
Using Dynamic Performance Views......Page 589
Understanding Latency and Bandwidth......Page 590
Solving Network Problems......Page 591
Finding Bottlenecks......Page 592
Dissecting Bottlenecks......Page 594
Adjusting Session Data Unit Buffer Size......Page 596
Using Connection Manager......Page 597
23 Tuning the Operating System......Page 599
Raw Devices......Page 600
Operating System Resource Managers......Page 601
Solving Operating System Problems......Page 603
Performance on Mainframe Computers......Page 604
24 Tuning Instance Recovery Performance......Page 607
Trade-offs of Minimizing Recovery Duration......Page 608
Using Initialization Parameters to Influence Recovery Time......Page 609
Using Redo Log Size to Influence Checkpointing Frequency......Page 612
Monitoring Instance Recovery......Page 613
Tuning the Phases of Instance Recovery......Page 620
Tuning the Rolling Forward Phase......Page 621
Tuning the Rolling Back Phase......Page 622
Index......Page 625