Oracle Database Time Model Viewer in Excel 4

In the previous three parts of this series we started building an Oracle Database time model statistics viewer using nothing more than Microsoft Excel. The end result will hopefully duplicate some of the functionality that is found in a portion of a program that I have been building over the course of the last several years. My program’s window that shows the Oracle Database time model statistics looks like this:

If you have been following along with the previous articles in this series, at this point your Excel UserForm project should look something like the following screen capture:

We still need to add the wait events to the project. While we could display the wait events information in a TreeView control, there would not be much sense to display the wait event information in a grid type control when we are able to just push that information out to a worksheet in the Excel workbook. So that we are able to control which worksheet that information will be push to, we should begin by naming the worksheet. In the main Excel window, right-click Sheet1 and select Rename. Change the name to Wait Events.

Now switch back to the code window for the project (note that you can display the code window by right-click a worksheet tab and selecting View Code). Right-click frmTimeModel (under the Forms heading which is below Microsoft Excel Objects) and select View Object. The Caption property of the UserForm likely still shows UserForm1 – change the Caption property to something that is more meaningful (I will change my Caption to I Can’t Believe I Build an Oracle Database Time Model Viewer in Excel – yes, that typo is intentional). Double-click the background of the UserForm to show the code for the UserForm’s Initialize event. Locate the following text in the Initialize event:

'More code will be copied here ' ' '

Move the cursor just above that text and press the Enter key a couple of times. Add the following code above the ‘More code will be copied here line. Note that the second of the SQL statements uses bind variables:

Scroll up in the code until you locate the ReadData subroutine. Locate the following line at the bottom of that subroutine:

dteLastLoopStart = Now

Move the cursor just above that line and press the Enter key a couple of times. In the blank area that was just created paste the following code which will read in the system-wide wait event statistics and store those statistics in variables:

Note in the above code the snpDataWait.Close command that is below the Loop and above the End If lines. That command closes the snpDataWait recordset, which is necessary to prevent cursor leaks. Review the other code sections that are in the ReadData subroutine and verify that the recordset is always closed between the Loop and End If lines, and if it is not, fix that problem (hint: there is at least one code section that needs to be corrected).

Find the UpdateDisplay subroutine and locate the following line near the start of the subroutine:

On Error Resume Next

Just above that line, press the Enter key a couple of times and add the following variable declaration – this variable will be used to control where the output is placed on the worksheet:

Dim lngRow As Long

Scroll down to the bottom of the UpdateDisplay subroutine and locate the following line:

intCurrentSessionIndex = -1

Move the cursor just above that line, press the Enter key a couple of times. Paste in the following code on the blank lines:

You may have noticed that the chkPauseRefresh CheckBox (with caption Pause Refresh) does not work quite as expected – this is a simple logic problem that needs to be addressed. Find the chkPauseRefresh CheckBox on the UserForm and double-click it to show the code that is behind the CheckBox control. You will see a line of code that looks like this:

If intKillFlag <> False Then

Change the <> characters to an = character so that the line appears as follows:

If intKillFlag = False Then

If you then show the UserForm (switch to the Initialize event of the UserForm and press the F5 key, you may then need to toggle the chkPauseRefresh check box between checked and not checked) you should see something like this after the second 60 second delay:

We still have a slight problem that needs to be addressed. We need another Label control on the UserForm, with the name lblTotalWaitTime. Once you have created that Label control, you can go back to the UpdateDisplay subroutine and remove the single quote (‘) that is in front of the following line:

' lblTotalWaitTime = Format(sglTotalWaitTime / 100, "0.00")

When the single quote is removed, the text color should change from green to black.

What more are we able to do with this project? We could display the execution plans for the captured SQL_ID and CHILD_NUMBER (stored in the strSQLID() array), we could enable/disable 10046 tracing for sessions, we could write out the statistics in real-time to another worksheet and then graph the results, or maybe we could just sit back and stare at the screen in amazement that this project was built in Microsoft Excel😉

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: