Oracle Database 12c Performance Tuning Recipes: A Problem-Solution Approach

621 Pages·2013·5.76 MB·English

by  Sam R. Alapati

BOOKS FOR PROFESSIONALS BY PROFESSIONALS®
Alapati
Kuhn
Padfield
Oracle Database 12c
RELATED
Performance Tuning Recipes
Performance problems are rarely “problems” per se. They are more often “crises” during
which you’re pressured for results by a manager standing outside your cubicle while
your phone rings with queries from the help desk. You won’t have the time for a leisurely
perusal of the manuals, nor to lean back and read a book on theory. What you need in
that situation is a book of solutions, and solutions are precisely what Oracle Database
12c Performance Tuning Recipes delivers.
Oracle Database 12c Performance Tuning Recipes is a ready reference for database
a dministrators in need of immediate help with performance issues relating to Oracle Database.
The book takes an example-based approach, wherein each chapter covers a specific problem
domain. Within each chapter are “recipes,” showing by example how to perform common tasks
in that chapter’s domain. Solutions in the recipes are backed by clear explanations of b ackground
and theory from the author team. Whatever the task, if it’s performance-related, you’ll probably
find a recipe and a solution in this book.
• Provides proven solutions to real-life Oracle performance problems
• Offers relevant background and theory to support each solution
• Gets straight to the point for when you’re under pressure for results
What You’ll Learn:
• Optimize the use of memory and storage
• Monitor performance and troubleshoot problems
• Identify and improve poorly-performing SQL statements
• Adjust the most important optimizer parameters to your advantage
• Create indexes that get used and make a positive impact upon performance
• Automate and stabilize using key features such as SQL Tuning Advisor and
SQL Plan Baselines
Shelve in ISBN 978–1–4302–6187–2
55999
Databases/Oracle
User level:
Intermediate–Advanced

