Oracle performance tuning for 10gR2

✍ Scribed by Gavin Powell

• Publisher: Digital Press
• Year: 2007
• Tongue: English
• Leaves: 572
• Category: Library

✦ Table of Contents

Contents......Page 3
Send Us Your Comments......Page 15
Preface......Page 17
Organization......Page 18
Related Documentation......Page 21
Conventions......Page 22
Documentation Accessibility......Page 24
What's New in Oracle Performance?......Page 27
Oracle Database 10g Release 1 (10.1) New and Updated Features for Performance Tuning......Page 28
Part I Performance Tuning......Page 33
1 Performance Tuning Overview......Page 35
Instance Tuning......Page 36
SQL Tuning......Page 39
Introduction to Performance Tuning Features and Tools......Page 40
Automatic Performance Tuning Features......Page 41
Additional Oracle Tools......Page 42
Part II Performance Planning......Page 43
2 Designing and Developing for Performance......Page 45
Understanding Investment Options......Page 46
What is Scalability?......Page 47
System Scalability......Page 48
Factors Preventing Scalability......Page 49
Hardware and Software Components......Page 51
Configuring the Right System Architecture for Your Requirements......Page 54
Simplicity In Application Design......Page 57
Table and Index Design......Page 58
SQL Execution Efficiency......Page 61
Implementing the Application......Page 63
Trends in Application Development......Page 65
Sizing Data......Page 66
Estimating Workloads......Page 67
Testing, Debugging, and Validating a Design......Page 68
Rollout Strategies......Page 70
Performance Checklist......Page 71
3 Performance Improvement Methods......Page 73
The Oracle Performance Improvement Method......Page 74
Steps in The Oracle Performance Improvement Method......Page 75
A Sample Decision Process for Performance Conceptual Modeling......Page 77
Top Ten Mistakes Found in Oracle Systems......Page 78
Emergency Performance Methods......Page 80
Steps in the Emergency Performance Method......Page 81
Part III Optimizing Instance Performance......Page 83
4 Configuring a Database for Performance......Page 85
Initialization Parameters......Page 86
Configuring Undo Space......Page 88
Creating Subsequent Tablespaces......Page 89
Creating and Maintaining Tables for Good Performance......Page 91
Table Compression......Page 92
Indexing Data......Page 93
Performance Considerations for Shared Servers......Page 94
Identifying Contention Using the Dispatcher-Specific Views......Page 95
Identifying Contention for Shared Servers......Page 97
5 Automatic Performance Statistics......Page 99
Overview of Data Gathering......Page 100
Database Statistics......Page 101
Operating System Statistics......Page 103
Interpreting Statistics......Page 106
Automatic Workload Repository......Page 108
Accessing the Automatic Workload Repository with Oracle Enterprise Manager......Page 110
Managing Snapshot and Baseline Data with APIs......Page 111
Workload Repository Views......Page 114
Workload Repository Reports......Page 115
6 Automatic Performance Diagnostics......Page 117
Introduction to Database Diagnostic Monitoring......Page 118
Automatic Database Diagnostic Monitor......Page 119
ADDM Analysis Results......Page 120
An ADDM Example......Page 121
Setting Up ADDM......Page 122
Accessing ADDM with Oracle Enterprise Manager......Page 123
Diagnosing Database Performance Issues with ADDM......Page 124
Views with ADDM Information......Page 128
7 Memory Configuration and Use......Page 129
Oracle Memory Caches......Page 130
Automatic Shared Memory Management......Page 131
Dynamically Changing Cache Sizes......Page 132
Operating System Memory Use......Page 134
Iteration During Configuration......Page 135
Sizing the Buffer Cache......Page 136
Interpreting and Using the Buffer Cache Advisory Statistics......Page 140
Considering Multiple Buffer Pools......Page 142
Buffer Pool Data in V$DB_CACHE_ADVICE......Page 144
Determining Which Segments Have Many Buffers in the Pool......Page 145
KEEP Pool......Page 147
Configuring and Using the Shared Pool and Large Pool......Page 148
Shared Pool Concepts......Page 149
Using the Shared Pool Effectively......Page 152
Sizing the Shared Pool......Page 157
Interpreting Shared Pool Statistics......Page 163
Using the Large Pool......Page 164
Using CURSOR_SPACE_FOR_TIME......Page 168
Caching Session Cursors......Page 169
Configuring the Reserved Pool......Page 170
Keeping Large Objects to Prevent Aging......Page 172
CURSOR_SHARING for Existing Applications......Page 173
Maintaining Connections......Page 175
Configuring and Using the Redo Log Buffer......Page 176
Log Buffer Statistics......Page 177
PGA Memory Management......Page 178
Configuring Automatic PGA Memory......Page 180
Configuring OLAP_PAGE_POOL_SIZE......Page 196
8 I/O Configuration and Design......Page 199
Lay Out the Files Using Operating System or Hardware Striping......Page 200
Manually Distributing I/O......Page 204
When to Separate Files......Page 205
Three Sample Configurations......Page 207
Oracle-Managed Files......Page 208
Choosing Data Block Size......Page 209
9 Understanding Operating System Resources......Page 213
Using Operating System Caches......Page 214
Memory Usage......Page 215
Using Operating System Resource Managers......Page 216
Solving Operating System Problems......Page 217
Performance Hints on Midrange and Mainframe Computers......Page 218
Understanding CPU......Page 219
Context Switching......Page 221
Checking Memory Management......Page 222
Checking Process Management......Page 223
10 Instance Tuning Using Performance Views......Page 225
Instance Tuning Steps......Page 226
Define the Problem......Page 227
Examine the Host System......Page 228
Examine the Oracle Statistics......Page 231
Implement and Measure Change......Page 236
Examine Load......Page 237
Using Wait Event Statistics to Drill Down to Bottlenecks......Page 238
Table of Wait Events and Potential Causes......Page 240
Additional Statistics......Page 242
Wait Events Statistics......Page 245
SQL*Net Events......Page 247
buffer busy waits......Page 249
db file scattered read......Page 251
db file sequential read......Page 253
direct path read and direct path read temp......Page 255
direct path write and direct path write temp......Page 257
enqueue (enq:) waits......Page 258
free buffer waits......Page 261
latch events......Page 264
library cache lock......Page 269
log file switch......Page 270
log file sync......Page 271
Idle Wait Events......Page 272
11 Tuning Networks......Page 275
Shared Server Configuration......Page 276
Using Dynamic Performance Views for Network Performance......Page 280
Understanding Latency and Bandwidth......Page 281
Solving Network Problems......Page 282
Finding Network Bottlenecks......Page 283
Dissecting Network Bottlenecks......Page 284
Using Array Interfaces......Page 287
Using Connection Manager......Page 288
Part IV Optimizing SQL Statements......Page 289
12 SQL Tuning Overview......Page 291
Reduce the Workload......Page 292
Identifying Resource-Intensive SQL......Page 293
Gathering Data on the SQL Identified......Page 295
Automatic SQL Tuning Features......Page 296
Developing Efficient SQL Statements......Page 297
Reviewing the Execution Plan......Page 298
Restructuring the SQL Statements......Page 299
Controlling the Access Path and Join Order with Hints......Page 307
Restructuring the Indexes......Page 311
Visiting Data as Few Times as Possible......Page 312
13 Automatic SQL Tuning......Page 315
Types of Tuning Analysis......Page 316
Input Sources......Page 320
Accessing the SQL Tuning Advisor with Oracle Enterprise Manager......Page 321
Using SQL Tuning Advisor APIs......Page 322
Managing SQL Profiles with APIs......Page 324
Dropping a SQL Profile......Page 325
Accessing SQL Tuning Sets with Oracle Enterprise Manager......Page 326
Managing SQL Tuning Sets......Page 327
SQL Tuning Information Views......Page 330
14 The Query Optimizer......Page 331
Optimizer Operations......Page 332
Choosing an Optimizer Goal......Page 333
OPTIMIZER_MODE Initialization Parameter......Page 334
Optimizer SQL Hints for Changing the Query Optimizer Goal......Page 335
Enabling Query Optimizer Features......Page 336
Controlling the Behavior of the Query Optimizer......Page 338
Understanding the Query Optimizer......Page 339
Components of the Query Optimizer......Page 340
Reading and Understanding Execution Plans......Page 345
Full Table Scans......Page 348
Rowid Scans......Page 350
Index Scans......Page 351
Cluster Access......Page 357
How the Query Optimizer Chooses an Access Path......Page 358
Understanding Joins......Page 359
How the Query Optimizer Chooses Execution Plans for Joins......Page 360
Nested Loop Joins......Page 362
Hash Joins......Page 364
Sort Merge Joins......Page 365
Outer Joins......Page 366
15 Managing Optimizer Statistics......Page 371
Understanding Statistics......Page 372
GATHER_STATS_JOB......Page 373
Considerations When Gathering Statistics......Page 374
Manual Statistics Gathering......Page 376
Gathering Statistics with DBMS_STATS Procedures......Page 377
System Statistics......Page 381
Restoring Previous Versions of Statistics......Page 383
Exporting and Importing Statistics......Page 384
Locking Statistics for a Table or Schema......Page 385
Estimating Statistics with Dynamic Sampling......Page 386
Handling Missing Statistics......Page 388
Statistics on Tables, Indexes and Columns......Page 389
Viewing Histograms......Page 390
16 Using Indexes and Clusters......Page 395
Tuning the Logical Structure......Page 396
Index Tuning using the SQLAccess Advisor......Page 397
Choosing Columns and Expressions to Index......Page 398
Choosing Composite Indexes......Page 399
Writing Statements That Avoid Using Indexes......Page 400
Re-creating Indexes......Page 401
Using Nonunique Indexes to Enforce Uniqueness......Page 402
Using Enabled Novalidated Constraints......Page 403
Using Function-based Indexes for Performance......Page 404
Using Partitioned Indexes for Performance......Page 405
Using Bitmap Join Indexes for Performance......Page 406
Using Domain Indexes for Performance......Page 407
Using Clusters for Performance......Page 408
Using Hash Clusters for Performance......Page 409
17 Optimizer Hints......Page 411
Type of Hints......Page 412
Specifying Hints......Page 413
Using Hints with Views......Page 420
Hints for Optimization Approaches and Goals......Page 422
Hints for Access Paths......Page 425
Hints for Query Transformations......Page 433
Hints for Join Orders......Page 441
Hints for Join Operations......Page 442
Hints for Parallel Execution......Page 446
Additional Hints......Page 451
18 Using Plan Stability......Page 459
Using Hints with Plan Stability......Page 460
Using Supplied Packages to Manage Stored Outlines......Page 462
Creating Outlines......Page 463
Using and Editing Stored Outlines......Page 464
Viewing Outline Data......Page 467
Moving Outline Tables......Page 468
Moving from RBO to the Query Optimizer......Page 470
Moving to a New Oracle Release under the Query Optimizer......Page 472
19 Using EXPLAIN PLAN......Page 475
How Execution Plans Can Change......Page 476
Minimizing Throw-Away......Page 477
Looking Beyond Execution Plans......Page 478
The PLAN_TABLE Output Table......Page 479
Identifying Statements for EXPLAIN PLAN......Page 480
Displaying PLAN_TABLE Output......Page 481
Customizing PLAN_TABLE Output......Page 482
Reading EXPLAIN PLAN Output......Page 483
Viewing Parallel Execution with EXPLAIN PLAN......Page 484
Viewing Parallel Queries with EXPLAIN PLAN......Page 486
Viewing Bitmap Indexes with EXPLAIN PLAN......Page 487
Examples of Displaying Range and Hash Partitioning with EXPLAIN PLAN......Page 488
Examples of Pruning Information with Composite Partitioned Objects......Page 490
Examples of Partial Partition-wise Joins......Page 492
Examples of Full Partition-wise Joins......Page 494
Examples of INLIST ITERATOR and EXPLAIN PLAN......Page 495
Example of Domain Indexes and EXPLAIN PLAN......Page 496
PLAN_TABLE Columns......Page 497
20 Using Application Tracing Tools......Page 509
End to End Application Tracing......Page 510
Managing End to End Tracing with APIs and Views......Page 511
Using the trcsess Utility......Page 515
Sample Output of trcsess......Page 516
Understanding the SQL Trace Facility......Page 517
Using the SQL Trace Facility and TKPROF......Page 519
Step 1: Setting Initialization Parameters for Trace File Management......Page 520
Step 2: Enabling the SQL Trace Facility......Page 522
Step 3: Formatting Trace Files with TKPROF......Page 523
Step 4: Interpreting TKPROF Output......Page 528
Step 5: Storing SQL Trace Facility Statistics......Page 534
Avoiding the Read Consistency Trap......Page 537
Avoiding the Schema Trap......Page 538
Avoiding the Time Trap......Page 539
Sample TKPROF Header......Page 540
Sample TKPROF Body......Page 541
Sample TKPROF Summary......Page 544
Glossary......Page 545
A......Page 555
C......Page 556
D......Page 557
E......Page 558
G......Page 559
H......Page 560
I......Page 561
L......Page 562
O......Page 563
P......Page 565
R......Page 566
S......Page 567
T......Page 569
V......Page 570
W......Page 571