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 & "  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"))

    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

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