Hyper-Extended Oracle Performance Monitor 7.0 Beta

16 08 2015

August 16, 2015

Three and a half years ago I offered the Hyper-Extended Oracle Performance 6.0 Beta for download – that Beta version expired a year later.  This program has been somewhat of a pet project for the last 13 years (almost 10 years at the time of the previous Beta offering), so I was excited to read some of the feedback about the 6.0 Beta.  The minimal feedback had me wondering for a while, then the pet project was pushed to a dark corner for roughly three years.  I taught the pet a few new tricks on Windows 10, so I thought that I make the latest version available.  Unlike the previous Beta, Beta 7.0 does not have an expiration date.

NOTE August 17, 2015: The first two download links are working now – Wordpress was blocking the downloads.  Note that the first two downloads are actually compressed .zip files with a .doc extension – inside those .zip files are the files that are of interest.  Right-click the files, save the files to your computer, then rename the files to end with .zip.

  1. Program download (right-click Save As, then change the .doc extension to .zip): Hyper-ExtendedOraclePerformanceMonitor7.zip
  2. Documentation download (right-click Save As, then change the .doc extension to .zip – still incomplete, see old docs): Hyper-Extended Oracle Performance Monitor7.doc
  3. Old Documentation download: Hyper-Extended Oracle Performance Monitor3.doc
  4. Update August 20, 2015: If you have an old computer, you may need to put the MSCOMCTL.OCX file in your C:\Windows\SysWOW64 folder (on 64 bit Windows, or C:\Windows\System32 on 32 bit Windows).  You will then need to register (process) the file with REGSVR32 – see the command in the instructions below.  A recent version of MSCOMCTL.OCX may be downloaded here: MSCOMCTL.OCX (save the file, then rename the file as MSCOMCTL.OCX).

Requirements:

Windows 2000 through Windows 10, 32 bit Oracle Client, MDAC/ADO 2.8 (or greater – preinstalled starting with Windows XP), connection to the Oracle database by database SID (tnsnames.ora) using Oracle’s Oracle Provider for OLE DB (Oracle’s Oracle ODBC Driver is required for connections that are initiated by SYS – primarily for access to the Advanced Init Parameter functionality), Excel 2000-2013, Microsoft Grid control (provided in the download – put into the program’s folder), TIMED_STATISTICS set to TRUE. Most features require DBA permissions (SYSADM or SYS), or specific grants to views or packages. For example, sessions that use the DBMS Xplan and Trace functionality must have permission to execute ALTER SESSION, have execute permission on the DBMS_XPLAN package, and select permissions on V$SQL, V$SQL_PLAN, and V$SQL_PLAN_STATISTICS.  Configuring session tracing requires execute permission on the DBMS_SYSTEM package and/or the DBMS_MONITOR package.

Description:

The Hyper-Extended Oracle Performance Monitor provides a variety of functions to simplify working with and performance monitoring Oracle databases. Various types of simple data dictionary and database instance related reports may be generated in Excel by simply putting a check in a box on the program’s main window, and clicking the Report button. Generating DBMS Xplan output (using the raw information from a 10046 level 4/12 trace) with optional 10046/10053/other traces, creating data change logging triggers using the data dictionary, peeking at the hidden and non-hidden initialization parameters, and access to a quick Oracle keyword search are also a click away on the program’s main window. Performance monitoring and tracing tools for checking a cross-reference of the time model and wait event statistics (at the system and session levels), checking a cross-reference of the system statistics and wait event statistics (with drill-down to the session level), reviewing potentially high-load SQL statements and their execution plans, cross-referencing the current session waits with enqueues, and enabling various types of Oracle traces are also a click away on the program’s main window. Permanently recording of performance monitoring statistics in a Microsoft Access compatible database (Microsoft Access is not required) is also a click away on the program’s main window.

General Command Line Parameters:

  • -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.

Logging-Specific Command Line Parameters:

  • -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

Using the above command line parameters, you could create a program shortcut for each of the databases that you administer.  For example, create a shortcut, and set its Target to (assuming that the program is on the root of the F: drive, the Oracle SID is OR1122P, the username is myuser, the password for that account is pword, and the smart logging should default to logging when 10% of CPU utilization is observed):

"F:\Hyper-Extended Oracle Performance Monitor.exe" -D OR1122P -U myname -p pword -LC 10

What’s New:

The Hyper-Extended Oracle Performance Monitor runs on Windows 10, even though the touchpad on the Sony laptop sometimes stops working until the computer is put to sleep and then awoken.  The program should also work with Oracle Database 12c (and might still be compatible with Oracle Database 8i, although the Time Model Viewer will not work).

Hyper7HyperExtendedMainScreen

The Time Model Viewer main window is still essentially the same, showing the hierarchy of the time model statistics, with color-coded session-level time model statistics (color ranges from yellow to red to indicate the percentage of the total represented by the session).  The bottom of the window shows the system-wide wait events, and the sessions that contributed to those system level wait events:

Hyper7TimeModelViewer

When the Time Model Viewer is active, an Excel spreadsheet is created that shows the graphic history of the statistics for the 20 most recent time capture periods – if you find this view helpful, you might want to widen the DB Time and CPU chart:

Hyper7TimeModelViewerExcelCharts

The statistics also appear in numerical form on the Statistics tab in the Excel spreadsheet:

Hyper7TimeModelViewerExcelStatistics

The Real-Time Monitor is also essentially the same as in previous Beta versions:

Hyper7RealTimeMonitor

Like the Time Model Viewer, an Excel spreadsheet is created showing the graphical history of the statistics.  Which statistics?  Click a wait event or one of the white statistics boxes to add that statistic to the list for which charts are created:

Hyper7RealTimeMonitorExcelCharts

Previous Beta versions of the program wrote out a tab delimited file containing the statistic deltas after every 30 time capture periods (this version also creates such a file).  The Beta for version 7 writes those statistics to Excel after each time capture period to facilitate the chart creation:

Hyper7RealTimeMonitorExcelStatistics

Oracle’s OLEDB provider is used for connectivity, see this article for a description of how to fix the bug in the Oracle Database 11.2.0.3 Client installer that is related to the OLEDB provider (the problem may have been corrected in later releases).  I have not yet found a method for SYS to login using Oracle’s OLEDB provider when “AS SYSDBA” must be specified for the login to complete.  As such, Oracle’s ODBC driver is used when SYS tries to login and the login attempt using the OLEDB provider fails.  Oracle’s ODBC driver is also used when viewing the normally hidden parameters using the SYS login.  Because there could be multiple Oracle homes, you must create a 32 bit ODBC System DSN using the Oracle ODBC driver – name that DSN HYPEREXTEND:

Hyper7Login

Hyper7ODBC1

Hyper7ODBC2

Hyper7ODBC3

The ODBC connection driver information is used to login as the SYS user (AS SYSDBA) to view the Advanced Initialization Parameters:

Hyper7AdvancedInitParameters

Hyper-Extended Oracle Performance Monitor 7.0 Beta is also more compliant with restrictions placed on the computer by User Account Control (User Access Control), which prohibits programs from create folders in the root of the C:\ drive and writing files into that folder.  Version 7 instead creates the OracleLog folder in the current user’s profile (typically C:\Users\username\OracleLog\), and separates the files created by database.  The picture below shows a couple of Microsoft Access compatible logging database that were created, as well as some of the tab-delimited statistic files, and various other generated files:

Hyper7LoggingFolder

As mentioned above, the documentation is still incomplete, with several pages of the documentation containing nothing but pictures.  It takes a lot of effort and time  to put the documentation together, so I will try to piece it together over the next couple of months.  You might be able to refer to the older documentation if the pictures are not self-explanatory.  If you find the program useful, please leave a note.  If you need help understanding a program window or the generated output, leave a comment attached to this blog article and I will try to help.

