Oracle Database 11gR2 Performance Tuning Cookbook

Shifting your Oracle Database into top gear takes a lot of know-how and fine-tuning ability. The 80+ recipes in this Cookbook will give you those skills along with the ability to troubleshoot if things starts running slowly.

Oracle Database 11gR2 Performance Tuning Cookbook

Cookbook
Ciro Fiorillo

Shifting your Oracle Database into top gear takes a lot of know-how and fine-tuning ability. The 80+ recipes in this Cookbook will give you those skills along with the ability to troubleshoot if things starts running slowly.
$32.99
$54.99
RRP $32.99
RRP $54.99
eBook
Print + eBook
$12.99 p/month

Get Access

Get Unlimited Access to every Packt eBook and Video course

Enjoy full and instant access to over 3000 books and videos – you’ll find everything you need to stay ahead of the curve and make sure you can always get the job done.

+ Collection
Free Sample

Book Details

ISBN 139781849682602
Paperback542 pages

About This Book

  • 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

Who This Book Is For

This book is aimed at software developers, software and data architects, and DBAs who are beginning to use the Oracle Database, and want to solve performance problems faster and in a rigorous way.

If you are an architect who wants to design fast performing 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 slowly this book will provide a good start for your career in performance tuning.

Table of Contents

Chapter 1: Starting with Performance Tuning
Introduction
Reviewing the performance tuning process
Exploring the example database
Acquiring data using a data dictionary and dynamic performance views
Analyzing data using Statspack reports
Diagnosing performance issues using the alert log
Analyzing data using Automatic Workload Repository (AWR)
Analyzing data using Automatic Database Diagnostic Monitor (ADDM)
A working example
Chapter 2: Optimizing Application Design
Introduction
Optimizing connection management
Improving performance sharing reusable code
Reducing the number of requests to the database using stored procedures
Reducing the number of requests to the database using sequences
Reducing the number of requests to the database using materialized views
Optimizing performance with schema denormalization
Avoiding dynamic SQL
Chapter 3: Optimizing Storage Structures
Introduction
Avoiding row chaining
Avoiding row migration
Using LOBs
Using index clusters
Using hash clusters
Indexing the correct way
Rebuilding index
Compressing indexes
Using reverse key indexes
Using bitmap indexes
Migrating to index organized tables
Using partitioning
Chapter 4: Optimizing SQL Code
Introduction
Using bind variables
Avoiding full table scans
Exploring index lookup
Exploring index skip-scan and index range-scan
Introducing arrays and bulk operations
Optimizing joins
Using subqueries
Tracing SQL activity with SQL Trace and TKPROF
Chapter 5: Optimizing Sort Operations
Introduction
Sorting—in-memory and on-disk
Sorting and indexing
Writing top n queries and ranking
Using count, min/max, and group-by
Avoiding sorting in set operations: union, minus, and intersect
Troubleshooting temporary tablespaces
Chapter 6: Optimizing PL/SQL Code
Introduction
Using bind variables and parsing
Array processing and bulk-collect
Passing values with NOCOPY (or not)
Using short-circuit IF statements
Avoiding recursion
Using native compilation
Taking advantage of function result cache
Inlining PL/SQL code
Using triggers and virtual columns
Chapter 7: Improving the Oracle Optimizer
Introduction
Exploring optimizer hints
Collecting statistics
Using histograms
Managing stored outlines
Introducing Adaptive Cursor Sharing for bind variable peeking
Creating SQL Tuning Sets
Using the SQL Tuning Advisor
Configuring and using SQL Baselines
Chapter 8: Other Optimizations
Introduction
Caching results with the client-side result cache
Enabling parallel SQL
Direct path inserting
Using create table as select
Inspecting indexes and triggers overhead
Loading data with SQL*Loader and Data Pump
Chapter 9: Tuning Memory
Introduction
Tuning memory to avoid Operating System paging
Tuning the Library Cache
Tuning the Shared Pool
Tuning the Program Global Area and the User Global Area
Tuning the Buffer Cache
Chapter 10: Tuning I/O
Introduction
Tuning at the disk level and strategies to distribute Oracle files
Striping objects across multiple disks
Choosing different RAID levels for different Oracle files
Using asynchronous I/O
Tuning checkpoints
Tuning redo logs
Chapter 11: Tuning Contention
Introduction
Detecting and preventing lock contention
Investigating transactions and concurrency
Tuning latches
Tuning resources to minimize latch contention
Minimizing latches using bind variables