SOURCE CODE ONLINE 9 781430 261872
www.apress.com
For your convenience Apress has placed some of the front
matter material after the index. Please use the Bookmarks
and Contents at a Glance links to access them.
Contents at a Glance
About the Authors ��������������������������������������������������������������������������������������������������������������xlv
About the Technical Reviewers ���������������������������������������������������������������������������������������xlvii
Acknowledgments ������������������������������������������������������������������������������������������������������������xlix
Introduction ��������������������������������������������������������������������������������������������������������������������������li
■ Chapter 1: Optimizing Table Performance �������������������������������������������������������������������������1
■ Chapter 2: Choosing and Optimizing Indexes ������������������������������������������������������������������51
■ Chapter 3: Optimizing Instance Memory �������������������������������������������������������������������������95
■ Chapter 4: Monitoring System Performance �����������������������������������������������������������������125
■ Chapter 5: Minimizing System Contention ��������������������������������������������������������������������157
■ Chapter 6: Analyzing Operating System Performance ��������������������������������������������������193
■ Chapter 7: Troubleshooting the Database ����������������������������������������������������������������������217
■ Chapter 8: Creating Efficient SQL ����������������������������������������������������������������������������������259
■ Chapter 9: Manually Tuning SQL ������������������������������������������������������������������������������������307
■ Chapter 10: Tracing SQL Execution ��������������������������������������������������������������������������������335
■ Chapter 11: Automated SQL Tuning �������������������������������������������������������������������������������375
■ Chapter 12: Execution Plan Optimization and Consistency �������������������������������������������415
■ Chapter 13: Configuring the Optimizer ��������������������������������������������������������������������������457
■ Chapter 14: Implementing Query Hints �������������������������������������������������������������������������501
■ Chapter 15: Executing SQL in Parallel ���������������������������������������������������������������������������537
Index ���������������������������������������������������������������������������������������������������������������������������������569
v
Introduction
Oracle Database 12c Performance Tuning Recipes is a book of solutions—a crib sheet of sorts. Database performance
problems often rear themselves suddenly, and with that suddenness comes great pressure from management and
users to somehow work magic and get response time back under control. Everyone is in a panic. Everyone is upset
(except idealy you!). Such is no time for a leisurely read of a book. This book is written with that pressure in mind. It is
chock-full of prewritten queries and other solutions that you can immediately apply and get results.
Of course, you should not wait until the last minute. Take the time now when you’re not in crisis mode to get
familiar with the content in this book. Try the solutions for monitoring and analyzing. See what you can learn about
your current database performance levels. Then take action to set some benchmarks and work on improvements so as
to become practiced for when a crisis eventually hits. Or better yet, maybe you can avoid a crisis altogether.
Who This Book Is For
Oracle Database 12c Performance Tuning Recipes was written primarily for database administrators who manage
Oracle Database environments. The book is especially useful to those administrators involved in tackling performance
optimization problems. Serious SQL developers will also find the book useful, especially the chapters on aspects of SQL.
How This Book Is Structured
Solutions in this book are grouped into categories by chapter. Each chapter is composed of a number of recipes
relating to the chapter’s topic. Each recipe takes the following form:
Problem: A succinct description of the problem solved by the recipe
Solution: A terse and to-the-point presentation of queries and commands to execute in
order to accomplish the task described in the recipe’s problem statement
How It Works: A longer discourse on the solution and how and why it works, for those who
are interested in a deeper understanding of the material
The book’s structure allows you to open it to a chapter relating to a problem you are trying to solve. Then find a
recipe in the chapter that solves the problem or that can be adapted to solve the problem. Read the solution. Read the
“How It Works” description to fully understand the solution. Then apply the solution to your real-world problem.
Downloading the Code
The authors have made a zip file available with the queries and scripts from the recipe solutions. To download the
zip file, first visit the book’s catalog page on the Apress.com web site. The URL is as follows:
http://www.apress.com/9781430261872
Then scroll down the page and look for a tabbed section. Click the Source Code/Downloads tab, and download
the zip file of examples using the provided link.
li
Chapter 1
Optimizing Table Performance
This chapter details database features that impact the performance of storing and retrieving data within a table.
Table performance is partially determined by database characteristics implemented prior to creating tables.
For example, the physical storage features implemented when first creating a database and associated tablespaces
subsequently influence the performance of tables. Similarly, performance is also impacted by your choice of initial
physical features such as table types and data types. Therefore implementing practical database, tablespace, and table
creation standards (with performance in mind) forms the foundation for optimizing data availability and scalability.
An Oracle database is comprised of the physical structures used to store, manage, secure, and retrieve data.
When first building a database, there are several performance-related features that you can implement at the time of
database creation. For example, the initial layout of the datafiles and the type of tablespace management are specified
upon creation. Architectural decisions taken at this point often have long-lasting implications.
■ Tip An Oracle instance is defined to be the memory structures and background processes. Whereas an Oracle database
consists of physical files—namely, data files, control files, and online redo log files.
As depicted in Figure 1-1, a tablespace is the logical structure that allows you to manage a group of datafiles.
Datafiles are the physical datafiles on disk. When configuring tablespaces, there are several features to be aware of
that can have far-reaching performance implications, namely locally managed tablespaces and automatic segment
storage managed (ASSM) tablespaces. When you reasonably implement these features, you maximize your ability to
obtain acceptable future table performance.
1
ChApter 1 ■ Optimizing tAble perfOrmAnCe
physical storage
database
data files OS blocks
users logical storage
(owners)
segments:
tablespaces
-tables database
extents
-indexes blocks
-partitions
schemas -rollback
-and so on...
Figure 1-1. Relationships of logical and physical storage
The table is the object that stores data in a database. One measure of database performance is the speed at which
an application is able to insert, update, delete, and select data. Therefore it’s appropriate that we begin this book with
recipes that provide solutions regarding problems related to table performance.
We start by describing aspects of database and tablespace creation that impact table performance. We next move
on to topics such as choosing table types and data types that meet performance-related business requirements. Later
topics include managing the physical implementation of tablespace usage. We detail issues such as detecting table
fragmentation, dealing with free space under the high-water mark, row migration/chaining, and compressing data.
Also described is the Oracle Segment Advisor. This handy tool helps you with automating the detection and resolution
of table fragmentation and unused space.
1-1. Building a Database That Maximizes Performance
Problem
You realize when initially creating a database that some features (when enabled) have long-lasting implications for
table performance and availability. Specifically, when creating the database, you want to do the following:
• Enforce that every tablespace ever created in the database must be locally managed. Locally
managed tablespaces deliver better performance than the obsolete dictionary-managed
technology.
• Ensure users are automatically assigned a default permanent tablespace. This guarantees
that when users are created they are assigned a default tablespace other than SYSTEM. With
the deferred segment feature (more on this later), if a user has the CREATE TABLE privilege,
then it is possible for that user to create objects in the SYSTEM tablespace even without having
a space quota on the SYSTEM tablespace. This is undesirable. It’s true they won’t be able to
insert data into tables without appropriate space quotas, but they can create objects, and thus
inadvertently clutter up the SYSTEM tablespace.
• Ensure users are automatically assigned a default temporary tablespace. This guarantees that
when users are created they are assigned the correct temporary tablespace when no default is
explicitly provided.
2
ChApter 1 ■ Optimizing tAble perfOrmAnCe
Solution
There are two different tools that you can use to create an Oracle database:
• SQL*Plus using the CREATE DATABASE statement
• Database Configuration Assistant (dbca)
These techniques are described in the following subsections.
SQL*Plus
Use a script such as the following to create a database that adheres to reasonable standards that set the foundation for
a well-performing database:

