Statements like these are generated dynamically by procedural programs written in Java, PHP, C#, etc. (or even PL/SQL if you work really hard). Programs that generate SQL statements like these create a lot of performance problems:

Since 2008, Method R Corporation has operated software, education, and consulting lines of business. Today, June 2, 2014, the Method R consulting line of business is being transferred to Enkitec. Method R Corporation will continue to operate as a software and education company, but all of our consulting capacity will now be delivered by Enkitec. We are excited about this opportunity. Our companies’ shareholders have been friends for a long time, and we look forward to helping our new teammates, both within Enkitec and Accenture, build their consulting practices.

After removing think time (or idle SQL*Net message from client) from a trace file (see a description), an unwanted line of “unaccounted for between dbcalls” dominated my MethodR profiler report. After an e-mail to MethodR support, Cary Millsap & Jeff Holt, found a way to neutralize this unwanted line. In this post I’ll show how. Thanks to Cary Millsap & Jeff Holt!

One important task when working with Oracle trace is to distinguish between idle and significant “SQL*Net message from client” waits. Default, MethodR defines waits above 1 second as “think time”. These waits are usually identified as idle waits. For instance – in an application using a connection pool, the sessions will be waiting for a client thread to grab a connection. These waits are truly not tied to the application response times, but is idle waits. In this article I’ll show how these waits easily can be “removed” or neutralized by using the MethodR utilities.

Connection pools help solve a big performance problem, but they also make using trace data more difficult. Method R Tools, part of the Method R Workbench software package, makes it easier to measure individual user response time experiences on connection pooling systems. Now you can look at performance problems the way you’ve always wanted to see them.

Method R Tools version 3.1 is available now for downloading. Method R Tools is the perfect companion for the Method R Profiler. Prominent changes include a new mrkey utility that makes it easier to automate mrskew analysis sessions, a new RC file called txnz for mrskew that makes it easier to profile trace files generated on connection pooling systems. The new release also includes several other features and bug fixes. Visit the MR Tools Change Log for a complete list.

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.

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.

One of the most important tools we use in our performance analysis engagements is MR Skew. It’s been an important part of our software tools catalog for a little over a year. Today, I’ll explain why we built it and show you what it can do for you.

Last Friday, we released version 1.0.2.0 of our Method R Trace tool. I like any release that has a big long list of new features and bug fixes. It’s mostly little stuff, but there’s one feature in particular that I’m in love with. It’s our new file delete function.

Welcome to our new Method R Corporation blog. Here, we will blog about Method R software products, events we’re attending, our experiences, how we learn, cries for help, and whatever we think might be mutually interesting (to you and to us).

I'll contribute here personally, of course. More importantly, I intend to goad my colleagues here at Method R Corporation and perhaps some other friends into contributing as well. We'll see how that goes.