Hyper7b3.zip (Save with a .zip extension, not a .doc extension.)


Actions

Information

49 responses

17 08 2015
diaxlee

Hi Charles,
the download link for Hyper-ExtendedOraclePerformanceMonitor7.zip is not working ..
Follower from FRANCE

17 08 2015
Charles Hooper

Diaxlee,
I was able to fix the download for the Hyper-Extended Oracle Performance Monitor program. The link to the current documentation (mostly screen captures) is not working yet. This is a direct link to the download file – make certain that you right-click and select Save As. Once the download completes, change the extension so that it ends with .zip rather than .doc:
https://hoopercharles.files.wordpress.com/2015/08/hyper7_zip.doc

17 08 2015
Charles Hooper

Hi Diaxlee,
I am seeing the same problem – cannot download Hyper-ExtendedOraclePerformanceMonitor7.zip or Hyper-Extended Oracle Performance Monitor7.doc, although I am able to download the old documentation. I am not sure if this is a problem with the file attachment size, or if WordPress is simply blocking those types of files. I will try to fix the problem in 12 hours. Thank you for bringing this problem to my attention.

17 08 2015
Damir Vadas

nice work. appresciated

18 08 2015
jean-michel A.

Thank you very much Charles for sharing your tool.
I created a 11g ODBC driver called HYPEREXTEND.
I then launch a .bat to work on a 10g database.
HEOPM displays the report but displays also a window while stopping with :

==========
Signature du problème :
Nom d’événement de problème: BEX
Nom de l’application: Hyper-Extended Oracle Performance Monitor.exe
Version de l’application: 7.0.0.0
Horodatage de l’application: 55d11b5b
Nom du module par défaut: StackHash_f436
Version du module par défaut: 0.0.0.0
Horodateur du module par défaut: 00000000
Décalage de l’exception: 9b0cbf59
Code de l’exception: c0000005
Données d’exception: 00000008
Version du système: 6.1.7601.2.1.0.256.48
Identificateur de paramètres régionaux: 1036
Information supplémentaire n° 1: f436
Information supplémentaire n° 2: f436db21e647818801ca0ba57bcd565f
Information supplémentaire n° 3: 5dec
Information supplémentaire n° 4: 5dec29d25d6789393dca0f5b91a5e029

Lire notre déclaration de confidentialité en ligne :
http://go.microsoft.com/fwlink/?linkid=104288&clcid=0x040c

Si la déclaration de confidentialité en ligne n’est pas disponible, lisez la version hors connexion :
C:\Windows\system32\fr-FR\erofflps.txt
==========

Sorry for the french. Would you like me to translate a few words ?

Can you help me ?

Thanks.

Jean-michel from FRANCE, interested in your posts and your books reviews.

18 08 2015
Charles Hooper

Jean-Michel,
The HYPEREXTEND ODBC driver is only required when displaying the Advanced Init Parameters, or when logging into the program as the SYS user. Some functionality will not work correctly when the ODBC driver is used – I think that this is only a problem when traces are enabled or disabled in other sessions. In general, the SYS user probably should not be used in the program, but you do need to make certain that the username that you are using has permission to select from the various V$ views.
—-
The program reads the USERPROFILE environment variable, rather than making a Windows API call, to determine the location where the ORACLELOG folder will be created. There is a benefit to this approach – if you would like to have the ORACLELOG folder created somewhere else, and you are starting the program from a .bat file, you can change the USERPROFILE environment variable in the .bat file, and it will only affect the programs that are started within the .bat file (the Hyper-Extended Oracle Performance Monitor). There is a chance that the ORACLELOG folder is not being created inside the location specified by the USERPROFILE environment variable (that could be the problem if your USERPROFILE environment variable has a \ at the end, as would be the case if your user profile was moved to the root of a mapped drive letter, such as H:\ or Z:\).

Please open a Windows command line (run cmd.exe then press the Enter key). At the Windows command line, type SET then press the Enter key. You should see output that has several variable names followed by an = and then a value. For example:

Z:\>set
ALLUSERSPROFILE=C:\ProgramData
APPDATA=C:\Users\hooper\AppData\Roaming
BURN_AUTOPLAY=C:\Program Files (x86)\Roxio\OEM\Roxio Burn\
CI_HOLOS_CLI=C:\Program Files (x86)\Seagate Software\Open Olap\
...
SystemDrive=C:
SystemRoot=C:\Windows
TEMP=C:\Temp
TERM=nutc
TERMCAP=C:\PROGRA~1\PTC\MKSTOO~1\etc\termcap
TERMINFO=C:\PROGRA~1\PTC\MKSTOO~1\usr\lib\terminfo
TMP=C:\Temp
USERNAME=hooper
USERPROFILE=C:\Users\hooper
VS80COMNTOOLS=C:\Program Files (x86)\Microsoft Visual Studio 8\Common7\Tools\
windir=C:\Windows

Find the USERPROFILE variable. Does the value of that variable end with a \ character (the first bug in the program – I do not think that I checked for that problem)? Remember that because you are starting the program from a .bat file, we are actually able to have the OracleLog folder created somewhere else, for example, if you include the following command in the .bat file before the command to start the program, the OracleLog folder will be created in your temporary files folder (do not execute this command outside of the .bat file):

set USERPROFILE=%TEMP%

Next, see if the OracleLog folder was created in the correct location. At the Windows command prompt, type the following, and press the Enter key:

dir %USERPROFILE%

My output looks like this:
Z:\>dir %USERPROFILE%
 Volume in drive C has no label.
 Volume Serial Number is 5200-DC39
 
 Directory of C:\Users\hooper
 
08/18/2015  06:22 AM              .
08/18/2015  06:22 AM              ..
07/25/2011  10:59 AM           566,790 AdaptiveCursorBindTest.txt
04/20/2015  06:03 AM              Contacts
07/30/2015  02:43 PM              Desktop
08/17/2015  05:03 PM              Documents
06/17/2015  02:40 PM              Downloads
07/01/2015  06:32 AM              Favorites
08/15/2011  03:51 PM             2,187 justtesting.sql
04/20/2015  06:03 AM              Links
04/20/2015  06:03 AM              Music
06/23/2011  11:54 AM              Oracle
08/18/2015  06:22 AM              OracleLog
08/12/2015  12:57 PM              Pictures
04/20/2015  06:03 AM              Saved Games
04/20/2015  06:03 AM              Searches
07/30/2015  02:39 PM              Videos
04/20/2015  06:03 AM              Virtual Machines
01/10/2012  05:07 PM              XES
               6 File(s)      1,737,399 bytes
              17 Dir(s)  19,360,374,784 bytes free

The OracleLog folder should be created the first time you start the program. When you create a report in the Hyper-Extended Oracle Performance Monitor, the program writes a tab delimited text file into this folder (actually in a folder inside this folder), and then tells Excel to open the text file, referencing it as a QueryTable. If my program crashed, that text file should still be present in the folder (I think that the file is named Report.txt). Does that Report.txt file exist in the expected location?

18 08 2015
Charles Hooper

Jean-Michel,
As a follow up, on the Windows command line, I typed:

set USERPROFILE=%TEMP%

I then launched the Hyper-Extended Oracle Performance Monitor from the command line:

"c:\Hyper-Extended Oracle Performance Monitor.exe"

When trying to create a report in the program, Excel would start, the Report.txt file would be created, but the Excel window remained blank and the program locked up. I am not yet sure why that would happen. Setting the USERPROFILE environment variable back to the default value allowed Excel to open the report. I will look into this issue later today.

