Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying Oracle Database 11gR2 Performance Tuning Cookbook
  • Table Of Contents Toc
Oracle Database 11gR2 Performance Tuning Cookbook

Oracle Database 11gR2 Performance Tuning Cookbook

By : Ciro Fiorillo
4 (4)
Buy this Book
close
close
Oracle Database 11gR2 Performance Tuning Cookbook

Oracle Database 11gR2 Performance Tuning Cookbook

4 (4)
By: Ciro Fiorillo
Buy this Book

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.
Table of Contents (21 chapters)
close
close
close
Oracle Database 11gR2 Performance Tuning Cookbook
Icon
Oracle Database 11gR2 Performance Tuning Cookbook
Credits
Icon
Credits
About the Author
Icon
About the Author
Acknowledgement
Icon
Acknowledgement
About the Reviewers
Icon
About the Reviewers
www.PacktPub.com
Icon
www.PacktPub.com
Preface
Icon
Preface
Lock Free Chapter
1
Starting with Performance Tuning
Icon
Starting with Performance Tuning
Icon
Introduction
Icon
Reviewing the performance tuning process
Icon
Exploring the example database
Icon
Acquiring data using a data dictionary and dynamic performance views
Icon
Analyzing data using Statspack reports
Icon
Diagnosing performance issues using the alert log
Icon
Analyzing data using Automatic Workload Repository (AWR)
Icon
Analyzing data using Automatic Database Diagnostic Monitor (ADDM)
Icon
A working example
2
Optimizing Application Design
Icon
Optimizing Application Design
Icon
Introduction
Icon
Optimizing connection management
Icon
Improving performance sharing reusable code
Icon
Reducing the number of requests to the database using stored procedures
Icon
Reducing the number of requests to the database using sequences
Icon
Reducing the number of requests to the database using materialized views
Icon
Optimizing performance with schema denormalization
Icon
Avoiding dynamic SQL
3
Optimizing Storage Structures
Icon
Optimizing Storage Structures
Icon
Introduction
Icon
Avoiding row chaining
Icon
Avoiding row migration
Icon
Using LOBs
Icon
Using index clusters
Icon
Using hash clusters
Icon
Indexing the correct way
Icon
Rebuilding index
Icon
Compressing indexes
Icon
Using reverse key indexes
Icon
Using bitmap indexes
Icon
Migrating to index organized tables
Icon
Using partitioning
4
Optimizing SQL Code
Icon
Optimizing SQL Code
Icon
Introduction
Icon
Using bind variables
Icon
Avoiding full table scans
Icon
Exploring index lookup
Icon
Exploring index skip-scan and index range-scan
Icon
Introducing arrays and bulk operations
Icon
Optimizing joins
Icon
Using subqueries
Icon
Tracing SQL activity with SQL Trace and TKPROF
5
Optimizing Sort Operations
Icon
Optimizing Sort Operations
Icon
Introduction
Icon
Sorting—in-memory and on-disk
Icon
Sorting and indexing
Icon
Writing top n queries and ranking
Icon
Using count, min/max, and group-by
Icon
Avoiding sorting in set operations: union, minus, and intersect
Icon
Troubleshooting temporary tablespaces
6
Optimizing PL/SQL Code
Icon
Optimizing PL/SQL Code
Icon
Introduction
Icon
Using bind variables and parsing
Icon
Array processing and bulk-collect
Icon
Passing values with NOCOPY (or not)
Icon
Using short-circuit IF statements
Icon
Avoiding recursion
Icon
Using native compilation
Icon
Taking advantage of function result cache
Icon
Inlining PL/SQL code
Icon
Using triggers and virtual columns
7
Improving the Oracle Optimizer
chevron up
Icon
Improving the Oracle Optimizer
Icon
Introduction
Icon
Exploring optimizer hints
Icon
Collecting statistics
Icon
Using histograms
Icon
Managing stored outlines
Icon
Introducing Adaptive Cursor Sharing for bind variable peeking
Icon
Creating SQL Tuning Sets
Icon
Using the SQL Tuning Advisor
Icon
Configuring and using SQL Baselines
8
Other Optimizations
Icon
Other Optimizations
Icon
Introduction
Icon
Caching results with the client-side result cache
Icon
Enabling parallel SQL
Icon
Direct path inserting
Icon
Using create table as select
Icon
Inspecting indexes and triggers overhead
Icon
Loading data with SQL*Loader and Data Pump
9
Tuning Memory
Icon
Tuning Memory
Icon
Introduction
Icon
Tuning memory to avoid Operating System paging
Icon
Tuning the Library Cache
Icon
Tuning the Shared Pool
Icon
Tuning the Program Global Area and the User Global Area
Icon
Tuning the Buffer Cache
10
Tuning I/O
Icon
Tuning I/O
Icon
Introduction
Icon
Tuning at the disk level and strategies to distribute Oracle files
Icon
Striping objects across multiple disks
Icon
Choosing different RAID levels for different Oracle files
Icon
Using asynchronous I/O
Icon
Tuning checkpoints
Icon
Tuning redo logs
11
Tuning Contention
Icon
Tuning Contention
Icon
Introduction
Icon
Detecting and preventing lock contention
Icon
Investigating transactions and concurrency
Icon
Tuning latches
Icon
Tuning resources to minimize latch contention
Icon
Minimizing latches using bind variables
1
Dynamic Performance Views
Icon
Dynamic Performance Views
Icon
ALL_OBJECTS
Icon
DBA_BLOCKERS
Icon
DBA_DATA_FILES
Icon
DBA_EXTENTS
Icon
DBA_INDEXES
Icon
DBA_SQL_PLAN_BASELINES
Icon
DBA_TABLES
Icon
DBA_TEMP_FILES
Icon
DBA_VIEWS
Icon
DBA_WAITERS
Icon
INDEX_STATS
Icon
DBA_SEQUENCES
Icon
DBA_TABLESPACES
Icon
DBA_TAB_HISTOGRAMS
Icon
V$ADVISOR_PROGRESS
Icon
V$BUFFER_POOL_STATISTICS
Icon
V$CONTROLFILE
Icon
V$DATAFILE
Icon
V$DB_CACHE_ADVICE
Icon
V$DB_OBJECT_CACHE
Icon
V$ENQUEUE_LOCK
Icon
V$FILESTAT
Icon
V$FIXED_TABLE
Icon
V$INSTANCE_RECOVERY
Icon
V$LATCH
Icon
V$LATCH_CHILDREN
Icon
V$LIBRARYCACHE
Icon
V$LOCK
Icon
V$LOCKED_OBJECT
Icon
V$LOG
Icon
V$LOG_HISTORY
Icon
V$LOGFILE
Icon
V$MYSTAT
Icon
V$PROCESS
Icon
V$ROLLSTAT
Icon
V$ROWCACHE
Icon
V$SESSION
Icon
V$SESSION_EVENT
Icon
V$SESSTAT
Icon
V$SGA
Icon
V$SGAINFO
Icon
V$SHARED_POOL_RESERVED
Icon
V$SORT_SEGMENT
Icon
V$SQL
Icon
V$SQL_PLAN
Icon
V$SQLAREA
Icon
V$STATNAME
Icon
V$SYSSTAT
Icon
V$SYSTEM_EVENT
Icon
V$TEMPFILE
Icon
V$TEMPSTAT
Icon
V$WAITSTAT
Icon
X$BH
2
A Summary of Oracle Packages Used for Performance Tuning
Icon
A Summary of Oracle Packages Used for Performance Tuning
Icon
DBMS_ADDM
Icon
DBMS_ADVISOR
Icon
DBMS_JOB
Icon
DBMS_LOB
Icon
DBMS_MVIEW
Icon
DBMS_OUTLN
Icon
DBMS_OUTLN_EDIT
Icon
DBMS_SHARED_POOL
Icon
DBMS_SPACE
Icon
DBMS_SPM
Icon
DBMS_SQL
Icon
DBMS_SQLTUNE
Icon
DBMS_STATS
Icon
DBMS_UTILITY
Icon
DBMS_WORKLOAD_REPOSITORY
3
Index
Icon
Index