What You Will Learn

  • 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

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.

Authors

Table of Contents

Chapter 1: Starting with Performance Tuning
Introduction
Reviewing the performance tuning process
Exploring the example database
Acquiring data using a data dictionary and dynamic performance views
Analyzing data using Statspack reports
Diagnosing performance issues using the alert log
Analyzing data using Automatic Workload Repository (AWR)
Analyzing data using Automatic Database Diagnostic Monitor (ADDM)
A working example
Chapter 2: Optimizing Application Design
Introduction
Optimizing connection management
Improving performance sharing reusable code
Reducing the number of requests to the database using stored procedures
Reducing the number of requests to the database using sequences
Reducing the number of requests to the database using materialized views
Optimizing performance with schema denormalization
Avoiding dynamic SQL
Chapter 3: Optimizing Storage Structures
Introduction
Avoiding row chaining
Avoiding row migration
Using LOBs
Using index clusters
Using hash clusters
Indexing the correct way
Rebuilding index
Compressing indexes
Using reverse key indexes
Using bitmap indexes
Migrating to index organized tables
Using partitioning
Chapter 4: Optimizing SQL Code
Introduction
Using bind variables
Avoiding full table scans
Exploring index lookup
Exploring index skip-scan and index range-scan
Introducing arrays and bulk operations
Optimizing joins
Using subqueries
Tracing SQL activity with SQL Trace and TKPROF
Chapter 5: Optimizing Sort Operations
Introduction
Sorting—in-memory and on-disk
Sorting and indexing
Writing top n queries and ranking
Using count, min/max, and group-by
Avoiding sorting in set operations: union, minus, and intersect
Troubleshooting temporary tablespaces
Chapter 6: Optimizing PL/SQL Code
Introduction
Using bind variables and parsing
Array processing and bulk-collect
Passing values with NOCOPY (or not)
Using short-circuit IF statements
Avoiding recursion
Using native compilation
Taking advantage of function result cache
Inlining PL/SQL code
Using triggers and virtual columns
Chapter 7: Improving the Oracle Optimizer
Introduction
Exploring optimizer hints
Collecting statistics
Using histograms
Managing stored outlines
Introducing Adaptive Cursor Sharing for bind variable peeking
Creating SQL Tuning Sets
Using the SQL Tuning Advisor
Configuring and using SQL Baselines
Chapter 8: Other Optimizations
Introduction
Caching results with the client-side result cache
Enabling parallel SQL
Direct path inserting
Using create table as select
Inspecting indexes and triggers overhead
Loading data with SQL*Loader and Data Pump
Chapter 9: Tuning Memory
Introduction
Tuning memory to avoid Operating System paging
Tuning the Library Cache
Tuning the Shared Pool
Tuning the Program Global Area and the User Global Area
Tuning the Buffer Cache
Chapter 10: Tuning I/O
Introduction
Tuning at the disk level and strategies to distribute Oracle files
Striping objects across multiple disks
Choosing different RAID levels for different Oracle files
Using asynchronous I/O
Tuning checkpoints
Tuning redo logs
Chapter 11: Tuning Contention
Introduction
Detecting and preventing lock contention
Investigating transactions and concurrency
Tuning latches
Tuning resources to minimize latch contention
Minimizing latches using bind variables

Book Details

ISBN 139781849682602
Paperback542 pages
Read More