Send an Email From Excel, Visual Basic 6, or a Windows Command Line Using Oracle’s UTL_MAIL Package

30 11 2012

November 30, 2012

(Back to the Previous Post in the Series)

Today is this blog’s third anniversary, so to celebrate, I thought that I would share a simple code example.  As many regular readers of this blog probably know, Oracle Database 10.1 introduced the UTL_MAIL package, which allowed programs accessing Oracle Database to easily send emails without using the more complex UTL_SMTP package.  Using UTL_MAIL requires that:

  1. The SMTP_OUT_SERVER parameter is set correctly, and potentially the email server is configured to permit receiving SMTP emails from the Oracle Database server.
  2. The utlmail.sql and prvtmail.plb scripts (found in the rdbms/admin directory of the Oracle home) are executed to create the UTL_MAIL package components in the database.
  3. The Oracle user account that will access the UTL_MAIL package’s procedures has sufficient access permissions for the package.

A couple of years ago I wrote an article that showed how to schedule the periodic sending of an email using UTL_MAIL – that article might also be of interest if you find this article helpful.

Let’s take a look at sample code that is compatible with Visual Basic 6 (VB 6) and the scripting language in Excel (the same scripting language is also available in Microsoft Word, Excel, Power Point, Outlook, Access, etc.):

Dim intResult As Integer
Dim strDatabase As String
Dim strUserName As String
Dim strPassword As String
Dim strSQL As String
Dim dbDatabase As ADODB.Connection
Dim comEmail As ADODB.Command

On Error Resume Next

strDatabase = "MyDB" 'From tnsnames.ora
strUserName = "MyUserID"
strPassword = "MyPassword"

Set dbDatabase = New ADODB.Connection

dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUserName & ";Password=" & strPassword & ";"
dbDatabase.Open

If (dbDatabase.State = 1) And (Err = 0) Then
    Set comEmail = New ADODB.Command

    With comEmail
        strSQL = "UTL_MAIL.SEND(" & vbCrLf
        strSQL = strSQL & "  'VisitorRegister@mysite.com'," & vbCrLf
        strSQL = strSQL & "  'MyRecipient1@mysite.com;MyRecipient2@mysite.com'," & vbCrLf
        strSQL = strSQL & "  null," & vbCrLf  'CC
        strSQL = strSQL & "  null," & vbCrLf  'BCC
        strSQL = strSQL & "  '" & strMessageSubject & "'," & vbCrLf
        strSQL = strSQL & "  '" & strMessage & "')"
        .CommandText = strSQL
        .CommandType = adCmdStoredProc                        ' Const adCmdStoredProc = 4
        .ActiveConnection = dbDatabase
    End With

    comEmail.Execute
End If

Set comEmail = Nothing

The sample code looks quite similar to code that has appeared on this site in the past (as such, regular readers will know that MyDB, MyUserID, and MyPassword should be changed to appropriate values for your database), were an ADO Command type object is set up to execute a SQL statement with bind variables.  The difference, however, is that there are no bind variables in the SQL statement, and the CommandType is set to adCmdStoredProc, rather than adCmdText.  Before we are able to use the above code sample, we must first add a reference in the project to the Microsoft ActiveX Data Objects Library:

connect-with-vb-6-references-2

Looks to be very simple, right?  But wait, maybe it would be better that the call to UTL_MAIL use bind variables, rather than literals, to not only save some space in the library cache, but also to make it a bit more difficult for the DBA to read sent emails from Oracle Database’s various V$ views, and to make it easier to include apostrophes (single quotes), line breaks, and other email formatting commands in the email message.

We might try to use something like the following, replacing literals with bind variable placeholders, as a replacement for the above code:

Dim intResult As Integer
Dim strDatabase As String
Dim strUserName As String
Dim strPassword As String
Dim strSQL As String
Dim dbDatabase As ADODB.Connection
Dim comEmail As ADODB.Command

On Error Resume Next

strDatabase = "MyDB" 'From tnsnames.ora
strUserName = "MyUserID"
strPassword = "MyPassword"

Set dbDatabase = New ADODB.Connection

dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUserName & ";Password=" & strPassword & ";"
dbDatabase.Open

