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
This is a great example, thanks for sharing and inspire!
Any successes making this project work?
While you are waiting for part three of the series, I thought that I would point out a couple of interesting things to keep you entertained.
First, I mentioned at the end of this blog article that there is a lot of code for what is displayed on the screen – that was intended as a hint. Find the following line of code in the project:
Change that line to show the following (we will need to change this back later):
Now what happens when the UserForm is displayed?
Second, updating the TreeView control can be a slow process, so it is often suggested to set the Visible property of that control to False before updating the control, and then set that property to True when finished updating the control. Locate the following two lines in the code (the two lines are not adjacent to one another):
After making certain that the Excel workbook is saved, remove the single quote (‘) character that is in front of each of those lines. Display the UserForm and let it update for about five minutes – speed kills?
Third, take a look at the screen capture that is at the end of this article. Is there anything suspicious about the information that appears in the screen capture?
Fourth, take a close look at the first screen capture in the first article of this series. Is there anything interesting about the information that appears in the screen capture – compare the first screen capture to the other three screen captures from my program.