Oracle Tuning Using Tracing, STATSPACK, AWR and Toad

Depending upon what you’re
investigating when it comes to performance, one tool may be all you need to
answer the question at hand. For more complex questions, chances are several
tools may be needed. Tools, in this scenario, can consist of using an explain
plan, monitoring sessions and diagnostic information as can be observed using
Toad, tracing a session (yours or someone else’s), and running a PERFSTAT
report. The tools just mentioned are not all inclusive. Many other tools,
Oracle-owned or otherwise, can be quite effective in helping a DBA solve a
performance problem.

It also helps to clarify
what is meant by performance problem. The “problem” or question may not be an
actual performance issue but more along the lines of confirming expected
results. For example, running an explain plan on a query can be used to confirm
whether or not an index is being used. How that confirmation is made can be
accomplished several ways. So, “problem” doesn’t always mean something bad is
taking place.

The tools used throughout
this and the next article include the following:

  • Setting autotrace trace explain
    in SQL*Plus
  • Using Toad, basic features plus
    those available in the DBA module
  • Executing the
    DBMS_MONITOR.SESSION_TRACE_ENABLE supplied PL/SQL built-in
  • Running the awrrpt SQL script
    found in the rdbms/admin directory (Oracle 10gR2, based on using the Automatic
    Workload Repository)

Let’s start with some
background on licensing.

Cost and Licensing

In the column labeled Free
(table below), everything but TRCESS is nothing new under the sun, and even
with TRCSESS, that is several years old. Use of these tools is well documented
on numerous Web sites and in Oracle documentation. Prior articles have
discussed Toad.
The interesting cost and licensing part here has to do with the Diagnostic Pack
from Oracle.

Free

Not Free

Autotrace

Toad, at some edition
level

Explain plan/plan table

Toad with add on modules
(e.g., DBA)

Utlbstat/utlestat
(ancient)

Oracle Diagnostic Pack
– EM

Tracing

Oracle Diagnostic Pack
– command line

STATSPACK

Other Oracle advisory
packs

TRCESS

Other third party tools

Most DBAs familiar with the
advisory or management packs are aware that these packs cost extra money
(licensing by named user at a minimum number of users or by CPU). The current
(as of December 2007’s price list) shows that a single by processor license for
Diagnostic Pack is $3000, plus a 22% ($660) maintenance fee. Here’s the part
that may surprise you.

You’ve no doubt read about
all of the new features in Oracle 10g and how much easier your job as a DBA can
be simply by using some of the (new) extra data dictionary views and scripts.
The V$ACTIVE_SESSION_HISTORY view, as an example, received a lot of attention
because of the sheer amount of information it provides. Another one, the
DBA_HIST_PGA_TARGET_ADVICE is also frequently used as it provides important
sizing information about the PGA. Users who want more than what STATSPACK
provides have ventured into the output from the awrrpt or awrrpti SQL scripts.
In order to use any of these views or scripts, you must first have licensed
them from Oracle. They are all part of the Diagnostics Pack, even though they
ship with Oracle. Just so the statement is clear: using any of several
Diagnostics Pack related views or scripts requires a license, regardless if
used in Enterprise Manager or on the command line.

How would you know which
scripts and views cost extra money to use? Aside from being bounced around four
or five sales people at Oracle before getting to someone who 1) speaks your
language well enough to carry on an intelligible conversation and 2) even knows
what you’re talking about when asking about a feature (yes, your call can be
forwarded to Oracle’s gift shop where tee-shirts and golf caps are sold), or a
licensed third party reseller such as TUSC, you can read the Oracle Database
Licensing Information
guide. Best to consult the latest version at http://tahiti.oracle.com. The current
guide (as of this writing) is dated November 2007.

To avoid inadvertent use of
a licensed feature, go to the setup page in Enterprise Manager. Disabling the
Diagnostics Pack option will disable relevant links throughout the rest of this
tool. If on the command line, review the Command-Line APIs section under Oracle
Diagnostic Pack in Chapter 2 of the guide.

Specifics versus generalization

If you need specific
information, then use tools which can provide specificity. Likewise, if all you
need is a one over the world view of what Oracle is doing, then use something
with less specificity. Specific information can be found in generalized
results, but don’t count on it. Let’s take a look at indexes to illustrate this
point.

Depending on the version, Oracle
provides you with the ability to monitor index usage. In later versions, simply
issue a command to monitor an index (or use a tool with that functionality). In
Oracle8i, there wasn’t a direct means of monitoring usage. One way to
get around this limitation was to examine explain plans and look for the index
in question. In Toad, you can see how the Index Monitoring menu option is
disabled while logged in to an 8i database. The moral here is a sophisticated
tool may have the same limitations an older version of Oracle has.

Oracle 10gR2

Oracle 8i

You can observe the fact
that an index was used, but are left with the question of used by who or what. Across
an instance, Toad will display overall index usage.

During the time interval
shown (roughly a day apart, same batch process in a warehouse being run), which
case would you prefer the instance be running in? It is obvious some indexing
is taking place, but the difference between the cases is like night and day.
Why wouldn’t an index be used? There are at least five reasons why.

First, an index doesn’t
exist, or existing indexes (more than likely concatenated) don’t include the
column(s) of interest. Second, the index exists, but has been marked unusable.
Third, the code or operation has used a hint that suppresses the index. Fourth,
based on the degree of selectivity, the optimizer may have decided that a full
table scan was more efficient. Lastly, a statement may bypass index usage
because of how the table is structured (specifically, the degree of
parallelism).

Parallelism, or more
precisely, too much of it, can be the reason why an index was not being used. Parallel
query can perform direct reads on a table, completely skipping an index.
Dialing down the degree of parallelism (to degree one) in this example caused
the amount of indexing to basically flip-flop. The mostly indexed case ran
orders of magnitude faster than its counterpart case. The Toad graphic shows
how little or how much indexing is taking place, but *which* indexes? A more
granular drill down tool is needed to confirm that the index of interest is
being used in a specific case.

Index usage, whether set to
be monitored and later queried in SQL*Plus, or tagged for monitoring in a GUI
tool (the Index Monitoring example discussed earlier) can tell you only that an
index was used, not specifically when and where. Running an explain plan
immediately showed what was taking place with respect to the index.

Of course, this begs the
question of why was the statement running that way in the first place. The
simple answer is that it was existing code and structure, and to a large degree,
most of these scenarios will be situations you inherit as opposed to inducing
yourself (given that you know better). In this case, Parallel Query was running
on a non-partitioned table, so not only was a perfectly valid index being
skipped, there was also the issue of increased time related to PQ wait events.

In Closing

In the next article, we’ll
drill down into comparisons between tools and how they display attributes about
the same pieces of information. Part of the difficulty in tuning is knowing
what to look for. Some idle events that are generally benign aren’t, and some
wait events are good. How do you know when something bad is really good, or
vice versa? The key is understanding the context of what is taking place at the
time the idle or wait event is being counted or collected. To further
complicate the tuning mystery, a high fill-in-the-blank ratio normally
classified as being a good indicator of performance may, in fact, be telling
you that something is awry within your database. It’s almost as if Oracle wants
to play the old television game show To Tell the Truth. Among all the wait
event or ratio panelists, one or more of them is lying about what their value
means. Your job is to determine the truth by focusing on what is relevant and what
is misleading.

»


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