Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

About TKPROF, Trace Analyzer and Method-R Profiler

with 7 comments

When trying to diagnose a process performing poorly, a SQL Trace is usually the starting point. First we enable EVENT 10046 level 1, 4, 8 or 12. Next we execute the process of concern. A SQL Trace is produced and we have our result, right? Well, not quite. There are some caveats. We need to be sure we included wait events on the trace file, so levels 8 or 12 are required. But once we have the raw SQL Trace: how do we analyze it? I talk here about 3 tools which input a SQL Trace and output some sort of profile.

TKPROF

TKPROF has been around for more than 20 years! It is part of the product, meaning: no extra cost. It is fast. And it provides the basics. For each SQL statement, we get execution metrics, and we can identify which statements are the ones consuming most service (CPU) or wait time. I would say that TKPROF is always a good starting point when we are tasked with the analysis of a process performing poorly.

TKPROF has some deficiencies although: It may core-dump with large files, and sometimes it is hard to read, specially with a complex row source plan. In the other hand, TKPROF is fast and does not require to connect back to the database where the Trace was produced. If I only care about CPU time and wait events associated only with database calls (non-idle), I can get a good picture of my issue by using plain old TKPROF. If wait events between database calls (idle) are my concern, I feel a bit lost with TKPROF.

Trace Analyzer

Trace Analyzer, also known as TRCANLZR or TRCA for short, has been available for about 10 years. It started as an Oracle internal tool, which provided a bit more than TKPROF. Over time it became more precise with the accounting of the 3 times: CPU Time, Elapsed Time and Response Time. TRCA works according to this simple Time model:

  1. Elapsed Time = “CPU Time” + “non-idle Waits” + “some un-accounted for time”
  2. Response Time = “Elapsed Time” + “idle Waits” + “some un-accounted for time”

Since a SQL Trace contains time stamps, the total Response Time can be easily computed. Also within the Trace we have a good record of database call “Elapsed and CPU Time”, and all waits are also measured. So, TRCA simply computes those un-accounted for times and provides all sorts of summaries, which mimic TKPROF well-known format, but adding some desired missing parts.

TRCA expands its output by gathering some interesting pieces out of the data dictionary, like CBO statistics and indexes. Does it mean it has to connect to a database? Yes, it does. It includes a technique that allows to process a SQL Trace on a system other than the one that originated the SQL Trace, but it requires some steps like taking a small snapshot of the source data dictionary and placing it within the TRCA repository on the target system. Not very complex, but not everybody knows how to do this…

Overall, I would say TRCA is good tool that provides more than TKPROF, takes longer to execute, and it is free to use.

Method-R Profiler

When I learned about Method-R Profiler some years ago, I decided to avoid looking at its output. The reason was simple: I did not want to be tempted to replicate some cool functionality into TRCA. I wanted TRCA to be what it is without any influence from some tool other than TKPROF. Nevertheless, a few months ago, while reviewing a performance issue, the only file provided was the output of Method-R Profiler. I was surprised to learn how intuitive it was to read it. It was obvious to me that Method-R Profiler was designed by someone who really masters the topic of performance analysis. Method-R focus is WHERE is the time. To me, that is the only right approach for this kind of tool. We all want to find quickly and easily WHERE is the time consumed. It could be service time (CPU) or some wait event (non-idle or idle).

I had the pleasure to chat with Cary Millsap from Method-R during RMOUG 2013. We talked about a few things, including Method-R Profiler and Trace Analyzer. Cary offered me a temporary license for evaluation, which I accepted. I tested Method-R Profiler using a SQL Trace from one of my typical test scripts. I totally like Method-R. It gives me what I need to diagnose a process performing poorly. Its output is accurate, clear and clean.

Pros:

  • Best content. It clearly identifies WHERE the time is spent. So we can focus correctly where we need to.
  • Easy to install. I installed it on my laptop and it was as simple as downloading a file. I thought I was missing something, but not at all. Installation took me no time at all!
  • Installs nothing in the database. This means I can analyze Trace files on a system other than their source.
  • No need to connect to a database.
  • Output is clean and easy to read.

Cons:

  • It is a licensed product. That means $. But in my opinion, its license prices are peanuts compared to the benefit of having the right tool when needed.

Conclusion

Performance tools are like a car insurance. They provide peace of mind while we expect never to use them. As with car insurances, the question is: which one do we get? Do we decide by cost only of by looking at the possible benefit in case we actually have to use it? When dealing with performance issues, I personally prefer to have the best tools available in the market. And I’d rather get to know them well BEFORE I need to actually use them in a production issue. Having that in mind, I strongly suggest you invest some time getting acquainted with your options, then be sure you have the right tools handy, just in case you face a high-priority issue requiring your expertise (and quick solution).

Written by Carlos Sierra

February 26, 2013 at 4:10 pm

Posted in TRCANLZR (TRCA)

7 Responses

Subscribe to comments with RSS.

  1. Thanks for sharing..

    That nice to know method – R work in right direction…Is the possible to certified method – R profiler tool with Oracle corp which is easy for method – R team also to get profiler tool to next level..

    Because most of the client will ask whether this tool is certified from Oracle to install…

    Thanks

    Mohammed.

    Oracle SME.

    Mohammed

    February 27, 2013 at 10:59 am

    • Since Method-R Profiler does not connect to the database, I do not see how a certification would add value. Method-R Profiler simply reads a SQL Trace, processes its content, then produces a nice report that highlights areas of opportunity for process tuning.

      Carlos Sierra

      February 27, 2013 at 3:57 pm

  2. I’ve been using Method-R tools for a few years because they are good.
    Glad to see you had a chance to check them out.

    Jared

    February 27, 2013 at 11:32 am

  3. Hello There. I discovered your blog using msn. This is a very well written article. I will make sure to bookmark it and come back to read extra of your helpful information. Thanks for the post. I will certainly return.

    dostawa kwiatów

    March 31, 2013 at 8:59 am

  4. Thank you for the auspicious writeup. It in fact was a amusement account it. Look advanced to more added agreeable from you! However, how can we communicate?

    Jon Salta

    April 10, 2013 at 12:13 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,538 other followers

%d bloggers like this: