Thoughts on a Hyper-Extended Oracle Performance Monitor Beta

12 03 2012

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…


Actions

Information

14 responses

12 03 2012
jeremy

Wow. I see some things have changed from the last one I got. Ill have to break it out again. I’ve been meaning to do some programming for myself using an oracle database backend just to keep busy. I keep coming across your program in my files and think to myself that I’ll need to use that to make sure my code is as clean as I can make it.

13 03 2012
Charles Hooper

Jeremy,

Great to hear from you again, and to see that you are following this blog. Also good to see that you are continuing to pursue deepening of various skills – those attributes will certainly pay off in the future. How is the reading of that Tom Kyte book coming along?

13 03 2012
amar

hi
hope to have one day a chance to test it!!
regards

13 03 2012
Charles Hooper

Amar,

Thanks for the comment.

13 03 2012
Mich talebzadeh

Hi Charles,

Looks neat. I gather this is a windows based tool that runs on your PC. It can access any Oracle instance anywhere (via Oracle client, ODBC …?) and it has a light footprint on the server.

From what i have seen so far it seems to be geared towards P&T (as opposed to DBA admin tool), which is good as P&T is an area worth investing. If this is windows then I gather the binaries will wok with vista, windows 7 etc.

Final point does it access 11g OK or geared towards earlier releases.

Cheers,

Mich

13 03 2012
Charles Hooper

Mich,

The original version of the program required configuration of ODBC connections to each database. That requirement was a hassle for some of the people who tested the first version that I allowed other people to use, so I switched to an OraOLEDB.Oracle connection. An unfortunate side effect of this change is that I was no longer able to log into the program as SYS AS SYSDBA for certain tasks such as accessing the X$ structures (specifically X$BH and the X$ structures needed for viewing the hidden initialization parameters). Setting the O7_DICTIONARY_ACCESSIBILITY parameter to TRUE will allow the program to connect as the SYS user (without AS SYSDBA), but doing so may represent a security risk.

The tool runs from a Windows client PC (XP with ADO 2.8+ installed, Vista, Windows 7 32/64 bit, Server 2003, Server 2008) and for some tasks, such as report generation, requires MIcrosoft Excel to be present on the PC (2000, XP, 2003, 2007, or 2010). The Windows interfaces need to be installed with the Oracle Client software, which should add the OraOLEDB functionality. The program writes nothing to the Oracle database that is monitored, although it might try to automatically adjust the MAX_DUMP_FILE_SIZE parameter if you attempt to enable a 10046 trace using the program, and the program senses that the MAX_DUMP_FILE_SIZE parameter is set far too small (installing the program is a matter of putting 2 files in a folder on the client computer). The user that logs into the program will need proper permissions to access the various V$ views and also access the various packages that enable 10046/10053 traces. Everything that is logged is written to a C:\OracleLog folder on the client computer, and unfortunately that likely means that User Access Control in Vista and Windows 7 will either need to be turned down or turned off completely.

One of the goals of the program was to assemble the various statistics from the different V$ views into a meaningful format that permits cross-analysis of the statistics from the different performance views. There are some non-performance tuning related areas of the program – the data dictionary reference that the program creates in Excel could be quite helpful. You can see some of the program’s other capabilities here:

http://hoopercharles.wordpress.com/2009/11/30/toy-project-for-performance-tuning/

http://hoopercharles.wordpress.com/2009/12/13/toy-project-for-performance-tuning-2/

The 10046 trace file parser is still a bit stuck in the land of Oracle Database 8.1 – it still expects to find p1, p2, and p3 on WAIT event lines if the Table and Object Lookup option is selected for trace file parsing (and for certain wait event analysis). Later versions of Oracle Database emitted obj parameters on the WAIT lines, and I believe that my program will use that obj value rather than trying to look up the OBJECT_ID value using the p1, p2, and p3 parameters. The 10046 trace file parser performs a trick to handle the extremely long cursor numbers found in Oracle Database 11.2.0.2 and later.

What I tried to do is to make everything in the program work with Oracle Database 10.2, and (hopefully) gracefully degrade when an older version of Oracle Database is encountered.

Logging of database performance is scriptable. The documentation from 2008 includes the following command line parameters to enable scripting:

-LC 20   Specifies Force a Log Capture when CPU Usage Exceeds value to 20%
-LI 30   Specifies Force a Log Capture if No Log Captured in Minutes value to 30 minutes
-LB   Specifies the Force a Log Capture when a Blocking Lock is Detected value to checked
-LW   Specifies the Force a Log Capture when a Wait Reason is Detected value to checked
-LR   Specifies the Capture SQL Execution Statistics for Wait Reasons value to checked
-LD   Specifies the Capture Segment Change Statistics value to checked
-LO   Specifies the Capture Operating System and Time Model Statistics value to checked
-LH   Specifies the Capture High Load SQL Statement Statistics value to checked
-LT   Specifies the Capture High Load SQL Statement Text value to checked
-LP   Specifies the Capture High Load SQL Statement Plan value to checked
-LHC 60   Species the minimum CPU time that is considered high load to 60 seconds accum.
-LHE 90   Species the minimum elapsed time that is considered high load to 90 seconds accum.
-LS   Specifies that Smart Logging should begin as soon as the login completes
-LE 240   Specifies that Smart Logging should end after 240 minutes
-LQ   Specifies that the program should quit (end) when logging ends
-D   The Database instance SID to which the program should connect.
-U   The user name to be used for connecting to the database instance.
-P   The password to be used for connecting to the database instance.
13 03 2012
Gokhan Atil

Hello Charles,

I’m interested in joining beta program :) I can test it on Oracle 9-10-11.

Regards

Gokhan

13 03 2012
Charles Hooper

Thanks for the comment Gokhan.

You might be able to test whether the program (hopefully) gracefully degrades when an older version of Oracle Database is encountered.

13 03 2012
Marcus Mönnig

Hello Charles,

having written a tool with some similar features (10046 trace file parser, snapper support, actual executions plans, etc. – see link on my name), I would love to try out your toy project! :-)

Cheers,
Marcus

13 03 2012
Chris Saxon

This looks very interesting Charles, I’d like to join a beta program if you run one!

13 03 2012
Tony Johnson

Charles,

Would be very interested in running this in a number of large PeopleSoft 11gR1/R2 RAC and Standalone environments.

Thanks,

Tony

13 03 2012
Charles Hooper

Tony,

The program only queries V$ performance views and not GV$ performance views, so that might create a bit of a challenge in a RAC environment.

14 03 2012
Mark Davidson

Charles,

I’d love to beta-test your application. We’ve been looking for a tool for performance tuning. OEM is helpful but can be a bit clunky. We’re currently running 10g but are upgrading to11g this summer.

Thanks,
Mark

14 03 2012
Charles Hooper

Great to see the level of interest in the Beta version of the program.

The number of blog views yesterday was the highest ever in a single day, so there might be a couple people interested that have not left a comment. I will see if there is a way that I can post the latest Beta version of my program to the blog in the next day or two.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 141 other followers

%d bloggers like this: