Book Image

Oracle Database 11gR2 Performance Tuning Cookbook

By : Ciro Fiorillo
Book Image

Oracle Database 11gR2 Performance Tuning Cookbook

By: Ciro Fiorillo

Overview of this book

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.

Related Content you might be interested in

Current Title:

Small book image
Oracle Database 11gR2 Performance Tuning Cookbook
Ciro Fiorillo
Jan, 2012 | 542 pages
No titles found
Table of Contents (21 chapters)
Oracle Database 11gR2 Performance Tuning Cookbook
Oracle Database 11gR2 Performance Tuning Cookbook
Credits
Credits
About the Author
About the Author
Acknowledgement
Acknowledgement
About the Reviewers
About the Reviewers
www.PacktPub.com
www.PacktPub.com
Preface
Preface
Free Chapter
1
Starting with Performance Tuning
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
2
Optimizing Application Design
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
3
Optimizing Storage Structures
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
4
Optimizing SQL Code
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
5
Optimizing Sort Operations
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
6
Optimizing PL/SQL Code
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
7
Improving the Oracle Optimizer
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
8
Other Optimizations
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
9
Tuning Memory
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
10
Tuning I/O
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
11
Tuning Contention
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
Dynamic Performance Views
Dynamic Performance Views
ALL_OBJECTS
DBA_BLOCKERS
DBA_DATA_FILES
DBA_EXTENTS
DBA_INDEXES
DBA_SQL_PLAN_BASELINES
DBA_TABLES
DBA_TEMP_FILES
DBA_VIEWS
DBA_WAITERS
INDEX_STATS
DBA_SEQUENCES
DBA_TABLESPACES
DBA_TAB_HISTOGRAMS
V$ADVISOR_PROGRESS
V$BUFFER_POOL_STATISTICS
V$CONTROLFILE
V$DATAFILE
V$DB_CACHE_ADVICE
V$DB_OBJECT_CACHE
V$ENQUEUE_LOCK
V$FILESTAT
V$FIXED_TABLE
V$INSTANCE_RECOVERY
V$LATCH
V$LATCH_CHILDREN
V$LIBRARYCACHE
V$LOCK
V$LOCKED_OBJECT
V$LOG
V$LOG_HISTORY
V$LOGFILE
V$MYSTAT
V$PROCESS
V$ROLLSTAT
V$ROWCACHE
V$SESSION
V$SESSION_EVENT
V$SESSTAT
V$SGA
V$SGAINFO
V$SHARED_POOL_RESERVED
V$SORT_SEGMENT
V$SQL
V$SQL_PLAN
V$SQLAREA
V$STATNAME
V$SYSSTAT
V$SYSTEM_EVENT
V$TEMPFILE
V$TEMPSTAT
V$WAITSTAT
X$BH
A Summary of Oracle Packages Used for Performance Tuning
A Summary of Oracle Packages Used for Performance Tuning
DBMS_ADDM
DBMS_ADVISOR
DBMS_JOB
DBMS_LOB
DBMS_MVIEW
DBMS_OUTLN
DBMS_OUTLN_EDIT
DBMS_SHARED_POOL
DBMS_SPACE
DBMS_SPM
DBMS_SQL
DBMS_SQLTUNE
DBMS_STATS
DBMS_UTILITY
DBMS_WORKLOAD_REPOSITORY
Index
Index

Using count, min/max, and group-by

In this recipe, we will see how to count rows, compute min/max aggregates, and use filters in group-by queries.

How to do it...

The following steps will demonstrate the use of count, min/max, and group-by:

  1. Connect to the SH schema:

    CONNECT sh@TESTDB/sh

  2. Show the execution plan for a MIN/MAX query:

    SET AUTOT TRACE EXP
SELECT MAX(CUST_CREDIT_LIMIT) FROM CUSTOMERS;
SELECT MIN(CUST_CREDIT_LIMIT) FROM CUSTOMERS;

  3. Show the execution plan for a query which returns the MIN and the MAX:

    SELECT MAX(CUST_CREDIT_LIMIT), MIN(CUST_CREDIT_LIMIT)
FROM CUSTOMERS;

  4. Create an index on CUSTOMERS in the column in which we need to aggregate:

    CREATE INDEX IX_CUST_CREDIT_LIMIT
  ON CUSTOMERS (CUST_CREDIT_LIMIT);

  5. Execute the query in step 2:

    SET AUTOT TRACE EXP STAT
SELECT MAX(CUST_CREDIT_LIMIT) FROM CUSTOMERS;
SELECT MIN(CUST_CREDIT_LIMIT) FROM CUSTOMERS;

  6. Execute the query in step 3:

    SELECT MAX(CUST_CREDIT_LIMIT), MIN(CUST_CREDIT_LIMIT)
FROM CUSTOMERS;

  7. Use different ways to count the...

Previous Section
End of Section 6
Next Section