Oracle Tuning Using Tracing, STATSPACK, AWR and Toad

Wednesday Dec 12th 2007 by Steve Callan

Learn the pros and cons of different tools, Oracle-owned or otherwise, that can be effective in helping a DBA solve a performance problem.

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.


Not Free


Toad, at some edition level

Explain plan/plan table

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

Utlbstat/utlestat (ancient)

Oracle Diagnostic Pack – EM


Oracle Diagnostic Pack – command line


Other Oracle advisory packs


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

Mobile Site | Full Site