August 18, 2011
(Back to the Previous Post in the Series)
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:
'
Sheets("Wait Events").Range("B2").Select
ActiveWindow.FreezePanes = True
Sheets("Statistics").Cells(1, 1).Value = "Time"
Sheets("Statistics").Cells(1, 2).Value = "Administrative"
Sheets("Statistics").Cells(1, 3).Value = "Application"
Sheets("Statistics").Cells(1, 4).Value = "Commit"
Sheets("Statistics").Cells(1, 5).Value = "Concurrency"
Sheets("Statistics").Cells(1, 6).Value = "Configuration"
Sheets("Statistics").Cells(1, 7).Value = "Network"
Sheets("Statistics").Cells(1, 8).Value = "Other"
Sheets("Statistics").Cells(1, 9).Value = "System I/O"
Sheets("Statistics").Cells(1, 10).Value = "User I/O"
Sheets("Statistics").Columns("B:AC").EntireColumn.AutoFit
Sheets("Statistics").Rows("2:10000").Delete Shift:=xlUp
Next, we will programmatically create four charts on the Charts worksheet. Directly below were the above code was copied (above the ‘More code will be copied here line), add the following code:
'Remove existing charts, Add the charts
Sheets("Charts").ChartObjects.Delete
'Note that these chart styles are likely only compatible with Excel 2007 and later
With Sheets("Charts").ChartObjects.Add(10, 10, 400, 200)
.Chart.SetSourceData Source:=Sheets("Statistics").Range("$B$1:$J$21")
.Chart.SeriesCollection(1).XValues = "='Statistics'!$A$2:$A$21"
.Chart.ChartType = xlColumnStacked 'xlAreaStacked
.Chart.HasTitle = True
.Chart.ChartStyle = 42
.Chart.ChartTitle.Text = "Wait Event Classes"
'Rotate the X axis titles
.Chart.Axes(xlCategory).TickLabels.Orientation = xlUpward
'Add the vertical Y axis title
.Chart.Axes(xlValue, xlPrimary).HasTitle = True
.Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Seconds"
'Add a gradient to the background of the chart
.Chart.PlotArea.Fill.OneColorGradient Style:=msoGradientHorizontal, Variant:=2, Degree:=0.756847486076142
.Chart.PlotArea.Fill.ForeColor.SchemeColor = 23
.Chart.PlotArea.Fill.Visible = True
End With
With Sheets("Charts").ChartObjects.Add(410, 10, 400, 200)
.Chart.SetSourceData Source:=Sheets("Statistics").Range("$W$1:$AC$21")
.Chart.SeriesCollection(1).XValues = "='Statistics'!$A$2:$A$21"
.Chart.ChartType = xlColumn 'xlArea
.Chart.HasTitle = True
.Chart.ChartStyle = 42
.Chart.ChartTitle.Text = "DB Time and CPU"
'Rotate the X axis titles
.Chart.Axes(xlCategory).TickLabels.Orientation = xlUpward
'Add the vertical Y axis title
.Chart.Axes(xlValue, xlPrimary).HasTitle = True
.Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Seconds"
'Add a gradient to the background of the chart
.Chart.PlotArea.Fill.OneColorGradient Style:=msoGradientHorizontal, Variant:=2, Degree:=0.756847486076142
.Chart.PlotArea.Fill.ForeColor.SchemeColor = 23
.Chart.PlotArea.Fill.Visible = True
End With
With Sheets("Charts").ChartObjects.Add(10, 215, 400, 200)
.Chart.SetSourceData Source:=Sheets("Statistics").Range("$S$1:$T$21")
.Chart.SeriesCollection(1).XValues = "='Statistics'!$A$2:$A$21"
.Chart.ChartType = xlColumnStacked 'xlAreaStacked
.Chart.HasTitle = True
.Chart.ChartStyle = 42
.Chart.ChartTitle.Text = "Server-Wide CPU Usage"
'Rotate the X axis titles
.Chart.Axes(xlCategory).TickLabels.Orientation = xlUpward
'Add the vertical Y axis title
.Chart.Axes(xlValue, xlPrimary).HasTitle = True
.Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Seconds"
'Add a gradient to the background of the chart
.Chart.PlotArea.Fill.OneColorGradient Style:=msoGradientHorizontal, Variant:=2, Degree:=0.756847486076142
.Chart.PlotArea.Fill.ForeColor.SchemeColor = 23
.Chart.PlotArea.Fill.Visible = True
End With
With Sheets("Charts").ChartObjects.Add(410, 215, 400, 200)
.Chart.SetSourceData Source:=Sheets("Statistics").Range("$O$1:$P$21")
.Chart.SeriesCollection(1).XValues = "='Statistics'!$A$2:$A$21"
.Chart.ChartType = xlAreaStacked100 'xlColumnStacked100 'xlAreaStacked100
.Chart.HasTitle = True
.Chart.ChartStyle = 42
.Chart.ChartTitle.Text = "Server-Wide CPU Utilization"
'Rotate the X axis titles
.Chart.Axes(xlCategory).TickLabels.Orientation = xlUpward
'Add the vertical Y axis title
.Chart.Axes(xlValue, xlPrimary).HasTitle = True
.Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Percent"
'Add a gradient to the background of the chart
.Chart.PlotArea.Fill.OneColorGradient Style:=msoGradientHorizontal, Variant:=2, Degree:=0.756847486076142
.Chart.PlotArea.Fill.ForeColor.SchemeColor = 23
.Chart.PlotArea.Fill.Visible = True
End With
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:
chkPauseRefresh.Value = True chkDisplaySessionDetail.Value = True chkExcludeIdleWaits.Value = True Me.Caption = "Charles Hooper's Time Model Viewer in Microsoft Excel"
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:
tvTimeModel.Nodes.Clear
Just above that line, add the following code:
'Added in Article 6
If dblDBTimeLast > 0 Then
'Note that the first two methods cause the data source range for the charts to shift down 1 row, so copty-paste is used
'Sheets("Statistics").Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
'Range("Statistics!A2:AC1000").Cut Destination:=Range("Statistics!A3:AC1001")
Sheets("Statistics").Range("A2:AC1000").Copy
Sheets("Statistics").Range("A3:AC1001").PasteSpecial xlPasteValuesAndNumberFormats
Sheets("Statistics").Range("A2").Select
Sheets("Statistics").Cells(2, 1) = Format(Now, "hh:nn am/pm")
Sheets("Statistics").Cells(2, 2) = 0
Sheets("Statistics").Cells(2, 3) = 0
Sheets("Statistics").Cells(2, 4) = 0
Sheets("Statistics").Cells(2, 5) = 0
Sheets("Statistics").Cells(2, 6) = 0
Sheets("Statistics").Cells(2, 7) = 0
Sheets("Statistics").Cells(2, 8 ) = 0
Sheets("Statistics").Cells(2, 9) = 0
Sheets("Statistics").Cells(2, 10) = 0
Sheets("Statistics").Cells(2, 15) = Val(lblBusyTime)
Sheets("Statistics").Cells(2, 16) = Val(lblIdleTime)
Sheets("Statistics").Cells(2, 17) = Val(lblBusyPercent)
Sheets("Statistics").Cells(2, 19) = Val(lblUserMode)
Sheets("Statistics").Cells(2, 20) = Val(lblKernelMode)
Sheets("Statistics").Cells(2, 21) = Val(lblUserModePercent)
Sheets("Statistics").Cells(2, 23) = Format((dblDBTime - dblDBTimeLast) / 1000000, "0.00")
Sheets("Statistics").Cells(2, 24) = Format((dblDBCPU - dblDBCPULast) / 1000000, "0.00")
Sheets("Statistics").Cells(2, 25) = Val(lblCPUUsedBySession)
Sheets("Statistics").Cells(2, 26) = Val(lblParseTimeCPU)
Sheets("Statistics").Cells(2, 27) = Val(lblRecursiveCPUUsage)
Sheets("Statistics").Cells(2, 28) = Val(lblOtherCPU)
Sheets("Statistics").Cells(2, 29) = Format((dblBackgroundCPU - dblBackgroundCPULast) / 1000000, "0.00")
Sheets("Statistics").Range("B2:AC2").NumberFormat = "0.00"
End If
Scroll down toward the end of the UpdateDisplay subroutine and locate the following line:
Sheets("Wait Events").Cells(intLastWaitClassRow, 2).Value = Format(sglWaitClassTime / 100, "0.00")
Immediately after the above line, add the following lines of code:
'Added in Article 6
'Add wait events to statistics worksheet
If dblDBTimeLast > 0 Then
Select Case UCase(strLastWaitClass)
Case "ADMINISTRATIVE"
Sheets("Statistics").Cells(2, 2) = Format(sglWaitClassTime / 100, "0.00")
Case "APPLICATION"
Sheets("Statistics").Cells(2, 3) = Format(sglWaitClassTime / 100, "0.00")
Case "COMMIT"
Sheets("Statistics").Cells(2, 4) = Format(sglWaitClassTime / 100, "0.00")
Case "CONCURRENCY"
Sheets("Statistics").Cells(2, 5) = Format(sglWaitClassTime / 100, "0.00")
Case "CONFIGURATION"
Sheets("Statistics").Cells(2, 6) = Format(sglWaitClassTime / 100, "0.00")
Case "NETWORK"
Sheets("Statistics").Cells(2, 7) = Format(sglWaitClassTime / 100, "0.00")
Case "OTHER"
Sheets("Statistics").Cells(2, 8 ) = Format(sglWaitClassTime / 100, "0.00")
Case "SYSTEM I/O"
Sheets("Statistics").Cells(2, 9) = Format(sglWaitClassTime / 100, "0.00")
Case "USER I/O"
Sheets("Statistics").Cells(2, 10) = Format(sglWaitClassTime / 100, "0.00")
End Select
End If
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?
Added August 19, 2015:
The Excel project code to this point, save with a .XLS extension (currently has a .DOC extension, please change):
Charles Hooper's Oracle Notes 
Charles,
Thank you for this series on Excel and Visual Basic. Can you recommend a book on excel / Visual Basic programming? I’ve searched your Amazon reviews and did not find one.
Hi Steve,
Thanks for the comment.
I thought that it might be odd to see a handful of Excel book reviews mixed in with a fairly long series of Oracle Database related books, so I have not formally reviewed any of those books.
In 2009 I purchased 3 books on the topic of Visual Basic programming in Excel.
Excel 2007 Power Programming with VBA
Professional Excel Development: The Definitive Guide
Pro Excel 2007 VBA
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.
I hope what I provided above helps.
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.