CREATE DATABASE O12C
MAXLOGFILES 16
MAXLOGMEMBERS 4
MAXDATAFILES 1024
MAXINSTANCES 1
MAXLOGHISTORY 680
CHARACTER SET AL32UTF8
DATAFILE
'/u01/dbfile/O12C/system01.dbf'
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
UNDO TABLESPACE undotbs1 DATAFILE
'/u02/dbfile/O12C/undotbs01.dbf'
SIZE 800M
SYSAUX DATAFILE
'/u01/dbfile/O12C/sysaux01.dbf'
SIZE 500M
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE
'/u02/dbfile/O12C/temp01.dbf'
SIZE 500M
DEFAULT TABLESPACE USERS DATAFILE
'/u01/dbfile/O12C/users01.dbf'
SIZE 50M
LOGFILE GROUP 1
('/u01/oraredo/O12C/redo01a.rdo',
'/u02/oraredo/O12C/redo01b.rdo') SIZE 200M,
GROUP 2
('/u01/oraredo/O12C/redo02a.rdo',
'/u02/oraredo/O12C/redo02b.rdo') SIZE 200M,
GROUP 3
('/u01/oraredo/O12C/redo03a.rdo',
'/u02/oraredo/O12C/redo03b.rdo') SIZE 200M
USER sys IDENTIFIED BY f0obar
USER system IDENTIFIED BY f0obar;

3
ChApter 1 ■ Optimizing tAble perfOrmAnCe
The prior CREATE DATABASE script helps establish a good foundation for performance by enabling features such
as the following:
• Defines the SYSTEM tablespace as locally managed via the EXTENT MANAGEMENT LOCAL clause;
this ensures that all tablespaces ever created in database are locally managed. Starting with
Oracle Database 12c, the SYSTEM tablespace is always created as locally managed.
• Defines a default tablespace named USERS for any user created without an explicitly defined default
tablespace; this helps prevent users from being assigned the SYSTEM tablespace as the default.
• Defines a default temporary tablespace named TEMP for all users; this helps prevent users from
being assigned the SYSTEM tablespace as the default temporary tablespace. Users created with
a default temporary tablespace of SYSTEM can have an adverse impact on performance, as this
will cause contention for resources in the SYSTEM tablespace.
Solid performance starts with a correctly configured database. The prior recommendations help you create
a reliable infrastructure for your table data.
dbca
Oracle’s dbca utility has a graphical interface and a command line mode from which you can configure and create
databases. The visual tool is easy to use and has a very intuitive interface. In Linux/Unix environments to use the dbca
in graphical mode, ensure you have the proper X software installed, then issue the xhost + command, and make
certain your DISPLAY variable is set; for example:

$ xhost +
$ echo $DISPLAY
:0.0

$ xhost +
$ echo $DISPLAY
:0.0

The dbca is invoked from the operating system as follows:

$ dbca

You’ll be presented with a series of screens that allow you to make choices on the configuration. You can choose
the “Advanced Mode” option which gives you more control on aspects such as file placement and multiplexing of the
online redo logs.
By default, the dbca creates a database with the following characteristics:
• Defines the SYSTEM tablespace as locally managed.
• Defines a default tablespace named USERS for any user created without an explicitly defined
default tablespace.
• Defines a default temporary tablespace named TEMP for all users.
Like the SQL*Plus approach, these are all desirable features that provide a good foundation to build
applications on.
The dbca utility also allows you to create a database in silent mode, without the graphical component.
Using dbca in silent mode with a response file is an efficient way to create databases in a consistent and repeatable
manner. This approach also works well when you’re installing on remote servers, which could have a slow network
connection or not have the appropriate X software installed.
4
ChApter 1 ■ Optimizing tAble perfOrmAnCe
You can also run the dbca in silent mode with a response file. In some situations, using dbca in graphical mode
isn’t feasible. This may be due to slow networks or the unavailability of X software. To create a database, using dbca in
silent mode, perform the following steps:
1. Locate the dbca.rsp file.
2. Make a copy of the dbca.rsp file.
3. Modify the copy of the dbca.rsp file for your environment.
4. Run the dbca utility in silent mode.
First, navigate to the location in which you copied the Oracle database installation software, and use the find
command to locate dbca.rsp:

