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
Charles Hooper's Oracle Notes 
Recent Comments