We love to discuss Oracle trace data with customers. A few months ago, one of our customers in The Netherlands, Andre van Winssen, contacted us to ask whether we could help him see the order in which SQL statements were being fired by an individual Oracle client process on his system. My response was that he could do this easily with Method R Tools. With Andre’s kind permission, I share our conversation with you here. Thank you, Andre, for having us give Method R Tools a stretch for you.
Programmers who use profilers write better code than programmers who don’t. In the old days, the only way to profile an Oracle application we were writing (or fixing) was to trace our code and then use Oracle’s tkprof to make sense of the detailed trace data. I have Oracle course material from 1990 that shows tkprof output with elements circled and little notes explaining what they meant. It’s what the best SQL developers of the day used to refine and optimize their code.
But tkprof has lots of problems. First, it’s a SQL profiler, not an application profiler. That means that it accounts for time spent in SQL statements, but it doesn’t try to account for time spent outside of SQL statements. It also ignores a lot of what’s in your trace file. For example, it ignores parent-child relationships among database calls (and OS calls), and it pays no attention to passage of time that isn’t accounted for by DB or OS call durations. It double-counts time. That’s why tkprof might show that your 10-second experience consisted of 8 seconds of DB call time and 6 seconds of “wait” time. Another problem is that tkprof aggregates when we don’t want it to, like showing only call counts and totals with no min/max or other skew information. It also doesn’t aggregate when we do want it to, like when it treats SQL statements that differ only in literal values as completely distinct.
There was a lot more information in our trace files than tkprof could show us, so we’d study the raw trace data with a text editor. Eventually, my colleague Jeff Holt got tired of studying raw Oracle trace data with a text editor, so he wrote a better profiler. That profiler ultimately grew up to be the Method R Profiler. Over the years, my staff and I have created a software tools ecosystem around our Profiler. These tools often give application developers and operational managers (DBAs, Unix admins, SAN admins, network admins, etc.) the first direct view of their end-users’ response time experiences that they’ve ever had in their careers.
By now, a few thousand of you are aware of MR Trace, the Method R Trace extension for Oracle SQL Developer. It’s the easiest way to create a perfectly time-scoped trace file and put it onto your desktop computer without having to do any of the work of talking to your DBA, finding your trace file directory on the database server, groping (or grepping) through all the files to find the one you want, and then copying the file across your network to where you want it.
MR Trace eliminates all that work for you, completely automatically, with no extra clicking. You just run your SQL or PL/SQL code in your SQL Developer worksheet with the Run Script (F5) button click, and—presto!—your trace file appears on your desktop.
But what if you want to fetch a trace file that you didn’t just now create from SQL Developer? What if you want to grab that trace file created by the GL Posting job that ran last night? What then? Enter MR Trace version 2.
New Release! Announcing MR Tools (Method R Tools) version 2.1, our new production version of the trace file tools that we use every time we analyze an Oracle trace file. MR Tools is the perfect companion for the Method R Profiler. If you use tkprof, MR Tools will show you what you’ve been missing. Priced beginning at us$397 per userid.
No other tools (free or commercial) come close to the precision and trustworthiness of Method R software.
Alex Gorbachev · Pythian · Ottawa, Canada
New Release! Announcing MR Tools (Method R Tools) version 2.0, our new production version of the trace file tools that we use every time we analyze an Oracle trace file. MR Tools is the perfect companion for the Method R Profiler. If you use tkprof, MR Tools will show you what you’ve been missing. Priced beginning at us$397 per userid.