March 15, 2012 (Modified March 16, 2012)
Several people expressed an interest in using the Beta version of my Hyper-Extended Oracle Performance Monitor 6.0 that has been under development for about a decade. Rather than trying to find a way to deliver the Beta version of the program to those people who left comments in the earlier thread, it seemed to be much easier to just post the Beta version to this blog.
The Hyper-Extended Oracle Performance Monitor 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 (2000, XP, 2003, 2007, or 2010) to be present on the PC. Everything that is logged is written to a C:\OracleLog folder on the client computer, and unfortunately that likely means that User Access Control (UAC) in Vista and Windows 7 will either need to be turned down or turned off completely (UAC will prevent programs from writing in folders that are located directly in the root of the C:\ drive). It is important to make certain that the Windows interfaces (all except MTS) are installed with the Oracle Client software, which should add the OraOLEDB functionality that is used by the program for connectivity to the databases.
An unfortunate side effect of using OraOLEDB functionality rather than ODBC is that the SYS user is not able to log in AS SYSDBA for certain tasks such as accessing the X$ structures (specifically X$BH, and the X$ structures (X$KSPPI, X$KSPPSV) needed for viewing the hidden initialization parameters). Setting the O7_DICTIONARY_ACCESSIBILITY initialization parameter to TRUE will allow the program to connect as the SYS user (without AS SYSDBA), but doing so may represent a security risk for the database.
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 the user attempts to enable a 10046 trace using the program, and the program determines that the MAX_DUMP_FILE_SIZE parameter is set far too small. The user that logs into the program will need proper permissions to access the various V$ views (GV$ views are not accessed) and also access the various packages that enable 10046/10053 traces (enabling a 10046 (or other trace) within the program’s interface requires that the user logging into the program have EXECUTE permission on the DBMS_SYSTEM and DBMS_MONITOR packages).
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 emit obj parameters on the WAIT lines, and the program should use the 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. The Hyper-Extended Oracle Performance Monitor is intended to work fully on Oracle Database 10.2, and (hopefully) gracefully degrade when an older version of Oracle Database is encountered.
The program supports several command line parameters, most of which are used to configure performance logging capabilities:
-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. -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
–
Important: Keep in mind that there is an overhead, primarily server CPU utilization, associated with performance monitoring. This overhead will be greatest when the program’s performance logging feature is utilized. This overhead, while typically minor, might negatively impact the performance of other database sessions. Under no circumstances should this program run directly on a Windows-based Oracle Database server’s console – doing so with performance logging enabled will significantly impact the performance of other database sessions.
This program does not phone home, nor does it collect any information that is not found in the C:\OracleLog folder on the client computer. The C:\OracleLog folder could prove to provide additional information that is not presented directly in the program interface. For example, when real-time performance is monitored, every 30 minutes the program will write one or more text files into the C:\OracleLog folder that show a crosstab style report of statistics and wait events (open the file with Microsoft Excel to aid readability). The performance logging feature creates a Microsoft Access compatible database (named to correspond to the logging date and time) in the C:\OracleLog folder – various information, such as in-effect initialization parameters, are written in that Access database, even though that information is not displayed in the program’s user interface.
———————————————————————————————–
———————————————————————————————–
———————————————————————————————–
DOWNLOAD:
The documentation for the program is at least four years out of date. You may download the program instructions for the Hyper-Extended Oracle Performance Monitor 3.0 Beta here: Hyper-ExtendedOraclePerformanceMonitor3Docs
The Beta version of the program is time limited, however it should continue functioning for the next 12 months. You may download the program by right clicking the file and saving it as “Hyper-ExtendedOraclePerformanceMonitor6.zip” (the .zip extension must be specified): Hyper-ExtendedOraclePerformanceMonitor6.zip
The program is compressed using WinZip – Windows XP and later are able to directly open .zip files. To install the program, simply extract the two files into the same folder; to uninstall, delete that folder and the C:\OracleLog folder.
If you find the program useful, feel free to leave a comment here. If you find that this program is the biggest waste of a decade’s worth of free time, I would be happy to hear that too. The program has a couple of known bugs – I know that they exist, but I do not know where they are in the program, nor do I yet know what the bugs affect.
———————————————————————————————–
———————————————————————————————–
———————————————————————————————–
Added March 16, 2012:
I thought that I would show a couple of screen captures from my program that are not necessarily performance tuning specific.
The Advanced Initialization Parameters Viewer (currently only works if the O7_DICTIONARY_ACCESSIBILITY initialization parameter is set to TRUE , but I am considering a couple of work around methods – note that the program’s description of the CURSOR_SHARING parameter does not yet mention that the SIMILAR value for the CURSOR_SHARING parameter is deprecated):
Keyword Search Viewer:
Lock/Wait Monitor:
Configure Data Change Log (showing one of the logging tables that was created by the program’s script generator):
DBMS_XPLAN and Trace:
Charles Hooper's Oracle Notes 
Hi Charles,
First of all, thank you for your website and also for your comments on Jonathan Lewis’s Oracle Core Essentials book.
I was impressed by the accuracy and the amount of things you said despite the work of the technical reviewer.
As concerning your Hyper-Extended Monitor, I have a question given I did not succeed in using it so far.
I started an XE instance on my localhost and I have a “Could not connect to the database. Check you user or password” message when using the “XE” Oracle_SID and system user.
What is wrong with me ?
A “tnsping XE” returns an “OK – SID=XE”.
Thanks for your help.
Jean-michel, Nemours, FRANCE
Jean-Michel,
Regarding the book, both Jonathan Lewis and Tanel Poder (the book’s technical reviewer) have Oracle knowledge that extends far beyond what I know about that product (I have learned from both, but do not always remember the fine details). It is mostly simple mistakes that I pointed out – items that are very easy to overlook when the author re-reads something that he wrote; to see the errors it is probably almost a necessity to allow what is written to remain untouched for a month before re-checking what was written. Jonathan’s approach to responding to my error reports was well handled, with him responding publically, and in detail within a couple of days. The same approach cannot be said about some of the other book authors – none of the 21 errata reports that I filed for the book “Oracle Database 11gR2 Performance Tuning Cookbook” have appeared yet on the publisher’s website, so there was little sense in me filing errata reports for the other errata items (that reminds me that I need to finish up the second half of that book review). I put a lot of effort into the book reviews to make those reviews as specific as possible – thank you for noticing.
Thank you for stopping by my blog and trying to use my program with Oracle XE. I do not have much experience with Oracle XE, other than installing it one time. A Google search found that several other people have also experienced difficulty in connecting to XE databases using other software. When the “Could not connect to the database. Check your user name and password.” error appears on the screen, a more descriptive error message should appear immediately after that text (on the next line) – did you see a more descriptive message with an ORA- error?
The documentation, under the heading “Making Oracle Database XE Available to Remote Clients” indicates that you might need to make a change to the XE configuration:
http://docs.oracle.com/cd/E17781_01/install.112/e18803/toc.htm#autoId21
One of the websites that I found through a Google search indicates that you should specify in the Oracle SID box the IP address of the computer, followed by a colon, the listener port number, a forward slash, and then XE, as follows:
Another site suggested using the localhost address (127.0.0.1) followed by a forward slash and XE:
Make certain that the Windows firewall (or firewall supplied with an antivirus product) is not blocking port 1521. Also, some sites mentioned that if you have an XE Oracle home along with other Oracle homes on the computer, you might experience problems during connection attempts (ADO may be seeing the wrong Oracle home).
The Hyper-Extended Oracle Performance Monitor program connects using ADO and the OraOLEDB.Oracle provider, similar to several of the VBS scripts that I have on this blog. Are you able to successfully connect to the XE database using any of these scripts:
https://hoopercharles.wordpress.com/2010/09/12/the-sql-to-the-orbiting-ball/
https://hoopercharles.wordpress.com/2010/08/25/oracle-logging-trigger-creator/
https://hoopercharles.wordpress.com/2010/01/14/working-with-oracles-time-model-data-2/
In the above scripts, you should see a line similar to the following:
Between the quotes in the above line, you can try the various naming conventions mentioned above (XE, 192.168.0.195:1521/XE,127.0.0.1/XE) to see if you are able to find one that works with XE. If you find one that works, it should also work with my Hyper-Extended Oracle Performance Monitor program.
If anyone has been able to use my Hyper-Extended Oracle Performance Monitor program or one of my VBS scripts with XE, please share what you needed to do to make the connection work.
Thank you very much for your detailed answer.
Being on holiday presently, I will check out my XE database with your vbs scripts in a few days.
Once more, thank you for sharing even the simple mistakes with others.
I had to tell my colleagues that the Oracle community is rich and share a lot to make the whole thing move.
Greetings
Jean-michel
[…] Charles Hooper talks about Hyper-Extended Oracle Performance Monitor 6.0 Beta. […]
I have gone throw your URL and there is amazing database related information. I believe you are all-rounder person and you have spent lot of time to make page very good.
You doing very good services and God bless you.
Selvam
Chennai,India.