$ find . -name dbca.rsp
./12.1.0.1/database/response/dbca.rsp

Copy the file so that you’re not modifying the original (in this way, you’ll always have a good, original file):

$ cp dbca.rsp mydb.rsp

Now, edit the mydb.rsp file. Minimally, you need to modify the following parameters: GDBNAME, SID, SYSPASSWORD,
SYSTEMPASSWORD, SYSMANPASSWORD, DBSNMPPASSWORD, DATAFILEDESTINATION, STORAGETYPE, CHARACTERSET, and
NATIONALCHARACTERSET. Following is an example of modified values in the mydb.rsp file:

[CREATEDATABASE]
GDBNAME = "O12C"
SID = "O12C"
TEMPLATENAME = "General_Purpose.dbc"
SYSPASSWORD = "f00bar"
SYSTEMPASSWORD = "f00bar"
SYSMANPASSWORD = "f00bar"
DBSNMPPASSWORD = "f00bar"
DATAFILEDESTINATION ="/u01/dbfile"
STORAGETYPE="FS"
CHARACTERSET = "AL32UTF8"
NATIONALCHARACTERSET= "UTF8"

Next, run the dbca utility in silent mode, using a response file:

$ dbca -silent -responseFile /home/oracle/orainst/mydb.rsp

You should see output such as

Copying database files
1% complete
...
Creating and starting Oracle instance
...
62% complete
Completing Database Creation
...
100% complete
Look at the log file ... for further details.

5
ChApter 1 ■ Optimizing tAble perfOrmAnCe
If you look in the log files, note that the dbca utility uses the rman utility to restore the data files used for the
database. Then, it creates the instance and performs post-installation steps. On a Linux server you should also have
an entry in the /etc/oratab file for your new database.
Many DBAs launch dbca and configure databases in the graphical mode, but a few exploit the options available to
them using the response file. With effective utilization of the response file, you can consistently automate the database
creation process. You can modify the response file to build databases on ASM and even create RAC databases. In addition,
you can control just about every aspect of the response file, similar to launching the dbca in graphical mode.
■ Tip You can view all options of the dbca via the help parameter: dbca -help
How It Works
A properly configured and created database will help ensure that your database performs well. It is true that you
can modify features after the database is created. However, often a poorly crafted CREATE DATABASE script leads
to a permanent handicap on performance. In production database environments, it’s sometimes difficult to get
the downtime that might be required to reconfigure an improperly configured database. If possible, think about
performance at every step in creating an environment, starting with how you create the database.
When creating a database, you should also consider features that affect maintainability. A sustainable database
results in more uptime, which is part of the overall performance equation. The CREATE DATABASE statement in the
“Solution” section also factors in the following sustainability features:
• Creates an automatic UNDO tablespace (automatic undo management is enabled by setting
the UNDO_MANAGEMENT and UNDO_TABLESPACE initialization parameters); this allows Oracle to
automatically manage the rollback segments. This relieves you of having to regularly monitor
and tweak.
• Places datafiles in directories that follow standards for the environment; this helps with
maintenance and manageability, which results in better long-term availability and thus better
performance.
• Sets passwords to non-default values for DBA-related users; this ensures the database is more
secure, which in the long run can also affect performance (e.g., if a malcontent hacks into the
database and deletes data, then performance will suffer).
• Establishes three groups of online redo logs, with two members each, sized appropriately
for the transaction load; the size of the redo log directly affects the rate at which they switch.
When redo logs switch too often, this can degrade performance. Keep in mind that when you
create a new database that you may not know the appropriate size and will have to adjust
this later.
You should take the time to ensure that each database you build adheres to commonly accepted standards that
help ensure you start on a firm performance foundation.
If you’ve inherited a database and want to verify the default permanent tablespace setting, use a query such as this:

SELECT *
FROM database_properties
WHERE property_name = 'DEFAULT_PERMANENT_TABLESPACE';

If you need to modify the default permanent tablespace, do so as follows:

SQL> alter database default tablespace users;

6