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