Thoughts on a Hyper-Extended Oracle Performance Monitor Beta

12032012

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:

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:

Actions

Information

16 responses

12032012

jeremy(22:45:29) :

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.

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?

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.

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.

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.

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.

Carol,
Unfortunately, I have not had a chance to do any development work on the program in almost three years. I remember that I was in the process of implementing some sort of feature into the program, but can’t remember exactly what it is right now. The version that I posted to my blog three years ago probably expired two years ago. Please check back over the next couple of weeks – I will try to release a new beta version (I have the feeling that the program may never leave the beta stage). I have been using a slightly older version of the program at work – that version gets tripped up when processing the 11.2.0.x style cursor numbers – I will have to see if the most recent version has the same problem.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: