Oracle Database 11gR2 Performance Tuning Cookbook: Over 80 recipes to help beginners achieve better performance from Oracle Database applications

Cover......Page 1
Copyright......Page 3
Credits......Page 4
About the Author......Page 5
Acknowledgement......Page 6
About the Reviewers......Page 7
www.PacktPub.com......Page 8
Table of Contents......Page 12
Preface......Page 18
Introduction......Page 24
Reviewing the performance tuning process......Page 29
Exploring the example database......Page 34
Acquiring data using a data dictionary and dynamic performance views......Page 37
Analyzing data using Statspack reports......Page 40
Diagnosing performance issues using the alert log......Page 45
Analyzing data using Automatic Workload Repository (AWR)......Page 46
Analyzing data using Automatic Database Diagnostic Monitor (ADDM)......Page 49
A working example......Page 53
Introduction......Page 58
Optimizing connection management......Page 59
Improving performance sharing \nreusable code......Page 65
Reducing the number of requests to the \ndatabase using stored procedures......Page 71
Reducing the number of requests to the \ndatabase using sequences......Page 76
Reducing the number of requests to the \ndatabase using materialized views......Page 82
Optimizing performance with schema denormalization......Page 88
Avoiding dynamic SQL......Page 96
Introduction......Page 100
Avoiding row chaining......Page 101
Avoiding row migration......Page 106
Using LOBs......Page 113
Using index clusters......Page 120
Using hash clusters......Page 126
Indexing the correct way......Page 130
Rebuilding index......Page 140
Compressing indexes......Page 145
Using reverse key indexes......Page 147
Using bitmap indexes......Page 153
Migrating to index organized tables......Page 159
Using partitioning......Page 163
Introduction......Page 170
Using bind variables......Page 171
Avoiding full table scans......Page 181
Exploring index lookup......Page 190
Exploring index skip-scan and index \nrange-scan......Page 194
Introducing arrays and bulk operations......Page 198
Optimizing joins......Page 204
Using subqueries......Page 209
Tracing SQL activity with SQL Trace \nand TKPROF......Page 218
Introduction......Page 224
Sorting—in-memory and on-disk......Page 225
Sorting and indexing......Page 232
Writing top n queries and ranking......Page 241
Using count, min/max, and group-by......Page 249
Avoiding sorting in set operations: union, \nminus, and intersect......Page 257
Troubleshooting temporary tablespaces......Page 265
Introduction......Page 270
Using bind variables and parsing......Page 271
Array processing and bulk-collect......Page 274
Passing values with NOCOPY (or not)......Page 279
Using short-circuit IF statements......Page 283
Avoiding recursion......Page 286
Using native compilation......Page 288
Taking advantage of function result cache......Page 293
Inlining PL/SQL code......Page 298
Using triggers and virtual columns......Page 301
Introduction......Page 308
Exploring optimizer hints......Page 309
Collecting statistics......Page 315
Using histograms......Page 322
Managing stored outlines......Page 327
Introducing Adaptive Cursor Sharing for \nbind variable peeking......Page 334
Creating SQL Tuning Sets......Page 344
Using the SQL Tuning Advisor......Page 348
Configuring and using SQL Baselines......Page 352
Introduction......Page 358
Caching results with the client-side \nresult cache......Page 359
Enabling parallel SQL......Page 363
Direct path inserting......Page 368
Using create table as select......Page 372
Inspecting indexes and triggers overhead......Page 376
Loading data with SQL*Loader and \nData Pump......Page 383
Introduction......Page 392
Tuning memory to avoid Operating System paging......Page 393
Tuning the Library Cache......Page 401
Tuning the Shared Pool......Page 405
Tuning the Program Global Area and the User Global Area......Page 413
Tuning the Buffer Cache......Page 417
Introduction......Page 428
Tuning at the disk level and strategies to distribute Oracle files......Page 429
Striping objects across multiple disks......Page 436
Choosing different RAID levels for different Oracle files......Page 439
Using asynchronous I/O......Page 442
Tuning checkpoints......Page 445
Tuning redo logs......Page 450
Introduction......Page 454
Detecting and preventing lock contention......Page 455
Investigating transactions and concurrency......Page 461
Tuning latches......Page 469
Tuning resources to minimize latch \ncontention......Page 474
Minimizing latches using bind variables......Page 477
ALL_OBJECTS......Page 486
DBA_DATA_FILES......Page 487
DBA_INDEXES......Page 488
DBA_TABLES......Page 489
DBA_TEMP_FILES......Page 490
INDEX_STATS......Page 491
DBA_SEQUENCES......Page 492
DBA_TAB_HISTOGRAMS......Page 493
V$BUFFER_POOL_STATISTICS......Page 494
V$DATAFILE......Page 495
V$DB_CACHE_ADVICE......Page 496
V$ENQUEUE_LOCK......Page 497
V$FILESTAT......Page 498
V$INSTANCE_RECOVERY......Page 499
V$LATCH_CHILDREN......Page 500
V$LIBRARYCACHE......Page 501
V$LOCK......Page 502
V$LOG......Page 503
V$LOG_HISTORY......Page 504
V$MYSTAT......Page 505
V$ROLLSTAT......Page 506
V$SESSION......Page 507
V$SESSION_EVENT......Page 508
V$SGA......Page 509
V$SHARED_POOL_RESERVED......Page 510
V$SQL......Page 511
V$SQL_PLAN......Page 512
V$STATNAME......Page 513
V$SYSSTAT......Page 514
V$TEMPFILE......Page 515
V$WAITSTAT......Page 516
X$BH......Page 517
DBMS_ADDM......Page 518
DBMS_JOB......Page 519
DBMS_OUTLN......Page 520
DBMS_SHARED_POOL......Page 521
DBMS_SQL......Page 522
DBMS_STATS......Page 523
DBMS_WORKLOAD_REPOSITORY......Page 524
Index......Page 526