Thank you for the information.

18 08 2015
Charles Hooper

Follow up. The Hyper-Extended Oracle Performance Monitor does correctly handle cases where the last character of the USERPROFILE environment variable is a \ character, such as at the root of the H:\ or Z:\ drive. I forgot that I programmed for this situation (a potential problem that I learned probably 21 years ago). Do NOT change the USERPROFILE location and then expect the program to be able to start Excel – it will start, but the window will not be visible, and my program will hang. Excel did start correctly when I launched Excel from the command line after changing the USERPROFILE location, but at some point Excel tried to create the following nested folders in the location that I specified for the USERPROFILE variable:
\AppData\Roaming\Microsoft\AddIns

Jean-Michel, please let me know the results of your test when you have some time to experiment.

18 08 2015
jean-michel A.

Hi Charles,
A second comment while trying the Time Model Viewer functionality.
I have the following window :
“The selected sheets will be deleted permanently.
. Tu delete definitely the selected sheets, click on OK
. To cancel the deletion, click on Cancel.

A click on OK and excel displays a gray window with no information on it except a few icons in the menu bar.
HEOPM then aborts with the following message :

=========
Signature du problème :
Nom d’événement de problème: BEX
Nom de l’application: Hyper-Extended Oracle Performance Monitor.exe
Version de l’application: 7.0.0.0
Horodatage de l’application: 55d11b5b
Nom du module par défaut: StackHash_f436
Version du module par défaut: 0.0.0.0
Horodateur du module par défaut: 00000000
Décalage de l’exception: 9b0cbf59
Code de l’exception: c0000005
Données d’exception: 00000008
Version du système: 6.1.7601.2.1.0.256.48
Identificateur de paramètres régionaux: 1036
Information supplémentaire n° 1: f436
Information supplémentaire n° 2: f436db21e647818801ca0ba57bcd565f
Information supplémentaire n° 3: 5dec
Information supplémentaire n° 4: 5dec29d25d6789393dca0f5b91a5e029

=========

Thanks in advance.

Jean-michel A., Nemours, FRANCE

18 08 2015
Charles Hooper

Jean-Michel,
I have tested the Time Model Excel chart functionality with Windows XP Pro and Excel 2003, Windows 7 Pro (and Ultimate) and Office 2010, Windows 10 Pro and Office 2010, and Windows Server 2012 and Office 2013. The Time Model Excel chart functionality seemed to work in all cases, although the charts in Excel 2003 did not look as nice. The message “The selected sheets will be deleted permanently” never appeared with those versions of Excel. That said, I have an idea what would cause that message to appear.

My program instructs Excel to create a new workbook. It then verifies that at least two worksheets are in the new workbook, and if not, it creates blank worksheets until there are two worksheets. A typical installation of Excel will create three worksheets by default when a new workbook is created. As such, next the program instructs Excel to delete the third worksheet until there are only two worksheets remaining in the workbook. If these worksheets are not completely blank when deleted, Excel will display the warning “The selected sheets will be deleted permanently”, and ask if you would like to continue. Do you use the standard Excel blank workbook template in Excel, or has the standard template been replaced so that default text appears on a worksheet? Do you have macros in Excel that automatically add text to a worksheet when a workbook is created? I am able to shut off that warning when the excess worksheets are deleted, but I am now curious why Excel does not consider those worksheets as being blank.

The gray screen in Excel is caused by the Hyper-Extended Oracle Performance Monitor. It instructs Excel to stop updating the screen when each value is entered into the worksheet, which greatly decreases the time to update the spreadsheet. At the end of the update process it instructs Excel to update the screen, which will make the gray screen go away. It appears that my program crashes before the update process completes, causing the Excel window to remain gray.

Anyone else see these issues (or not see these issues)? Is it specific to the French version of Windows/Excel, an Excel template issue, a virus scanner issue (some virus scanners will block a program from remotely controlling another program, although the Hyper-Extended Oracle Performance Monitor controls Excel through its published command API), the location of the user profile, or something else?

18 08 2015
jean-michel A.

Thank you Charles for the answer.
I’ll check asap ( tomorrow in fact ).
In fact I did not mention my versions … Sorry.
I have an old excel version but given you mentioned that Excel 2000 is supported, I do not think that is the reason it failed.

Jean-michel

18 08 2015
Charles Hooper

It has been a long time since I tested Excel 2000, but I did test Excel 2003 a couple of hours ago on a nearly 10 year old Windows XP Pro computer. Which Oracle Client version are you using? 11.2.0.1 Client has issues with ODBC ( https://hoopercharles.wordpress.com/2010/11/02/11-2-0-1-odbc-update-problem/ ), 11.2.0.2 Client seems to be OK, but occasionally has issues with certain OLE DB issued SELECT statements. 11.2.0.3 Client does not install OLE DB correctly ( https://hoopercharles.wordpress.com/2012/11/25/connecting-to-an-oracle-database-with-visual-basic-6-0-on-windows-8-64-bit/ ) but is fixable.

I strongly suspect that there is an issue with the USERPROFILE environment variable based on some quick testing this morning.

Please let me know what you find.

19 08 2015
jean-michel A.

Charles,

1) My USERPROFILE environment variable does not end with a \.
Here is its content :
C:\Users\jmalzingre

2) Excel sheets when opening
3 sheets are opened just lije you were thinking about
nothing is present in those sheets
I will from now on open two sheets only

3) I run HEOPM from a PC where I have installed a 11.2.0.1.0 database.

Thank you

Jean-michel

19 08 2015
jean-michel A.

Charles,

When running HEOPM, I still have my problem with the following error :

Signature du problème :
Nom d’événement de problème: BEX
Nom de l’application: Hyper-Extended Oracle Performance Monitor.exe
Version de l’application: 7.0.0.0
Horodatage de l’application: 55d11b5b
Nom du module par défaut: StackHash_f436
Version du module par défaut: 0.0.0.0
Horodateur du module par défaut: 00000000
Décalage de l’exception: 9b0cbf59
Code de l’exception: c0000005
Données d’exception: 00000008
Version du système: 6.1.7601.2.1.0.256.48
Identificateur de paramètres régionaux: 1036
Information supplémentaire n° 1: f436
Information supplémentaire n° 2: f436db21e647818801ca0ba57bcd565f
Information supplémentaire n° 3: 5dec
Information supplémentaire n° 4: 5dec29d25d6789393dca0f5b91a5e029

1) In report mode, I manage to close the error window to then read the excel sheet with the data in it. Once the sheet is closed, no error occur.
2) In Time Model Viewer mode, the HEOPM window remains greyed. When clicking on “Update” at the top left of the Excel sheet, I have an Excel “unaccessible file” window.
When clicking on OK, I can see the 4 charts with no data in them. When closing, Excel wants logically to know if I need to register the updates.

Thank you

Jean-michel

19 08 2015
Charles Hooper

Jean-Michel,
Thank you for the additional information. Since my program is able to create the charts, that means that it should be able to write the statistic values into Excel that are retrieved from the database server. Your USERPROFILE variable does not appear to be a problem. It may be a permission problem with the Oracle username that you are using to access the database – the program does a quick check to see if the Time Model Viewer should be enabled or disabled (based on Oracle Database version, if I remember correctly). I created a test user and did not grant that user any permissions – the Time Model Viewer was disabled, but not the Real-Time Performance feature. If I grant the user permission to V_$DATABASE and V_$INSTANCE, the Time Model Viewer button is enabled, but the program locks up (the Excel window never appears either).

