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.
Preview in Mapt

Oracle Database 11gR2 Performance Tuning Cookbook

Ciro Fiorillo

1 customer reviews
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.
Mapt Subscription
FREE
$29.99/m after trial
eBook
$10.00
RRP $32.99
Save 69%
Print + eBook
$54.99
RRP $54.99
What do I get with a Mapt Pro subscription?
  • Unlimited access to all Packt’s 5,000+ eBooks and Videos
  • Early Access content, Progress Tracking, and Assessments
  • 1 Free eBook or Video to download and keep every month after trial
What do I get with an eBook?
  • Download this book in EPUB, PDF, MOBI formats
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
What do I get with Print & eBook?
  • Get a paperback copy of the book delivered to you
  • Download this book in EPUB, PDF, MOBI formats
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
What do I get with a Video?
  • Download this Video course in MP4 format
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
$0.00
$10.00
$54.99
$29.99 p/m after trial
RRP $32.99
RRP $54.99
Subscription
eBook
Print + eBook
Start 30 Day Trial

Frequently bought together


Oracle Database 11gR2 Performance Tuning Cookbook Book Cover
Oracle Database 11gR2 Performance Tuning Cookbook
$ 32.99
$ 10.00
Oracle Database XE 11gR2 Jump Start Guide Book Cover
Oracle Database XE 11gR2 Jump Start Guide
$ 17.99
$ 10.00
Buy 2 for $20.00
Save $30.98
Add to Cart

Book Details

ISBN 139781849682602
Paperback542 pages

Book Description

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.

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

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
From 1 reviews

Read More Reviews

Recommended for You

OCA Oracle Database 11g: Database Administration I: A Real-World Certification Guide Book Cover
OCA Oracle Database 11g: Database Administration I: A Real-World Certification Guide
$ 35.99
$ 10.00
Oracle Advanced PL/SQL Developer Professional Guide Book Cover
Oracle Advanced PL/SQL Developer Professional Guide
$ 35.99
$ 10.00
Oracle Enterprise Manager 12c Administration Cookbook Book Cover
Oracle Enterprise Manager 12c Administration Cookbook
$ 29.99
$ 10.00
Oracle Data Guard 11gR2 Administration : Beginner's Guide  Book Cover
Oracle Data Guard 11gR2 Administration : Beginner's Guide
$ 35.99
$ 10.00
Oracle Goldengate 11g Complete Cookbook Book Cover
Oracle Goldengate 11g Complete Cookbook
$ 41.99
$ 10.00
Business Intelligence Cookbook: A Project Lifecycle Approach Using Oracle Technology Book Cover
Business Intelligence Cookbook: A Project Lifecycle Approach Using Oracle Technology
$ 35.99
$ 10.00