Oracle Database 11g R2 Performance Tuning Cookbook

✍ Scribed by Ciro Fiorillo

• Publisher: Packt Publishing
• Year: 2012
• Tongue: English
• Leaves: 543
• Category: Library

✦ Synopsis

Achieve better performance from your Oracle Database applications Learn the right techniques to achieve best performance from the Oracle Database Avoid common myths and pitfalls that slow down the database Diagnose problems when they arise and employ tricks to prevent them Explore various aspects that affect performance, from application design to system tuning In Detail Oracle's Database offers great performance, scalability, and many features for DBAs and developers. Due to a wide choice of technologies, successful applications are good candidates to run into performance issues and when a problem arises it's very difficult to identify the cause and the right solution to the problem. The Oracle Database 11g R2 Performance Tuning Cookbook helps DBAs and developers to understand every aspect of Oracle Database that can affect performance. You will be guided through implementing the correct solution in a proactive way before problems arise, and how to diagnose issues on your Oracle database-based solutions. This fast-paced book offers solutions starting from application design and development, through the implementation of well-performing applications, to the details of deployment and delivering best-performance databases. With this book you will quickly learn to apply the right methodology to tune the performance of an Oracle Database, and to optimize application design and SQL and PL/SQL code. By following the real-world examples you will see how to store your data in correct structures and access and manipulate them at a lightning speed. You will learn to speed up sort operations, hack the optimizer and the data loading process, and diagnose and tune memory, I/O, and contention issues. The purpose of this cookbook is to provide concise recipes, which will help you to build and maintain a very high-speed Oracle Database environment. Effectively apply performance tuning principles with concise recipes What you will learn from this book Design applications that run at lightning speed Implement fast and scalable SQL and PL/SQL code Choose the correct structures to store the data and access them Optimize sort operations, such as order-by, Top-N queries, ranking, and set operators Help the optimizer to choose the right access plan to retrieve data at the best available speed Load data in the database at a faster speed by using the correct tools and options Tune the database memory to obtain maximum performance using available resources Tune the I/O operations, by designing a database over the I/O system Tune and reduce contention issues on data and structures by using an optimal design Approach In this book you will find both examples and theoretical concepts covered. Every recipe is based on a script/procedure explained step-by-step, with screenshots, while theoretical concepts are explained in the context of the recipe, to explain why a solution performs better than another. Who this book is written for This book is aimed at software developers, software and data architects, and DBAs who are using or are planning to use the Oracle Database, who have some experience and want to solve performance problems faster and in a rigorous way. If you are an architect who wants to design better applications, a DBA who is keen to dig into the causes of performance issues, or a developer who wants to learn why and where the application is running slow, this is the book for you. Basic knowledge of SQL language is required and general knowledge of the Oracle Database architecture is preferable.

✦ Table of Contents

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
reusable code......Page 65
Reducing the number of requests to the
database using stored procedures......Page 71
Reducing the number of requests to the
database using sequences......Page 76
Reducing the number of requests to the
database 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
range-scan......Page 194
Introducing arrays and bulk operations......Page 198
Optimizing joins......Page 204
Using subqueries......Page 209
Tracing SQL activity with SQL Trace
and 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,
minus, 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
bind 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
result 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
Data 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
contention......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