Exploring optimizer hints

In this recipe, we will see how to suggest (to the optimizer) the strategy to adopt for choosing the best execution plan, using optimizer hints.

Note

The use of optimizer hints is a trick and should be considered only when no solution seems to work. The query optimizer is designed to choose the best execution plan, based on many different considerations, so it's very important to keep updated statistics to help the query optimizer in doing its work.

How to do it...

The following steps will explore optimizer hints:

  1. Connect the database to the SH schema:

    CONNECT sh@TESTDB/sh

  2. Set the auto-trace functionality in SQL*Plus to see only the execution plan without executing the queries:

    SET AUTOT TRACE EXP

  3. Select some records from the CUSTOMERS table (say, all customers born in 1949):

    SELECT
  C.CUST_FIRST_NAME, C.CUST_LAST_NAME
FROM sh.CUSTOMERS C
WHERE C.CUST_YEAR_OF_BIRTH = 1949;

  4. Execute the same query suggesting that it's better to full-scan the CUSTOMERS table in order to...

CONTINUE READING
83
Tech Concepts
36
Programming languages
73
Tech Tools
Icon Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.
Icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Icon 50+ new titles added per month and exclusive early access to books as they are being written.
Oracle Database 11gR2 Performance Tuning Cookbook
End of Section 7
Next Section
notes
bookmark Notes and Bookmarks search Search in title playlist Add to playlist font-size Font size

Change the font size

margin-width Margin width

Change margin width

day-mode Day/Sepia/Night Modes

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Confirmation

Modal Close icon
claim successful
Order Page Read Now

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY

Submit Your Feedback

Modal Close icon
Modal Close icon
Modal Close icon