Method-R Profiler – Find out where the time goes!

If anyone has spent time supporting a database in a client-server environment, you know the user cries of ‘the system is slow’ and it is up to you avoid being labeled guilty by proving that it is not a database issue.  This would involve pulling up AWR reports and looking at system stats that usually proves it is not a database issue because the database is virtually idle.  This type of back and forth is all too common and I have lost count the number of times it has happened to me.

It would be great to have some proof that the users could understand that would clear the DBA’s good name. Enter the Method-R Profiler, part of the Method-R Workbench set of tools. You can find the tools and information here.

The Method-R Profiler, named profiler from this point on, is a tool you can drag-and-drop Oracle trace files into and out comes very detailed HTML output that show where the database spent its time.

Using this tool, I was able to diagnose a problem in an environment where the code in question was issuing hundreds of database calls resulting in no data returned and the majority of them being duplicate calls.  The number of calls increased exponentially for every user connection, which explained the complaint concerning the more users connecting, the slower the environment.  In another situation, I used the profiler to determine that over 90% of time spent in an application was used for data fetches across the network with a default Java cache size.  Increasing the cache size sped up the system.

The profiler is based on methodology from an Oracle book that should be on every DBA’s bookshelf.  You can find that book here.  This information has been revised in this book.

Update 4/16/15

Another time when the profiler would come in handy is when a developer called to ask my help on a problem they were having with stored procedure calls through PHP.  They were making identical calls to two databases of the same version with the same init parameters running on the same server.  The only differences being that a call to one database was virtually instantaneous and the other took 20 seconds.  The one taking 20 seconds had been refreshed days ago with production data while the other had not.  After being assured by the developer that a difference in data could not possibly be the cause, I prepared to trace his session and use the profiler to determine where the 20 seconds was being spent.  However, when I called the developer to announce that I was ready to trace his session, he informed me that he had been running the wrong stored procedure.  After slapping my forehead several times, I decided to update this post as a means to demonstrate another situation where the profiler would be useful and to vent a bit.