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 18.104.22.168 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.
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:
Configure Data Change Log (showing one of the logging tables that was created by the program’s script generator):
DBMS_XPLAN and Trace: