Oracle Database Performance Tuning Guide, 10g Release 2 (10.2) b14211

Contents......Page 3
Documentation Accessibility......Page 23
Conventions......Page 24
What's New in Oracle Performance?......Page 25
Part I Performance Tuning......Page 27
Instance Tuning......Page 29
The Symptoms and the Problems......Page 30
SQL Tuning......Page 31
Introduction to Performance Tuning Features and Tools......Page 32
V$ Performance Views......Page 33
Part II Performance Planning......Page 35
Understanding Investment Options......Page 37
What is Scalability?......Page 38
System Scalability......Page 39
Factors Preventing Scalability......Page 40
I/O Subsystem......Page 41
Managing User Requests and Resource Allocation......Page 42
Configuring the Right System Architecture for Your Requirements......Page 43
Application Design Principles......Page 45
Table and Index Design......Page 46
Partitioned Indexes......Page 47
Using Views......Page 48
SQL Execution Efficiency......Page 49
Implementing the Application......Page 50
Trends in Application Development......Page 51
Sizing Data......Page 52
Benchmarking......Page 53
Testing, Debugging, and Validating a Design......Page 54
Rollout Strategies......Page 55
Performance Checklist......Page 56
The Oracle Performance Improvement Method......Page 57
Steps in The Oracle Performance Improvement Method......Page 58
A Sample Decision Process for Performance Conceptual Modeling......Page 59
Top Ten Mistakes Found in Oracle Systems......Page 60
Steps in the Emergency Performance Method......Page 62
Part III Optimizing Instance Performance......Page 65
Initialization Parameters......Page 67
Sizing Redo Log Files......Page 69
Creating Permanent Tablespaces - Automatic Segment-Space Management......Page 70
Creating and Maintaining Tables for Good Performance......Page 71
Reclaiming Unused Space......Page 72
Performance Considerations for Shared Servers......Page 73
Identifying Contention Using the Dispatcher-Specific Views......Page 74
Identifying Contention for Shared Servers......Page 75
Overview of Data Gathering......Page 77
Wait Events......Page 78
Active Session History (ASH)......Page 79
Operating System Statistics......Page 80
Network Statistics......Page 81
Interpreting Statistics......Page 82
Automatic Workload Repository......Page 83
Accessing the Automatic Workload Repository with Oracle Enterprise Manager......Page 85
Managing Snapshot and Baseline Data with APIs......Page 86
Modifying Snapshot Settings......Page 87
Creating and Dropping Baselines......Page 88
Automatic Workload Repository Reports......Page 89
Running Workload Repository Reports Using Enterprise Manager......Page 90
Running Workload Repository Compare Period Report Using Enterprise Manager......Page 91
Running Workload Repository Reports Using SQL Scripts......Page 93
Running the awrsqrpt.sql Report......Page 94
Running the awrddrpt.sql Report......Page 95
Running the awrddrpi.sql Report......Page 96
Active Session History Reports......Page 97
Running the ashrpti.sql Report......Page 98
Introduction to Database Diagnostic Monitoring......Page 99
Automatic Database Diagnostic Monitor......Page 100
An ADDM Example......Page 101
Setting Up ADDM......Page 102
Accessing ADDM with Oracle Enterprise Manager......Page 103
Running ADDM Using addmrpt.sql......Page 104
Running ADDM using DBMS_ADVISOR APIs......Page 105
Views with ADDM Information......Page 106
Understanding Memory Allocation Issues......Page 109
Automatic Shared Memory Management......Page 110
Dynamically Changing Cache Sizes......Page 111
Application Considerations......Page 112
Allow adequate memory to individual users......Page 113
Using V$DB_CACHE_ADVICE......Page 114
Calculating the Buffer Cache Hit Ratio......Page 116
Interpreting and Using the Buffer Cache Advisory Statistics......Page 117
Increasing Memory Allocated to the Buffer Cache......Page 118
Random Access to Large Segments......Page 119
Buffer Pool Hit Ratios......Page 120
Determining Which Segments Have Many Buffers in the Pool......Page 121
KEEP Pool......Page 122
Configuring and Using the Shared Pool and Large Pool......Page 123
Library Cache Concepts......Page 124
SQL Sharing Criteria......Page 125
Using the Shared Pool Effectively......Page 126
Single-User Logon and Qualified Table Reference......Page 127
Cursor Access and Management......Page 128
Reducing Parse Calls with Oracle Forms......Page 129
V$LIBRARYCACHE......Page 130
V$JAVA_POOL_ADVICE and V$JAVA_LIBRARY_CACHE_MEMORY......Page 132
Shared Pool: Dictionary Cache Statistics......Page 133
Allocating Additional Memory to the Data Dictionary Cache......Page 134
Tuning the Large Pool and Shared Pool for the Shared Server Architecture......Page 135
Determining an Effective Setting for Shared Server UGA Storage......Page 136
Limiting Memory Use for Each User Session by Setting PRIVATE_SGA......Page 137
Using CURSOR_SPACE_FOR_TIME......Page 138
Configuring the Reserved Pool......Page 139
When SHARED_POOL_RESERVED_SIZE Is Too Large......Page 140
Keeping Large Objects to Prevent Aging......Page 141
When to use CURSOR_SHARING......Page 142
Configuring and Using the Redo Log Buffer......Page 143
Log Buffer Statistics......Page 144
PGA Memory Management......Page 145
Configuring Automatic PGA Memory......Page 146
Monitoring the Performance of the Automatic PGA Memory Management......Page 147
V$PGASTAT......Page 148
V$SQL_WORKAREA_HISTOGRAM......Page 150
V$SQL_WORKAREA_ACTIVE......Page 152
V$SQL_WORKAREA......Page 153
V$PGA_TARGET_ADVICE......Page 154
How to Tune PGA_AGGREGATE_TARGET......Page 157
V$PGA_TARGET_ADVICE_HISTOGRAM......Page 158
Configuring OLAP_PAGE_POOL_SIZE......Page 159
Basic I/O Configuration......Page 161
Requested I/O Size......Page 162
Alignment of Physical Stripe Boundaries with Block Size Boundaries......Page 163
Manually Distributing I/O......Page 164
Redo Log Files......Page 165
Archived Redo Logs......Page 166
Oracle-Managed Files......Page 167
Writes......Page 168
Block Size Advantages and Disadvantages......Page 169
Understanding Operating System Performance Issues......Page 171
FILESYSTEMIO_OPTIONS Initialization Parameter......Page 172
Using Operating System Resource Managers......Page 173
Performance Hints on UNIX-Based Systems......Page 174
Understanding CPU......Page 175
Finding System CPU Utilization......Page 177
Checking Process Management......Page 178
Starting New Operating System Processes......Page 179
Instance Tuning Steps......Page 181
Examine the Host System......Page 182
Interpreting CPU Statistics......Page 183
Detecting I/O Problems......Page 184
Setting the Level of Statistics Collection......Page 185
Dynamic Performance Views Containing Wait Event Statistics......Page 186
Segment-Level Statistics......Page 188
Interpreting Oracle Statistics......Page 189
Using Wait Event Statistics to Drill Down to Bottlenecks......Page 190
Table of Wait Events and Potential Causes......Page 192
Read Consistency......Page 193
Table Fetch by Continued Row......Page 194
Parse-Related Statistics......Page 195
Wait Events Statistics......Page 196
Network Bottleneck......Page 197
Causes......Page 198
undo header......Page 199
Managing Excessive I/O......Page 200
db file sequential read......Page 201
direct path read and direct path read temp......Page 202
Hash Area Size......Page 203
enqueue (enq:) waits......Page 204
ST enqueue......Page 205
TX enqueue......Page 206
Cache is Too Small......Page 207
Choosing Between Multiple DBWR Processes and I/O Slaves......Page 208
Example: Find Latches Currently Waited For......Page 209
By Session......Page 211
cache buffers chains......Page 212
log file switch......Page 213
log file sync......Page 214
Idle Wait Events......Page 215
Part IV Optimizing SQL Statements......Page 217
Goals for Tuning......Page 219
Identifying Resource-Intensive SQL......Page 220
Tuning an Application / Reducing Load......Page 221
Information to Gather During Tuning......Page 222
Developing Efficient SQL Statements......Page 223
Reviewing the Execution Plan......Page 224
Avoid Transformed Columns in the WHERE Clause......Page 225
Write Separate SQL Statements for Specific Tasks......Page 226
Use of EXISTS versus IN for Subqueries......Page 227
Example 1: Using IN - Selective Filters in the Subquery......Page 228
Example 2: Using EXISTS - Selective Predicate in the Parent......Page 229
Controlling the Access Path and Join Order with Hints......Page 231
Use Caution When Joining Complex Views......Page 232
Store Intermediate Results......Page 233
Visiting Data as Few Times as Possible......Page 234
Modify All the Data Needed in One Statement......Page 235
Normal mode......Page 237
SQL Profiling......Page 238
SQL Tuning Advisor......Page 240
Advisor Output......Page 241
Using SQL Tuning Advisor with Oracle Enterprise Manager......Page 242
Using SQL Tuning Advisor APIs......Page 244
Creating a SQL Tuning Task......Page 245
Additional Operations on a SQL Tuning Task......Page 246
Using SQL Tuning Sets with Oracle Enterprise Manager......Page 247
Using SQL Tuning Sets APIs......Page 248
Loading a SQL Tuning Set......Page 249
Transporting a SQL Tuning Set......Page 250
Additional Operations on SQL Tuning Sets......Page 251
Accepting a SQL Profile......Page 252
SQL Tuning Information Views......Page 253
Optimizer Operations......Page 255
Choosing an Optimizer Goal......Page 256
OPTIMIZER_MODE Initialization Parameter......Page 257
Enabling and Controlling Query Optimizer Features......Page 258
Controlling the Behavior of the Query Optimizer......Page 259
Understanding the Query Optimizer......Page 260
Components of the Query Optimizer......Page 261
View Merging......Page 262
Selectivity......Page 263
Cost......Page 264
Overview of EXPLAIN PLAN......Page 265
Understanding Access Paths for the Query Optimizer......Page 267
Small Table......Page 268
Rowid Scans......Page 269
Assessing I/O for Blocks, not Rows......Page 270
When the Optimizer Uses Index Unique Scans......Page 271
When the Optimizer Uses Index Range Scans......Page 272
Index Skip Scans......Page 273
Index Joins......Page 274
Sample Table Scans......Page 275
How the Query Optimizer Executes Join Statements......Page 276
How the Query Optimizer Chooses Execution Plans for Joins......Page 277
Outer loop......Page 278
Nesting Nested Loops......Page 279
Sort Merge Joins......Page 280
Outer Joins......Page 281
Hash Join Outer Joins......Page 282
Full Outer Joins......Page 284
Understanding Statistics......Page 285
GATHER_STATS_JOB......Page 286
When to Use Manual Statistics......Page 287
Locking Statistics......Page 288
Gathering Statistics with DBMS_STATS Procedures......Page 289
Parallel Statistics Gathering......Page 290
Determining Stale Statistics......Page 291
System Statistics......Page 292
Multiblock Read Count......Page 294
Restoring Previous Versions of Statistics......Page 295
Exporting and Importing Statistics......Page 296
Locking Statistics for a Table or Schema......Page 297
When to Use Dynamic Sampling......Page 298
Dynamic Sampling Levels......Page 299
Statistics on Tables, Indexes and Columns......Page 300
Height-Balanced Histograms......Page 301
Frequency Histograms......Page 302
Tuning the Logical Structure......Page 305
Index Tuning using the SQLAccess Advisor......Page 306
Choosing Composite Indexes......Page 307
Writing Statements That Use Indexes......Page 308
Re-creating Indexes......Page 309
Using Enabled Novalidated Constraints......Page 310
Using Function-based Indexes for Performance......Page 311
Using Index-Organized Tables for Performance......Page 312
Using Domain Indexes for Performance......Page 313
Using Clusters for Performance......Page 314
Using Hash Clusters for Performance......Page 315
Types of Hints......Page 317
Hints for Optimization Approaches and Goals......Page 318
Hints for Query Transformations......Page 319
Hints for Parallel Execution......Page 320
Specifying a Full Set of Hints......Page 321
Specifying a Query Block in a Hint......Page 322
Specifying Global Table Hints......Page 323
Specifying Complex Index Hints......Page 324
Hints and Complex Views......Page 325
Hints and Nonmergeable Views......Page 326
Overview of the SQL Access Advisor in the DBMS_ADVISOR Package......Page 327
Overview of Using the SQL Access Advisor......Page 328
Steps for Using the SQL Access Advisor......Page 331
Setting Up Tasks and Templates......Page 332
Using Templates......Page 333
Managing Workloads......Page 334
Using Workloads......Page 335
SQL Tuning Sets......Page 336
Loading User-Defined Workloads......Page 337
Loading SQL Cache Workloads......Page 338
Using Summary Advisor Oracle Database 9i Workloads......Page 339
Adding SQL Statements to a Workload......Page 340
Changing SQL Statements in Workloads......Page 341
Removing a Link Between a Workload and a Task......Page 342
Recommendation Options......Page 343
Generating Recommendations......Page 344
Viewing Recommendations......Page 345
Interrupting Tasks......Page 349
Modifying Recommendations......Page 350
Generating SQL Scripts......Page 351
Performing a Quick Tune......Page 352
Updating Task Attributes......Page 353
Using SQL Access Advisor Constants......Page 354
Recommendations From a User-Defined Workload......Page 355
Generate Recommendations Using a Task Template......Page 357
Filter a Workload from the SQL Cache......Page 358
Evaluate Current Usage of Indexes and Materialized Views......Page 359
Tuning Materialized Views for Fast Refresh and Query Rewrite......Page 360
TUNE_MVIEW Syntax and Operations......Page 361
Accessing TUNE_MVIEW Output Results......Page 362
Script Generation DBMS_ADVISOR Function and Procedure......Page 363
Fast Refreshable with Optimized Sub-Materialized View......Page 367
Step 1: Select the Initial Options......Page 369
Step 2: Define the Workload Source......Page 370
Step 3: Choose the Types of Recommendations......Page 371
Step 4: Set a Schedule......Page 372
Step 5: Review and Submit Your Selections......Page 373
Step 6: Examine the Recommendations......Page 374
Using Hints with Plan Stability......Page 377
Storing Outlines......Page 378
Creating Outlines......Page 379
Using and Editing Stored Outlines......Page 380
How to Tell If an Outline Is Being Used......Page 382
Moving Outline Tables......Page 383
Using Plan Stability with Query Optimizer Upgrades......Page 384
Moving from RBO to the Query Optimizer......Page 385
Upgrading with a Test System......Page 386
Understanding EXPLAIN PLAN......Page 389
Minimizing Throw-Away......Page 390
Using V$SQL_PLAN Views......Page 391
The PLAN_TABLE Output Table......Page 392
Displaying PLAN_TABLE Output......Page 393
Customizing PLAN_TABLE Output......Page 394
Reading EXPLAIN PLAN Output......Page 395
Viewing Parallel Execution with EXPLAIN PLAN......Page 396
Viewing Bitmap Indexes with EXPLAIN PLAN......Page 397
Examples of Displaying Range and Hash Partitioning with EXPLAIN PLAN......Page 398
Examples of Pruning Information with Composite Partitioned Objects......Page 400
Examples of Partial Partition-wise Joins......Page 401
Examples of INLIST ITERATOR and EXPLAIN PLAN......Page 403
Example of Domain Indexes and EXPLAIN PLAN......Page 404
PLAN_TABLE Columns......Page 405
End to End Application Tracing......Page 415
Accessing the End to End Tracing with Oracle Enterprise Manager......Page 416
Viewing Gathered Statistics for End to End Application Tracing......Page 417
Tracing for Service, Module, and Action......Page 418
Tracing for Entire Instance or Database......Page 419
Syntax for trcsess......Page 420
Sample Output of trcsess......Page 421
Understanding the SQL Trace Facility......Page 422
Step 1: Setting Initialization Parameters for Trace File Management......Page 423
Step 2: Enabling the SQL Trace Facility......Page 425
Syntax of TKPROF......Page 426
TKPROF Example 1......Page 428
Step 4: Interpreting TKPROF Output......Page 429
Tabular Statistics in TKPROF......Page 430
Wait Event Information......Page 431
Identification of User Issuing the SQL Statement in TKPROF......Page 432
Deciding Which Statements to Tune......Page 433
Querying the Output Table......Page 434
Avoiding the Read Consistency Trap......Page 436
Avoiding the Schema Trap......Page 437
Sample TKPROF Header......Page 438
Sample TKPROF Body......Page 439
Sample TKPROF Summary......Page 441
Glossary......Page 443
B......Page 451
D......Page 452
E......Page 453
H......Page 454
J......Page 455
O......Page 456
P......Page 457
R......Page 458
S......Page 459
T......Page 460
V......Page 461
W......Page 462