Oracle Database Time Model Viewer in Excel 6

18 08 2011

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?


Actions

Information

3 responses

21 01 2012
Steve Gardiner

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.

21 01 2012
Charles Hooper

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.

21 01 2012
Steve Gardiner

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.

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




Follow

Get every new post delivered to your Inbox.

Join 147 other followers

%d bloggers like this: