Oracle Performance Tuning – Part 1

Performance tuning is a
broad and somewhat complex topic area when it comes to Oracle databases. Two of
the biggest questions faced by your average DBA concern where to start and what
to do. All you may know is that someone (a user) reports a problem about a slow
or poor performing application or query. Where do you even begin to start when
faced with this situation?

Oracle’s Approach to Tuning

For anyone who has taken the
Performance Tuning exam for Oracle8i certification, one of the testable areas
dealt with Oracle’s Tuning Methodology. Oracle’s emphasis on this particular methodology
changed when Oracle9i was released. The approach has gone from top-down in 8i
to that of following principles in 9i/10g. Neither methodology is absolute as
each has its advantages and disadvantages. In Oracle8i, the steps consisted of
the following:

1. 
Tuning the Business Rules
2. 
Tuning the Data Design
3. 
Tuning the Application Design
4. 
Tuning the Logical Structure of
the Database
5. 
Tuning Database Operations
6. 
Tuning the Access Paths
7. 
Tuning Memory Allocation
8. 
Tuning I/O and Physical Structure
9. 
Tuning Resource Contention
10. Tuning the Underlying Platform(s)

With Oracle9i’s
principle-based approach, the principles, in order of priority, are:

Priority

Description

First

Define the problem clearly
and then formulate a tuning goal.

Second

Examine the host system
and gather Oracle statistics.

Third

Compare the identified
problem to the common performance problems identified by Oracle in the
Oracle9i Database Performance Methods (Release 1)/Database Performance
Planning (Release 2)

Fourth

Use the statistics
gathered in the second step to get a conceptual picture of what might be
happening on the system.

Fifth

Identify the changes to be
made and then implement those changes.

Sixth

Determine whether the
objectives identified in step one have been met. If they have, stop tuning.
If not, repeat steps five and six until the tuning goal is met.

Reference: OCP: Oracle9i
Performance Tuning Study Guide
, SYBEX, Inc.

Interestingly, the emphasis
on identifying which step an action falls under went away with Oracle9i, and
recitation of the principles is not a testable item. The title of documentation
even changed between releases one and two, and that should send a clear signal
that the art of performance tuning (or, performance and tuning) is still just
that – an art. When it comes to instance tuning, the steps are even further
reduced in Oracle10g.

The performance tuning guide
for Oracle10g (Release 2) identifies the overall process as The Oracle
Performance Improvement Method
. The steps have been expanded, but overall,
remain the same.

1.  Perform the following initial standard checks:

a.   Get candid feedback from users. Determine the
performance project’s scope and subsequent performance goals, as well as
performance goals for the future. This process is key in future capacity
planning.

b.  Get a full set of operating system, database, and
application statistics from the system when the performance is both good and
bad. If these are not available, then get whatever is available. Missing
statistics are analogous to missing evidence at a crime scene: They make
detectives work harder and it is more time-consuming.

c.   Sanity-check the operating systems of all machines
involved with user performance. By sanity-checking the operating system, you
look for hardware or operating system resources that are fully utilized. List
any over-used resources as symptoms for analysis later. In addition, check
that all hardware shows no errors or diagnostics.

2.  Check for the top ten most common mistakes with
Oracle, and determine if any of these are likely to be the problem. List
these as symptoms for later analysis. These are included because they
represent the most likely problems. ADDM automatically detects and reports
nine of these top ten issues. See Chapter
6, "Automatic Performance Diagnostics"
and "Top
Ten Mistakes Found in Oracle Systems"
.

3.  Build a conceptual model of what is happening on
the system using the symptoms as clues to understand what caused the
performance problems. See "A
Sample Decision Process for Performance Conceptual Modeling"
.

4.  Propose a series of remedy actions and the
anticipated behavior to the system, then apply them in the order that can
benefit the application the most. ADDM produces recommendations each with an
expected benefit. A golden rule in performance work is that you only change
one thing at a time and then measure the differences. Unfortunately, system
downtime requirements might prohibit such a rigorous investigation method. If
multiple changes are applied at the same time, then try to ensure that they
are isolated so that the effects of each change can be independently
validated.