If (dbDatabase.State = 1) And (Err = 0) Then
    Set comEmail = New ADODB.Command

    With comEmail
        strSQL = "UTL_MAIL.SEND(" & vbCrLf
        strSQL = strSQL & "  ? ," & vbCrLf
        strSQL = strSQL & "  ? ," & vbCrLf
        strSQL = strSQL & "  ? ," & vbCrLf
        strSQL = strSQL & "  ? ," & vbCrLf
        strSQL = strSQL & "  ? ," & vbCrLf
        strSQL = strSQL & "  ? )"
        .CommandText = strSQL
        .CommandType = adCmdStoredProc ' Const adCmdStoredProc = 4
        .ActiveConnection = dbDatabase

        .Parameters.Append .CreateParameter("sender", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("recipients", adVarChar, adParamInput, 500)
        .Parameters.Append .CreateParameter("cc", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("bcc", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("subject", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("message", adVarChar, adParamInput, 500)
    End With

    comEmail("sender") = "VisitorRegister@mysite.com"
    comEmail("recipients") = "MyRecipient1@mysite.com;MyRecipient2@mysite.com"
    comEmail("subject") = strMessageSubject
    comEmail("message") = strMessage
    comEmail.Execute
End If

Set comEmail = Nothing

Well, that was easy, when executed, the code results in an error message that reads: “Unspecified Error“!  Fine, don’t tell me what is wrong… I will just search the Internet for the answer.

Hey, a site recommended using named variable in the SQL statement, rather than the usual question mark bind placeholders in the SQL statement to be executed, something similar to the following:

Dim intResult As Integer
Dim strDatabase As String
Dim strUserName As String
Dim strPassword As String
Dim strSQL As String
Dim dbDatabase As ADODB.Connection
Dim comEmail As ADODB.Command

On Error Resume Next

strDatabase = "MyDB" 'From tnsnames.ora
strUserName = "MyUserID"
strPassword = "MyPassword"

Set dbDatabase = New ADODB.Connection

dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUserName & ";Password=" & strPassword & ";"
dbDatabase.Open

If (dbDatabase.State = 1) And (Err = 0) Then
    Set comEmail = New ADODB.Command

    With comEmail
        strSQL = "UTL_MAIL.SEND(" & vbCrLf
        strSQL = strSQL & "  :sender ," & vbCrLf
        strSQL = strSQL & "  :recipients ," & vbCrLf
        strSQL = strSQL & "  :cc ," & vbCrLf
        strSQL = strSQL & "  :bcc ," & vbCrLf
        strSQL = strSQL & "  :subject ," & vbCrLf
        strSQL = strSQL & "  :message )"
        .CommandText = strSQL
        .CommandType = adCmdStoredProc ' Const adCmdStoredProc = 4
        .ActiveConnection = dbDatabase

        .Parameters.Append .CreateParameter("sender", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("recipients", adVarChar, adParamInput, 500)
        .Parameters.Append .CreateParameter("cc", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("bcc", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("subject", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("message", adVarChar, adParamInput, 500)
    End With

    comEmail("sender") = "VisitorRegister@mysite.com"
    comEmail("recipients") = "MyRecipient1@mysite.com;MyRecipient2@mysite.com"
    comEmail("subject") = strMessageSubject
    comEmail("message") = strMessage

    comEmail.Execute
End If

Set comEmail = Nothing

Well, that was easy, resulting in an error message that reads: “Unspecified Error! Fine, don’t tell me what is wrong, I will just guess.

Oh, a book recommended putting “Begin ” before the UTL_MAIL in the SQL statement, and “; END;” just after the “)” in the SQL statement.  “Unspecified Error“!

10046 trace at level 12 to see what Oracle Database rejected?  Sorry, no SQL statements that were attempted to be directly executed by the application appeared in the trace file.

Well, obviously it must be possible to execute stored procedures, such as those in the UTL_MAIL package, with bind variables from within Visual Basic 6 or Excel.  Maybe we are just trying too hard?  How about something like this:

Dim intResult As Integer
Dim strDatabase As String
Dim strUserName As String
Dim strPassword As String
Dim strSQL As String
Dim dbDatabase As ADODB.Connection
Dim comEmail As ADODB.Command

On Error Resume Next

strDatabase = "MyDB" 'From tnsnames.ora
strUserName = "MyUserID"
strPassword = "MyPassword"

Set dbDatabase = New ADODB.Connection

dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUserName & ";Password=" & strPassword & ";"
dbDatabase.Open

If (dbDatabase.State = 1) And (Err = 0) Then
    Set comEmail = New ADODB.Command

    With comEmail
        strSQL = "UTL_MAIL.SEND"

        .CommandText = strSQL
        .CommandType = adCmdStoredProc ' Const adCmdStoredProc = 4
        .ActiveConnection = dbDatabase

        .Parameters.Append .CreateParameter("sender", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("recipients", adVarChar, adParamInput, 500)
        .Parameters.Append .CreateParameter("cc", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("bcc", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("subject", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("message", adVarChar, adParamInput, 500)
    End With

    comEmail("sender") = "VisitorRegister@mysite.com"
    comEmail("recipients") = "MyRecipient1@mysite.com;MyRecipient2@mysite.com"
    comEmail("subject") = strMessageSubject
    comEmail("message") = strMessage

    comEmail.Execute
End If

Set comEmail = Nothing

Well that was easy, although it might seem a little confusing not being permitted to specify essentially the same (literal) SQL statement as was used originally, just with bind variable placeholders when calling UTL_MAIL procedures.

The title of this article seems to suggest that we are able to call Oracle’s UTL_MAIL package procedures from the Windows command line – that is almost true.  We need to create a plain text file using Notepad (or a similar tool), and simply make a couple of changes to the above code sample so that variable types are not declared (this code example has not been tested yet):

Dim intResult
Dim strDatabase
Dim strUserName
Dim strPassword
Dim strSQL
Dim dbDatabase
Dim comEmail

Const adCmdStoredProc = 4
Const adVarChar = 200
Const adParamInput = 1

On Error Resume Next

strDatabase = "MyDB" 'From tnsnames.ora
strUserName = "MyUserID"
strPassword = "MyPassword"

Set dbDatabase = CreateObject("ADODB.Connection")

dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUserName & ";Password=" & strPassword & ";"
dbDatabase.Open

If (dbDatabase.State = 1) And (Err = 0) Then
    Set comEmail = CreateObject("ADODB.Command")

    With comEmail
        strSQL = "UTL_MAIL.SEND"

        .CommandText = strSQL
        .CommandType = adCmdStoredProc
        .ActiveConnection = dbDatabase

        .Parameters.Append .CreateParameter("sender", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("recipients", adVarChar, adParamInput, 500)
        .Parameters.Append .CreateParameter("cc", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("bcc", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("subject", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("message", adVarChar, adParamInput, 500)
    End With

    comEmail("sender") = "VisitorRegister@mysite.com"
    comEmail("recipients") = "MyRecipient1@mysite.com;MyRecipient2@mysite.com"
    comEmail("subject") = strMessageSubject
    comEmail("message") = strMessage

    comEmail.Execute
End If

Set comEmail = Nothing

Then, all that we need to do is execute the saved plain text file using either the cscript or wscript command from the Windows command line.

A year older, any wiser?





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?





The New Order Oracle Coding Challenge 3 – Mind Boggle

5 08 2011

August 5, 2011 (Modified August 7, 2011)

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

In part 1 of this series the challenge was to simply reverse the order of digits in the numbers from 1 to 1,000,000 to find that cases where the numbers formed by the reverse ordered digits evenly divided into the original number.  In part 2 of this series the challenge required examining all of the numbers between 1000 and 9999, where arranging the digits of the original number into any of 23 valid combinations resulted in a new number that cleanly divided into the original four digit number.  There were several different solutions provided to the two challenges, so now it is time to move on to part three of the series.

In part 1 of this blog article series I mentioned playing a game years ago that used letters on the face of dice – the dice were rolled, and then the challenge was to find all words that could be completely spelled using the letters on the top of the dice.  I was not very good at the game, so I enlisted the help of a computer.  One such dice game is called Boggle, and that game’s name is probably fitting for today’s challenge.  Imagine that you played this game and the following letters appeared on the top of the dice:

One of the rules of the game requires that words must be at least 3 letters in length, for example: you say melee eye (I) see elfs file some mail (OK, the word I is too short, but we can have some fun with the words that are found).  As you might be able to guess, there are a lot of possible combinations of the 16 letters found on the dice, some of which are valid words.  If we just consider the 5 letter, 4 letter, and 3 letter combinations of the dice, there are more than a half million possible combinations (in the following table, multiply the numbers across and add the results for each row) – no wonder I needed the computer’s help with these puzzles.

16 15 14 13 12   = 16! / 11!
16 15 14 13     = 16! / 12!
16 15 14       = 16! / 13!
            = 571,200

To make the full challenge of finding words a little easier, let’s break the challenge into a couple of parts:

 Part 1: Consider the 2 x 2 letter arrangement at the left.  With the help of Oracle Database, list all of the three letter combinations of those four letters.  There will be 4 * 3 * 2 = 24 possible combinations of the letters.

 

 

 Part 2: Consider the 4 x 4 letter arrangement at the left.  With the help of Oracle Database, list all of the four letter combinations of those 16 letters.  There will be 16 * 15 * 14 * 13 = 43,680 possible combinations of the letters.

-

-

-

-

-

Part 3: Consider the 4 x 4 letter arrangement above.  With the help of Oracle Database, list all of the three, four, five, and six letter combinations of those 16 letters.  If you see any seven letter words in the above set of letters, you might as well retrieve those letter combinations also.  How many letter combinations do you have in total for part 3?

Part 4: Extra Credit: How many of the letter combinations generated in part 3 above are valid U.S. or U.K. English words?  List the words.

Part 5: Extra, Extra Credit: List any words found in the letters at the left that have any connection to Oracle Corporation.  Remember that a letter can only be used as many times in a single word as it appears at the left (if you can form a word with three letter A’s that have a connection to Oracle Corp., go for it.).

-

-

-

-

-

-

-

-

-

-

-

-

-

-

Added August 7, 2011:

When I put together this challenge I did not think that it was possible to complete Part 4 Extra Credit using just SQL.  I was fairly certain that there were some interesting techniques to retrieve HTML content with the help of PL/SQL, but I had not worked out a solution that utilized that technique.  As I write this, Radoslav Golian in the comments section appears to have both a PL/SQL and a SQL solution that uses the dictionary.reference.com website to validate the words (only 6 words to avoid a denial of service type attack on the dictionary.reference.com website).  One of the approaches that I considered, but did not develop, is something similar to how Radoslav verified the words, but I would use a VBS script to submit the request and check the result as is demonstrated in these two articles: Submit Input to an ASP Web Page and Retrieve the Result using VBS and Use VBS to Search for Oracle Books using Google’s Book Library.

The solution that I put together for Part 4 Extra Credit started with an Excel macro that I posted in another blog article, which was then converted to PL/SQL.  I then transformed the PL/SQL for use in this article, and generated a new Excel macro from the PL/SQL code.  The Excel macro (along with the calling code looks like this:

Sub StartBoggle()
    Call Boggle("ESOIMEFOALEUSAYE", 6, 3)
End Sub

Sub Boggle(strCharacters As String, intMaxWordLength As Integer, intMinWordLength As Integer)
    Dim i As Integer
    Dim strCharacter(20) As String
    Dim intCharacterIndex(20) As Integer
    Dim intCharacters As Integer
    Dim intCharactersMax As Integer
    Dim intCharactersMin As Integer
    Dim intNumberOfSuppliedCharacters As Integer
    Dim intAdjustmentPosition As Integer
    Dim intFlag As Integer
    Dim strOutput As String
    Dim strWords(10000) As String
    Dim intWordCount As Integer
    Dim intFilenum As Integer

    intFilenum = FreeFile
    Open "C:\Words " & strCharacters & ".txt" For Output As #intFilenum

    If intMaxWordLength = 0 Then
        intCharactersMax = Len(strCharacters)
    Else
        If intMaxWordLength <= Len(strCharacters) Then
            intCharactersMax = intMaxWordLength
        Else
            intCharactersMax = Len(strCharacters)
        End If
    End If

    If intMinWordLength = 0 Then
        intCharactersMin = 3
    Else
        If intMaxWordLength < intMinWordLength Then
            intCharactersMin = intCharactersMax
        Else
            intCharactersMin = intMinWordLength
        End If
    End If

    intNumberOfSuppliedCharacters = Len(strCharacters)

    For i = 1 To intNumberOfSuppliedCharacters
        strCharacter(i) = Mid(strCharacters, i, 1)
    Next i

    intCharacters = intCharactersMin - 1
    intWordCount = 0

    Do While intCharacters < intCharactersMax
        intCharacters = intCharacters + 1
        intAdjustmentPosition = 1
        For i = 1 To intCharacters
            intCharacterIndex(i) = i
        Next i

        Do While intAdjustmentPosition > 0
            intFlag = 0
            For i = 1 To intAdjustmentPosition - 1
                If intCharacterIndex(i) = intCharacterIndex(intAdjustmentPosition) Then
                    ' Found a duplicate index position in the other values to the left
                    intFlag = 1
                    Exit For
                End If
            Next i

            If intFlag = 1 Then
                ' Try the next index position in this element
                intCharacterIndex(intAdjustmentPosition) = intCharacterIndex(intAdjustmentPosition) + 1
            Else
                If intAdjustmentPosition = intCharacters Then
                    ' Output
                    strOutput = ""
                    For i = 1 To intCharacters
                        strOutput = strOutput & strCharacter(intCharacterIndex(i))
                    Next i

                    intFlag = 0
                    For i = intWordCount To 1 Step -1
                        If strOutput = strWords(i) Then
                            intFlag = 1
                            Exit For
                        End If
                    Next i
                    If intFlag = 0 Then
                        If Application.CheckSpelling(Word:=UCase(strOutput)) <> 0 Then
                            intWordCount = intWordCount + 1
                            strWords(intWordCount) = strOutput

                            Print #intFilenum, strOutput
                            Debug.Print strOutput
                        End If
                    End If

                    If intCharacterIndex(intAdjustmentPosition) = intNumberOfSuppliedCharacters Then
                        ' No more available values in the last position
                        intCharacterIndex(intAdjustmentPosition) = 1
                        intAdjustmentPosition = intAdjustmentPosition - 1
                        If intAdjustmentPosition > 0 Then
                            intCharacterIndex(intAdjustmentPosition) = intCharacterIndex(intAdjustmentPosition) + 1
                        End If
                    Else
                        intCharacterIndex(intAdjustmentPosition) = intCharacterIndex(intAdjustmentPosition) + 1
                    End If
                Else
                    ' No duplicate so prepare to check the next position
                    intAdjustmentPosition = intAdjustmentPosition + 1
                End If
            End If

            Do While (intAdjustmentPosition > 0) And (intCharacterIndex(intAdjustmentPosition) > intNumberOfSuppliedCharacters)
                ' Roll back one index position as many times as necessary
                intCharacterIndex(intAdjustmentPosition) = 1
                intAdjustmentPosition = intAdjustmentPosition - 1
                If intAdjustmentPosition > 0 Then
                    intCharacterIndex(intAdjustmentPosition) = intCharacterIndex(intAdjustmentPosition) + 1
                End If
            Loop ' (intAdjustmentPosition > 0) And
        Loop 'intAdjustmentPosition > 0
    Loop 'intCharacters < intCharactersMax

    Close #intFilenum
End Sub 

The Excel macro builds letter combinations that are between the minimum and maximum length, and then tests those letter combinations using the built-in dictionary that is in Excel.  I had a little bit of difficulty coming up with a way to generate the letter combinations of variable length, so I settled on a custom developed technique – I would simply keep track of the original character positions, manipulate those original character positions, and then output the corresponding characters.  The challenge is then how does one verify that the same character position is not used more than once in a single word? 

 The method that I came up with is as follows, which assumes that we are trying to build four letter words from the supplied 16 letters.  We can start with the seed combination 1,2,3,4.  The idea is to work from left to right, and then back to the left.  Every time to make it to the right, we output a word, when we make it all the way back to the left (just before the number 1 in the above), we are done.  The rules are simple:

  • Increment the number in a position, and if that number does not appear in a position to the left, move one position to the right.
  • When the maximum character number (16 in this example) is exceeded in a position, reset the number to 1, move one position to the left, and increment the value in the new position by 1.
  • In the last position the character number should be incremented as many times as necessary to reach the maximum character number – each time a potential new combination will be generated.

 But there is a problem with this approach – it does not use Oracle Database!

-

Let’s go back to the PL/SQL function from which I created the Excel function (I have not worked much with pipelined functions – so there may be one or two errors):

CREATE OR REPLACE FUNCTION BOGGLE_VAR_LENGTH(strCHARACTERS IN VARCHAR2, intMaxWordLength IN NUMBER, intMinWordLength IN NUMBER) RETURN SYS.AQ$_MIDARRAY PIPELINED
AS
  TYPE NUMBER_ARRAY IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  TYPE CHARACTER_ARRAY IS TABLE OF VARCHAR(1) INDEX BY PLS_INTEGER;
  strCharacter CHARACTER_ARRAY;
  intCharacterIndex NUMBER_ARRAY;
  intCharacters NUMBER;
  intCharactersMax NUMBER;
  intCharactersMin NUMBER;
  intNumberOfSuppliedCharacters NUMBER;
  intAdjustmentPosition NUMBER;
  intFlag NUMBER;
  intI NUMBER;
  strOutput VARCHAR2(100);
BEGIN
  IF intMaxWordLength IS NULL THEN
    intCharactersMax := LENGTH(strCHARACTERS);
  ELSE
    IF intMaxWordLength <= LENGTH(strCHARACTERS) THEN
      intCharactersMax := intMaxWordLength;
    ELSE
      intCharactersMax := LENGTH(strCHARACTERS);
    END IF;
  END IF;

  IF intMinWordLength IS NULL THEN
    intCharactersMin := 3;
  ELSE
    IF intMaxWordLength < intMinWordLength THEN
      intCharactersMin := intCharactersMax;
    ELSE
      intCharactersMin := intMinWordLength;
    END IF;
  END IF;

  intNumberOfSuppliedCharacters := LENGTH(strCHARACTERS);

  FOR I IN 1.. intNumberOfSuppliedCharacters LOOP
    strCharacter(I) := SUBSTR(strCHARACTERS, I, 1);
  END LOOP;

  intCharacters := intCharactersMin - 1;
  WHILE intCharacters < intCharactersMax LOOP
    intCharacters := intCharacters + 1;
    intAdjustmentPosition := 1;
    FOR I IN 1 .. intCharacters LOOP
      intCharacterIndex(I) := I;
    END LOOP;

    WHILE intAdjustmentPosition > 0 LOOP
      intFlag := 0;
      FOR I IN 1 .. intAdjustmentPosition - 1 LOOP
        IF intCharacterIndex(I) = intCharacterIndex(intAdjustmentPosition) Then
          -- Found a duplicate index position in the other values to the left
          intFlag := 1;
        END IF;
      END LOOP;
      IF intFlag = 1 Then
        -- Try the next index position in this element
        intCharacterIndex(intAdjustmentPosition) := intCharacterIndex(intAdjustmentPosition) + 1;
      ELSE
        IF intAdjustmentPosition = intCharacters Then
          -- Output
          strOutput := '';
          FOR i IN 1 .. intCharacters LOOP
            strOutput := strOutput || strCharacter(intCharacterIndex(i));
          END LOOP;

          PIPE ROW (strOutput);

          IF intCharacterIndex(intAdjustmentPosition) = intNumberOfSuppliedCharacters THEN
            -- No more available values in the last position
            intCharacterIndex(intAdjustmentPosition) := 1;
            intAdjustmentPosition := intAdjustmentPosition - 1;
            IF intAdjustmentPosition > 0 THEN
              intCharacterIndex(intAdjustmentPosition) := intCharacterIndex(intAdjustmentPosition) + 1;
            END IF;
          ELSE
            intCharacterIndex(intAdjustmentPosition) := intCharacterIndex(intAdjustmentPosition) + 1;
          END IF;
        ELSE
          -- No duplicate so prepare to check the next position
          intAdjustmentPosition := intAdjustmentPosition + 1;
        END IF;
      END IF;

      WHILE (intAdjustmentPosition > 0) And (intCharacterIndex(intAdjustmentPosition) > intNumberOfSuppliedCharacters) LOOP
        -- Roll back one index position as many times as necessary
        intCharacterIndex(intAdjustmentPosition) := 1;
        intAdjustmentPosition := intAdjustmentPosition - 1;
        IF intAdjustmentPosition > 0 THEN
          intCharacterIndex(intAdjustmentPosition) := intCharacterIndex(intAdjustmentPosition) + 1;
        END IF;
      END LOOP;
    END LOOP;
  END LOOP;
END;
/ 

 We are able to call the function from a SQL statement like this:

SELECT
  *
FROM
  TABLE(BOGGLE_VAR_LENGTH('ESOIMEFOALEUSAYE', 6, 3)); 

Remember that there are more than a half million character combinations for just the 3, 4, and 5 letter combinations – the above will as for 6,336,960 letter combinations to be generated.   But there is a problem with this approach – it does not verify that the letter combinations are actual words!

For fun, let’s see how many possible combinations will result if we allow 3, 4, 5, 6, 7, and 8 letter combinations:

Len                 Combinations  
8 16 15 14 13 12 11 10 9 518,918,400 = 16! / 8!
7 16 15 14 13 12 11 10   57,657,600 = 16! / 9!
6 16 15 14 13 12 11     5,765,760 = 16! / 10!
5 16 15 14 13 12       524,160 = 16! / 11!
4 16 15 14 13         43,680 = 16! / 12!
3 16 15 14           3,360 = 16! / 13!
                  582,912,960 582,912,960

That is more than a half billion combinations!  Warning, significant database server CPU consumption will result when generating all combinations.

Let’s take a look at the final solution that I created for Part 4 Extra, Extra Credit.  The solution is an Excel macro that calls the PL/SQL function through a SQL statement:

Sub StartBoggleOracle()
    Call BoggleOracle("ESOIMEFOALEUSAYE", 8, 3)
End Sub

Sub BoggleOracle(strCharacters As String, intMaxWordLength As Integer, intMinWordLength As Integer)
    Dim strSQL As String
    Dim strUsername As String
    Dim strPassword As String
    Dim strDatabase As String
    Dim intFilenum As Integer

    Dim intCharacters As Integer
    Dim intCharactersMax As Integer
    Dim intCharactersMin As Integer
    Dim strOutput As String

    Dim dbDatabase As ADODB.Connection
    Dim snpData As ADODB.Recordset

    Set dbDatabase = New ADODB.Connection
    Set snpData = New ADODB.Recordset

    strUsername = "MyUsername"
    strPassword = "MyPassword"
    strDatabase = "MyDatabase"

    dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";FetchSize=5000;"
    dbDatabase.Open

    intFilenum = FreeFile
    Open "C:\WordsOracle " & strCharacters & ".txt" For Output As #intFilenum

    If intMaxWordLength = 0 Then
        intCharactersMax = Len(strCharacters)
    Else
        If intMaxWordLength <= Len(strCharacters) Then
            intCharactersMax = intMaxWordLength
        Else
            intCharactersMax = Len(strCharacters)
        End If
    End If

    If intMinWordLength = 0 Then
        intCharactersMin = 3
    Else
        If intMaxWordLength < intMinWordLength Then
            intCharactersMin = intCharactersMax
        Else
            intCharactersMin = intMinWordLength
        End If
    End If

    strSQL = "SELECT DISTINCT" & vbCrLf
    strSQL = strSQL & "  *" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  (SELECT" & vbCrLf
    strSQL = strSQL & "    *" & vbCrLf
    strSQL = strSQL & "  FROM" & vbCrLf
    strSQL = strSQL & "    TABLE(BOGGLE_VAR_LENGTH('" & strCharacters & "', " & Format(intCharactersMax) & ", " & Format(intCharactersMin) & ")))" & vbCrLf
    strSQL = strSQL & "ORDER BY" & vbCrLf
    strSQL = strSQL & "  1"
    snpData.Open strSQL, dbDatabase

    If snpData.State = 1 Then
        Do While Not snpData.EOF
            strOutput = snpData(0)
            If Application.CheckSpelling(Word:=UCase(strOutput)) <> 0 Then
                Print #intFilenum, strOutput
                Debug.Print strOutput
            End If

            snpData.MoveNext
        Loop

        snpData.Close
    End If

    Close #intFilenum
    dbDatabase.Close
    Set snpData = Nothing
    Set dbDatabase = Nothing
End Sub

The words found appear to depend on the version of Excel – Excel 2010 seems to find more words than Excel 2007.

  • The 799 word list from Excel 2007 for word lengths between 3 and 8 characters, including the timing information to show when the SQL statement was submitted, when the first 5,000 combinations were retrieved from the database, and when the Excel spell check finished.  Words Oracle_ESOIMEFOALEUSAYE.txt
  • The 2,179 word list from Excel 2007 for word lengths between 3 and 8 characters, including the timing information to show when the SQL statement was submitted, when the first 5,000 combinations were retrieved from the database, and when the Excel spell check finished. Words Oracle_OSERIEFAARLNCAYL.txt

Excel found Ellison in the second word list.  For Part 5 Extra, Extra Credit, what other words connected to Oracle Corporation were found?





Oracle Database Time Model Viewer in Excel 5

16 03 2011

March 16, 2011 (Updated August 11, 2011)

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

In the previous articles in this series we looked at ways to analyze the Oracle time model data at the system-wide level with drill-down into the session level detail, with cross references to a handful of statistics found in V$OSSTAT and V$SYSSTAT, and the system-wide wait events with drill-down into the session level wait event detail.  There is a chance that some of the statistics might appear to be inconsistent between the various performance views, or possibly even between the start of the retrieval of the rows from a single performance view and the retrieval of the final row from the query of that performance view.  You might even find in some cases, with some combinations of operating system platform and Oracle release version that the statistics in V$OSSTAT might not be in the unit of measure described in the Oracle Database documentation – a recent thread on the OTN forums contains an example of such a case (I completely overlooked the inconsistency that was pointed out in that thread).  Is there a reason for the inconsistency?  A quick browse through Metalink (MOS) finds the following articles:

Bug 7430365: INCORRECT VALUES FOR USER_TIME IN V$OSSTAT (3.79 hours per CPU per elapsed hour)
Bug 3574504: INCORRECT STATISTICS IN V$OSSTAT IN HP-UX
Bug 5933195: NUM_CPUS VALUE IN V$OSSTAT IS WRONG
Bug 5639749: CPU_COUNT NOT SHOWN PROPERLY FROM THE DATABASE
Bug 10427553: HOW DOES V$OSSTAT GET IT’S INFORMATION ON AIX
Bug 9228541: CPU TIME REPORTED INCORRECTLY IN V$SYSMETRIC_HISTORY (3.75 hours per CPU per elapsed hour)
Doc ID 889396.1: Very large value for OS_CPU_WAIT_TIME FROM V$OSSTAT / AWR Report
Bug 7447648: OS_CPU_WAIT_TIME VALUE FROM V$OSSTAT IS INCORRECT

At the end of the previous article, we had produced a demonstration project that generated screen output similar to the following (note that the project code as of the end of part four in the series may be downloaded by using the link at the end of the fourth article):

Let’s continue adding features to the project.  We will start by adding three new CommandButtons to the UserForm with the (Name) property set to the following (one name per CommandButton): cmdTraceSession, cmdStopTrace, and cmdShowExecutionPlan.  Assign useful titles to the CommandButtons by setting appropriate values for the Caption property – due to limited available space on the UserForm I selected the Captions: Trace, Stop Trace, and XPLAN.  The UserForm in my sample project currently appears as shown in the following screen capture:

Next, we need to add a little more code to the Initialize event of the UserForm to add the SQL statements that will allow the CommandButtons to function.  Double-click in the background area of the UserForm to display the Initialize event code for the UserForm.

Scroll down through the code until you find the following comment section:

    'More code will be copied here
    '
    '
    '

Add a couple of blank lines above that comment section and paste in the following code:

    Set snpSQLStats = New ADODB.Recordset
    Set comSQLStats = New ADODB.Command

    With comTrace
        strSQL = "DBMS_MONITOR.SESSION_TRACE_ENABLE(?,?,TRUE,TRUE)"

        .CommandText = strSQL
        .CommandType = adCmdStoredProc
        .ActiveConnection = dbDatabase
    End With

    With comXPLAN
        strSQL = "SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR( ?, ?, 'TYPICAL +PEEKED_BINDS'))"

        'Add the bind variables
        .Parameters.Append .CreateParameter("sqlid", adVarChar, adParamInput, 40, "")
        .Parameters.Append .CreateParameter("childnumber", adNumeric, adParamInput, 8, 0)

        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30
        .ActiveConnection = dbDatabase
    End With 

    With comSQLChildReason
        strSQL = "SELECT" & vbCrLf
        strSQL = strSQL & "  SSC.*" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  V$SQL_SHARED_CURSOR SSC" & vbCrLf
        strSQL = strSQL & "WHERE" & vbCrLf
        strSQL = strSQL & "  SSC.SQL_ID= ?" & vbCrLf
        strSQL = strSQL & "  AND SSC.CHILD_NUMBER= ?" & vbCrLf

        'Set up the command properties
        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30

        .ActiveConnection = dbDatabase

        'Add the bind variables
        .Parameters.Append .CreateParameter("sqlid", adVarChar, adParamInput, 40, "")
        .Parameters.Append .CreateParameter("childnumber", adNumeric, adParamInput, 8, 0)
    End With

    With comSQLChildBind
        strSQL = "SELECT" & vbCrLf
        strSQL = strSQL & "  SBM.POSITION," & vbCrLf
        strSQL = strSQL & "  SBM.DATATYPE," & vbCrLf
        strSQL = strSQL & "  SBM.MAX_LENGTH," & vbCrLf
        strSQL = strSQL & "  SBM.BIND_NAME" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  V$SQL S," & vbCrLf
        strSQL = strSQL & "  V$SQL_BIND_METADATA SBM" & vbCrLf
        strSQL = strSQL & "WHERE" & vbCrLf
        strSQL = strSQL & "  S.SQL_ID= ?" & vbCrLf
        strSQL = strSQL & "  AND S.CHILD_NUMBER= ?" & vbCrLf
        strSQL = strSQL & "  AND S.CHILD_ADDRESS=SBM.ADDRESS" & vbCrLf
        strSQL = strSQL & "ORDER BY" & vbCrLf
        strSQL = strSQL & "  SBM.POSITION" & vbCrLf

        'Set up the command properties
        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30

        .ActiveConnection = dbDatabase

        'Add the bind variables
        .Parameters.Append .CreateParameter("sqlid", adVarChar, adParamInput, 40, "")
        .Parameters.Append .CreateParameter("childnumber", adNumeric, adParamInput, 8, 0)
    End With

    Set snpSQLStats = New ADODB.Recordset
    Set comSQLStats = New ADODB.Command

    With comSQLStats
        strSQL = "SELECT" & vbCrLf
        strSQL = strSQL & "  LAST_ACTIVE_TIME," & vbCrLf
        strSQL = strSQL & "  PARSE_CALLS," & vbCrLf
        strSQL = strSQL & "  EXECUTIONS," & vbCrLf
        strSQL = strSQL & "  CPU_TIME," & vbCrLf
        strSQL = strSQL & "  ELAPSED_TIME," & vbCrLf
        strSQL = strSQL & "  APPLICATION_WAIT_TIME," & vbCrLf
        strSQL = strSQL & "  CONCURRENCY_WAIT_TIME," & vbCrLf
        strSQL = strSQL & "  CLUSTER_WAIT_TIME," & vbCrLf
        strSQL = strSQL & "  USER_IO_WAIT_TIME," & vbCrLf
        strSQL = strSQL & "  PLSQL_EXEC_TIME," & vbCrLf
        strSQL = strSQL & "  JAVA_EXEC_TIME," & vbCrLf
        strSQL = strSQL & "  BUFFER_GETS," & vbCrLf
        strSQL = strSQL & "  DISK_READS," & vbCrLf
        strSQL = strSQL & "  DIRECT_WRITES," & vbCrLf
        strSQL = strSQL & "  ROWS_PROCESSED," & vbCrLf
        strSQL = strSQL & "  FETCHES," & vbCrLf
        strSQL = strSQL & "  LOADS," & vbCrLf
        strSQL = strSQL & "  INVALIDATIONS" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  V$SQLSTATS" & vbCrLf
        strSQL = strSQL & "WHERE" & vbCrLf
        strSQL = strSQL & "  SQL_ID= ?" & vbCrLf
        strSQL = strSQL & "  AND PLAN_HASH_VALUE= ?"
        'Set up the command properties
        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30

        .ActiveConnection = dbDatabase

        'Add the bind variables
        .Parameters.Append .CreateParameter("sqlid", adVarChar, adParamInput, 40, "")
        .Parameters.Append .CreateParameter("plan_hash_value", adNumeric, adParamInput, 8, 0)
    End With

In the above, you might have noticed that I neglected to set up the two bind variables for the comTrace object (an object of type ADODB.Command), while I did set up the two bind variables for the comXPLAN object.  The reason for this omission is that in previous testing I simply could not make the SQL code execute with bind variables, so I cheated a bit, and will simply replace the CommandText property value for the comTrace object with hardcoded values for the session’s SID and SERIAL# when the cmdTraceSession CommandButton is clicked.

View the UserForm again (find frmTimeModel under the Forms heading, right-click the UserForm’s name, and select View Object).  Then double-click the cmdTraceSession CommandButton to show the Click event for that object.  Change the Click event so that it includes the following code:

Private Sub cmdTraceSession_Click()
    Dim i As Integer
    Dim intSessionTrace As Integer
    Dim strSQL As String

    If intCurrentSessionIndex > -1 Then
        strSQL = "DBMS_MONITOR.SESSION_TRACE_ENABLE(" & Format(lngSID(intCurrentSessionIndex)) & "," & Format(lngSerial(intCurrentSessionIndex)) & " ,TRUE,TRUE)"
        comTrace.CommandText = strSQL
        comTrace.Execute
    End If

    'Remember that we enabled trace for this session
    intSessionTrace = 0
    For i = 1 To 1000
        If SessionTrace(i).lngSID = lngSID(intCurrentSessionIndex) Then
            intSessionTrace = i
            Exit For
        End If
    Next i
    If intSessionTrace = 0 Then
        'Find an unused entry
        For i = 1 To 1000
            If SessionTrace(i).lngSID = 0 Then
                intSessionTrace = i
                SessionTrace(i).lngSID = lngSID(intCurrentSessionIndex)
                SessionTrace(i).lngSerial = lngSerial(intCurrentSessionIndex)
                Exit For
            End If
        Next i
    End If
    SessionTrace(intSessionTrace).int10046Level = 12
    cmdStopTrace.Enabled = True
    cmdTraceSession.Enabled = False
    tvTimeModel.SetFocus 
End Sub

If you examine the code, you will see that we use the intCurrentSessionIndex variable’s value to know which session is the session of interest, and then set the SessionTrace object’s int10046Level property to 12 to indicate that we have enabled a level 12 trace for the session (this allows us to remember which sessions are being traced).  But, we have a couple of problems: the intCurrentSessionIndex variable has a default value of 0 and so far we have not added code to assign a value to that variable; secondly, the SessionTrace object simply does not exist.  Adding the SessionTrace object is easy, so we will start there.  Scroll all the way up to the top of the project’s code and add the following code above everything else:

Private Type TraceDefinition
    lngSID As Long
    lngSerial As Long
    int10046Level As Integer
    int10053Level As Integer
    int10032Level As Integer
    int10033Level As Integer
    int10104Level As Integer
End Type

Dim SessionTrace(1000) As TraceDefinition

The first of the above sections creates the definition of the object type TraceDefinition which is simply composed of seven variables.

While in that section of the project code, add a couple of more variable declarations that will be used later (first scroll down to where you see similar variable declarations):

Dim snpSQLStats As ADODB.Recordset          'ADO recordset object used to retrieve the statistics for the execution plan
Dim comSQLStats As ADODB.Command            'ADO command object used to retrieve the statistics for the execution plan

The second section (beginning with the word Dim) creates an array of 1,000 (actually 1,001) objects of type TraceDefinition.

Now for the hard part – how do we know which session is the current session – the one that is currently highlighted in the TreeView control?  The key to this is in how we named the TreeView rows (by setting the Key property) as the rows were added in the UpdateDisplay subroutine.  Each row that contains session-level detail has a Key property that begins with the value SESSION, so we are able to easily determine when a session-level detail row is clicked.  Also part of the Key property is an underscore ( _ ) character that acts as a field delimiter between the rest of the name and a sequential number that points to additional information about that session.  View the UserForm object again and double-click the TreeView control.  Switch to the NodeClick event for the TreeView control (in the drop-down list at the right of the code window) and change the NodeClick event’s code to show the following:

Private Sub tvTimeModel_NodeClick(ByVal Node As MSComctlLib.Node)
    Dim i As Integer
    Dim intSessionTrace As Integer

    If Left(Node.Key, 7) = "SESSION" Then
        intCurrentSessionIndex = Right(Node.Key, Len(Node.Key) - InStr(Node.Key, "_"))

        intSessionTrace = 0
        For i = 1 To 1000
            If (SessionTrace(i).lngSID = lngSID(intCurrentSessionIndex)) And (SessionTrace(i).lngSerial = lngSerial(intCurrentSessionIndex)) Then
                intSessionTrace = i
                Exit For
            End If
        Next i
        If intSessionTrace > 0 Then
            If SessionTrace(intSessionTrace).int10046Level > 0 Then
                cmdTraceSession.Enabled = False
                cmdStopTrace.Enabled = True
            Else
                cmdTraceSession.Enabled = True
                cmdStopTrace.Enabled = False
            End If
        Else
            cmdTraceSession.Enabled = True
            cmdStopTrace.Enabled = False
        End If
        If strSQLID(intCurrentSessionIndex) <> "" Then
            cmdShowExecutionPlan.Enabled = True
        Else
            cmdShowExecutionPlan.Enabled = False
        End If
    Else
        intCurrentSessionIndex = -1
        cmdTraceSession.Enabled = False
        cmdStopTrace.Enabled = False
        cmdShowExecutionPlan.Enabled = False
    End If
End Sub

Easy to understand so far?  Let’s add the code to the cmdStopTrace CommandButton.  View the UserForm and then double-click the cmdStopTrace CommandButton.  Change the Click event’s code to show the following:

Private Sub cmdStopTrace_Click()
    Dim i As Integer
    Dim intSessionTrace As Integer
    Dim strSQL As String

    If intCurrentSessionIndex > -1 Then
        strSQL = "DBMS_MONITOR.SESSION_TRACE_DISABLE(" & Format(lngSID(intCurrentSessionIndex)) & "," & Format(lngSerial(intCurrentSessionIndex)) & ")"
        comTrace.CommandText = strSQL
        comTrace.Execute

        'Remember that we disabled trace for this session
        intSessionTrace = 0
        For i = 1 To 1000
            If SessionTrace(i).lngSID = lngSID(intCurrentSessionIndex) Then
                intSessionTrace = i
                Exit For
            End If
        Next i
        If intSessionTrace <> 0 Then
            SessionTrace(intSessionTrace).int10046Level = 0
            If (SessionTrace(intSessionTrace).int10032Level = 0) _
              And (SessionTrace(intSessionTrace).int10033Level = 0) _
              And (SessionTrace(intSessionTrace).int10046Level = 0) _
              And (SessionTrace(intSessionTrace).int10053Level = 0) _
              And (SessionTrace(intSessionTrace).int10104Level = 0) Then
                'Forget this trace entry
                SessionTrace(intSessionTrace).lngSID = 0
                SessionTrace(intSessionTrace).lngSerial = 0
            End If
        End If
        cmdStopTrace.Enabled = False
        cmdTraceSession.Enabled = True
    End If
    tvTimeModel.SetFocus
End Sub

You might notice that the code to stop the trace for a session is very similar to the code to start the trace.

The code for the cmdShowExecutionPlan CommandButton could be quite tame, as it was in the original Time Model Viewer project, but that would be a bit boring.  Let’s do something a little special.  First, view the UserForm object and then double-click the cmdShowExecutionPlan CommandButton.  Change the Click event for the CommandButton to show the following:

Private Sub cmdShowExecutionPlan_Click()
    Dim strOut As String
    Dim strSQLSQLID As String
    Dim intSQLChildNumber As Integer
    Dim intFileNum As Integer
    Dim intFlag As Integer
    Dim j As Integer

    On Error Resume Next

    tvTimeModel.SetFocus
    If intCurrentSessionIndex > -1 Then
        strSQLSQLID = Left(strSQLID(intCurrentSessionIndex), InStr(strSQLID(intCurrentSessionIndex), "/") - 1)
        intSQLChildNumber = Val(Right(strSQLID(intCurrentSessionIndex), Len(strSQLID(intCurrentSessionIndex)) - InStr(strSQLID(intCurrentSessionIndex), "/")))

        comXPLAN("sqlid") = strSQLSQLID
        comXPLAN("childnumber") = Null
        'If you only want the plan for the current SQL statement, uncomment the following
        'comXPLAN("childnumber") = intSQLChildNumber

        Set snpXPLAN = comXPLAN.Execute
        If Not (snpXPLAN Is Nothing) Then
            If snpXPLAN.State = 1 Then
                'Create the directory if it does not already exist
                If Len(Dir("C:\ExcelTimeModelViewer", vbDirectory)) < 5 Then
                    MkDir "C:\ExcelTimeModelViewer"
                End If

                intFileNum = FreeFile
                Open "C:\ExcelTimeModelViewer\DBMS_XPLAN.txt" For Output As #intFileNum

                If snpXPLAN.EOF = True Then
                    strOut = "No Execution Plans for SQL ID " & strSQLSQLID
                Else
                    strOut = ""
                End If
                Do While Not snpXPLAN.EOF
                    If (Left(snpXPLAN(0), 7) = "SQL_ID ") And (InStr(LCase(snpXPLAN(0)), "child number ") > 1) And (InStr(LCase(snpXPLAN(0)), "cannot be found") = 0) Then
                        If intFlag = True Then
                            strOut = strOut & String(100, "~") & vbCrLf
                            strOut = strOut & "" & vbCrLf
                            intFlag = False
                        End If
                        strOut = strOut & String(100, "*") & vbCrLf
                        comSQLChildReason("sqlid") = strSQLSQLID
                        comSQLChildReason("childnumber") = CInt(Right(snpXPLAN(0), Len(snpXPLAN(0)) - (InStr(snpXPLAN(0), "child number ") + 12)))
                        Set snpSQLChildReason = comSQLChildReason.Execute

                        If Not (snpSQLChildReason Is Nothing) Then
                            If snpSQLChildReason.State = 1 Then
                                If Not (snpSQLChildReason.EOF) Then
                                    strOut = strOut & "Reason for Child Cursor Creation" & vbCrLf
                                    For j = 4 To snpSQLChildReason.Fields.Count - 1
                                        If snpSQLChildReason(j) = "Y" Then
                                            strOut = strOut & snpSQLChildReason.Fields(j).Name & "" & vbCrLf
                                        End If
                                    Next j
                                    strOut = strOut & "**********" & vbCrLf
                                End If
                                snpSQLChildReason.Close
                            End If
                        End If

                        comSQLChildBind("sqlid") = strSQLSQLID
                        comSQLChildBind("childnumber") = CInt(Right(snpXPLAN(0), Len(snpXPLAN(0)) - (InStr(snpXPLAN(0), "child number ") + 12)))
                        Set snpSQLChildBind = comSQLChildBind.Execute

                        If Not (snpSQLChildBind Is Nothing) Then
                            If snpSQLChildBind.State = 1 Then
                                If Not (snpSQLChildBind.EOF) Then
                                    strOut = strOut & "Bind Variable Definitions" & vbCrLf
                                    Do While Not (snpSQLChildBind.EOF)
                                        strOut = strOut & "  Position:" & CStr(snpSQLChildBind("position"))
                                        strOut = strOut & "  Max Length:" & CStr(snpSQLChildBind("max_length"))
                                        Select Case snpSQLChildBind("datatype")
                                          Case 1
                                              strOut = strOut & "  VARCHAR2"
                                          Case 2
                                              strOut = strOut & "  NUMBER"
                                          Case 8
                                              strOut = strOut & "  LONG"
                                          Case 11
                                              strOut = strOut & "  ROWID"
                                          Case 12
                                              strOut = strOut & "  DATE"
                                          Case 23
                                              strOut = strOut & "  RAW"
                                          Case 24
                                              strOut = strOut & "  LONG RAW"
                                          Case 96
                                              strOut = strOut & "  CHAR"
                                          Case 112
                                              strOut = strOut & "  CLOB"
                                          Case 113
                                              strOut = strOut & "  BLOB"
                                          Case 114
                                              strOut = strOut & "  BFILE"
                                          Case Else
                                              strOut = strOut & "  TYPE " & CStr(snpSQLChildBind("datatype"))
                                        End Select
                                        strOut = strOut & "  Name:" & snpSQLChildBind("bind_name")
                                        strOut = strOut & "" & vbCrLf

                                        snpSQLChildBind.MoveNext
                                    Loop
                                    strOut = strOut & "**********" & vbCrLf
                                End If
                                snpSQLChildBind.Close
                            End If
                        End If
                    End If

                    If InStr(UCase(snpXPLAN(0)), "PLAN HASH VALUE:") = 1 Then
                        'Found the PLAN_HASH_VALUE
                        comSQLStats("sqlid") = strSQLSQLID
                        comSQLStats("plan_hash_value") = Val(Right(snpXPLAN(0), Len(snpXPLAN(0)) - InStr(snpXPLAN(0), ":")))
                        Set snpSQLStats = comSQLStats.Execute

                        If Not (snpSQLStats Is Nothing) Then
                            If snpSQLStats.State = 1 Then
                                If Not (snpSQLStats.EOF) Then
                                    If snpSQLStats("executions") > 0 Then
                                        strOut = strOut & "" & vbCrLf
                                        strOut = strOut & "Statistics for Execution Plan:" & vbCrLf
                                        strOut = strOut & "  Last Active: " & snpSQLStats("last_active_time") & vbCrLf
                                        strOut = strOut & "  Parses:                " & Format(Format(snpSQLStats("parse_calls"), "#,##0   "), "@@@@@@@@@@@@@@") & vbCrLf
                                        strOut = strOut & "  Executions:            " & Format(Format(snpSQLStats("executions"), "#,##0   "), "@@@@@@@@@@@@@@") _
                                            & "    Exec Per Parse: " & Format(Format(snpSQLStats("executions") / snpSQLStats("parse_calls"), "#,##0.00"), "@@@@@@@@@@@@@@") & vbCrLf
                                        strOut = strOut & "  CPU Time:              " & Format(Format(snpSQLStats("cpu_time") / 1000000, "#,##0.00"), "@@@@@@@@@@@@@@") _
                                            & "    Per Exec:       " & Format(Format(snpSQLStats("cpu_time") / snpSQLStats("executions") / 1000000, "#,##0.00"), "@@@@@@@@@@@@@@") & vbCrLf
                                        strOut = strOut & "  Elapsed Time:          " & Format(Format(snpSQLStats("elapsed_time") / 1000000, "#,##0.00"), "@@@@@@@@@@@@@@") _
                                            & "    Per Exec:       " & Format(Format(snpSQLStats("elapsed_time") / snpSQLStats("executions") / 1000000, "#,##0.00"), "@@@@@@@@@@@@@@") & vbCrLf
                                        strOut = strOut & "  Application Wait Time: " & Format(Format(snpSQLStats("application_wait_time") / 1000000, "#,##0.00"), "@@@@@@@@@@@@@@") _
                                            & "    Per Exec:       " & Format(Format(snpSQLStats("application_wait_time") / snpSQLStats("executions") / 1000000, "#,##0.00"), "@@@@@@@@@@@@@@") & vbCrLf
                                        strOut = strOut & "  Concurrency Wait Time: " & Format(Format(snpSQLStats("concurrency_wait_time") / 1000000, "#,##0.00"), "@@@@@@@@@@@@@@") _
                                            & "    Per Exec:       " & Format(Format(snpSQLStats("concurrency_wait_time") / snpSQLStats("executions") / 1000000, "#,##0.00"), "@@@@@@@@@@@@@@") & vbCrLf
                                        strOut = strOut & "  User IO Wait Time:     " & Format(Format(snpSQLStats("user_io_wait_time") / 1000000, "#,##0.00"), "@@@@@@@@@@@@@@") _
                                            & "    Per Exec:       " & Format(Format(snpSQLStats("user_io_wait_time") / snpSQLStats("executions") / 1000000, "#,##0.00"), "@@@@@@@@@@@@@@") & vbCrLf
                                        strOut = strOut & "  Application Wait Time: " & Format(Format(snpSQLStats("application_wait_time") / 1000000, "#,##0.00"), "@@@@@@@@@@@@@@") _
                                            & "    Per Exec:       " & Format(Format(snpSQLStats("application_wait_time") / snpSQLStats("executions"), "#,##0.00"), "@@@@@@@@@@@@@@") & vbCrLf
                                        strOut = strOut & "  Cluster Wait Time:     " & Format(Format(snpSQLStats("cluster_wait_time") / 1000000, "#,##0.00"), "@@@@@@@@@@@@@@") _
                                            & "    Per Exec:       " & Format(Format(snpSQLStats("cluster_wait_time") / snpSQLStats("executions"), "#,##0.00"), "@@@@@@@@@@@@@@") & vbCrLf
                                        strOut = strOut & "  PL/SQL Execute Time:   " & Format(Format(snpSQLStats("plsql_exec_time") / 1000000, "#,##0.00"), "@@@@@@@@@@@@@@") _
                                            & "    Per Exec:       " & Format(Format(snpSQLStats("plsql_exec_time") / snpSQLStats("executions") / 1000000, "#,##0.00"), "@@@@@@@@@@@@@@") & vbCrLf
                                        strOut = strOut & "  Java Execution Time:   " & Format(Format(snpSQLStats("java_exec_time") / 1000000, "#,##0.00"), "@@@@@@@@@@@@@@") _
                                            & "    Per Exec:       " & Format(Format(snpSQLStats("java_exec_time") / snpSQLStats("executions") / 1000000, "#,##0.00"), "@@@@@@@@@@@@@@") & vbCrLf
                                        strOut = strOut & "  Buffer (Cons.) Gets:   " & Format(Format(snpSQLStats("buffer_gets"), "#,##0   "), "@@@@@@@@@@@@@@") _
                                            & "    Per Exec:       " & Format(Format(snpSQLStats("buffer_gets") / snpSQLStats("executions"), "#,##0.00"), "@@@@@@@@@@@@@@") & vbCrLf
                                        strOut = strOut & "  Disk (Block) Reads:    " & Format(Format(snpSQLStats("disk_reads"), "#,##0   "), "@@@@@@@@@@@@@@") _
                                            & "    Per Exec:       " & Format(Format(snpSQLStats("disk_reads") / snpSQLStats("executions"), "#,##0.00"), "@@@@@@@@@@@@@@") & vbCrLf
                                        strOut = strOut & "  Direct Writes:         " & Format(Format(snpSQLStats("direct_writes"), "#,##0   "), "@@@@@@@@@@@@@@") _
                                            & "    Per Exec:       " & Format(Format(snpSQLStats("direct_writes") / snpSQLStats("executions"), "#,##0.00"), "@@@@@@@@@@@@@@") & vbCrLf
                                        strOut = strOut & "  Rows Processed:        " & Format(Format(snpSQLStats("rows_processed"), "#,##0   "), "@@@@@@@@@@@@@@") _
                                            & "    Per Exec:       " & Format(Format(snpSQLStats("rows_processed") / snpSQLStats("executions"), "#,##0.00"), "@@@@@@@@@@@@@@") & vbCrLf
                                        strOut = strOut & "  Fetches:               " & Format(Format(snpSQLStats("fetches"), "#,##0   "), "@@@@@@@@@@@@@@") _
                                            & "    Per Exec:       " & Format(Format(snpSQLStats("fetches") / snpSQLStats("executions"), "#,##0.00"), "@@@@@@@@@@@@@@") & vbCrLf
                                        strOut = strOut & "  Loads:                 " & Format(Format(snpSQLStats("loads"), "#,##0   "), "@@@@@@@@@@@@@@") _
                                            & "    Per Exec:       " & Format(Format(snpSQLStats("loads") / snpSQLStats("executions"), "#,##0.00"), "@@@@@@@@@@@@@@") & vbCrLf
                                        strOut = strOut & "  Invalidations:         " & Format(Format(snpSQLStats("invalidations"), "#,##0   "), "@@@@@@@@@@@@@@") _
                                            & "    Per Exec:       " & Format(Format(snpSQLStats("invalidations") / snpSQLStats("executions"), "#,##0.00"), "@@@@@@@@@@@@@@") & vbCrLf

                                        strOut = strOut & "" & vbCrLf
                                    End If
                                End If
                                snpSQLStats.Close
                            End If
                        End If
                    End If

                    If (InStr(snpXPLAN(0), "SQL_ID") > 0) And (InStr(snpXPLAN(0), "child number " & Format(intSQLChildNumber)) > 0) Then
                        intFlag = True
                        strOut = strOut & "Plan Used by the Session" & vbCrLf
                        strOut = strOut & String(100, "~") & vbCrLf
                        strOut = strOut & snpXPLAN(0) & vbCrLf
                        strOut = strOut & String(100, "~") & vbCrLf
                    Else
                        strOut = strOut & snpXPLAN(0) & vbCrLf
                    End If

                    snpXPLAN.MoveNext
                Loop
                snpXPLAN.Close
                If intFlag = True Then
                    strOut = strOut & String(100, "~") & vbCrLf
                End If
                strOut = strOut & vbCrLf
            End If
        End If

        Print #intFileNum, strOut
        Close #intFileNum
        Shell "notepad.exe C:\ExcelTimeModelViewer\DBMS_XPLAN.txt", vbNormalFocus
    End If
End Sub

Note that in the above code, if you are running Windows Vista or Window 7 with User Access Control enabled, you will need to replace all instances of C:\ExcelTimeModelViewer in the code with a writeable file location.

Press the F5 key to start up the execution of the UserForm.  You might need to toggle the chkPauseFresh CheckBox to allow the UserForm data to begin updating.  Just as an experiment, I decided to take a look at the execution plan for the SQL_ID that was identified for my Excel session.  The following is the output that was displayed on screen (in the Notepad application window):

****************************************************************************************************
Reason for Child Cursor Creation
**********
Plan Used by the Session
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID  6uw0vzxdsd8f8, child number 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-------------------------------------
SELECT    S.SID,    S.SERIAL#,    S.STATUS,    NVL(S.USERNAME,' ')
USERNAME,    NVL(S.MACHINE,' ') MACHINE,    NVL(S.PROGRAM,' ') PROGRAM,
   NVL(S.SQL_ID,NVL(S.PREV_SQL_ID,' ')) SQL_ID,   
NVL(S.SQL_CHILD_NUMBER,NVL(S.PREV_CHILD_NUMBER,0)) SQL_CHILD_NUMBER,   
STM.VALUE,    STM.STAT_NAME  FROM    V$SESS_TIME_MODEL STM,   
V$SESSION S  WHERE    S.SID=STM.SID  ORDER BY    S.USERNAME,   
S.PROGRAM,    S.SID

Statistics for Execution Plan:
  Last Active: 3/15/2011 11:12:31 PM
  Parses:                         14  
  Executions:                     18       Exec Per Parse:           1.29
  CPU Time:                        0.06    Per Exec:                 0.00
  Elapsed Time:                    0.08    Per Exec:                 0.00
  Application Wait Time:           0.00    Per Exec:                 0.00
  Concurrency Wait Time:           0.00    Per Exec:                 0.00
  User IO Wait Time:               0.01    Per Exec:                 0.00
  Application Wait Time:           0.00    Per Exec:                 0.00
  Cluster Wait Time:               0.00    Per Exec:                 0.00
  PL/SQL Execute Time:             0.00    Per Exec:                 0.00
  Java Execution Time:             0.00    Per Exec:                 0.00
  Buffer (Cons.) Gets:           201       Per Exec:                11.17
  Disk (Block) Reads:              4       Per Exec:                 0.22
  Direct Writes:                   0       Per Exec:                 0.00
  Rows Processed:              8,170       Per Exec:               453.89
  Fetches:                        90       Per Exec:                 5.00
  Loads:                           2       Per Exec:                 0.11
  Invalidations:                   0       Per Exec:                 0.00

Plan hash value: 186343697

------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |       |       |     2 (100)|          |        |
|   1 |  SORT ORDER BY               |                    |   273 | 27846 |     2 (100)| 00:00:01 |        |
|*  2 |   HASH JOIN                  |                    |   273 | 27846 |     1 (100)| 00:00:01 |        |
|*  3 |    FIXED TABLE FULL          | X$KEWSSMAP         |    13 |   455 |     0   (0)|          |  OR112 |
|   4 |    NESTED LOOPS              |                    |  1604 |   104K|     0   (0)|          |        |
|   5 |     NESTED LOOPS             |                    |    24 |  1224 |     0   (0)|          |        |
|   6 |      NESTED LOOPS            |                    |    24 |   288 |     0   (0)|          |        |
|   7 |       FIXED TABLE FULL       | X$KSLWT            |    24 |   192 |     0   (0)|          |  OR112 |
|*  8 |       FIXED TABLE FIXED INDEX| X$KSLED (ind:2)    |     1 |     4 |     0   (0)|          |  OR112 |
|*  9 |      FIXED TABLE FIXED INDEX | X$KSUSE (ind:1)    |     1 |    39 |     0   (0)|          |  OR112 |
|* 10 |     FIXED TABLE FIXED INDEX  | X$KEWSSESV (ind:1) |    67 |  1072 |     0   (0)|          |  OR112 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("MAP"."SOFFST"="SESV"."KEWSNUM")
   3 - filter(("MAP"."AGGID"=1 AND INTERNAL_FUNCTION("MAP"."STYPE") AND
              "MAP"."INST_ID"=USERENV('INSTANCE')))
   8 - filter("W"."KSLWTEVT"="E"."INDX")
   9 - filter((BITAND("S"."KSUSEFLG",1)<>0 AND BITAND("S"."KSSPAFLG",1)<>0 AND
              "S"."INST_ID"=USERENV('INSTANCE') AND "S"."INDX"="W"."KSLWTSID"))
  10 - filter((BITAND("SESV"."KSUSEFLG",1)<>0 AND BITAND("SESV"."KSSPAFLG",1)<>0 AND
              "S"."INDX"="SESV"."KSUSENUM"))

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

****************************************************************************************************
Reason for Child Cursor Creation
STATS_ROW_MISMATCH
**********
SQL_ID  6uw0vzxdsd8f8, child number 1
-------------------------------------
SELECT    S.SID,    S.SERIAL#,    S.STATUS,    NVL(S.USERNAME,' ')
USERNAME,    NVL(S.MACHINE,' ') MACHINE,    NVL(S.PROGRAM,' ') PROGRAM,
   NVL(S.SQL_ID,NVL(S.PREV_SQL_ID,' ')) SQL_ID,   
NVL(S.SQL_CHILD_NUMBER,NVL(S.PREV_CHILD_NUMBER,0)) SQL_CHILD_NUMBER,   
STM.VALUE,    STM.STAT_NAME  FROM    V$SESS_TIME_MODEL STM,   
V$SESSION S  WHERE    S.SID=STM.SID  ORDER BY    S.USERNAME,   
S.PROGRAM,    S.SID

Statistics for Execution Plan:
  Last Active: 3/15/2011 11:12:31 PM
  Parses:                         14  
  Executions:                     18       Exec Per Parse:           1.29
  CPU Time:                        0.06    Per Exec:                 0.00
  Elapsed Time:                    0.08    Per Exec:                 0.00
  Application Wait Time:           0.00    Per Exec:                 0.00
  Concurrency Wait Time:           0.00    Per Exec:                 0.00
  User IO Wait Time:               0.01    Per Exec:                 0.00
  Application Wait Time:           0.00    Per Exec:                 0.00
  Cluster Wait Time:               0.00    Per Exec:                 0.00
  PL/SQL Execute Time:             0.00    Per Exec:                 0.00
  Java Execution Time:             0.00    Per Exec:                 0.00
  Buffer (Cons.) Gets:           201       Per Exec:                11.17
  Disk (Block) Reads:              4       Per Exec:                 0.22
  Direct Writes:                   0       Per Exec:                 0.00
  Rows Processed:              8,170       Per Exec:               453.89
  Fetches:                        90       Per Exec:                 5.00
  Loads:                           2       Per Exec:                 0.11
  Invalidations:                   0       Per Exec:                 0.00

Plan hash value: 186343697

------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |       |       |     2 (100)|          |        |
|   1 |  SORT ORDER BY               |                    |   273 | 27846 |     2 (100)| 00:00:01 |        |
|*  2 |   HASH JOIN                  |                    |   273 | 27846 |     1 (100)| 00:00:01 |        |
|*  3 |    FIXED TABLE FULL          | X$KEWSSMAP         |    13 |   455 |     0   (0)|          |  OR112 |
|   4 |    NESTED LOOPS              |                    |  1604 |   104K|     0   (0)|          |        |
|   5 |     NESTED LOOPS             |                    |    24 |  1224 |     0   (0)|          |        |
|   6 |      NESTED LOOPS            |                    |    24 |   288 |     0   (0)|          |        |
|   7 |       FIXED TABLE FULL       | X$KSLWT            |    24 |   192 |     0   (0)|          |  OR112 |
|*  8 |       FIXED TABLE FIXED INDEX| X$KSLED (ind:2)    |     1 |     4 |     0   (0)|          |  OR112 |
|*  9 |      FIXED TABLE FIXED INDEX | X$KSUSE (ind:1)    |     1 |    39 |     0   (0)|          |  OR112 |
|* 10 |     FIXED TABLE FIXED INDEX  | X$KEWSSESV (ind:1) |    67 |  1072 |     0   (0)|          |  OR112 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("MAP"."SOFFST"="SESV"."KEWSNUM")
   3 - filter(("MAP"."AGGID"=1 AND INTERNAL_FUNCTION("MAP"."STYPE") AND
              "MAP"."INST_ID"=USERENV('INSTANCE')))
   8 - filter("W"."KSLWTEVT"="E"."INDX")
   9 - filter((BITAND("S"."KSUSEFLG",1)<>0 AND BITAND("S"."KSSPAFLG",1)<>0 AND
              "S"."INST_ID"=USERENV('INSTANCE') AND "S"."INDX"="W"."KSLWTSID"))
  10 - filter((BITAND("SESV"."KSUSEFLG",1)<>0 AND BITAND("SESV"."KSSPAFLG",1)<>0 AND
              "S"."INDX"="SESV"."KSUSENUM"))

If you examine the above, you will notice that the output from V$SQLSTATS is identical for both of the child cursors – that is because the PLAN_HASH_VALUE is identical for both of the child cursors.

Where do we head next with the project?  I am a person who prefers looking at raw numbers, but maybe someone reading this blog prefers pictures?

Added August 11, 2011:

The Excel project code to this point, save with a .XLS extension (currently has a .DOC extension, please change):
TimeModelViewerExcelArticle5.xls





Oracle Database Time Model Viewer in Excel 4

6 03 2011

March 6, 2011 (Updated March 15, 2011)

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

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:

    'Added in Article 4
    With comDataWait
        strSQL = "SELECT" & vbCrLf
        strSQL = strSQL & "  WAIT_CLASS," & vbCrLf
        strSQL = strSQL & "  EVENT," & vbCrLf
        strSQL = strSQL & "  TOTAL_WAITS," & vbCrLf
        strSQL = strSQL & "  TOTAL_TIMEOUTS," & vbCrLf
        strSQL = strSQL & "  TIME_WAITED" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  V$SYSTEM_EVENT" & vbCrLf
        strSQL = strSQL & "ORDER BY" & vbCrLf
        strSQL = strSQL & "  WAIT_CLASS," & vbCrLf
        strSQL = strSQL & "  EVENT"

        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30
        .ActiveConnection = dbDatabase
    End With

    With comSessionWait
        strSQL = "SELECT" & vbCrLf
        strSQL = strSQL & "  S.SID," & vbCrLf
        strSQL = strSQL & "  S.SERIAL#," & vbCrLf
        strSQL = strSQL & "  S.STATUS," & vbCrLf
        strSQL = strSQL & "  NVL(S.USERNAME,' ') USERNAME," & vbCrLf
        strSQL = strSQL & "  NVL(S.MACHINE,' ') MACHINE," & vbCrLf
        strSQL = strSQL & "  NVL(S.PROGRAM,' ') PROGRAM," & vbCrLf
        strSQL = strSQL & "  NVL(S.SQL_ID,NVL(S.PREV_SQL_ID,' ')) SQL_ID," & vbCrLf
        strSQL = strSQL & "  NVL(S.SQL_CHILD_NUMBER,NVL(S.PREV_CHILD_NUMBER,0)) SQL_CHILD_NUMBER," & vbCrLf
        strSQL = strSQL & "  SE.EVENT," & vbCrLf
        strSQL = strSQL & "  SE.TOTAL_WAITS," & vbCrLf
        strSQL = strSQL & "  SE.TOTAL_TIMEOUTS," & vbCrLf
        strSQL = strSQL & "  SE.TIME_WAITED" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  V$SESSION_EVENT SE," & vbCrLf
        strSQL = strSQL & "  V$SESSION S" & vbCrLf
        strSQL = strSQL & "WHERE" & vbCrLf
        strSQL = strSQL & "  SE.EVENT = ?" & vbCrLf
        strSQL = strSQL & "  AND SE.SID=S.SID" & vbCrLf
        strSQL = strSQL & "ORDER BY" & vbCrLf
        strSQL = strSQL & "  S.SID"

        .CommandText = strSQL
        .CommandType = adCmdText
        .ActiveConnection = dbDatabase

        'Add the bind variables
        .Parameters.Append .CreateParameter("event", adVarChar, adParamInput, 40, "")
    End With 

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:

    'Added in Article 4
    Set snpDataWait = comDataWait.Execute
    If Not (snpDataWait Is Nothing) Then
        Do While Not (snpDataWait.EOF)
            intWaitCurrent = intWaitCount + 1
            'Find the previous entry for this wait event
            For j = 1 To intWaitCount
                If strWaitEventName(j) = CStr(snpDataWait("event")) Then
                    intWaitCurrent = j
                    Exit For
                End If
            Next j
            If intWaitCurrent = intWaitCount + 1 Then
                'New entry
                intWaitCount = intWaitCount + 1
                strWaitEventName(intWaitCurrent) = CStr(snpDataWait("event"))
                strWaitEventClass(intWaitCurrent) = snpDataWait("wait_class")
            End If
            dblWaitValueLast(intWaitCurrent) = dblWaitValue(intWaitCurrent)
            dblWaitValue(intWaitCurrent) = CDbl(snpDataWait("time_waited"))
            dblWaitWaitsValueLast(intWaitCurrent) = dblWaitWaitsValue(intWaitCurrent)
            dblWaitWaitsValue(intWaitCurrent) = CDbl(snpDataWait("total_waits"))
            dblWaitTOValueLast(intWaitCurrent) = dblWaitTOValue(intWaitCurrent)
            dblWaitTOValue(intWaitCurrent) = CDbl(snpDataWait("total_timeouts"))

            snpDataWait.MoveNext
        Loop
        snpDataWait.Close
    End If 

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:

    'Added in Article 4
    lngRow = 1
    Sheets("Wait Events").Rows("1:10000").Delete Shift:=xlUp
    Sheets("Wait Events").Cells(lngRow, 1).Value = "Wait Event Name"
    Sheets("Wait Events").Cells(lngRow, 2).Value = "Wait Time"
    Sheets("Wait Events").Cells(lngRow, 3).Value = "Waits"
    Sheets("Wait Events").Cells(lngRow, 4).Value = "Timeouts"
    Sheets("Wait Events").Cells(lngRow, 5).Value = "Session Description"

    For j = 1 To intWaitCount
        If (strWaitEventClass(j) <> "Idle") Or (intExcludeIdleWaits = False) Then
            If strLastWaitClass <> strWaitEventClass(j) Then
                If strLastWaitClass <> "" Then
                    'Do not write out on the first pass
                    Sheets("Wait Events").Cells(intLastWaitClassRow, 2).Value = Format(sglWaitClassTime / 100, "0.00")
                End If

                strLastWaitClass = strWaitEventClass(j)
                lngRow = lngRow + 1
                Sheets("Wait Events").Cells(lngRow, 1).Value = strLastWaitClass & " Wait Class"
                Sheets("Wait Events").Cells(lngRow, 1).Font.Bold = True
                Sheets("Wait Events").Cells(lngRow, 2).Font.Bold = True
                intLastWaitClassRow = lngRow
                sglWaitClassTime = 0
            End If

            If (dblWaitValue(j) - dblWaitValueLast(j)) <> 0 Then
                sglWaitClassTime = sglWaitClassTime + (dblWaitValue(j) - dblWaitValueLast(j))

                If strWaitEventClass(j) <> "Idle" Then
                    sglTotalWaitTime = sglTotalWaitTime + (dblWaitValue(j) - dblWaitValueLast(j))
                End If

                lngRow = lngRow + 1
                Sheets("Wait Events").Cells(lngRow, 1).Value = "-- " & strWaitEventName(j)
                Sheets("Wait Events").Cells(lngRow, 2).Value = Format((dblWaitValue(j) - dblWaitValueLast(j)) / 100, "0.00")
                Sheets("Wait Events").Cells(lngRow, 3).Value = Format((dblWaitWaitsValue(j) - dblWaitWaitsValueLast(j)), "0")
                Sheets("Wait Events").Cells(lngRow, 4).Value = Format((dblWaitTOValue(j) - dblWaitTOValueLast(j)), "0")
                Sheets("Wait Events").Cells(lngRow, 5).Value = ""

                If (intDisplaySessionDetail = True) Then
                    comSessionWait("event") = strWaitEventName(j)
                    Set snpSessionWait = comSessionWait.Execute

                    If Not (snpSessionWait Is Nothing) Then
                        If snpSessionWait.State = 1 Then
                            Do While Not (snpSessionWait.EOF)
                                intSessionCurrent = 0
                                For k = 1 To intSessionCount
                                    If (lngSID(k) = CLng(snpSessionWait("sid"))) And (lngSerial(k) = CLng(snpSessionWait("serial#"))) Then
                                        intSessionCurrent = k

                                        'Output the session wait information
                                        If (snpSessionWait("time_waited") - dblSessionWaitValue(j, intSessionCurrent) > 0) Then
                                            'Output only if time change is more that the sglSessionMinimumPercent value
                                            If ((CDbl(snpSessionWait("time_waited")) - dblSessionWaitValue(j, intSessionCurrent)) > 0) And (CDbl(snpSessionWait("time_waited")) - dblSessionWaitValue(j, intSessionCurrent)) / (dblWaitValue(j) - dblWaitValueLast(j)) >= sglSessionMinimumPercent Then
                                                lngRow = lngRow + 1
                                                Sheets("Wait Events").Cells(lngRow, 1).Value = "---- session level wait " & Format((CDbl(snpSessionWait("time_waited")) - dblSessionWaitValue(j, intSessionCurrent)) / (dblWaitValue(j) - dblWaitValueLast(j)), "0.00%") & " of system wait event"
                                                Sheets("Wait Events").Cells(lngRow, 2).Value = Format((CDbl(snpSessionWait("time_waited")) - dblSessionWaitValue(j, intSessionCurrent)) / 100, "0.00")
                                                Sheets("Wait Events").Cells(lngRow, 3).Value = Format((CDbl(snpSessionWait("total_waits")) - dblSessionWaitWaitsValue(j, intSessionCurrent)), "0")
                                                Sheets("Wait Events").Cells(lngRow, 4).Value = Format((CDbl(snpSessionWait("total_timeouts")) - dblSessionWaitTOValue(j, intSessionCurrent)), "0")

                                                strOut = "SID:" & Format(snpSessionWait("sid")) & " SERIAL#:" & Format(snpSessionWait("serial#"))
                                                strOut = strOut & " ~ Machine: " & snpSessionWait("machine") & " ~ " & snpSessionWait("username") & " ~ " & snpSessionWait("program")
                                                If snpSessionWait("sql_id") <> " " Then
                                                    strOut = strOut & " ~ SQL_ID/Child: " & snpSessionWait("sql_id") & "/" & Format(snpSessionWait("sql_child_number"), "0") & " "
                                                    If UCase(snpSessionWait("status")) = "ACTIVE" Then
                                                        strOut = strOut & " (A)"
                                                    End If
                                                End If
                                                Sheets("Wait Events").Cells(lngRow, 5).Value = strOut
                                            End If
                                        End If
                                        dblSessionWaitValue(j, intSessionCurrent) = CDbl(snpSessionWait("time_waited"))
                                        dblSessionWaitWaitsValue(j, intSessionCurrent) = CDbl(snpSessionWait("total_waits"))
                                        dblSessionWaitTOValue(j, intSessionCurrent) = CDbl(snpSessionWait("total_timeouts"))

                                        Exit For
                                    End If
                                Next k

                                snpSessionWait.MoveNext
                            Loop
                            snpSessionWait.Close
                        End If
                    End If
                End If
            End If
        End If
    Next j

    If strLastWaitClass <> "" Then
        Sheets("Wait Events").Cells(intLastWaitClassRow, 2).Value = Format(sglWaitClassTime / 100, "0.00")
    End If

    Sheets("Wait Events").Columns("A:A").EntireColumn.AutoFit
    Sheets("Wait Events").Columns("B:B").EntireColumn.AutoFit
    Sheets("Wait Events").Columns("C:C").EntireColumn.AutoFit
    Sheets("Wait Events").Columns("D:D").EntireColumn.AutoFit
    Sheets("Wait Events").Columns("E:E").EntireColumn.AutoFit
    Sheets("Wait Events").Columns("B:B").NumberFormat = "0.00"
    Sheets("Wait Events").Range("B2").Select
    ActiveWindow.FreezePanes = True 

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

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 ;-)

—-

Added March 15, 2011:

The Excel project code to this point, save with a .XLS extension (currently has a .DOC extension, please change):
timemodelviewerexcelarticle4xls





Oracle Database Time Model Viewer in Excel 3

3 03 2011

March 3, 2011 (Updated March 5, 2011)

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

In the previous articles of this series we started building a solution in Microsoft Excel that duplicates some of the functionality in one of my programs – the Oracle Database Time Model viewer.  So far, the project only works at just the system-wide level.  Hopefully, not too many people are having difficulty following along with this article series – it has been more than a decade since I professionally taught programming and Microsoft Excel, so I might be overlooking a couple of “obviously” difficult points. 

If you have not done so yet, you may need to change Excel’s default behavior when an error occurs in the code, since some errors are expected and should be handled appropriately within our programming code.  To check the error trapping setting in the Excel code editor window, select from the menu ToolsOptions.  On the General tab, find and select the setting Break on Unhandled Errors, then click the OK button.  If you plan to do much programming in Excel, I also suggest setting the Require Variable Declaration option on the Editor tab – setting that option helps to avoid some forms of typing errors (all used variables must be officially declared before use).

If you find that the macro seems to pause unexpectedly when first started, there is a simple solution for that issue, and we will apply the simple solution later in this article.

We will start by adding a couple of more controls to the UserForm to add a little more functionality to the programming code.  In my sample project I will be adding the extra controls at the top of the UserForm.  We need to add three CheckBox controls to the UserForm with the (Name) property set to: chkPauseRefresh, chkDisplaySessionDetail, and chkExcludeIdleWaits (one name for each CheckBox).  Change the Caption property of those checkboxes to describe the function of the CheckBoxes (I used: Pause Refresh, Show Session Detail, and No Idle Waits).  Add two ComboBox controls to the UserForm with the (Name) property set to: cboUpdateFrequency and cboSessionMinimumPercent.  Set the Text property of the cboUpdateFrequency ComboBox to 60.  Set the Text property of the cboSessionMinimumPercent ComboBox to 10.  Add a Label control near each of the ComboBox controls, and change the Caption property of the Label controls to describe the purpose of the ComboBox controls (I used: Update Freq (S) and Min Utilization to Inc. Session %).  Your UserForm should look something like this when finished with the above instructions:

The extra controls at this point do nothing, other than occupy space on the UserForm, so we need to add functionality to the extra controls.  Double-click the chkPauseRefresh CheckBox to show the default code event for the CheckBox – the “Click” event.  The TimerEvent subroutine that we modified in the previous article is set to abort re-executing the TimerEvent subroutine any time the intKillFlag variable is set to something other than False (a value of 0).  So, the code for the chkPauseRefresh CheckBox’s Click event will simply toggle this intKillFlag variable between the values of True and False – if the value becomes True we need to restart the re-execution of the TimerEvent procedure.  The easiest way to accomplish this task is with the following code in the chkPauseRefresh CheckBox’s Click event (note that in Visual Basic versions 4.0 through 6.0 this same code will toggle the intKillFlag variable between the values of 0 and 1, but the code as written will behave the same way.  Value is the default property of a CheckBox control, so technically we could have omitted the .Value portion of the code):

Private Sub chkPauseRefresh_Click()
    intKillFlag = chkPauseRefresh.Value

    If intKillFlag <> False Then
        TimerEvent
    End If
End Sub 

The chkDisplaySessionDetail CheckBox technically does not require any special programming code in its Click event, so we will come back to this program functionality later.

The Click event for the chkExcludeIdleWaits CheckBox is similar to that of the same event for the chkPauseRefresh CheckBox.  On the UserForm, double-click the chkExcludeIdleWaits CheckBox (or simply select that name from the left ComboBox (drop-down list) in the code editor).  Change the Click event for that CheckBox to the following:

Private Sub chkExcludeIdleWaits_Click()
    intExcludeIdleWaits = chkExcludeIdleWaits.Value
End Sub

We need to add a little code to the two ComboBox controls in order to prevent the user of this tool from entering silly values, such as Sixty for the update frequency or 1,000,000 for the minimum utilization percent – we will check the entered values only when the user “tabs” out of the controls (or clicks something else).  Double-click the cboUpdateFrequency ComboBox, then select the Exit event from the ComboBox (drop-down list) that is at the top-right of the code window.  Add the following code to that event:

Private Sub cboUpdateFrequency_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim intResult As Integer

    If IsNumeric(cboUpdateFrequency.Text) Then
        If (Val(cboUpdateFrequency.Text) >= 1) And (Val(cboUpdateFrequency.Text) <= 18000) Then
            'OK
            lngTimerTriggerSeconds = Val(cboUpdateFrequency.Text)
        Else
            intResult = MsgBox(cboUpdateFrequency.Text & " is an invalid value." & vbCrLf & _
                "Must enter a number between 1 and 18000", vbCritical, "Charles Hooper's Oracle Database Time Model Viewer") 

            cboUpdateFrequency.Text = "60"
            lngTimerTriggerSeconds = 60
        End If
    Else
        intResult = MsgBox(cboUpdateFrequency.Text & " is an invalid value." & vbCrLf & _
            "Must enter a number between 1 and 18000", vbCritical, "Charles Hooper's Oracle Database Time Model Viewer")

        cboUpdateFrequency.Text = "60"
        lngTimerTriggerSeconds = 60
    End If
End Sub 

We need similar code in the Exit event of the cboUpdateFrequency ComboBox.  Double-click the cboUpdateFrequency ComboBox, and switch to the Exit event.  Add the following code:

Private Sub cboSessionMinimumPercent_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim intResult As Integer

    If IsNumeric(cboSessionMinimumPercent.Text) Then
        If (Val(cboSessionMinimumPercent.Text) >= 0.001) And (Val(cboSessionMinimumPercent.Text) <= 100) Then
            'OK
        Else
            intResult = MsgBox(cboSessionMinimumPercent.Text & " is an invalid value." & vbCrLf & _
                "Must enter a number between 0.001 and 100.0", vbCritical, "Charles Hooper's Oracle Database Time Model Viewer")

            cboSessionMinimumPercent.Text = "10"
        End If
    Else
        intResult = MsgBox(cboSessionMinimumPercent.Text & " is an invalid value." & vbCrLf & _
            "Must enter a number between 0.001 and 100.0", vbCritical, "Charles Hooper's Oracle Database Time Model Viewer")

        cboSessionMinimumPercent.Text = "10"
    End If
End Sub 

We still need to add the functionality for the chkDisplaySessionDetail CheckBox and the cboSessionMinimumPercent ComboBox, and we have not done anything with the wait events yet (that feature will be added in the next article in this series).  Find the following code section in the UpdateDisplay subroutine:

'    If chkDisplaySessionDetail = 0 Then
'        intDisplaySessionDetail = False
'    Else
'        intDisplaySessionDetail = True
'    End If
'    sglSessionMinimumPercent = Val(cboSessionMinimumPercent.Text) / 100 

Remove the single quote characters (‘) in front of each of those lines so that the code appears like this:

    If chkDisplaySessionDetail = 0 Then
        intDisplaySessionDetail = False
    Else
        intDisplaySessionDetail = True
    End If
    sglSessionMinimumPercent = Val(cboSessionMinimumPercent.Text) / 100 

The above simple fix adds the functionality to the chkDisplaySessionDetail CheckBox and the cboSessionMinimumPercent ComboBox.  We still need to provide a list of items in the ComboBoxes that the users are able to select from, and fix the problem where the macro seems to pause unexpectedly when first started.  Switch to the Initialize event in the UserForm (double-click the UserForm’s background area), and then locate the following code in that subroutine:

    'More code will be copied here
    '
    '
    ' 

Just below that section of the code (and above the TimerEvent line), add the following code:

    cboUpdateFrequency.AddItem "5"
    cboUpdateFrequency.AddItem "10"
    cboUpdateFrequency.AddItem "30"
    cboUpdateFrequency.AddItem "60"
    cboUpdateFrequency.AddItem "120"
    cboUpdateFrequency.AddItem "600"
    cboUpdateFrequency.AddItem "3600"
    cboUpdateFrequency.AddItem "7200"
    cboUpdateFrequency.Text = "60"
    lngTimerTriggerSeconds = 60

    cboSessionMinimumPercent.AddItem "1"
    cboSessionMinimumPercent.AddItem "5"
    cboSessionMinimumPercent.AddItem "10"
    cboSessionMinimumPercent.AddItem "15"
    cboSessionMinimumPercent.AddItem "20"
    cboSessionMinimumPercent.AddItem "25"
    cboSessionMinimumPercent.AddItem "50"
    cboSessionMinimumPercent.AddItem "75"
    cboSessionMinimumPercent.Text = "10"

    DoEvents 

In the above, the lines containing .AddItem add entries to the list that is suggested to the user of the tool that we are building.  The lines containing .Text set the default text that appears in each of the ComboBoxes, and the lngTimerTriggerSeconds value must be identical to the numeric value that is assigned to the cboUpdateFrequency.Text property.  Save the project and press the F5 key on the keyboard to display the UserForm and start updating the statistics (after a 60 second delay).  Place a check in the chkDisplaySessionDetail CheckBox (identified as Show Session Detail in the sample project).  You should see something like this (up to 60 seconds after placing a check in that CheckBox):

By looking at the above screen capture it is probably obvious that those sessions which had consumed a small percentage of a Time Model Statistic are displayed with a yellow background, those sessions that had consumed 50% of a Time Model Statistic are displayed with a deep orange background, and sessions that had consumed 100% of a Time Model Statistic are displayed with a solid red background.

We still have a bit more to add to this tool, so keep an eye open for the next article in this series.

—-

Added March 5, 2011:

The Excel project code to this point, save with a .XLS extension (currently has a .DOC extension, please change):
timemodelviewerexcelarticle3xls





Oracle Database Time Model Viewer in Excel 2

1 03 2011

March 1, 2011 (Updated March 2, 2011)

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

In the previous blog article in this series, we examined the output generated by one of my programs.  Most of that output was centered on the analysis of the time model statistics in Oracle Database 10.1 and above.  The intention of this blog article series is to try to build a similar tool using nothing more than Microsoft Excel.  I do not know yet if it is possible, but we will definitely try to make it work.  Unfortunately, there are a couple of features missing in Oracle Database 10.1 as well as some of the statistics being named differently from later release versions of Oracle Database, so this project will target Oracle Database 10.2.0.1 and above.  This project will use a Microsoft TreeView control which might not be installed on your computer, however I verified on a Windows 7 32 bit computer with Excel 2010 installed, that if you are able to find the OCX file containing the Treeview and register that file with Windows, it can be used in the Excel development environment for this project.

If you attempted the project in the previous article of this series (that project is the starting point for today’s article), you may have received an odd error message when opening the project (at least I did on Excel 2007).  The message is “Can’t execute code in break mode” – if you received that error message, there is a simple fix – just hit the F5 key on the keyboard to tell Excel to continue executing the code.  The reason why this error message appears is likely related to the ShowModal property of the UserForm.  When that property is set to True (apparently the default), that UserForm has the full attention of Microsoft Excel until it is closed – that of course causes problems when the worksheet opens and the form displays automatically.  There are two methods to work around this issue, the first requires changing the following line in the code:

frmTimeModel.Show 

to this:

frmTimeModel.Show vbModeless 

In my opinion, the above is a little ugly, so let’s instead change the ShowModal property of the UserForm.  View the code in the worksheet (see the directions found in the previous article), double-click frmTimeModel under the Forms heading (below Microsoft Excel Objects that was used in the previous article), click once on the background of the UserForm, and then in the Properties window, change the ShowModal property from True to False (don’t ask me why the constant is spelled differently than the property, someone was just being creative I guess):

Now double-click the UserForm’s background area – that will display the programming code that is related to what we double-clicked (the UserForm in this case).  So far, only the following code should appear in the UserForm’s code – this is what causes the TimerEvent procedure to start executing once a second when the UserForm is first displayed on the screen:

Private Sub UserForm_Initialize()
    TimerEvent
End Sub 

Move the cursor in front of “P” in the word Private and hit the Enter key a couple of times to insert a couple of blank lines.  We will need to copy in a lot of code into that blank area – some of the code will not be used immediately, but we will need it later.  Add the following code above the word Private – these are all of the variables that are needed throughout our UserForm:

Option Explicit

Dim dbDatabase As ADODB.Connection          'The ADO connection to the database

Dim dteLastUpdateDate As Variant            'Last update date for queries
Dim intCheckIterations As Integer           'Number of times to check the instances
Dim intDelayIterations As Integer           'Number of seconds to delay between iterations
Dim sglSessionMinimumPercent As Single      'Minimum percent of the total required for the session to be included in the report detail
Dim dteLastLoopStart As Variant             'Time of the last loop start
Dim intDisplaySessionDetail As Integer      'Indicates whether or not to display the session level detail

Dim intFlag As Integer                      'Loop control variable, allow to jump out of the loop early

Dim intCurrentSessionIndex As Integer       'Session index to the currently selected item in the tree view
Dim intNumCPUs As Integer                   'Number of CPUs
Dim dblIdleTime As Double                   'Current value of idle time from V$OSSTAT
Dim dblBusyTime As Double                   'Current value of busy time from V$OSSTAT
Dim dblUserTime As Double                   'Current value of user time from V$OSSTAT
Dim dblSysTime As Double                    'Current value of system/kernel mode time from V$OSSTAT
Dim dblIdleTimeLast As Double               'Previous value of idle time from V$OSSTAT
Dim dblBusyTimeLast As Double               'Previous value of busy time from V$OSSTAT
Dim dblUserTimeLast As Double               'Previous value of user time from V$OSSTAT
Dim dblSysTimeLast As Double                'Previous value of system/kernel mode time from V$OSSTAT

Dim dblCPUUsedByThisSession As Double       'Current value of 'CPU used by this session' from V$SYSSTAT, in seconds
Dim dblParseTimeCPU As Double               'Current value of 'parse time cpu' from V$SYSSTAT, in seconds
Dim dblRecursiveCPUUsage As Double          'Current value of 'recursive cpu usage' from V$SYSSTAT, in seconds
Dim lngParseCountTotal As Long              'Current value of 'parse count (total)' from V$SYSSTAT
Dim lngParseCountHard As Long               'Current value of 'parse count (hard)' from V$SYSSTAT
Dim lngParseCountFailures As Long           'Current value of 'parse count (failures)' from V$SYSSTAT
Dim lngSessionCacheHits As Long             'Current value of 'session cursor cache hits' from V$SYSSTAT
Dim dblCPUUsedByThisSessionLast As Double   'previous value of 'CPU used by this session' from V$SYSSTAT, in seconds
Dim dblParseTimeCPULast As Double           'Previous value of 'parse time cpu' from V$SYSSTAT, in seconds
Dim dblRecursiveCPUUsageLast As Double      'Previous value of 'recursive cpu usage' from V$SYSSTAT, in seconds
Dim lngParseCountTotalLast As Long          'Previous value of 'parse count (total)' from V$SYSSTAT
Dim lngParseCountHardLast As Long           'Previous value of 'parse count (hard)' from V$SYSSTAT
Dim lngParseCountFailuresLast As Long       'Previous value of 'parse count (failures)' from V$SYSSTAT
Dim lngSessionCacheHitsLast As Long         'Previous value of 'session cursor cache hits' from V$SYSSTAT

Dim dblDBCPU As Double                      'Current value of DB CPU from V$SYS_TIME_MODEL
Dim dblDBTime As Double                     'Current value of DB time from V$SYS_TIME_MODEL
Dim dblJavaTime As Double                   'Current value of Java execution elapsed time from V$SYS_TIME_MODEL
Dim dblPLSQLCompile As Double               'Current value of PL/SQL compilation elapsed time from V$SYS_TIME_MODEL
Dim dblPLSQLExecution As Double             'Current value of PL/SQL execution elapsed time from V$SYS_TIME_MODEL
Dim dblRMANCPU As Double                    'Current value of RMAN cpu time (backup/restore) from V$SYS_TIME_MODEL
Dim dblBackgroundCPU As Double              'Current value of background cpu time from V$SYS_TIME_MODEL
Dim dblBackgroundElapsed As Double          'Current value of background elapsed time from V$SYS_TIME_MODEL
Dim dblConnectMgmt As Double                'Current value of connection management call elapsed time from V$SYS_TIME_MODEL
Dim dblFailedParseMemory As Double          'Current value of failed parse (out of shared memory) elapsed time from V$SYS_TIME_MODEL
Dim dblFailedParseElapsed As Double         'Current value of failed parse elapsed time from V$SYS_TIME_MODEL
Dim dblHardParseBind As Double              'Current value of hard parse (bind mismatch) elapsed time from V$SYS_TIME_MODEL
Dim dblHardParseSharing As Double           'Current value of hard parse (sharing criteria) elapsed time from V$SYS_TIME_MODEL
Dim dblHardParseElapsed As Double           'Current value of hard parse elapsed time from V$SYS_TIME_MODEL
Dim dblInboundPLSQL As Double               'Current value of inbound PL/SQL rpc elapsed time from V$SYS_TIME_MODEL
Dim dblParseTimeElapsed As Double           'Current value of parse time elapsed from V$SYS_TIME_MODEL
Dim dblRepeatedBind As Double               'Current value of repeated bind elapsed time from V$SYS_TIME_MODEL
Dim dblSequenceLoad As Double               'Current value of sequence load elapsed time from V$SYS_TIME_MODEL
Dim dblSQLExecuteTime As Double             'Current value of sql execute elapsed time from V$SYS_TIME_MODEL

Dim dblDBCPULast As Double                  'Last value of DB CPU from V$SYS_TIME_MODEL
Dim dblDBTimeLast As Double                 'Last value of DB time from V$SYS_TIME_MODEL
Dim dblJavaTimeLast As Double               'Last value of Java execution elapsed time from V$SYS_TIME_MODEL
Dim dblPLSQLCompileLast As Double           'Last value of PL/SQL compilation elapsed time from V$SYS_TIME_MODEL
Dim dblPLSQLExecutionLast As Double         'Last value of PL/SQL execution elapsed time from V$SYS_TIME_MODEL
Dim dblRMANCPULast As Double                'Last value of RMAN cpu time (backup/restore) from V$SYS_TIME_MODEL
Dim dblBackgroundCPULast As Double          'Last value of background cpu time from V$SYS_TIME_MODEL
Dim dblBackgroundElapsedLast As Double      'Last value of background elapsed time from V$SYS_TIME_MODEL
Dim dblConnectMgmtLast As Double            'Last value of connection management call elapsed time from V$SYS_TIME_MODEL
Dim dblFailedParseMemoryLast As Double      'Last value of failed parse (out of shared memory) elapsed time from V$SYS_TIME_MODEL
Dim dblFailedParseElapsedLast As Double     'Last value of failed parse elapsed time from V$SYS_TIME_MODEL
Dim dblHardParseBindLast As Double          'Last value of hard parse (bind mismatch) elapsed time from V$SYS_TIME_MODEL
Dim dblHardParseSharingLast As Double       'Last value of hard parse (sharing criteria) elapsed time from V$SYS_TIME_MODEL
Dim dblHardParseElapsedLast As Double       'Last value of hard parse elapsed time from V$SYS_TIME_MODEL
Dim dblInboundPLSQLLast As Double           'Last value of inbound PL/SQL rpc elapsed time from V$SYS_TIME_MODEL
Dim dblParseTimeElapsedLast As Double       'Last value of parse time elapsed from V$SYS_TIME_MODEL
Dim dblRepeatedBindLast As Double           'Last value of repeated bind elapsed time from V$SYS_TIME_MODEL
Dim dblSequenceLoadLast As Double           'Last value of sequence load elapsed time from V$SYS_TIME_MODEL
Dim dblSQLExecuteTimeLast As Double         'Last value of sql execute elapsed time from V$SYS_TIME_MODEL

Dim intSessionCount As Integer              'Number of sessions logged
Dim intSessionCurrent As Integer            'Index of the current session
Dim lngSIDLast As Long                      'SID for the previous row from the database
Dim lngSerialLast As Long                   'SERIAL# for the previous row
Dim intSessionExists(999) As Integer        'Used to determine if the session is still found in the system
Dim lngSID(999) As Long                     'SID for session
Dim lngSerial(999) As Long                  'SERIAL# for the session
Dim strSessionOther(999) As String          'USERNAME, MACHINE, PROGRAM
Dim strSQLID(999) As String                 'SQL_ID and CHILD_NUMBER for the session
Dim dblSessionWait(999, 999) As Double           'Session Amount of time waited by the session in the selected wait event (wait#, session#)
Dim dblSessionWaitValue(999, 999) As Double      'Session wait event total time (wait#, session#)
Dim dblSessionWaitWaitsValue(999, 999) As Double 'Session wait event number of waits (wait#, session#)
Dim dblSessionWaitTOValue(999, 999) As Double    'Session wait event number of timeouts (wait#, session#)

Dim strSessionWaitEvent As String           'Name of the monitored wait event
Dim dblDBCPUS(999) As Double                'Current value of DB CPU from V$SESS_TIME_MODEL
Dim dblDBTimeS(999) As Double               'Current value of DB time from V$SESS_TIME_MODEL
Dim dblJavaTimeS(999) As Double             'Current value of Java execution elapsed time from V$SESS_TIME_MODEL
Dim dblPLSQLCompileS(999) As Double         'Current value of PL/SQL compilation elapsed time from V$SESS_TIME_MODEL
Dim dblPLSQLExecutionS(999) As Double       'Current value of PL/SQL execution elapsed time from V$SESS_TIME_MODEL
Dim dblRMANCPUS(999) As Double              'Current value of RMAN cpu time (backup/restore) from V$SESS_TIME_MODEL
Dim dblBackgroundCPUS(999) As Double        'Current value of background cpu time from V$SESS_TIME_MODEL
Dim dblBackgroundElapsedS(999) As Double    'Current value of background elapsed time from V$SESS_TIME_MODEL
Dim dblConnectMgmtS(999) As Double          'Current value of connection management call elapsed time from V$SESS_TIME_MODEL
Dim dblFailedParseMemoryS(999) As Double    'Current value of failed parse (out of shared memory) elapsed time from V$SESS_TIME_MODEL
Dim dblFailedParseElapsedS(999) As Double   'Current value of failed parse elapsed time from V$SESS_TIME_MODEL
Dim dblHardParseBindS(999) As Double        'Current value of hard parse (bind mismatch) elapsed time from V$SESS_TIME_MODEL
Dim dblHardParseSharingS(999) As Double     'Current value of hard parse (sharing criteria) elapsed time from V$SESS_TIME_MODEL
Dim dblHardParseElapsedS(999) As Double     'Current value of hard parse elapsed time from V$SESS_TIME_MODEL
Dim dblInboundPLSQLS(999) As Double         'Current value of inbound PL/SQL rpc elapsed time from V$SESS_TIME_MODEL
Dim dblParseTimeElapsedS(999) As Double     'Current value of parse time elapsed from V$SESS_TIME_MODEL
Dim dblRepeatedBindS(999) As Double         'Current value of repeated bind elapsed time from V$SESS_TIME_MODEL
Dim dblSequenceLoadS(999) As Double         'Current value of sequence load elapsed time from V$SESS_TIME_MODEL
Dim dblSQLExecuteTimeS(999) As Double       'Current value of sql execute elapsed time from V$SESS_TIME_MODEL

Dim dblDBCPUSLast(999) As Double                 'Last value of DB CPU from V$SESS_TIME_MODEL
Dim dblDBTimeSLast(999) As Double                'Last value of DB time from V$SESS_TIME_MODEL
Dim dblJavaTimeSLast(999) As Double              'Last value of Java execution elapsed time from V$SESS_TIME_MODEL
Dim dblPLSQLCompileSLast(999) As Double          'Last value of PL/SQL compilation elapsed time from V$SESS_TIME_MODEL
Dim dblPLSQLExecutionSLast(999) As Double        'Last value of PL/SQL execution elapsed time from V$SESS_TIME_MODEL
Dim dblRMANCPUSLast(999) As Double               'Last value of RMAN cpu time (backup/restore) from V$SESS_TIME_MODEL
Dim dblBackgroundCPUSLast(999) As Double         'Last value of background cpu time from V$SESS_TIME_MODEL
Dim dblBackgroundElapsedSLast(999) As Double     'Last value of background elapsed time from V$SESS_TIME_MODEL
Dim dblConnectMgmtSLast(999) As Double           'Last value of connection management call elapsed time from V$SESS_TIME_MODEL
Dim dblFailedParseMemorySLast(999) As Double     'Last value of failed parse (out of shared memory) elapsed time from V$SESS_TIME_MODEL
Dim dblFailedParseElapsedSLast(999) As Double    'Last value of failed parse elapsed time from V$SESS_TIME_MODEL
Dim dblHardParseBindSLast(999) As Double         'Last value of hard parse (bind mismatch) elapsed time from V$SESS_TIME_MODEL
Dim dblHardParseSharingSLast(999) As Double      'Last value of hard parse (sharing criteria) elapsed time from V$SESS_TIME_MODEL
Dim dblHardParseElapsedSLast(999) As Double      'Last value of hard parse elapsed time from V$SESS_TIME_MODEL
Dim dblInboundPLSQLSLast(999)                    'Last value of inbound PL/SQL rpc elapsed time from V$SESS_TIME_MODEL
Dim dblParseTimeElapsedSLast(999)                'Last value of parse time elapsed from V$SESS_TIME_MODEL
Dim dblRepeatedBindSLast(999)                    'Last value of repeated bind elapsed time from V$SESS_TIME_MODEL
Dim dblSequenceLoadSLast(999)                    'Last value of sequence load elapsed time from V$SESS_TIME_MODEL
Dim dblSQLExecuteTimeSLast(999)                  'Last value of sql execute elapsed time from V$SESS_TIME_MODEL

Dim intWaitCount As Integer                 'Number of wait events read from the database
Dim intWaitCurrent As Integer               'Current index of the wait event
Dim strWaitEventName(1300) As String        'Name of the wait event
Dim strWaitEventClass(1300) As String       'Wait Class of the wait event
Dim dblWaitValue(1300) As Double            'Current wait event total time
Dim dblWaitValueLast(1300) As Double        'Previous wait event total time
Dim dblWaitWaitsValue(1300) As Double       'Current wait event number of waits
Dim dblWaitWaitsValueLast(1300) As Double   'Previous wait event number of waits
Dim dblWaitTOValue(1300) As Double          'Current wait event number of timeouts
Dim dblWaitTOValueLast(1300) As Double      'Previous wait event number of timeouts

Dim snpDataWait As ADODB.Recordset          'ADO recordset used to query V$SYSTEM_EVENT
Dim comDataWait As ADODB.Command            'ADO command object used to retrieve data from V$SYSTEM_EVENT
Dim snpDataOSStat As ADODB.Recordset        'ADO recordset used to query V$OSSTAT
Dim comDataOSStat As ADODB.Command          'ADO command object used to retrieve data from V$OSSTAT
Dim snpDataSysTime As ADODB.Recordset       'ADO recordset used to query V$SYS_TIME_MODEL
Dim comDataSysTime As ADODB.Command         'ADO command object used to retrieve from V$SYS_TIME_MODEL
Dim snpDataSessTime As ADODB.Recordset      'ADO recordset used to query V$SESS_TIME_MODEL
Dim comDataSessTime As ADODB.Command        'ADO command object used to retrieve from V$SESS_TIME_MODEL
Dim comTrace As ADODB.Command               'ADO command object used to enable a 10046 trace
Dim snpXPLAN As ADODB.Recordset             'ADO Recordset object used to retrieve the execution plan
Dim comXPLAN As ADODB.Command               'ADO command object used to retrieve the execution plan
Dim snpSQLChildReason As ADODB.Recordset    'ADO recordset object used to retrieve the reason for the child cursor
Dim comSQLChildReason As ADODB.Command      'ADO command object used to retrieve the reason for the child cursor
Dim snpSQLChildBind As ADODB.Recordset      'ADO recordset object used to retrieve the bind variable definitions
Dim comSQLChildBind As ADODB.Command        'ADO command object used to retrieve the bind variable definitions
Dim snpSessionWait As ADODB.Recordset       'ADO recordset object used to retrieve the session level waits
Dim comSessionWait As ADODB.Command         'ADO command object used to retrieve the session level waits
Dim snpSYSSTAT As ADODB.Recordset           'ADO recordset object used to retrieve the system level statistics from V$SYSSTAT
Dim comSYSSTAT As ADODB.Command             'ADO command object used to retrieve the system level statistics from V$SYSSTAT

Dim intActivated As Integer                 'Indicates whether or not the form is refreshed and ready for use
Dim intRefreshSeconds As Integer            'Minimum number of seconds to wait before the next refresh
Dim intRefreshCount As Integer              'Counter that indicates the number of elapsed seconds since the last refresh
Dim intPauseRefresh As Integer              'Indicates whether or not the refresh is paused
Dim intExcludeIdleWaits As Integer          'Indicates whether or not to exclude idle wait events 

Now change the UserForm_Initialize procedure so that it shows the following:

Private Sub UserForm_Initialize()
    Dim intResult As Integer
    Dim strUsername As String
    Dim strPassword As String
    Dim strDatabase As String
    Dim strSQL As String

    On Error Resume Next

    'Database configuration
    strUsername = "MyUsername"
    strPassword = "MyPassword"
    strDatabase = "MyDB"

    Set dbDatabase = New ADODB.Connection

    dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase _
        & ";User ID=" & strUsername & ";Password=" & strPassword & ";ChunkSize=1000;FetchSize=100;"

    dbDatabase.ConnectionTimeout = 40
    dbDatabase.CursorLocation = adUseClient
    dbDatabase.Open

    If (dbDatabase.State <> 1) Or (Err <> 0) Then
        intResult = MsgBox("Could not connect to the database.  Check your user name and password." & vbCrLf & Error(Err), 16, "Charles Hooper's Oracle Database Time Model Viewer")
        Exit Sub
    End If

    'More code will be copied here
    '
    '
    '
   
    TimerEvent
End Sub 

At this point, after replacing MyUsername, MyPassword, and MyDB (the DB name from the tnsnames.ora file) with the appropriate logon credentials for one of your databases, you should be able to verify that the database connection works by running the UserForm_Initialize subroutine, simply by pressing the F5 key on the keyboard.  If no error message appears on the screen, the macro is able to connect to the database.  Now that we have verified that database connectivity works as expected, let’s close the UserForm’s window and add a little more code to the UserForm_Initialize subroutine.  Move the cursor just above the ‘More code will be copied here line and press the Enter key a couple of times to move that line down a little.  Move the cursor back up to one of the blank lines and paste in the following code which sets up a couple of the SQL statements that will be used by this tool:

    lngTimerTriggerSeconds = 60
    sglSessionMinimumPercent = 0.1  '10% of the total for the time period needed to be included in the detail
    Set snpDataWait = New ADODB.Recordset
    Set comDataWait = New ADODB.Command
    Set snpDataOSStat = New ADODB.Recordset
    Set comDataOSStat = New ADODB.Command
    Set snpDataSysTime = New ADODB.Recordset
    Set comDataSysTime = New ADODB.Command
    Set snpDataSessTime = New ADODB.Recordset
    Set comDataSessTime = New ADODB.Command
    Set comTrace = New ADODB.Command
    Set snpXPLAN = New ADODB.Recordset
    Set comXPLAN = New ADODB.Command
    Set snpSQLChildReason = New ADODB.Recordset
    Set comSQLChildReason = New ADODB.Command
    Set snpSQLChildBind = New ADODB.Recordset
    Set comSQLChildBind = New ADODB.Command
    Set snpSessionWait = New ADODB.Recordset
    Set comSessionWait = New ADODB.Command
    Set snpSYSSTAT = New ADODB.Recordset
    Set comSYSSTAT = New ADODB.Command

    With comDataOSStat
        strSQL = "SELECT" & vbCrLf
        strSQL = strSQL & "  STAT_NAME," & vbCrLf
        strSQL = strSQL & "  VALUE" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  V$OSSTAT" & vbCrLf
        strSQL = strSQL & "WHERE" & vbCrLf
        strSQL = strSQL & "  STAT_NAME IN ('NUM_CPUS','IDLE_TIME','BUSY_TIME','USER_TIME','SYS_TIME')"

        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30
        .ActiveConnection = dbDatabase
    End With

    With comDataSysTime
        strSQL = "SELECT" & vbCrLf
        strSQL = strSQL & "  VALUE," & vbCrLf
        strSQL = strSQL & "  STAT_NAME" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  V$SYS_TIME_MODEL"

        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30
        .ActiveConnection = dbDatabase
    End With

    With comDataSessTime
        strSQL = "SELECT" & vbCrLf
        strSQL = strSQL & "  S.SID," & vbCrLf
        strSQL = strSQL & "  S.SERIAL#," & vbCrLf
        strSQL = strSQL & "  S.STATUS," & vbCrLf
        strSQL = strSQL & "  NVL(S.USERNAME,' ') USERNAME," & vbCrLf
        strSQL = strSQL & "  NVL(S.MACHINE,' ') MACHINE," & vbCrLf
        strSQL = strSQL & "  NVL(S.PROGRAM,' ') PROGRAM," & vbCrLf
        strSQL = strSQL & "  NVL(S.SQL_ID,NVL(S.PREV_SQL_ID,' ')) SQL_ID," & vbCrLf
        strSQL = strSQL & "  NVL(S.SQL_CHILD_NUMBER,NVL(S.PREV_CHILD_NUMBER,0)) SQL_CHILD_NUMBER," & vbCrLf
        strSQL = strSQL & "  STM.VALUE," & vbCrLf
        strSQL = strSQL & "  STM.STAT_NAME" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  V$SESS_TIME_MODEL STM," & vbCrLf
        strSQL = strSQL & "  V$SESSION S" & vbCrLf
        strSQL = strSQL & "WHERE" & vbCrLf
        strSQL = strSQL & "  S.SID=STM.SID" & vbCrLf
        strSQL = strSQL & "ORDER BY" & vbCrLf
        strSQL = strSQL & "  S.USERNAME," & vbCrLf
        strSQL = strSQL & "  S.PROGRAM," & vbCrLf
        strSQL = strSQL & "  S.SID"

        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30
        .ActiveConnection = dbDatabase
    End With

    With comSYSSTAT
        strSQL = "SELECT" & vbCrLf
        strSQL = strSQL & "  NAME," & vbCrLf
        strSQL = strSQL & "  VALUE" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  V$SYSSTAT" & vbCrLf
        strSQL = strSQL & "WHERE" & vbCrLf
        strSQL = strSQL & "  NAME IN ('CPU used by this session','parse time cpu','recursive cpu usage','parse count (total)','parse count (hard)','parse count (failures)','session cursor cache hits')"

        .CommandText = strSQL
        .CommandType = adCmdText
        .ActiveConnection = dbDatabase
    End With 

We will add more later, but that is sufficient for a starting point.  Just below the End Sub in the UserForm_Initialize procedure, add the following code, which will perform clean up when the UserForm is closed:

Private Sub UserForm_Terminate()
    intKillFlag = True

    If dbDatabase.State = 1 Then
        dbDatabase.Close
    End If

    Set snpDataWait = Nothing
    Set comDataWait = Nothing
    Set snpDataOSStat = Nothing
    Set comDataOSStat = Nothing
    Set snpDataSysTime = Nothing
    Set comDataSysTime = Nothing
    Set snpDataSessTime = Nothing
    Set comDataSessTime = Nothing
    Set comTrace = Nothing
    Set snpXPLAN = Nothing
    Set comXPLAN = Nothing
    Set snpSQLChildReason = Nothing
    Set comSQLChildReason = Nothing
    Set snpSQLChildBind = Nothing
    Set comSQLChildBind = Nothing
    Set snpSessionWait = Nothing
    Set comSessionWait = Nothing
    Set snpSYSSTAT = Nothing
    Set comSYSSTAT = Nothing
    Set dbDatabase = Nothing
End Sub

Let’s switch back to the design of the UserForm (if you cannot see the UserForm’s window, right-click frmTimeModel below Microsoft Excel Objects and select View Object from the menu), where we will add a couple of labels to the form.  This will be a little time consuming, but if you create one of the white box labels and a heading label, you can copy and paste the two labels as many times as is necessary (hold down the shift key to select more than one label to copy).  We will need a total of 11 labels with BackColor property set to &H00FFFFFF&  (white) and with the BorderStyle property set to 1 – fmBorderStyleSingle and with the TextAlign property set to 3 – fmTextAlignRight – we will also need a total of 11 plain labels with the TextAlign property set to 2 – fmTextAlignCenter

Set the (Name) property of the white background labels using the following list (one name per label): lblCPUs, lblBusyTime, lblIdleTime, lblBusyPercent, lblUserMode, lblKernelMode, lblUserModePercent, lblCPUUsedBySession, lblParseTimeCPU, lblRecursiveCPUUsage, lblOtherCPU

For the 11 plain labels with the centered text, change the Caption property of those labels to identify the contents (such as CPUs, Busy Time, etc.) of the closest white background label.  When the additions are complete, your UserForm might look something like the picture below:

Now we need to switch back to the code for the frmTimeModel UserForm and add a little more code to actually query the database.  Scroll up toward the top of the code in the UserForm, and locate this line:

Dim intExcludeIdleWaits As Integer          'Indicates whether or not to exclude idle wait events 

Move the cursor to the end of that line and press the Enter key a couple of times to add a couple of blank lines.  On one of the new blank lines paste the following code, which will query the database when executed:

Public Sub ReadData()
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim strSQL As String

    On Error Resume Next

    dteLastUpdateDate = Now

    Set snpDataOSStat = comDataOSStat.Execute
    If Not (snpDataOSStat Is Nothing) Then
        Do While Not (snpDataOSStat.EOF)
          Select Case CStr(snpDataOSStat("stat_name"))
            Case "NUM_CPUS"
              intNumCPUs = CInt(snpDataOSStat("value"))
            Case "IDLE_TIME"
              dblIdleTimeLast = dblIdleTime
              dblIdleTime = CDbl(snpDataOSStat("value"))
            Case "BUSY_TIME"
              dblBusyTimeLast = dblBusyTime
              dblBusyTime = CDbl(snpDataOSStat("value"))
            Case "USER_TIME"
              dblUserTimeLast = dblUserTime
              dblUserTime = CDbl(snpDataOSStat("value"))
            Case "SYS_TIME"
              dblSysTimeLast = dblSysTime
              dblSysTime = CDbl(snpDataOSStat("value"))
          End Select

          snpDataOSStat.MoveNext
        Loop
    End If

    Set snpDataSysTime = comDataSysTime.Execute
    If Not (snpDataSysTime Is Nothing) Then
        Do While Not (snpDataSysTime.EOF)
          Select Case CStr(snpDataSysTime("stat_name"))
            Case "DB CPU"
              dblDBCPULast = dblDBCPU
              dblDBCPU = CDbl(snpDataSysTime("value"))
            Case "DB time"
              dblDBTimeLast = dblDBTime
              dblDBTime = CDbl(snpDataSysTime("value"))
            Case "Java execution elapsed time"
              dblJavaTimeLast = dblJavaTime
              dblJavaTime = CDbl(snpDataSysTime("value"))
            Case "PL/SQL compilation elapsed time"
              dblPLSQLCompileLast = dblPLSQLCompile
              dblPLSQLCompile = CDbl(snpDataSysTime("value"))
            Case "PL/SQL execution elapsed time"
              dblPLSQLExecutionLast = dblPLSQLExecution
              dblPLSQLExecution = CDbl(snpDataSysTime("value"))
            Case "RMAN cpu time (backup/restore)"
              dblRMANCPULast = dblRMANCPU
              dblRMANCPU = CDbl(snpDataSysTime("value"))
            Case "background cpu time"
              dblBackgroundCPULast = dblBackgroundCPU
              dblBackgroundCPU = CDbl(snpDataSysTime("value"))
            Case "background elapsed time"
              dblBackgroundElapsedLast = dblBackgroundElapsed
              dblBackgroundElapsed = CDbl(snpDataSysTime("value"))
            Case "connection management call elapsed time"
              dblConnectMgmtLast = dblConnectMgmt
              dblConnectMgmt = CDbl(snpDataSysTime("value"))
            Case "failed parse (out of shared memory) elapsed time"
              dblFailedParseMemoryLast = dblFailedParseMemory
              dblFailedParseMemory = CDbl(snpDataSysTime("value"))
            Case "failed parse elapsed time"
              dblFailedParseElapsedLast = dblFailedParseElapsed
              dblFailedParseElapsed = CDbl(snpDataSysTime("value"))
            Case "hard parse (bind mismatch) elapsed time"
              dblHardParseBindLast = dblHardParseBind
              dblHardParseBind = CDbl(snpDataSysTime("value"))
            Case "hard parse (sharing criteria) elapsed time"
              dblHardParseSharingLast = dblHardParseSharing
              dblHardParseSharing = CDbl(snpDataSysTime("value"))
            Case "hard parse elapsed time"
              dblHardParseElapsedLast = dblHardParseElapsed
              dblHardParseElapsed = CDbl(snpDataSysTime("value"))
            Case "inbound PL/SQL rpc elapsed time"
              dblInboundPLSQLLast = dblInboundPLSQL
              dblInboundPLSQL = CDbl(snpDataSysTime("value"))
            Case "parse time elapsed"
              dblParseTimeElapsedLast = dblParseTimeElapsed
              dblParseTimeElapsed = CDbl(snpDataSysTime("value"))
            Case "repeated bind elapsed time"
              dblRepeatedBindLast = dblRepeatedBind
              dblRepeatedBind = CDbl(snpDataSysTime("value"))
            Case "sequence load elapsed time"
              dblSequenceLoadLast = dblSequenceLoad
              dblSequenceLoad = CDbl(snpDataSysTime("value"))
            Case "sql execute elapsed time"
              dblSQLExecuteTimeLast = dblSQLExecuteTime
              dblSQLExecuteTime = CDbl(snpDataSysTime("value"))
          End Select

          snpDataSysTime.MoveNext
        Loop
    End If

    For j = 1 To intSessionCount
        intSessionExists(j) = False
    Next j

    Set snpDataSessTime = comDataSessTime.Execute
    If Not (snpDataSessTime Is Nothing) Then
        Do While Not (snpDataSessTime.EOF)
          'Find the matching session's previous statistics
          If (lngSIDLast <> CLng(snpDataSessTime("sid"))) Or (lngSerialLast <> CLng(snpDataSessTime("serial#"))) Then
            'This is a different session, see if the session was previously captured
            lngSIDLast = CLng(snpDataSessTime("sid"))
            lngSerialLast = CLng(snpDataSessTime("serial#"))

            intSessionCurrent = intSessionCount + 1
            For j = 1 To intSessionCount
              If (lngSID(j) = CLng(snpDataSessTime("sid"))) And (lngSerial(j) = CLng(snpDataSessTime("serial#"))) Then
                intSessionCurrent = j
                Exit For
              End If
            Next j
            If intSessionCurrent = intSessionCount + 1 Then
              intSessionCount = intSessionCount + 1
              lngSID(intSessionCurrent) = CLng(snpDataSessTime("sid"))
              lngSerial(intSessionCurrent) = CLng(snpDataSessTime("serial#"))
              strSessionOther(intSessionCurrent) = CStr(snpDataSessTime("machine")) & " ~ " & _
                 CStr(snpDataSessTime("username")) & " ~ " & _
                 CStr(snpDataSessTime("program")) & " ~ "
              If snpDataSessTime("sql_id") <> " " Then
                strSessionOther(intSessionCurrent) = strSessionOther(intSessionCurrent) & "SQL_ID/Child: " & _
                  CStr(snpDataSessTime("sql_id")) & "/" & CStr(snpDataSessTime("sql_child_number"))
                If UCase(snpDataSessTime("status")) = "ACTIVE" Then
                    strSessionOther(intSessionCurrent) = strSessionOther(intSessionCurrent) & " (A)"
                End If
                strSQLID(intSessionCurrent) = CStr(snpDataSessTime("sql_id")) & "/" & CStr(snpDataSessTime("sql_child_number"))
              Else
                strSQLID(intSessionCurrent) = ""
              End If
            End If
          End If

          intSessionExists(intSessionCurrent) = True
          Select Case CStr(snpDataSessTime("stat_name"))
            Case "DB CPU"
              dblDBCPUSLast(intSessionCurrent) = dblDBCPUS(intSessionCurrent)
              dblDBCPUS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
            Case "DB time"
              dblDBTimeSLast(intSessionCurrent) = dblDBTimeS(intSessionCurrent)
              dblDBTimeS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
            Case "Java execution elapsed time"
              dblJavaTimeSLast(intSessionCurrent) = dblJavaTimeS(intSessionCurrent)
              dblJavaTimeS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
            Case "PL/SQL compilation elapsed time"
              dblPLSQLCompileSLast(intSessionCurrent) = dblPLSQLCompileS(intSessionCurrent)
              dblPLSQLCompileS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
            Case "PL/SQL execution elapsed time"
              dblPLSQLExecutionSLast(intSessionCurrent) = dblPLSQLExecutionS(intSessionCurrent)
              dblPLSQLExecutionS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
            Case "RMAN cpu time (backup/restore)"
              dblRMANCPUSLast(intSessionCurrent) = dblRMANCPUS(intSessionCurrent)
              dblRMANCPUS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
            Case "background cpu time"
              dblBackgroundCPUSLast(intSessionCurrent) = dblBackgroundCPUS(intSessionCurrent)
              dblBackgroundCPUS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
            Case "background elapsed time"
              dblBackgroundElapsedSLast(intSessionCurrent) = dblBackgroundElapsedS(intSessionCurrent)
              dblBackgroundElapsedS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
            Case "connection management call elapsed time"
              dblConnectMgmtSLast(intSessionCurrent) = dblConnectMgmtS(intSessionCurrent)
              dblConnectMgmtS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
            Case "failed parse (out of shared memory) elapsed time"
              dblFailedParseMemorySLast(intSessionCurrent) = dblFailedParseMemoryS(intSessionCurrent)
              dblFailedParseMemoryS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
            Case "failed parse elapsed time"
              dblFailedParseElapsedSLast(intSessionCurrent) = dblFailedParseElapsedS(intSessionCurrent)
              dblFailedParseElapsedS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
            Case "hard parse (bind mismatch) elapsed time"
              dblHardParseBindSLast(intSessionCurrent) = dblHardParseBindS(intSessionCurrent)
              dblHardParseBindS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
            Case "hard parse (sharing criteria) elapsed time"
              dblHardParseSharingSLast(intSessionCurrent) = dblHardParseSharingS(intSessionCurrent)
              dblHardParseSharingS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
            Case "hard parse elapsed time"
              dblHardParseElapsedSLast(intSessionCurrent) = dblHardParseElapsedS(intSessionCurrent)
              dblHardParseElapsedS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
            Case "inbound PL/SQL rpc elapsed time"
              dblInboundPLSQLSLast(intSessionCurrent) = dblInboundPLSQLS(intSessionCurrent)
              dblInboundPLSQLS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
            Case "parse time elapsed"
              dblParseTimeElapsedSLast(intSessionCurrent) = dblParseTimeElapsedS(intSessionCurrent)
              dblParseTimeElapsedS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
            Case "repeated bind elapsed time"
              dblRepeatedBindSLast(intSessionCurrent) = dblRepeatedBindS(intSessionCurrent)
              dblRepeatedBindS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
            Case "sequence load elapsed time"
              dblSequenceLoadSLast(intSessionCurrent) = dblSequenceLoadS(intSessionCurrent)
              dblSequenceLoadS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
            Case "sql execute elapsed time"
              dblSQLExecuteTimeSLast(intSessionCurrent) = dblSQLExecuteTimeS(intSessionCurrent)
              dblSQLExecuteTimeS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
          End Select

          snpDataSessTime.MoveNext
        Loop
        snpDataSessTime.Close
    End If

    Set snpSYSSTAT = comSYSSTAT.Execute
    If Not (snpSYSSTAT Is Nothing) Then
        Do While Not (snpSYSSTAT.EOF)
            Select Case snpSYSSTAT("name")
                Case "CPU used by this session"
                    dblCPUUsedByThisSessionLast = dblCPUUsedByThisSession
                    dblCPUUsedByThisSession = CDbl(snpSYSSTAT("value")) / 100
                Case "parse time cpu"
                    dblParseTimeCPULast = dblParseTimeCPU
                    dblParseTimeCPU = CDbl(snpSYSSTAT("value")) / 100
                Case "recursive cpu usage"
                    dblRecursiveCPUUsageLast = dblRecursiveCPUUsage
                    dblRecursiveCPUUsage = CDbl(snpSYSSTAT("value")) / 100
                Case "parse count (total)"
                    lngParseCountTotalLast = lngParseCountTotal
                    lngParseCountTotal = snpSYSSTAT("value")
                Case "parse count (hard)"
                    lngParseCountHardLast = lngParseCountHard
                    lngParseCountHard = snpSYSSTAT("value")
                Case "parse count (failures)"
                    lngParseCountFailuresLast = lngParseCountFailures
                    lngParseCountFailures = snpSYSSTAT("value")
                Case "session cursor cache hits"
                    lngSessionCacheHitsLast = lngSessionCacheHits
                    lngSessionCacheHits = snpSYSSTAT("value")
            End Select

            snpSYSSTAT.MoveNext
        Loop
        snpSYSSTAT.Close
    End If

    dteLastLoopStart = Now
End Sub 

As you are probably able to tell, the above code reads a lot of data from the database and places that data into variables.  Now we need code to display the query results on the UserForm.  Paste this code directly below the code that was just pasted:

Public Sub UpdateDisplay()
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim sglColor As Single
    Dim lngResult As Long
    Dim dblDBTimeDelta As Double
    Dim dblBusyTimeSecondsDelta As Double
    Dim strAT As String
    Dim strOut As String
    Dim strLine As String
    Dim strLastWaitClass As String
    Dim intLastWaitClassRow As Integer
    Dim sglWaitClassTime As Single
    Dim sglTotalWaitTime As Single

    On Error Resume Next

    intActivated = False
'    If chkDisplaySessionDetail = 0 Then
'        intDisplaySessionDetail = False
'    Else
'        intDisplaySessionDetail = True
'    End If
'    sglSessionMinimumPercent = Val(cboSessionMinimumPercent.Text) / 100
    If sglSessionMinimumPercent = 0 Then
        sglSessionMinimumPercent = 0.1
    End If

    lblCPUs = Format(intNumCPUs)
    lblBusyTime = Format((dblBusyTime - dblBusyTimeLast) / 100, "0.00")
    lblIdleTime = Format((dblIdleTime - dblIdleTimeLast) / 100, "0.00")
    lblBusyPercent = Format(((dblBusyTime - dblBusyTimeLast) / ((dblBusyTime - dblBusyTimeLast) + (dblIdleTime - dblIdleTimeLast)) * 100), "0.00")
    lblUserMode = Format((dblUserTime - dblUserTimeLast) / 100, "0.00")
    lblKernelMode = Format((dblSysTime - dblSysTimeLast) / 100, "0.00")
    lblUserModePercent = Format(((dblUserTime - dblUserTimeLast) / ((dblUserTime - dblUserTimeLast) + (dblSysTime - dblSysTimeLast)) * 100), "0.00")

    lblCPUUsedBySession = Format(dblCPUUsedByThisSession - dblCPUUsedByThisSessionLast, "0.00")
    lblParseTimeCPU = Format(dblParseTimeCPU - dblParseTimeCPULast, "0.00")
    lblRecursiveCPUUsage = Format(dblRecursiveCPUUsage - dblRecursiveCPUUsageLast, "0.00")
    lblOtherCPU = Format((dblCPUUsedByThisSession - dblCPUUsedByThisSessionLast) - (dblParseTimeCPU - dblParseTimeCPULast) - (dblRecursiveCPUUsage - dblRecursiveCPUUsageLast), "0.00")

    dblBusyTimeSecondsDelta = (dblBusyTime - dblBusyTimeLast) / 100

    strAT = String(Len(Format(((dblBackgroundElapsed - dblBackgroundElapsedLast) + (dblDBTime - dblDBTimeLast)) / 1000000, "0.00")), "@")

    'tvTimeModel.Visible = False
    tvTimeModel.Nodes.Clear
    tvTimeModel.Nodes.Add , , "BackgroundElapsedTime", Format(Format((dblBackgroundElapsed - dblBackgroundElapsedLast) / 1000000, "0.00"), strAT) & " Background Elapsed Time"
    If ((dblBackgroundElapsed - dblBackgroundElapsedLast) <> 0) And (intDisplaySessionDetail = True) Then
        For j = 1 To intSessionCount
            If intSessionExists(j) = True Then
                If ((dblBackgroundElapsedS(j) - dblBackgroundElapsedSLast(j)) / (dblBackgroundElapsed - dblBackgroundElapsedLast) >= sglSessionMinimumPercent) Then
                    tvTimeModel.Nodes.Add "BackgroundElapsedTime", tvwChild, "SESSIONBackgroundElapsedTime_" & Format(j), "-- " & Format(Format((dblBackgroundElapsedS(j) - dblBackgroundElapsedSLast(j)) / 1000000, "0.00"), strAT) & "  SID: " & Format(lngSID(j)) & " Serial #: " & Format(lngSerial(j)) & " " & strSessionOther(j)
                    sglColor = Round((dblBackgroundElapsedS(j) - dblBackgroundElapsedSLast(j)) / (dblBackgroundElapsed - dblBackgroundElapsedLast) * 255)
                    If sglColor > 255 Then
                        sglColor = 255
                    Else
                        If sglColor < 0 Then
                            sglColor = 0
                        End If
                    End If
                    tvTimeModel.Nodes(tvTimeModel.Nodes.Count).BackColor = RGB(255, 255 - sglColor, 0)
                End If
            End If
        Next j
    End If

    strLine = Format(Format((dblBackgroundCPU - dblBackgroundCPULast) / 1000000, "0.00"), strAT) & " Background CPU Time"
    If (dblBackgroundElapsed - dblBackgroundElapsedLast) <> 0 Then
        strLine = strLine & " (" & Format((dblBackgroundCPU - dblBackgroundCPULast) / (dblBackgroundElapsed - dblBackgroundElapsedLast), "0.00%") & " of Background Time)"
    End If
    If dblBusyTimeSecondsDelta <> 0 Then
        strLine = strLine & " (" & Format((dblBackgroundCPU - dblBackgroundCPULast) / 1000000 / dblBusyTimeSecondsDelta, "0.00%") & " of Total Consumed Server CPU)"
    End If
    tvTimeModel.Nodes.Add "BackgroundElapsedTime", tvwChild, "BackgroundCPU", strLine
    If ((dblBackgroundCPU - dblBackgroundCPULast) <> 0) And (intDisplaySessionDetail = True) Then
        For j = 1 To intSessionCount
            If intSessionExists(j) = True Then
                If ((dblBackgroundCPUS(j) - dblBackgroundCPUSLast(j)) / (dblBackgroundCPU - dblBackgroundCPULast) >= sglSessionMinimumPercent) Then
                    tvTimeModel.Nodes.Add "BackgroundCPU", tvwChild, "SESSIONBackgroundCPU_" & Format(j), "-- " & Format(Format((dblBackgroundCPUS(j) - dblBackgroundCPUSLast(j)) / 1000000, "0.00"), strAT) & "  SID: " & Format(lngSID(j)) & " Serial #: " & Format(lngSerial(j)) & " " & strSessionOther(j)
                    sglColor = Round((dblBackgroundCPUS(j) - dblBackgroundCPUSLast(j)) / (dblBackgroundCPU - dblBackgroundCPULast) * 255)
                    If sglColor > 255 Then
                        sglColor = 255
                    Else
                        If sglColor < 0 Then
                            sglColor = 0
                        End If
                    End If
                    tvTimeModel.Nodes(tvTimeModel.Nodes.Count).BackColor = RGB(255, 255 - sglColor, 0)
                End If
            End If
        Next j
    End If

    strLine = Format(Format((dblRMANCPU - dblRMANCPULast) / 1000000, "0.00"), strAT) & " RMAN CPU Time (Backup/Restore)"
    If (dblBackgroundElapsed - dblBackgroundElapsedLast) <> 0 Then
        strLine = strLine & " (" & Format((dblRMANCPU - dblRMANCPULast) / (dblBackgroundElapsed - dblBackgroundElapsedLast), "0.00%") & " of Background Time)"
    End If
    If (dblBackgroundCPU - dblBackgroundCPULast) <> 0 Then
        strLine = strLine & " (" & Format((dblRMANCPU - dblRMANCPULast) / (dblBackgroundCPU - dblBackgroundCPULast), "0.00%") & " of Background CPU)"
    End If
    If dblBusyTimeSecondsDelta <> 0 Then
        strLine = strLine & " (" & Format((dblRMANCPU - dblRMANCPULast) / 1000000 / dblBusyTimeSecondsDelta, "0.00%") & " of Total Consumed Server CPU)"
    End If
    tvTimeModel.Nodes.Add "BackgroundCPU", tvwChild, "RMANCPU", strLine
    If ((dblRMANCPU - dblRMANCPULast) <> 0) And (intDisplaySessionDetail = True) Then
        For j = 1 To intSessionCount
            If intSessionExists(j) = True Then
                If ((dblRMANCPUS(j) - dblRMANCPUSLast(j)) / (dblRMANCPU - dblRMANCPULast) >= sglSessionMinimumPercent) Then
                    tvTimeModel.Nodes.Add "RMANCPU", tvwChild, "SESSIONRMANCPU_" & Format(j), "-- " & Format(Format((dblRMANCPUS(j) - dblRMANCPUSLast(j)) / 1000000, "0.00"), strAT) & "  SID: " & Format(lngSID(j)) & " Serial #: " & Format(lngSerial(j)) & " " & strSessionOther(j)
                    sglColor = Round((dblRMANCPUS(j) - dblRMANCPUSLast(j)) / (dblRMANCPU - dblRMANCPULast) * 255)
                    If sglColor > 255 Then
                        sglColor = 255
                    Else
                        If sglColor < 0 Then
                            sglColor = 0
                        End If
                    End If
                    tvTimeModel.Nodes(tvTimeModel.Nodes.Count).BackColor = RGB(255, 255 - sglColor, 0)
                End If
            End If
        Next j
    End If

    dblDBTimeDelta = dblDBTime - dblDBTimeLast
    tvTimeModel.Nodes.Add , , "DBTime", Format(Format((dblDBTime - dblDBTimeLast) / 1000000, "0.00"), strAT) & " DB Time"
    If ((dblDBTime - dblDBTimeLast) <> 0) And (intDisplaySessionDetail = True) Then
        For j = 1 To intSessionCount
            If intSessionExists(j) = True Then
                If ((dblDBTimeS(j) - dblDBTimeSLast(j)) / (dblDBTime - dblDBTimeLast) >= sglSessionMinimumPercent) Then
                    tvTimeModel.Nodes.Add "DBTime", tvwChild, "SESSIONDBTime_" & Format(j), "-- " & Format(Format((dblDBTimeS(j) - dblDBTimeSLast(j)) / 1000000, "0.00"), strAT) & "  SID: " & Format(lngSID(j)) & " Serial #: " & Format(lngSerial(j)) & " " & strSessionOther(j)
                    sglColor = Round((dblDBTimeS(j) - dblDBTimeSLast(j)) / (dblDBTime - dblDBTimeLast) * 255)
                    If sglColor > 255 Then
                        sglColor = 255
                    Else
                        If sglColor < 0 Then
                            sglColor = 0
                        End If
                    End If
                    tvTimeModel.Nodes(tvTimeModel.Nodes.Count).BackColor = RGB(255, 255 - sglColor, 0)
                End If
            End If
        Next j
    End If

    strLine = Format(Format((dblDBCPU - dblDBCPULast) / 1000000, "0.00"), strAT) & " DB CPU"
    If dblDBTimeDelta <> 0 Then
        strLine = strLine & " (" & Format((dblDBCPU - dblDBCPULast) / dblDBTimeDelta, "0.00%") & " of DB Time)"
    End If
    If dblBusyTimeSecondsDelta <> 0 Then
        strLine = strLine & " (" & Format((dblDBCPU - dblDBCPULast) / 1000000 / dblBusyTimeSecondsDelta, "0.00%") & " of Total Consumed Server CPU)"
    End If
    tvTimeModel.Nodes.Add "DBTime", tvwChild, "DBCPU", strLine
    If ((dblDBCPU - dblDBCPULast) <> 0) And (intDisplaySessionDetail = True) Then
        For j = 1 To intSessionCount
            If intSessionExists(j) = True Then
                If ((dblDBCPUS(j) - dblDBCPUSLast(j)) / (dblDBCPU - dblDBCPULast) >= sglSessionMinimumPercent) Then
                    tvTimeModel.Nodes.Add "DBCPU", tvwChild, "SESSIONDBCPU_" & Format(j), "-- " & Format(Format((dblDBCPUS(j) - dblDBCPUSLast(j)) / 1000000, "0.00"), strAT) & "  SID: " & Format(lngSID(j)) & " Serial #: " & Format(lngSerial(j)) & " " & strSessionOther(j)
                    sglColor = Round((dblDBCPUS(j) - dblDBCPUSLast(j)) / (dblDBCPU - dblDBCPULast) * 255)
                    If sglColor > 255 Then
                        sglColor = 255
                    Else
                        If sglColor < 0 Then
                            sglColor = 0
                        End If
                    End If
                    tvTimeModel.Nodes(tvTimeModel.Nodes.Count).BackColor = RGB(255, 255 - sglColor, 0)
                End If
            End If
        Next j
    End If

    strLine = Format(Format((dblConnectMgmt - dblConnectMgmtLast) / 1000000, "0.00"), strAT) & " Connection Management Call Elapsed Time"
    If dblDBTimeDelta <> 0 Then
        strLine = strLine & " (" & Format((dblConnectMgmt - dblConnectMgmtLast) / dblDBTimeDelta, "0.00%") & " of DB Time)"
    End If
    tvTimeModel.Nodes.Add "DBTime", tvwChild, "ConnectionManagementCallElapsedTime", strLine
    If ((dblConnectMgmt - dblConnectMgmtLast) <> 0) And (intDisplaySessionDetail = True) Then
        For j = 1 To intSessionCount
            If intSessionExists(j) = True Then
                If ((dblConnectMgmtS(j) - dblConnectMgmtSLast(j)) / (dblConnectMgmt - dblConnectMgmtLast) >= sglSessionMinimumPercent) Then
                    tvTimeModel.Nodes.Add "ConnectionManagementCallElapsedTime", tvwChild, "SESSIONConMgm_" & Format(j), "-- " & Format(Format((dblConnectMgmtS(j) - dblConnectMgmtSLast(j)) / 1000000, "0.00"), strAT) & "  SID: " & Format(lngSID(j)) & " Serial #: " & Format(lngSerial(j)) & " " & strSessionOther(j)
                    sglColor = Round((dblConnectMgmtS(j) - dblConnectMgmtSLast(j)) / (dblConnectMgmt - dblConnectMgmtLast) * 255)
                    If sglColor > 255 Then
                        sglColor = 255
                    Else
                        If sglColor < 0 Then
                            sglColor = 0
                        End If
                    End If
                    tvTimeModel.Nodes(tvTimeModel.Nodes.Count).BackColor = RGB(255, 255 - sglColor, 0)
                End If
            End If
        Next j
    End If

    strLine = Format(Format((dblSequenceLoad - dblSequenceLoadLast) / 1000000, "0.00"), strAT) & " Sequence Load Elapsed Time"
    If dblDBTimeDelta <> 0 Then
        strLine = strLine & " (" & Format((dblSequenceLoad - dblSequenceLoadLast) / dblDBTimeDelta, "0.00%") & " of DB Time)"
    End If
    tvTimeModel.Nodes.Add "DBTime", tvwChild, "SequenceLoadElapsedTime", strLine
    If ((dblSequenceLoad - dblSequenceLoadLast) <> 0) And (intDisplaySessionDetail = True) Then
        For j = 1 To intSessionCount
            If intSessionExists(j) = True Then
                If ((dblSequenceLoadS(j) - dblSequenceLoadSLast(j)) / (dblSequenceLoad - dblSequenceLoadLast) >= sglSessionMinimumPercent) Then
                    tvTimeModel.Nodes.Add "SequenceLoadElapsedTime", tvwChild, "SESSIONSeqLoad_" & Format(j), "-- " & Format(Format((dblSequenceLoadS(j) - dblSequenceLoadSLast(j)) / 1000000, "0.00"), strAT) & "  SID: " & Format(lngSID(j)) & " Serial #: " & Format(lngSerial(j)) & " " & strSessionOther(j)
                    sglColor = Round((dblSequenceLoadS(j) - dblSequenceLoadSLast(j)) / (dblSequenceLoad - dblSequenceLoadLast) * 255)
                    If sglColor > 255 Then
                        sglColor = 255
                    Else
                        If sglColor < 0 Then
                            sglColor = 0
                        End If
                    End If
                    tvTimeModel.Nodes(tvTimeModel.Nodes.Count).BackColor = RGB(255, 255 - sglColor, 0)
                End If
            End If
        Next j
    End If

    strLine = Format(Format((dblSQLExecuteTime - dblSQLExecuteTimeLast) / 1000000, "0.00"), strAT) & " SQL Execute Elapsed Time"
    If dblDBTimeDelta <> 0 Then
        strLine = strLine & " (" & Format((dblSQLExecuteTime - dblSQLExecuteTimeLast) / dblDBTimeDelta, "0.00%") & " of DB Time)"
    End If
    tvTimeModel.Nodes.Add "DBTime", tvwChild, "SQLExecuteElapsedTime", strLine
    If ((dblSQLExecuteTime - dblSQLExecuteTimeLast) <> 0) And (intDisplaySessionDetail = True) Then
        For j = 1 To intSessionCount
            If intSessionExists(j) = True Then
                If ((dblSQLExecuteTimeS(j) - dblSQLExecuteTimeSLast(j)) / (dblSQLExecuteTime - dblSQLExecuteTimeLast) >= sglSessionMinimumPercent) Then
                    tvTimeModel.Nodes.Add "SQLExecuteElapsedTime", tvwChild, "SESSIONSQLExec_" & Format(j), "-- " & Format(Format((dblSQLExecuteTimeS(j) - dblSQLExecuteTimeSLast(j)) / 1000000, "0.00"), strAT) & "  SID: " & Format(lngSID(j)) & " Serial #: " & Format(lngSerial(j)) & " " & strSessionOther(j)
                    sglColor = Round((dblSQLExecuteTimeS(j) - dblSQLExecuteTimeSLast(j)) / (dblSQLExecuteTime - dblSQLExecuteTimeLast) * 255)
                    If sglColor > 255 Then
                        sglColor = 255
                    Else
                        If sglColor < 0 Then
                            sglColor = 0
                        End If
                    End If
                    tvTimeModel.Nodes(tvTimeModel.Nodes.Count).BackColor = RGB(255, 255 - sglColor, 0)
                End If
            End If
        Next j
    End If

    strLine = Format(Format((dblParseTimeElapsed - dblParseTimeElapsedLast) / 1000000, "0.00"), strAT) & " Parse Time Elapsed"
    If dblDBTimeDelta <> 0 Then
        strLine = strLine & " (" & Format((dblParseTimeElapsed - dblParseTimeElapsedLast) / dblDBTimeDelta, "0.00%") & " of DB Time)"
    End If
    If (lngParseCountTotal - lngParseCountTotalLast) <> 0 Then
        strLine = strLine & " (" & Format(lngParseCountTotal - lngParseCountTotalLast) & " Total Parses, " & Format(lngParseCountHard - lngParseCountHardLast) & " Hard Parses, " & Format((lngParseCountTotal - lngParseCountTotalLast) - (lngParseCountHard - lngParseCountHardLast)) & " Soft Parses with " & (lngSessionCacheHits - lngSessionCacheHitsLast) & " Session Cache Cursor Hits, " & Format(lngParseCountFailures - lngParseCountFailuresLast) & " Failed Parses)"
    End If
    tvTimeModel.Nodes.Add "DBTime", tvwChild, "ParseTimeElapsed", strLine
    If ((dblParseTimeElapsed - dblParseTimeElapsedLast) <> 0) And (intDisplaySessionDetail = True) Then
        For j = 1 To intSessionCount
            If intSessionExists(j) = True Then
                If ((dblParseTimeElapsedS(j) - dblParseTimeElapsedSLast(j)) / (dblParseTimeElapsed - dblParseTimeElapsedLast) >= sglSessionMinimumPercent) Then
                    tvTimeModel.Nodes.Add "ParseTimeElapsed", tvwChild, "SESSIONParseTimeElapsed_" & Format(j), "-- " & Format(Format((dblParseTimeElapsedS(j) - dblParseTimeElapsedSLast(j)) / 1000000, "0.00"), strAT) & "  SID: " & Format(lngSID(j)) & " Serial #: " & Format(lngSerial(j)) & " " & strSessionOther(j)
                    sglColor = Round((dblParseTimeElapsedS(j) - dblParseTimeElapsedSLast(j)) / (dblParseTimeElapsed - dblParseTimeElapsedLast) * 255)
                    If sglColor > 255 Then
                        sglColor = 255
                    Else
                        If sglColor < 0 Then
                            sglColor = 0
                        End If
                    End If
                    tvTimeModel.Nodes(tvTimeModel.Nodes.Count).BackColor = RGB(255, 255 - sglColor, 0)
                End If
            End If
        Next j
    End If

    strLine = Format(Format((dblHardParseElapsed - dblHardParseElapsedLast) / 1000000, "0.00"), strAT) & " Hard Parse Elapsed Time"
    If dblDBTimeDelta <> 0 Then
        strLine = strLine & " (" & Format((dblHardParseElapsed - dblHardParseElapsedLast) / dblDBTimeDelta, "0.00%") & " of DB Time)"
    End If
    If (dblParseTimeElapsed - dblParseTimeElapsedLast) <> 0 Then
        strLine = strLine & " (" & Format((dblHardParseElapsed - dblHardParseElapsedLast) / (dblParseTimeElapsed - dblParseTimeElapsedLast), "0.00%") & " of Parse Time)"
    End If
    If ((lngParseCountHard - lngParseCountHardLast) <> 0) And ((lngParseCountTotal - lngParseCountTotalLast) <> 0) Then
        strLine = strLine & " (" & Format((lngParseCountHard - lngParseCountHardLast) / (lngParseCountTotal - lngParseCountTotalLast), "0.00%") & " of All Parses are Hard Parses)"
    End If

    tvTimeModel.Nodes.Add "ParseTimeElapsed", tvwChild, "HardParseElapsedTime", strLine
    If ((dblHardParseElapsed - dblHardParseElapsedLast) <> 0) And (intDisplaySessionDetail = True) Then
        For j = 1 To intSessionCount
            If intSessionExists(j) = True Then
                If ((dblHardParseElapsedS(j) - dblHardParseElapsedSLast(j)) / (dblHardParseElapsed - dblHardParseElapsedLast) >= sglSessionMinimumPercent) Then
                    tvTimeModel.Nodes.Add "HardParseElapsedTime", tvwChild, "SESSIONHardParseTimeElapsed_" & Format(j), "-- " & Format(Format((dblHardParseElapsedS(j) - dblHardParseElapsedSLast(j)) / 1000000, "0.00"), strAT) & "  SID: " & Format(lngSID(j)) & " Serial #: " & Format(lngSerial(j)) & " " & strSessionOther(j)
                    sglColor = Round((dblHardParseElapsedS(j) - dblHardParseElapsedSLast(j)) / (dblHardParseElapsed - dblHardParseElapsedLast) * 255)
                    If sglColor > 255 Then
                        sglColor = 255
                    Else
                        If sglColor < 0 Then
                            sglColor = 0
                        End If
                    End If
                    tvTimeModel.Nodes(tvTimeModel.Nodes.Count).BackColor = RGB(255, 255 - sglColor, 0)
                End If
            End If
        Next j
    End If

    strLine = Format(Format((dblHardParseSharing - dblHardParseSharingLast) / 1000000, "0.00"), strAT) & " Hard Parse (Sharing Criteria) Elapsed Time"
    If dblDBTimeDelta <> 0 Then
        strLine = strLine & " (" & Format((dblHardParseSharing - dblHardParseSharingLast) / dblDBTimeDelta, "0.00%") & " of DB Time)"
    End If
    If (dblParseTimeElapsed - dblParseTimeElapsedLast) <> 0 Then
        strLine = strLine & " (" & Format((dblHardParseSharing - dblHardParseSharingLast) / (dblParseTimeElapsed - dblParseTimeElapsedLast), "0.00%") & " of Parse Time)"
    End If
    If (dblHardParseElapsed - dblHardParseElapsedLast) <> 0 Then
        strLine = strLine & " (" & Format((dblHardParseSharing - dblHardParseSharingLast) / (dblHardParseElapsed - dblHardParseElapsedLast), "0.00%") & " of Hard Parse Time)"
    End If
    tvTimeModel.Nodes.Add "HardParseElapsedTime", tvwChild, "HardParseSCElapsedTime", strLine
    If ((dblHardParseSharing - dblHardParseSharingLast) <> 0) And (intDisplaySessionDetail = True) Then
        For j = 1 To intSessionCount
            If intSessionExists(j) = True Then
                If ((dblHardParseSharingS(j) - dblHardParseSharingSLast(j)) / (dblHardParseSharing - dblHardParseSharingLast) >= sglSessionMinimumPercent) Then
                    tvTimeModel.Nodes.Add "HardParseSCElapsedTime", tvwChild, "SESSIONHardParseSCEElapsedTime_" & Format(j), "-- " & Format(Format((dblHardParseSharingS(j) - dblHardParseSharingSLast(j)) / 1000000, "0.00"), strAT) & "  SID: " & Format(lngSID(j)) & " Serial #: " & Format(lngSerial(j)) & " " & strSessionOther(j)
                    sglColor = Round((dblHardParseSharingS(j) - dblHardParseSharingSLast(j)) / (dblHardParseSharing - dblHardParseSharingLast) * 255)
                    If sglColor > 255 Then
                        sglColor = 255
                    Else
                        If sglColor < 0 Then
                            sglColor = 0
                        End If
                    End If
                    tvTimeModel.Nodes(tvTimeModel.Nodes.Count).BackColor = RGB(255, 255 - sglColor, 0)
                End If
            End If
        Next j
    End If

    strLine = Format(Format((dblHardParseBind - dblHardParseBindLast) / 1000000, "0.00"), strAT) & " Hard Parse (Bind Mismatch) Elapsed Time"
    If dblDBTimeDelta <> 0 Then
        strLine = strLine & " (" & Format((dblHardParseBind - dblHardParseBindLast) / dblDBTimeDelta, "0.00%") & " of DB Time)"
    End If
    If (dblParseTimeElapsed - dblParseTimeElapsedLast) <> 0 Then
        strLine = strLine & " (" & Format((dblHardParseBind - dblHardParseBindLast) / (dblParseTimeElapsed - dblParseTimeElapsedLast), "0.00%") & " of Parse Time)"
    End If
    If (dblHardParseElapsed - dblHardParseElapsedLast) <> 0 Then
        strLine = strLine & " (" & Format((dblHardParseBind - dblHardParseBindLast) / (dblHardParseElapsed - dblHardParseElapsedLast), "0.00%") & " of Hard Parse Time)"
    End If
    If (dblHardParseSharing - dblHardParseSharingLast) <> 0 Then
        strLine = strLine & " (" & Format((dblHardParseBind - dblHardParseBindLast) / (dblHardParseSharing - dblHardParseSharingLast), "0.00%") & " of Sharing Criteria Time)"
    End If
    tvTimeModel.Nodes.Add "HardParseSCElapsedTime", tvwChild, "HardParseBMElapsedTime", strLine
    If ((dblHardParseBind - dblHardParseBindLast) <> 0) And (intDisplaySessionDetail = True) Then
        For j = 1 To intSessionCount
            If intSessionExists(j) = True Then
                If ((dblHardParseBindS(j) - dblHardParseBindSLast(j)) / (dblHardParseBind - dblHardParseBindLast) >= sglSessionMinimumPercent) Then
                    tvTimeModel.Nodes.Add "HardParseBMElapsedTime", tvwChild, "SESSIONHardParseBMElapsedTime_" & Format(j), "-- " & Format(Format((dblHardParseBindS(j) - dblHardParseBindSLast(j)) / 1000000, "0.00"), strAT) & "  SID: " & Format(lngSID(j)) & " Serial #: " & Format(lngSerial(j)) & " " & strSessionOther(j)
                    sglColor = Round((dblHardParseBindS(j) - dblHardParseBindSLast(j)) / (dblHardParseBind - dblHardParseBindLast) * 255)
                    If sglColor > 255 Then
                        sglColor = 255
                    Else
                        If sglColor < 0 Then
                            sglColor = 0
                        End If
                    End If
                    tvTimeModel.Nodes(tvTimeModel.Nodes.Count).BackColor = RGB(255, 255 - sglColor, 0)
                End If
            End If
        Next j
    End If

    strLine = Format(Format((dblFailedParseElapsed - dblFailedParseElapsedLast) / 1000000, "0.00"), strAT) & " Failed Parse Elapsed Time"
    If dblDBTimeDelta <> 0 Then
        strLine = strLine & " (" & Format((dblFailedParseElapsed - dblFailedParseElapsedLast) / dblDBTimeDelta, "0.00%") & " of DB Time)"
    End If
    If (dblParseTimeElapsed - dblParseTimeElapsedLast) <> 0 Then
        strLine = strLine & " (" & Format((dblFailedParseElapsed - dblFailedParseElapsedLast) / (dblParseTimeElapsed - dblParseTimeElapsedLast), "0.00%") & " of Parse Time)"
    End If
    If ((lngParseCountFailures - lngParseCountFailuresLast) <> 0) And ((lngParseCountTotal - lngParseCountTotalLast) <> 0) Then
        strLine = strLine & " (" & Format((lngParseCountFailures - lngParseCountFailuresLast) / (lngParseCountTotal - lngParseCountTotalLast), "0.00%") & " of All Parses Failed)"
    End If
    tvTimeModel.Nodes.Add "ParseTimeElapsed", tvwChild, "FailedParseElapsedTime", strLine
    If ((dblFailedParseElapsed - dblFailedParseElapsedLast) <> 0) And (intDisplaySessionDetail = True) Then
        For j = 1 To intSessionCount
            If intSessionExists(j) = True Then
                If ((dblFailedParseElapsedS(j) - dblFailedParseElapsedSLast(j)) / (dblFailedParseElapsed - dblFailedParseElapsedLast) >= sglSessionMinimumPercent) Then
                    tvTimeModel.Nodes.Add "FailedParseElapsedTime", tvwChild, "SESSIONFailedParseElapsedTime_" & Format(j), "-- " & Format(Format((dblFailedParseElapsedS(j) - dblFailedParseElapsedSLast(j)) / 1000000, "0.00"), strAT) & "  SID: " & Format(lngSID(j)) & " Serial #: " & Format(lngSerial(j)) & " " & strSessionOther(j)
                    sglColor = Round((dblFailedParseElapsedS(j) - dblFailedParseElapsedSLast(j)) / (dblFailedParseElapsed - dblFailedParseElapsedLast) * 255)
                    If sglColor > 255 Then
                        sglColor = 255
                    Else
                        If sglColor < 0 Then
                            sglColor = 0
                        End If
                    End If
                    tvTimeModel.Nodes(tvTimeModel.Nodes.Count).BackColor = RGB(255, 255 - sglColor, 0)
                End If
            End If
        Next j
    End If

    strLine = Format(Format((dblFailedParseMemory - dblFailedParseMemoryLast) / 1000000, "0.00"), strAT) & " Failed Parse (Out of Shared Memory) Elapsed Time"
    If dblDBTimeDelta <> 0 Then
        strLine = strLine & " (" & Format((dblFailedParseMemory - dblFailedParseMemoryLast) / dblDBTimeDelta, "0.00%") & " of DB Time)"
    End If
    If (dblParseTimeElapsed - dblParseTimeElapsedLast) <> 0 Then
        strLine = strLine & " (" & Format((dblFailedParseMemory - dblFailedParseMemoryLast) / (dblParseTimeElapsed - dblParseTimeElapsedLast), "0.00%") & " of Parse Time)"
    End If
    If (dblFailedParseElapsed - dblFailedParseElapsedLast) <> 0 Then
        strLine = strLine & " (" & Format((dblFailedParseMemory - dblFailedParseMemoryLast) / (dblFailedParseElapsed - dblFailedParseElapsedLast), "0.00%") & " of Failed Parse Time)"
    End If
    tvTimeModel.Nodes.Add "FailedParseElapsedTime", tvwChild, "FailedParseOutofSharedMemory", strLine
    If ((dblFailedParseMemory - dblFailedParseMemoryLast) <> 0) And (intDisplaySessionDetail = True) Then
        For j = 1 To intSessionCount
            If intSessionExists(j) = True Then
                If ((dblFailedParseMemoryS(j) - dblFailedParseMemorySLast(j)) / (dblFailedParseMemory - dblFailedParseMemoryLast) >= sglSessionMinimumPercent) Then
                    tvTimeModel.Nodes.Add "FailedParseOutofSharedMemory", tvwChild, "SESSIONFailedParseMemory_" & Format(j), "-- " & Format(Format((dblFailedParseMemoryS(j) - dblFailedParseMemorySLast(j)) / 1000000, "0.00"), strAT) & "  SID: " & Format(lngSID(j)) & " Serial #: " & Format(lngSerial(j)) & " " & strSessionOther(j)
                    sglColor = Round((dblFailedParseMemoryS(j) - dblFailedParseMemorySLast(j)) / (dblFailedParseMemory - dblFailedParseMemoryLast) * 255)
                    If sglColor > 255 Then
                        sglColor = 255
                    Else
                        If sglColor < 0 Then
                            sglColor = 0
                        End If
                    End If
                    tvTimeModel.Nodes(tvTimeModel.Nodes.Count).BackColor = RGB(255, 255 - sglColor, 0)
                End If
            End If
        Next j
    End If

    strLine = Format(Format((dblPLSQLExecution - dblPLSQLExecutionLast) / 1000000, "0.00"), strAT) & " PL/SQL Execution Elapsed Time"
    If dblDBTimeDelta <> 0 Then
        strLine = strLine & " (" & Format((dblPLSQLExecution - dblPLSQLExecutionLast) / dblDBTimeDelta, "0.00%") & " of DB Time)"
    End If
    tvTimeModel.Nodes.Add "DBTime", tvwChild, "PLSQLExecutionElapsedTime", strLine
    If ((dblPLSQLExecution - dblPLSQLExecutionLast) <> 0) And (intDisplaySessionDetail = True) Then
        For j = 1 To intSessionCount
            If intSessionExists(j) = True Then
                If ((dblPLSQLExecutionS(j) - dblPLSQLExecutionSLast(j)) / (dblPLSQLExecution - dblPLSQLExecutionLast) >= sglSessionMinimumPercent) Then
                    tvTimeModel.Nodes.Add "PLSQLExecutionElapsedTime", tvwChild, "SESSIONPLSQLExecutionElapsedTime_" & Format(j), "-- " & Format(Format((dblPLSQLExecutionS(j) - dblPLSQLExecutionSLast(j)) / 1000000, "0.00"), strAT) & "  SID: " & Format(lngSID(j)) & " Serial #: " & Format(lngSerial(j)) & " " & strSessionOther(j)
                    sglColor = Round((dblPLSQLExecutionS(j) - dblPLSQLExecutionSLast(j)) / (dblPLSQLExecution - dblPLSQLExecutionLast) * 255)
                    If sglColor > 255 Then
                        sglColor = 255
                    Else
                        If sglColor < 0 Then
                            sglColor = 0
                        End If
                    End If
                    tvTimeModel.Nodes(tvTimeModel.Nodes.Count).BackColor = RGB(255, 255 - sglColor, 0)
                End If
            End If
        Next j
    End If

    strLine = Format(Format((dblInboundPLSQL - dblInboundPLSQLLast) / 1000000, "0.00"), strAT) & " Inbound PL/SQL RPC Elapsed Time"
    If dblDBTimeDelta <> 0 Then
        strLine = strLine & " (" & Format((dblInboundPLSQL - dblInboundPLSQLLast) / dblDBTimeDelta, "0.00%") & " of DB Time)"
    End If
    tvTimeModel.Nodes.Add "DBTime", tvwChild, "InboundPLSQLRPCElapsedTime", strLine
    If ((dblInboundPLSQL - dblInboundPLSQLLast) <> 0) And (intDisplaySessionDetail = True) Then
        For j = 1 To intSessionCount
            If intSessionExists(j) = True Then
                If ((dblInboundPLSQLS(j) - dblInboundPLSQLSLast(j)) / (dblInboundPLSQL - dblInboundPLSQLLast) >= sglSessionMinimumPercent) Then
                    tvTimeModel.Nodes.Add "InboundPLSQLRPCElapsedTime", tvwChild, "SESSIONInboundPLSQLRPCElapsedTime_" & Format(j), "-- " & Format(Format((dblInboundPLSQLS(j) - dblInboundPLSQLSLast(j)) / 1000000, "0.00"), strAT) & "  SID: " & Format(lngSID(j)) & " Serial #: " & Format(lngSerial(j)) & " " & strSessionOther(j)
                    sglColor = Round((dblInboundPLSQLS(j) - dblInboundPLSQLSLast(j)) / (dblInboundPLSQL - dblInboundPLSQLLast) * 255)
                    If sglColor > 255 Then
                        sglColor = 255
                    Else
                        If sglColor < 0 Then
                            sglColor = 0
                        End If
                    End If
                    tvTimeModel.Nodes(tvTimeModel.Nodes.Count).BackColor = RGB(255, 255 - sglColor, 0)
                End If
            End If
        Next j
    End If

    strLine = Format(Format((dblPLSQLCompile - dblPLSQLCompileLast) / 1000000, "0.00"), strAT) & " PL/SQL Compilation Elapsed Time"
    If dblDBTimeDelta <> 0 Then
        strLine = strLine & " (" & Format((dblPLSQLCompile - dblPLSQLCompileLast) / dblDBTimeDelta, "0.00%") & " of DB Time)"
    End If
    tvTimeModel.Nodes.Add "DBTime", tvwChild, "PLSQLCompilationElapsedTime", strLine
    If ((dblPLSQLCompile - dblPLSQLCompileLast) <> 0) And (intDisplaySessionDetail = True) Then
        For j = 1 To intSessionCount
            If intSessionExists(j) = True Then
                If ((dblPLSQLCompileS(j) - dblPLSQLCompileSLast(j)) / (dblPLSQLCompile - dblPLSQLCompileLast) >= sglSessionMinimumPercent) Then
                    tvTimeModel.Nodes.Add "PLSQLCompilationElapsedTime", tvwChild, "SESSIONPLSQLCompilationElapsedTime_" & Format(j), "-- " & Format(Format((dblPLSQLCompileS(j) - dblPLSQLCompileSLast(j)) / 1000000, "0.00"), strAT) & "  SID: " & Format(lngSID(j)) & " Serial #: " & Format(lngSerial(j)) & " " & strSessionOther(j)
                    sglColor = Round((dblPLSQLCompileS(j) - dblPLSQLCompileSLast(j)) / (dblPLSQLCompile - dblPLSQLCompileLast) * 255)
                    If sglColor > 255 Then
                        sglColor = 255
                    Else
                        If sglColor < 0 Then
                            sglColor = 0
                        End If
                    End If
                    tvTimeModel.Nodes(tvTimeModel.Nodes.Count).BackColor = RGB(255, 255 - sglColor, 0)
                End If
            End If
        Next j
    End If

    strLine = Format(Format((dblJavaTime - dblJavaTimeLast) / 1000000, "0.00"), strAT) & " Java Execution Elapsed Time"
    If dblDBTimeDelta <> 0 Then
        strLine = strLine & " (" & Format((dblJavaTime - dblJavaTimeLast) / dblDBTimeDelta, "0.00%") & " of DB Time)"
    End If
    tvTimeModel.Nodes.Add "DBTime", tvwChild, "JavaExecutionElapsedTime", strLine
    If ((dblJavaTime - dblJavaTimeLast) <> 0) And (intDisplaySessionDetail = True) Then
        For j = 1 To intSessionCount
            If intSessionExists(j) = True Then
                If ((dblJavaTimeS(j) - dblJavaTimeSLast(j)) / (dblJavaTime - dblJavaTimeLast) >= sglSessionMinimumPercent) Then
                    tvTimeModel.Nodes.Add "JavaExecutionElapsedTime", tvwChild, "SESSIONJavaExecutionElapsedTime_" & Format(j), "-- " & Format(Format((dblJavaTimeS(j) - dblJavaTimeSLast(j)) / 1000000, "0.00"), strAT) & "  SID: " & Format(lngSID(j)) & " Serial #: " & Format(lngSerial(j)) & " " & strSessionOther(j)
                    sglColor = Round((dblJavaTimeS(j) - dblJavaTimeSLast(j)) / (dblJavaTime - dblJavaTimeLast) * 255)
                    If sglColor > 255 Then
                        sglColor = 255
                    Else
                        If sglColor < 0 Then
                            sglColor = 0
                        End If
                    End If
                    tvTimeModel.Nodes(tvTimeModel.Nodes.Count).BackColor = RGB(255, 255 - sglColor, 0)
                End If
            End If
        Next j
    End If

    strLine = Format(Format((dblRepeatedBind - dblRepeatedBindLast) / 1000000, "0.00"), strAT) & " Repeated Bind Elapsed Time"
    If dblDBTimeDelta <> 0 Then
        strLine = strLine & " (" & Format((dblRepeatedBind - dblRepeatedBindLast) / dblDBTimeDelta, "0.00%") & " of DB Time)"
    End If
    tvTimeModel.Nodes.Add "DBTime", tvwChild, "RepeatedBindElapsedTime", strLine
    If ((dblRepeatedBind - dblRepeatedBindLast) <> 0) And (intDisplaySessionDetail = True) Then
        For j = 1 To intSessionCount
            If intSessionExists(j) = True Then
                If ((dblRepeatedBindS(j) - dblRepeatedBindSLast(j)) / (dblRepeatedBind - dblRepeatedBindLast) >= sglSessionMinimumPercent) Then
                    tvTimeModel.Nodes.Add "RepeatedBindElapsedTime", tvwChild, "SESSIONRepeatedBindElapsedTime_" & Format(j), "-- " & Format(Format((dblRepeatedBindS(j) - dblRepeatedBindSLast(j)) / 1000000, "0.00"), strAT) & "  SID: " & Format(lngSID(j)) & " Serial #: " & Format(lngSerial(j)) & " " & strSessionOther(j)
                    sglColor = 255 - Round((dblRepeatedBindS(j) - dblRepeatedBindSLast(j)) / (dblRepeatedBind - dblRepeatedBindLast) * 255)
                    If sglColor > 255 Then
                        sglColor = 255
                    Else
                        If sglColor < 0 Then
                            sglColor = 0
                        End If
                    End If
                    tvTimeModel.Nodes(tvTimeModel.Nodes.Count).BackColor = RGB(255, sglColor, 0)
                End If
            End If
        Next j
    End If

    For j = 1 To tvTimeModel.Nodes.Count
        'Force all of the nodes to appear expanded
         tvTimeModel.Nodes(j).Expanded = True
    Next j
    tvTimeModel.Nodes(1).Selected = True

    'tvTimeModel.Visible = True

    intCurrentSessionIndex = -1
    intActivated = True
End Sub 

We are almost ready to take the code for a test drive.  First, we need to make a couple of changes to the code in the TimerEvent subroutine.  That subroutine is found in Module1 under the Modules heading (which is below the Microsoft Excel Objects heading).  Double-click Module1 to display the code in that module.  Change the TimerEvent subroutine to look like this:

Public Sub TimerEvent()
    lngTimerEventCounter = lngTimerEventCounter + 1

    If intKillFlag = False Then
        If lngTimerTriggerSeconds <= lngTimerEventCounter Then
            lngTimerEventCounter = 0
            frmTimeModel.ReadData
            frmTimeModel.UpdateDisplay
        End If

        'Instruct Excel to execute the TimerEvent sub again in 1 second
        Application.OnTime DateAdd("s", 1, Now), "TimerEvent"
    End If
End Sub 

The above code tells Excel to execute the ReadData subroutine and then the UpdateData subroutine every time the number of elapsed seconds is equal to the value specified by the lngTimerTriggerSeconds variable, which is set to 60 by default in the code.

Save the Excel workbook, close the workbook, and then open the workbook again.  If everything works right, after about 60 seconds you should see the time model statistics for the previous 60 seconds, something like this:

Wow, that is a lot of code for what little is shown in the above screen capture.  We will continue from here in the next segment of the series.

—-

Added March 2, 2011:

The Excel project code to this point, save with a .XLS extension (currently has a .DOC extension, please change):
TimeModelViewerExcelArticle2.xls








Follow

Get every new post delivered to your Inbox.

Join 142 other followers