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

About the Reviewers

April Sims is currently the Database Administrator at Southern Utah University and an Oracle Certified Professional: 8i, 9i, and 10g with a Master's degree in Business Administration from the University of Texas at Dallas. Involved as a volunteer with the Independent Oracle Users Group for over seven years, April is currently a Contributing Editor for the IOUG SELECT Journal. April is an annual presenter at Oracle OpenWorld, IOUG COLLABORATE, and numerous regional Oracle-related conferences.

Advait V. Deo is a graduate from NIT, Nagpur and a post graduate from Birla Institute of Technology & Science (BITS), Pilani. After graduation he joined TCS and worked there for a couple of years. Later, he joined Oracle Corp. and worked closely with databases.

He's had seven years' experience working in the database world, having spent time on many aspects of database till now starting from Oracle Version 8 until 11g. He mainly focuses on database performance tuning, integrating databases with front-end application, scripting, and automation. Currently he is working as a Lead Database Administrator in Amazon.com, Inc. (world leader in retail business), handling a fleet of around 300 production databases.

Currently he resides in Hyderabad, India, with his wife Abha. In his time off from his busy work schedule, he spends quality time with his family, riding a bike, and watching movies.

He updates some of his work and learning on his website at http://www.avdeo.com, whenever he gets time.

Asif Momen has been working with Oracle technologies for over 12 years and has expertise in performance tuning and high availability. He has a master''s degree in Software Systems from Birla Institute of Technology & Science (BITS), Pilani.

Asif is an Oracle ACE and is OCP-Certified DBA, Forms Developer, and RAC Expert. He is a speaker at Oracle OpenWorld and All India Oracle User Group (AIOUG). In addition, he is the Editor of Oracle Connect—the quarterly publication of AIOUG. His particular interests are Database tuning, Oracle RAC, Oracle Data Guard, and Backup and Recovery.

Asif posts his ideas and opinions on The Momen Blog (http://momendba.blogspot.com). He can be reached at .

Previous Section
Next Chapter