5.  Validate that the changes made have had the desired
effect, and see if the user’s perception of performance has improved.
Otherwise, look for more bottlenecks, and continue refining the conceptual
model until your understanding of the application becomes more accurate.

6. 
Repeat the last three steps until
performance goals are met or become impossible due to other constraints.

The
performance tuning guide for Oracle10g (Release 2)

The Change is Part of the Problem

The change from a top-down
structured approach to a principle-based “make it stop hurting” one is part of
the problem. Gathering statistics is obviously important because how else do
you know if you have improved (or worsened) the problem? Still, to some degree
with either approach, you are left with the original two questions: what do I
look for, and how do I make it better? If the structured approach left you
scratching your head, the principled approach only adds to the confusion.

What would help the novice
tuner (disclaimer: I am far from being an expert) is a list of items or areas
to evaluate (configure, diagnose, and tune) in each of the following areas:

  • Tuning the Buffer Cache

  • Tuning the Redo Log Buffer

  • Tuning the Shared Pool Memory

  • Tuning the Program Global Area

  • Optimizing Data Storage

  • Optimizing Tablespaces

  • Tuning Undo Segments

  • Detecting Lock Contention

  • Tuning SQL

These areas pretty much
cover the Oracle RDBMS and instance from top to bottom. The remainder of this
article will focus on tuning SQL, or more precisely, preventing slow SQL
execution. Aren’t these the same thing? Mostly yes, but a common approach in
development is making a statement perform well enough or fast enough. Each and
every statement does not have to be optimal, but some thought has to go into
coding them. You do not have the time to optimize hundreds or even thousands of
SQL statements, but at the same time, there are guidelines you can follow to
avoid common mistakes and bad coding.

17 Tips for Avoiding Problematic Queries

The source of these 17 tips
is from Oracle9i Performance Tuning: Optimizing Database Productivity by
Hassan Afyouni (Thompson Course Technology, 2004). These tips provide a solid
foundation for two outcomes: making a SQL statement perform better, and
determining that nothing else can be done in this regard (i.e., you have done
all you can with the SQL statement, time to move on to another area).

The 17 tips are listed
below.

1. 
Avoid Cartesian products
2. 
Avoid full table scans on large
tables
3. 
Use SQL standards and conventions
to reduce parsing
4. 
Lack of indexes on columns
contained in the WHERE clause
5. 
Avoid joining too many tables
6. 
Monitor V$SESSION_LONGOPS to
detect long running operations
7. 
Use hints as appropriate
8. 
Use the SHARED_CURSOR parameter
9. 
Use the Rule-based optimizer if I
is better than the Cost-based optimizer
10. Avoid unnecessary sorting
11. Monitor index browning (due to deletions; rebuild as
necessary)
12. Use compound indexes with care (Do not repeat
columns)
13. Monitor query statistics
14. Use different tablespaces for tables and indexes (as
a general rule; this is old-school somewhat, but the main point is reduce I/O
contention)
15. Use table partitioning (and local indexes) when
appropriate (partitioning is an extra cost feature)
16. Use literals in the WHERE clause (use bind variables)
17. Keep statistics up to date

That is quite a list and
overall is thorough and accurate. Step 9, referring to the use of the
Rule-based optimizer, may cause a reliance or dependency on a feature Oracle
has identified as a future item to be deprecated. You are eventually going to
have to solve the problem using the CBO, so you may as well start now and
forget about the RBO. Step 14 should be changed to something along the
lines of "reduce I/O contention" instead of its currently stated
"separate index and table tablespaces" guidance.

In Closing

In the next article of this
series, we will look at some specific steps of these tips. For example, advice
given on many Web sites about how to improve a SQL statement’s performance
typically includes “use bind variables.” Well, I am sure many people have this
question: “How, exactly, do I do that?” It is actually pretty simple, as are
many of the details of how to use many of these tips.

»


See All Articles by Columnist
Steve Callan

Steve Callan
Steve Callan
Steve is an Oracle DBA (OCP 8i and 9i)/developer working in Denver. His Oracle experience also includes Forms and Reports, Oracle9iAS and Oracle9iDS.

Latest Articles