Do you have SQL in your system that doesn’t use placeholders, like this?

[3r3dhkb0z824v] select stuff from t where id=18432...
[5wamvs45j6nh4] select stuff from t where id=4286...
[ih5x9lgg492nk] select stuff from t where id=329971...

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:

  • They spend too much CPU time on database parse calls;
  • They serialize on library cache and shared pool latches, which diminishes your application’s scalability;
  • They abuse the library cache, causing your application to consume way more memory than it ought to;
  • And they create unnecessary network congestion.

It’s easy to detect this kind of a problem in your trace files with the mrskew utility in the Method R Tools package.

Guest post from our friend, Lasse Jenssen

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!

Guest post from our friend, Lasse Jenssen

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.

Method R Tools with valid maintenance contracts as of 2014-01-31 are eligible to download this release at the software downloads page.

The Method R Tools suite is available at our online store.