Thoughts on a Hyper-Extended Oracle Performance Monitor Beta

March 12, 2012

As long time readers of this blog might know, in my free time during roughly the last 10 years I have been working on a program named “Hyper-Extended Oracle Performance Monitor”.  Since 2005 or 2006 I have permitted a few people to try the beta versions of the program, thinking that I might obtain a bit of feedback about what works well, and what needs a lot of work.  I was recently informed of a couple of situations where one or two features in the program were extremely useful – I would much rather hear that kind of feedback, rather than “I forgot about that program.” 🙂

What started as a simple 10046 trace file parser, easy method to execute a handful of scripts, and a V$ performance view logger has certainly grown over the years.  I have not updated the documentation for the program in almost four years, and some suggestions offered by the program seem to be Oracle Database 8.1 specific… one of these days I might have some time to address those issues.

Over the last couple of days I found a couple of unplanned features (bugs) in the program – some of those features have been in the program for a couple of years, others were added just last week.  I am currently debating whether or not to open up the beta of the program to a wider audience.  Are any readers of this blog interested?

The main screen in the program probably looks unlike any program that you have seen in the past – menus, who needs menus:

–

If you drag and drop an Oracle 10046 trace file on the picture in the main screen, you will see a daunting list of options:

–

If you have followed along with the six part series on building an Oracle Database Time Model Viewer, you might recognize this screen in my program:

–

One of the original purposes of the program was to log the various statistics found in certain V$ performance views.  Over the years I added additional information that the program is able to optionally capture, and set up the logging capabilities so that certain events will force more frequent logging of statistics: 

 

–

With logging enabled, statistics are written to an dynamically created Microsoft Access compatible database, and as the statistics are captured, a summary of the statistics is written to the main program window:

–

Once you have logged something interesting, you can go back and review the information using a variety of interfaces in the program (or just stare blankly at the Microsoft Access database that was created).  Among other things, the below screen capture shows that one session spent roughly 24 seconds of the roughly 60 second time period in the wait event enq: TX – row lock contention.

–

We can easily take a look at the system level wait events and statistics for this time period:

–

Or drill-down to the session level waits and statistics from the table at the bottom of the Review Time Model Statistics window.  There is the session and its wait event, but what caused the wait event?

–

Maybe we should investigate… there’s a button for that.  Blocker and Blocked near the bottom left of the window – I wonder if that is a clue?

–

Let’s double-click one of those rows to see what happens:

–

Nice start, but let’s ask for more information by clicking Yes.

We now have the SQL statement the blocked session was attempting to execute, and possibly the SQL statement that the blocker executed which caused the  enq: TX – row lock contention wait event (the SQL statement is actually the most recent SQL statement executed by the blocker in the time period).

–

We are also able to take a quick tour of some of the SQL statements executed in the capture period and an extended version of the execution plans for those SQL statements:

–

There are another eight year’s worth of development in the program…