Are you able to create a 10046 trace at level 8 or 12 for the Hyper-Extended Oracle Performance Monitor, and then try to start the Time Model Viewer? If there is a permission problem, you should see lines in the trace file indicating tables/views do not exist.

19 08 2015
jean-michel A.

Charles,

I have made two tries at 19h09 pm then 19h14 pm ( each one with : 1) simple report 2) Time Model Viewer )
I changed my database ( I was working on a distant one ) to work on my local database ( ORCL ) but still encounters the same errors. Sorry …

Here is what I got :

*** 2015-08-19 19:08:38.628
*** SESSION ID:(11.5) 2015-08-19 19:08:38.629
*** CLIENT ID:() 2015-08-19 19:08:38.629
*** SERVICE NAME:(SYS$USERS) 2015-08-19 19:08:38.629
*** MODULE NAME:(SQL*Plus) 2015-08-19 19:08:38.629
*** ACTION NAME:() 2015-08-19 19:08:38.629
 
WAIT #4: nam='SQL*Net message to client' ela= 4 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=21500628125

*** 2015-08-19 19:09:25.728
WAIT #4: nam='SQL*Net message from client' ela= 47095681 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=21547726307
CLOSE #4:c=0,e=43,dep=0,type=1,tim=21547726585
=====================
PARSING IN CURSOR #5 len=55 dep=0 uid=5 oct=42 lid=5 tim=21547726795 hv=2217940283 ad='0' sqlid='06nvwn223659v'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #5:c=0,e=163,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=21547726794
EXEC #5:c=0,e=527,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=21547727378

*** 2015-08-19 19:14:56.507
WAIT #2: nam='SQL*Net message to client' ela= 4 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=21878484726

*** 2015-08-19 19:15:35.565
WAIT #2: nam='SQL*Net message from client' ela= 39055710 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=21917540613
CLOSE #2:c=0,e=9,dep=0,type=3,tim=21917540873
=====================
PARSING IN CURSOR #4 len=55 dep=0 uid=5 oct=42 lid=5 tim=21917540960 hv=2217940283 ad='0' sqlid='06nvwn223659v'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #4:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=21917540959
EXEC #4:c=0,e=1748,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=21917542821

No error at first glance.

Thanlks

Jean-michel

19 08 2015
Charles Hooper

Jean-Michel,
Thank you for posting the trace. I might be reading the trace file incorrectly, but it appears that you may have enabled a 10046 trace for your SQL*Plus session for 47 seconds, rather than the session for the Hyper-Extended Oracle Performance Monitor program.

You may use either the DBMS_MONITOR or DBMS_SYSTEM packages to enable a trace in another session – there are examples of the two methods at the bottom left of the Hyper-Extended Oracle Performance Monitor program. You first need to find the SID and SERIAL# of the session to be traced:

SET LINESIZE 140
 
SELECT
  SID,
  SERIAL#,
  USERNAME,
  PROGRAM
FROM
  V$SESSION
WHERE
  UPPER(PROGRAM) LIKE '%HYPER%';

Your output may look something like this:

SID    SERIAL# USERNAME                       PROGRAM
--- ---------- ------------------------------ ---------------------------------------------
 68       6559 TESTUSER                       Hyper-Extended Oracle Performance Monitor.exe

The general syntax for enabling a 10046 trace at level 12 in another session is as follows:

EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(SESSION_ID=>sid,SERIAL_NUM=>serial#,WAITS=>TRUE,BINDS=>TRUE)

So, if I wanted to enable a 10046 trace at level 12 for the above session, I would execute this in SQL*Plus (note that you probably should not log into the Hyper-Extended Oracle Performance Monitor program as the SYS user):

EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(SESSION_ID=>68,SERIAL_NUM=>6559,WAITS=>TRUE,BINDS=>TRUE)

Note that enabling 10046 traces is something that may be done within the program by using the Config. Session Tracing button in the program’s main window, but it will be easy enough for you to enable this trace using SQL*Plus.

I might try to do a little more testing later tonight to see if I am able to duplicate the problem that you are having with Microsoft Excel. Do you have a virus scanner on the computer – there is a small chance that a virus scanner could be blocking some of the communication. However, you told me that the program was able to create a couple of charts in Excel, so that is not likely.

A macro in Excel that automatically executes when a cell’s value changes might be causing some issues if you do not see any errors in the 10046 trace file. In Excel 2007 and above it is not easy to view the macros that are set up in Excel because by default the Developer menu is turned off. If you use the Time Model Viewer, and you see that four charts are created, please right-click the Charts tab and select View Code. My program does not create any macros in Excel, so you should not see any macro code when you view the code in Sheet1 (Charts), Sheet2 (Statistics), ThisWorkbook, or any modules (Module1, Module2, etc) when clicking those items in the Project Explorer (you may need to select to view the Project Explorer from the View menu).


Normally, I would expect this reporting to Excel to just work without having to go through a lot of troubleshooting. Is anyone else having similar problems with the Hyper-Extended Oracle Performance Monitor program and Microsoft Excel?

19 08 2015
Charles Hooper

I just checked the programming code again. With the Time Model Viewer, the Excel charts are created _after_ the database performance views are queried. Immediately after the charts are created, my program sends the following instructions to Excel to move copy the cell contents, and then paste the cell values one row below (this method is used so that the data range for the chart is not changed):

Sheets("Statistics").Range("A2:AC1000").Copy
Sheets("Statistics").Range("A3:AC1001").PasteSpecial xlPasteValuesAndNumberFormats

Immediately after the above, the current time is put into cell A2, and the number 0 is entered into cells B2 to J2. If you are able to see the charts, but cannot see the current time in cell A2 or the number 0 in the cells B2 through J2, then the issue is very likely that copy to the Windows clipboard or paste from the Windows clipboard is not working. If you want to try an experiment, create a new Excel spreadsheet, highlight cells A2 to AC1000, press Ctrl C to copy the cells to the Windows clipboard, click in cell A3, then press Ctrl V to paste the cells from the clipboard.

Four years ago I published a six part article that showed how to build something similar to the Hyper-Extended Oracle Performance Monitor’s Time Model Viewer using just Excel. This is the final article in that series:
https://hoopercharles.wordpress.com/2011/08/18/oracle-database-time-model-viewer-in-excel-6/
If you want to try another test, download the project at the end of the above article – a link is provided below (save the file to your computer, replace the .doc extension with .xls):
https://hoopercharles.files.wordpress.com/2011/08/timemodelviewerexcelarticle6-xls.doc

When you open the Excel spreadsheet, you will need to make a minor modification to the program code to specify the database, username, and password. After opening the spreadsheet, right-click the Wait Events tab and select View Code. In the Project Explorer window, find the Forms and inside frmTimeModel. Right-click frmTimeModel and select View Code. In the left drop-down list (combobox), select UserForm. In the code window you should find the following lines:

    strUsername = "MyUsername"
    strPassword = "MyPassword"
    strDatabase = "MyDB"

Modify those lines. Inside the ” ” specify your username, password, and database SID, just as if you were starting my Hyper-Extended Oracle Performance Monitor. Save the code, then press the F5 key to run the code in the UserForm. Do you have the same lock up problem when running the macro code in this spreadsheet?

20 08 2015
jean-michel A.

Charles,
Thank you for the last updates.
I am so sorry for the 10046. Being not used to it, I got confused and I thank you for your patience and your very clear explanations.
I do have a virus scanner : F-Secure client security
After a richt click on the Charts tab, I do not have any code whatever the sheet.
There are no number on the whole Statistics sheet, only the header.
I will try the experiment asap along with the 1046 trace content.
Btw it is not easy to find the trc associated with the 10046 among many trace files, given Oracle reuses it as far as I can see.
On my windows 7 PC, I am going to try to use PowerShell to easily find a text string like ‘PARSING IN CURSOR’.

Thank you once more.

Jean-michel

20 08 2015
jean-michel A.

Charles,

The trc file has a 251 Ko size.
How can I find permission problems given there are no ‘ORA-‘ or “permission” strings in it ?
As for the experiment, I do not know what AC1000 means.
I created a new sheet then copied from A2 to IU2 ( sheet maximum line size ) and pasted the line into the A3 cell : no problem.
I wii try the 2011/08 project asap.

Thank you

Jean-michel

20 08 2015
Charles Hooper

As I mentioned above, right now I do not think that it is a permission problem in the Oracle Database. If it were a permission problem, I think that ORA- or the word error (or the French spelling of that word) would appear in the trace file.

The Excel charts are created _after_ the database performance views are queried. Immediately after the charts are created, a large number of cells on the Statistics tab in Excel are copied, and then pasted one row down in Excel. Immediately after the copy-paste, the current time is entered into cell A2 of the Statistics tab. Based on the information that you supplied above, the problem appears to be either with the copy-paste operation, or where my program enters the current time into cell A2 on the Statistics tab.

Regarding AC1000, the columns in Excel are labeled A, B, C, D, …, Y, Z, AA, AB, AC, AD, … AY, AZ, BA, BB, etc. To highlight cells A2 to AC1000, click in cell A2 (the cell in the first column, second row), hold down the Shift key on the keyboard, while holding down the Shift key, press the right arrow key on the keyboard roughly 28 times to reach column AC, then while continuing to hold down the Shift key, hold down the down arrow key (or press it 998 times) on the keyboard until row 1000 is reached. You may then release the Shift key once all of those cells are highlighted. Next, hold down the Ctrl key and press the C key to copy the cells to the clipboard. Finally, click in cell A3, hold down the Ctrl key, and press the V key to paste the cells. This will paste the cells over the top of the existing cells, one row down. You should not receive any warnings or errors when doing this operation.

20 08 2015
jean-michel A.

Charles,
Just to let you know, the error word cannot be found in the trace file.
As for the AC1000 experiment, it works. I managed to copy all the selected celles into the A3 cell.
At last, for the old frmTimeModel execution, it says :
“Compilation error – project or library not found” and highlights the following line :
Private Sub tvTimeModel_NodeClick(ByVal Node As MSComctlLib.Node

Thanks

Jean-michel

20 08 2015
Charles Hooper

Jean-Michel,
Thank you for the update. The “Compilation error – project or library not found” error message may be very important.

That error seems to mean that the Microsoft Treeview control (that is what permits the indented hierarchy of the Time Model statistics) has never been installed on the computer. Many programs will install that control, and I believe that it is installed by default starting with Windows Vista (it may have also been installed by default in Windows XP). Part 1 of the article series that showed how to build a Time Model viewer in Microsoft Excel contains the following information:

Now the potentially challenging part. We need a Microsoft Windows built-in 32 bit element (control) called TreeView. This 32 bit control is found in the MSCOMCTL.OCX, and on a 32 bit operating system the file should be found in the C:\Windows\System32 folder. On a 64 bit operating system the file should be found in the C:\Windows\SysWOW64 folder. This file might be installed by various installer programs, but can also be downloaded from Microsoft (this link ( http://www.microsoft.com/downloads/en/details.aspx?FamilyId=25437D98-51D0-41C1-BB14-64662F5F62FE&displaylang=en ) might also work, but the file is much older). If you had to download the file, put it in the correct folder location and then “register” the file with Windows, using a command like this (on a 64 bit client computer):

REGSVR32 c:\windows\SysWOW64\MSCOMCTL.OCX 

(Possible bad news, the TreeView control might not work without Visual Basic 5.0 or 6.0 installed, see this article ( http://support.microsoft.com/kb/194751 ) – we might need to simulate the TreeView using an Excel worksheet.)

So, you might need to verify that the MSCOMCTL.OCX file is on your computer, installed in the correct folder (64 bit computers: c:\windows\SysWOW64\MSCOMCTL.OCX , 32 bit computers: c:\windows\System32\MSCOMCTL.OCX ), and that the file has been processed (registered) by REGSVR32. If you do not have that MSCOMCTL.OCX file on your computer, and registered, that *might* be what is causing my program to crash (but that would not explain the crash when sending a report into Excel) – I think that Vista had a problem where the file is not correctly registered.

It really should not be this difficult to use my program – sorry for the problems.
Edit: Note that I added a download link for a recent version of MSCOMCTL.OCX in the article above.

21 08 2015
jean-michel A.

Charles,
The MSCOMCTL.OCX file is located at the right place ( C:\Windows\System32 ) on my windows 7 Pro 32 bit computer.
Through regedit, I found it under HKEY_CLASSES_ROOT\CLSID\{1EFB6596-857C-11D1-B16A-00C0F0283628}\InprocServer32
The ocx product version is 6.1.97.86 and the file version is 6.01.9786
I even registered the ocx again just to see if all worked well. It was ok.

Thank you

Jean-michel

22 08 2015
Charles Hooper

Jean-Michel,
The Hyper-Extended Oracle Performance Monitor should work without issue on Windows 7. I cannot think of any other reason why it crashes when trying to communicate with Microsoft Excel. Could you try testing it with your virus scanner disabled – specifically the part of the virus scanner that checks for unauthorized changes? The Hyper-Extended Oracle Performance Monitor program does not attempt to make any changes to your computer, but some virus scanners detect programs that remotely control other programs (Microsoft Excel) as being bad programs. You should NOT need to disable the virus scanner, but you might try doing that to see if it makes any difference.

For example, I wrote a program that opens the Windows 7 (now Windows 10) Windows Explorer to a particular folder on a NAS that has a large number of security camera videos. Once the Windows Explorer window is open, my program sends a Ctrl Home keyboard command to the Windows Explorer window, and then roughly every 0.8 seconds my program sends a cursor down keyboard command to the Windows Explorer window (actually the program that currently is receiving the keyboard input) roughly 1000 times. The Trend Micro virus scanner detected this activity as being potentially harmful once I upgraded the computer to Windows 10, and promptly deleted my program that opens the Windows Explorer (now called File Explorer) window. The Hyper-Extended Oracle Performance Monitor program does not send keyboard commands to other programs, rather it uses Excel’s published remote macro interface. There is a small chance that the virus scanner on your computer could be trying to prevent this communication.

You might be able to determine if a virus scanner is blocking the communication by using the script that is in the following article. The script displays a SQL statement’s Execution plan in an Internet Explorer window, and optionally sends the data retrieved by that SQL statement into Microsoft Excel using Excel’s published remote macro interface:
https://hoopercharles.wordpress.com/2010/02/11/automated-dbms_xplan-trace-and-send-to-excel/
Below is the direct download for the script. Save it to your computer, then replace the .doc extension with .vbs. You will then need to edit the code to specify your database, username, and password:
https://hoopercharles.files.wordpress.com/2010/02/xplanviewerwithtrace-vbs.doc

1 09 2015
jean-michel A.

Hi Charles,
Sorry for answering that late but I am just back from vacation.
The vbs works perfectly.
I can see the execution plan in IE and the query result in Excel.
Thanks.
Jean-michel

1 09 2015
Charles Hooper

Hi Jean-Michel,
Thank you for testing the VBS script. That is good news that the script worked – that means that the Oracle OLEDB is installed and working correctly, and that the virus scanner did not block Excel from being remotely controlled through its published API. However, now I cannot explain why the Hyper-Extended Oracle Performance Monitor locks up when using a similar technique to enter information into Excel. I wonder if it is a problem with requesting Excel to delete extra worksheets? I might be able to put together a version of the program that does not delete the extra worksheets.

Another thought is that maybe there is a default Excel workbook template or default Excel sheet template that is interfering? What version of Excel are you using (for example 2000, XP (2002), 2003, 2007, 2010, 2013) and what operating system (XP, Vista, Windows 7, Windows 8, Windows 10)?
If you open a new Windows Explorer window and paste the following into the address bar, you should be taken to the location where the Excel 2007 and above templates are stored:

%appdata%\Microsoft\Templates

In the above folder location, do you see any files with the following names:
* Book.xlt
* Book.xltx
* Book.xltm
* Sheet.xlt
* Sheet.xltx
* Sheet.xltm

If you do see one of the above, close Microsoft Excel and rename the file. For example, if you found Book.xlt, rename that file to _Book.xlt; if you found Sheet.xltm, rename it to _Sheet.xltm. By renaming the files, you are able to easily revert back to the previous Excel behavior by setting the name back to the original name.

If you renamed one of the above files, try to use the functionality in the Hyper-Extended Oracle Performance Monitor that previously caused the lock up.

Do you think that an option to turn on and turn off the Excel charts in the Time Model Viewer and the Real-Time Performance functions in the program would help? I might be able to create a new Beta version of the program in a couple of days that adds that setting, if you are interested in testing the functionality.

I normally turn off User Access (Account) Control. That functionality may be enabled on your computer – a may still be a problem. Please open a Windows Explorer window and enter this into the address bar:

%userprofile%\OracleLog

In that location, do you see a folder for the database that you connected to with the Hyper-Extended Oracle Performance Monitor? If so, open that folder. Are there any files inside that folder?

1 09 2015
jean-michel A.

On the other hand, I cannot switch off my F-Secure Client Security.
It has been installed by a specific team responsible for installing and managering every PC through the OCS Inventory tool.

1 09 2015
Charles Hooper

OK, if you are on a computer owned by a company, I recommend leaving the virus scanner enabled.

2 09 2015
jean-michel A.

Hi Charles,
I do not have a folder named “%appdata%\Microsoft\Templates”. I only have a “%appdata%\Microsoft\” folder.
After searching, I have the following folder with the string Templates in it.
C:\Users\jmalzingre\AppData\Roaming\Microsoft\Windows
I have an Excel 2000 (9.0.2812 ) version along with a windows 7 Professional OS.
When doing an Open … under Excel, I do not have any template to choose from.

But I do have a folder named “%userprofile%\OracleLog” : the folder orcl ( my local instance ) as well as another folder ( a distant database I worked with ) are present under OracleLog.
Nothing appears under the orcl folder whereas the Report.txt file appears under the other folder.

I am interested in testing the Beta version of course !

Thank you for your help.

Jean-michel

2 09 2015
Charles Hooper

Excel 2000 is a very old version. That said, it was not very old when I started creating the Hyper-Extended Oracle Performance Monitor program. I am fairly certain that the reports on the program’s main screen would open in Excel 2000, and I do not think that I have significantly changed the program code to alter how those reports are brought into Excel (the Time Model Viewer, of course, did not exist back when I used Excel 2000). I know that the Excel macro language (and therefore the published API) was different in early versions of Excel (the 95 version, for example), but I think that the syntax for Excel 2000 is roughly the same as it is in Excel 2013. I will have to see if I am able to find a computer with Excel 2000 still installed to see if it works with the Hyper-Extended Oracle Performance Monitor program.

In Excel 2000 the startup templates are in an one of two XLSTART folders, according to this document:
https://support.microsoft.com/en-us/kb/240150
Those folder are:
C:\Program Files\Microsoft Office\Office\Xlstart
C:\Documents and Settings\User name\Application Data\Microsoft\Excel\XLSTART

On Windows 7, the second folder is actually:
C:\Users\User name\Application Data\Microsoft\Excel\XLSTART

2 09 2015
jean-michel A.

My mistake : “Open … ” is in fact the “New …” menu choice under Excel.

2 09 2015
jean-michel A.

Charles,

C:\Program Files\Microsoft Office\Office\Xlstart is empty.
C:\Documents and Settings\jmalzingre\Application Data\Microsoft\Excel\XLSTART is empty.
C:\Users\jmalzingre\Application Data\Microsoft\Excel\XLSTART is also empty.

Thx

Jean-michel

2 09 2015
Charles Hooper

Jean-Michel,

OK, thanks. I just completed some testing. I have a computer that is running Windows 10 Pro Insider Preview Build 10532 (Build 10532 is at least two build releases after the “final” version of Windows 10 Pro that was released a month ago). This computer has the Oracle Client 11.2.0.3 installed (not listed as officially compatible with Windows 10, but seems to work OK with the fixes that I published previously). I just installed Excel 2000 on the computer without installing any service packs for Excel 2000 – a couple of minor errors appeared near the end of the install, but I ignored those. If my program will crash, this is the environment where it will happen. The results:
* Create a report showing the Backup Log: Excel spreadsheet is created correctly with only a single tab (my program tells Excel to suppress errors, so that is why no warning appears when the extra worksheet tabs are removed). However, my program crashed and the Excel spreadsheet was not saved. It should have been saved with a name similar to OracleStatus201509021640.xls in the database folder inside the %userprofile%\OracleLog folder.

* Time Model Viewer: Excel shows a message that the sheet will be permanently deleted (my program does NOT tell Excel to suppress errors, so that is why the warning appears – this warning does not appear in Excel 2003 and later). After clicking OK, my program crashes, the Excel worksheet is completely white, and the Excel spreadsheet was not saved (it should have a name such as TimeModelViewer201509021629.xls in the database folder inside the %userprofile%\OracleLog folder).

* Real-Time Performance: Excel behaves as it does with the Time Model Viewer, and the program crashes when trying to save.

The above results appear to be similar to your results. The above tells me that Microsoft must have changed the syntax of the command for saving the spreadsheet between the 2000 and the 2003 releases of Excel. I will use the Macro Recorder in Excel 2000 and 2003 to see if I am able to determine what changed between the versions. I think that we found the problem, so I just need to find a suitable solution and time to implement the solution.

3 09 2015
Charles Hooper

I checked the code created by the Excel macro recorder. The recorded code for a save operation is the same on Excel 2000 and 2003, but is different for Excel 2010. I tried making the program sensitive to the Excel version, issuing different save commands depending on the version of Excel. Excel 2003 does not crash when executing the save command, but Excel 2000 still crashes. I will have to look into this issue some more – as a short-term work around, I will try to have the program NOT save the Excel spreadsheet when Excel 2000 is used. I have some other programs that write to Excel and save – those programs work OK with Excel 2000 and above, but the programs were compiled on an old computer running Windows XP with Excel 2003 installed (and I think that I statically linked to the Excel 2000 published API in those programs, while a more recent Excel API is linked in the Hyper-Extended Oracle Performance Monitor).

4 09 2015
Charles Hooper

Jean-Michel,
Please try the following version of the Hyper-Extended Oracle Performance Monitor (note, change the .doc extension to .zip once downloaded):
https://hoopercharles.files.wordpress.com/2015/08/hyper7b3-zip.doc

The above version of the program does not attempt to save any of the generated Excel spreadsheets on Excel 2000 through Excel 2003. Additionally, I have disabled the Excel application alerts so that you do not receive a warning on Excel 2000 when the extra tabs in the spreadsheet are removed. These changes fixed the crashing problems with the Hyper-Extended Oracle Performance Monitor on my test computer with Windows 10 Pro Insider Preview Build 10532 and Excel 2000 without any service packs installed.

There is still a problem with Excel 2000, at least when no service packs are installed for Excel and it is running on the latest operating system. The copy-paste procedure in Excel 2000 fails when remotely requested through the Excel API. As a result, the Time Model Viewer’s Excel spreadsheet and the Real-Time Performance’s Excel spreadsheet only show the most recent statistics, rather than the 20 minute running history. I am interested to know if you see more than the most recent statistics (please wait a couple of minutes after starting the Time Model Viewer before deciding).

7 09 2015
jean-michel A.

Hi Charles,
I am so sorry : I still have the crash after using either a simple report or the Time Model viewer with the following message :
Nom d’événement de problème: BEX
Nom de l’application: Hyper-Extended Oracle Performance Monitor.exe
Version de l’application: 7.0.0.0
Horodatage de l’application: 55e8cca3
Nom du module par défaut: StackHash_f436
Version du module par défaut: 0.0.0.0
Horodateur du module par défaut: 00000000
Décalage de l’exception: 9b0cbf59
Code de l’exception: c0000005
Données d’exception: 00000008
Version du système: 6.1.7601.2.1.0.256.48
Identificateur de paramètres régionaux: 1036
Information supplémentaire n° 1: f436
Information supplémentaire n° 2: f436db21e647818801ca0ba57bcd565f
Information supplémentaire n° 3: c2b8
Information supplémentaire n° 4: c2b8319a5ea65e074b2873df2e963b14

Do I have to user the GRID32.OCX in a special way ?
Thanks

Jean-michel

7 09 2015
Charles Hooper

Please make certain that you have downloaded the Beta 3 version of the program (WordPress seems to indicate that no one has downloaded Beta 3):
https://hoopercharles.files.wordpress.com/2015/08/hyper7b3-zip.doc

The Beta 3 version will show Hyper-Extended Oracle Performance Monitor 7.0 (Beta 3) in the title bar of the main program window. Beta 3 did not crash when I used it on Windows 10 with Excel 2000.

The GRID32.OCX file is a Microsoft developed control (plugin) that allows the program to display certain information in a table (columns and rows, spreadsheet-like) layout on the screen. The Time Model Viewer uses this control to show the wait event information within the program, but it is also used in other places in the program (the simple reports do not use GRID32.OCX). It should be sufficient to have this control in the same folder as the Hyper-Extended Oracle Performance Monitor’s .EXE file. That said, this file when correctly installed is typically copied to the 32 bit System32 folder (C:\Windows\System32 on a 32 bit computer and C:\Windows\SysWOW64 on a 64 bit computer) and then registered with REGSVR32 on the Windows command line – as far as I am able to see, these steps are unnecessary if the file is in the same folder as the program.

7 09 2015
jean-michel A.

Hi Charles,
I have used the right version, with “beta 3” in the main program window, being connected with the system user.
Thank you.
Jean-michel

7 09 2015
Charles Hooper

Hi Jean-Michel,
When I try Beta 2 of the program on the Windows 10 computer with Excel 2000, I see an error like this in the Windows Event Viewer (Source is listed as “Application Error”):
Faulting application name: Hyper-Extended Oracle Performance Monitor.exe, version: 7.0.0.0, time stamp: 0x55e77aa7
Faulting module name: KERNELBASE.dll, version: 10.0.10532.0, time stamp: 0x55d90844
Exception code: 0xc0000005
Fault offset: 0x000b9eb8
Faulting process id: 0x1ac8
Faulting application start time: 0x01d0e972c7b5f1ad
Faulting application path: C:\Hyper7b2\Hyper-Extended Oracle Performance Monitor.exe
Faulting module path: C:\WINDOWS\SYSTEM32\KERNELBASE.dll
Report Id: daeba597-3479-4081-a041-513b984637ed
Faulting package full name:
Faulting package-relative application ID:

translate.google.com translates your error message as follows:
Problem Event Name: BEX
Application Name: Hyper-Extended Oracle Performance Monitor.exe Application Version: 7.0.0.0 Timestamp app: 55e8cca3
Default Module Name: StackHash_f436 Version of the default module: 0.0.0.0 Time Clock default module: 00000000 Shifting the exception 9b0cbf59
Except code: C0000005
Exception data: 00000008
OS Version: 6.1.7601.2.1.0.256.48
Locale ID: 1036
Additional Information 1: F436
Additional Information 2: f436db21e647818801ca0ba57bcd565f
Additional Information 3: C2B8
Additional Information 4: c2b8319a5ea65e074b2873df2e963b14

As you may see, the error message is different – your error message mentions BEX and StackHash. The following article seems to indicate that errors which mention BEX and StackHash are related to DEP (Data Execution Protection):
http://tdistler.com/2009/04/10/stackhash-and-application-crashes-on-windows

JUst want to report that your solution worked well for me.
I’m testing my old winXP setup on one free amazon AWS t1.micro AMI instance. (I intend to use this a an emergency backup while traveling far away)
Loading Office 97 on winServer 2008 has worked for me before, but in this case, I was getting those StackHash errors everytime I tried to use an Excel sheet with VBA code underneath.
Not having confidence in MS’s attempts at “protection”, I just checked the DoNot UseDEBunlessEssential button. No problem ever since. Too much protection is bad protection””

Here is a Microsoft article about DEP, and how it is possible to turn off DEP for individual applications – I did not find the DoNot UseDEBunlessEssential (Do Not Use DEP Unless Essential?) button in this article:
https://technet.microsoft.com/en-us/library/cc738483%28v=ws.10%29.aspx?f=255&MSPPError=-2147217396

Windows 10, at least the Technical Preview version where I have Excel 2000 installed, requires DEP to be turned on in the computer’s BIOS, otherwise the computer would enter a constant reboot cycle when a new Technical Preview version was installed. So, DEP is enabled in the BIOS, and my program seems to work OK with Excel 2000 on this Windows 10 computer. Some people have indicated that receiving BEX errors could mean that there is spyware installed on the computer, or that a bad driver is installed:
http://answers.microsoft.com/en-us/windows/forum/windows_other-update/bex-probleme/0c2141da-bc94-4891-a7c9-56cf03a06e6f?auth=1

My suggestion is to:
* Check the computer with a program such as MalwareBytes to make certain that there is not spyware on the computer that is not detected by the virus scanner.
* Try to disable DEP either in the computer’s BIOS, or just for specific applications (Excel and the Hyper-Extended Oracle Performance Monitor) – this may be difficult to do on a company owned computer.
* Try replacing the RAM memory in the computer with new memory. The C0000005 exception code with 00000008 means a memory access violation, which is probably caused by DEP, but could also be caused by a failing memory card.

7 09 2015
Charles Hooper

I found a couple of articles that indicate that BEX errors may happen in Excel 2010 and 2013 due to bad Add-ins installed by other programs. In Excel 2000, I think that the Add-ins settings are on the Tools menu:

Another article that I found suggests that having Nero (a CD/DVD burner program) on the computer may cause other programs to crash, and that one of the suggested fixes for those crashes causes Excel macros to crash. In the following article:
http://www.excelforum.com/excel-programming-vba-macros/626936-excel-2007-macros-have-stopped-working.html
A person mentioned that the following command, and then rebooting the computer, fixes the issue with Excel macros:

bcdedit.exe /set {current} nx Optin 

Microsoft’s site states that Optin is the default setting for Windows XP SP2:
“Optin: On systems with processors capable of hardware-enforced DEP, DEP is enabled by default for limited system binaries and applications that opt in. With this option, only Windows system binaries are covered by DEP by default.”

My Windows 7 64 bit computer seems to show that the default value for nx is Optin – is it possible that your system administrator changed that value? From a Windows command line:

C:\>bcdedit.exe /v
 
Windows Boot Manager
--------------------
identifier              {9dea862c-5cdd-4e70-acc1-f32b344d4795}
device                  partition=\Device\HarddiskVolume1
description             Windows Boot Manager
locale                  en-US
inherit                 {7ea2e1ac-2e61-4728-aaa3-896d9d0a9f0e}
default                 {12ad36ae-a4dd-11df-858b-b9caad5866f3}
resumeobject            {12ad36ad-a4dd-11df-858b-b9caad5866f3}
displayorder            {12ad36ae-a4dd-11df-858b-b9caad5866f3}
toolsdisplayorder       {b2721d73-1db4-4c62-bf78-c548a880142d}
timeout                 30
 
Windows Boot Loader
-------------------
identifier              {12ad36ae-a4dd-11df-858b-b9caad5866f3}
device                  partition=C:
path                    \Windows\system32\winload.exe
description             Windows 7
locale                  en-US
inherit                 {6efb52bf-1766-41db-a6b3-0ee5eff72bd7}
recoverysequence        {12ad36af-a4dd-11df-858b-b9caad5866f3}
recoveryenabled         Yes
osdevice                partition=C:
systemroot              \Windows
resumeobject            {12ad36ad-a4dd-11df-858b-b9caad5866f3}
nx                      OptIn
8 09 2015
jean-michel A.

Charles,
Thanks for your last updates.
Here is what I get in the last paragraph of the bcdedit command :
Chargeur de démarrage Windows
—————————–
identificateur {58278ffa-2abe-11e0-a4db-93b2fddbfbb7}
device partition=C:
path \Windows\system32\winload.exe
description Windows 7 Professional (récupéré)
locale fr-FR
recoverysequence {58279001-2abe-11e0-a4db-93b2fddbfbb7}
recoveryenabled Yes
osdevice partition=C:
systemroot \Windows
resumeobject {b930246c-4e24-11e0-a18f-806e6f6e6963}
nx OptOut

I changed the nx value with Optin, rebooted and I now have a different event problem : APPCRASH.

1) simple report with beta 3
Nom d’événement de problème: APPCRASH
Nom de l’application: Hyper-Extended Oracle Performance Monitor.exe
Version de l’application: 7.0.0.0
Horodatage de l’application: 55e8cca3
Nom du module par défaut: StackHash_f436
Version du module par défaut: 0.0.0.0
Horodateur du module par défaut: 00000000
Code de l’exception: c0000005
Décalage de l’exception: 9b0cbf59
Version du système: 6.1.7601.2.1.0.256.48
Identificateur de paramètres régionaux: 1036
Information supplémentaire n° 1: f436
Information supplémentaire n° 2: f436db21e647818801ca0ba57bcd565f
Information supplémentaire n° 3: c2b8
Information supplémentaire n° 4: c2b8319a5ea65e074b2873df2e963b14

2) Time Model Viwer with beta 3 :
Nom d’événement de problème: APPCRASH
Nom de l’application: Hyper-Extended Oracle Performance Monitor.exe
Version de l’application: 7.0.0.0
Horodatage de l’application: 55e8cca3
Nom du module par défaut: StackHash_f436
Version du module par défaut: 0.0.0.0
Horodateur du module par défaut: 00000000
Code de l’exception: c0000005
Décalage de l’exception: 9b0cbf59
Version du système: 6.1.7601.2.1.0.256.48
Identificateur de paramètres régionaux: 1036
Information supplémentaire n° 1: f436
Information supplémentaire n° 2: f436db21e647818801ca0ba57bcd565f
Information supplémentaire n° 3: c2b8
Information supplémentaire n° 4: c2b8319a5ea65e074b2873df2e963b14

In the meantime, I am going to see if I can run MalwareBytes on my PC.

Thanks a lot

Jean-michel

8 09 2015
Charles Hooper

StackHash_f436 is reported in your error log as it was earlier when BEX was also mentioned. Google searches seem to associate the various StackHash errors with a couple of different problems:
* Data Execution Prevention (DEP) – but I do not think that could be a problem with your changed setting with bcdedit.exe – did you restart the computer after you made that change?
* Virus or malware infection.
* One person reported that they stopped seeing StackHash errors when they right-clicked the program and selected Run as Administrator – you should not need to run my program as a local machine administrator.
* A lot of people did not receive a response when asking about StackHash errors.

Good luck with MalwareBytes – when you run the setup for the program, do not select the 30 day free trial for the Pro version of the program.

8 09 2015
Charles Hooper

In Excel 2000, when you click the Tools menu, and then Add-Ins… are there any items in that list that have checkmarks next to the item?

8 09 2015
jean-michel A.

Charles,
I actually restarted my computer after the update of the nx key.
MalwareBytes detected two threats :
…Downloads\01net_Recuva.exe : to recover disappeared files
…Downloads\SoftonicDownloader_pour_docx2rtf.exe
I have deleted those two files.
The error messages are the same when running the HEOM as an administrator …
Thx
Jean-michel

PS : perhaps I should ask for an Excel 2003 version ?

8 09 2015
Charles Hooper

Great that you were able to use MalwareBytes to check the computer. A Google search seems to indicate that the two files are spyware related – they possibly just display pop-up advertisements:
SoftonicDownloader_pour_docx2rtf.exe
PUP.Optional.Softonic.A, TROJ_GEN.F47V0408, Artemis!D48BF7631C38

01net_Recuva.exe
PUP.Optional.Conduit.A, Win32/OpenCandy

It is possible that there are other spyware/viruses on the computer, or possibly some files on the computer is damaged due to a previous infection. MalwareBytes is usually very good at detecting and removing spyware, but it occasionally misses some spyware. TDSSKiller is another good utility for checking for spyware – it has occasionally found problems that were missed by the Trend Micro virus scanner and MalwareBytes. This program is free, and is made available by an antivirus company. If you decide to try the program, click Change Parameters in the program and put a check in the box “Loaded Modules” – TDSSKiller will then need to restart your computer before you are able to instruct it to scan the computer.
http://usa.kaspersky.com/downloads/TDSSKiller

Reinstalling Excel 2000 or installing Excel 2003 might help. However, the access violation errors (related to the StackHash messages and BEX errors) suggest that there may be another problem that will not be solved by just reinstalling Excel. I do not have a good explanation why you are seeing the error messages.

9 09 2015
jean-michel A.

Hi Charles,
TDSSKiller has found no threat event after a reboot.
I will let you know in case I have an idea.
Thx
Jean-michel

17 01 2016
Anjul

Hi Charles,
I am getting error while connecting. Couldn’t understand the error. Please see the screenshot and suggest what could be wrong. Does it generate log file anywhere which i can check?

I have created DSN which is working properly.

18 01 2016
Charles Hooper

You need to make certain that the 32 bit Oracle Provider for OLE DB is installed with the Oracle Client software – there is an apparent bug in the 11.2.0.3 Client that causes the Oracle Provider for OLE DB to not be installed correctly. The problem is explained in the following article, a test .vbs script (LateBinding.vbs) is provided, and a potential solution is provided if you are using the 11.2.0.3 Client:
https://hoopercharles.wordpress.com/2012/11/25/connecting-to-an-oracle-database-with-visual-basic-6-0-on-windows-8-64-bit/

I may be releasing a slightly updated version of the Hyperextended Oracle Performance Monitor soon that fixes an issue with the long cursor numbers found in Oracle Database 11.2.0.2 and later 10046 trace files.

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




%d bloggers like this: