Oracle Database Time Model Viewer in Excel 6

It has been roughly five months since the last installment in this blog article series. Hopefully, several people have found this series helpful and have adapted the solution to better fit their specific needs. By the end of the last article the Excel based application not only displayed time model data at the system-wide and session-level, but also operating system statistics (from V$OSSTAT), system-wide and session level wait events, various other statistics (from V$SYSSTAT), execution plans, and also allowed enabling/disabling 10046 extended SQL traces. A lot of features, but what else may be added to the project?

As of part four of the series, the Excel project should appear similar to the following screen capture (10046 tracing and DBMS XPLAN were added in part five):

You can find the current project, through part five of this series, linked at the bottom of part five’s blog article. Open that project (or your customized version of the project), right-click the Sheet2 worksheet name and select Rename. Change the name to Statistics. Rename the Sheet3 worksheet to Charts by using the same process. Finally, right-click the Wait Events worksheet name and select View Code. Expand the Forms group, right-click the frmTimeModel name, and select View Source. Just before selecting View Source (or View Code), your editor window should appear similar to the following screen capture:

Locate the UserForm_Initialize subroutine (this is the subroutine that connects to the database and prepares to start retrieving statistics from the database). Locate the following code in that subroutine:

'More code will be copied here'''

Just above that section of code, add the following commands which will add column titles to the Statistics worksheet when the UserForm is displayed:

Let’s also change a couple of default settings found on the UserForm and correct the (intentional) spelling error found in the titlebar of the UserForm by using a couple of lines of code directly below the above code:

Now that the code to generate the blank charts has been added to the UserForm, we need code to add the chart data to the Statistics worksheet. If I so chose, rather than adding the data to the Statistics worksheet, I could simply build an array of numbers and use that array as the charts’ source data, however it might at times be helpful to see the raw data that is presented in the chart. Locate the UpdateDisplay subroutine in the UserForm’s code (Public Sub UpdateDisplay). In that subroutine, locate the following line:

Run the frmTimeModel UserForm by pressing the F5 key on the keyboard. If the code is working correctly, the Wait Events worksheet should update just as it had in the past:

You should also find that the Statistics worksheet now shows running delta values of the various statistics, with the most recent delta values on the second row of the worksheet:

One of the advantages of using Excel for the charts is that the charts automatically update as new data is added to the Statistics worksheet. Unfortunately, the data series range for the chart is also auto-modified every time a new row is inserted into the Statistics worksheet, such that the charts never actually show any information. To avoid this situation, the above code does not perform a row insert, rather it copies the existing data and pastes that data one row down in the worksheet.

The generated Charts worksheet should contain four charts, as shown below:

The chart formatting shown above is quite fancy – so fancy that it requires Microsoft Excel 2007 or later. The chart creation code may be altered to create the typical flat single color chart elements found in Excel 2003 and earlier.

Are we done yet? Your Excel worksheet contents are probably flickering quite a bit as additional data is added to the various worksheets. To correct that problem, switch back to the window that allows seeing the source code for the UserForm and again locate the UpdateDisplay subroutine. Locate the following line of code:

On Error Resume Next

Just above that line of code, add the following, which will tell Excel not to try updating the worksheet contents as displayed on screen until ScreenUpdating is re-enabled:

Application.ScreenUpdating = False

Scroll down to the last line of the UpdateDisplay subroutine. Immediately after the last line (intActivated = True), add the following line:

Application.ScreenUpdating = True

——————–

Are we done yet? Part 7 of this blog article series is still a very rough sketch. Any ideas for improvement?

The first two of the above books are very good, and seemed to cover a lot of ground without too much overlap between the topics of the books (the second book targets Excel 2003, but the examples worked fine in the 2007 version of Excel). The third book was a disappointment – written by a couple of authors with just a couple of years experience working with Excel. The third book was very short, and seemed to cover only the basics – items that you could discover yourself just by experimenting with Excel and reading an occasional example found in the Excel programming help manual.

Great. I’ll start with Excel 2010 Power Programming with VBA by John Walkenbach. I have Excel 2010 in Depth by Bill Jelen. 1100+ pages. Very little on VBA. There is a new edition of Professional Excel Development too.

Also, thank you for your Amazon reviews of Oracle books. I print them and keep them with the reviewed books for reference. I study your 2 chapters in Expert Oracle Practices too.

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: