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 
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):
intDisplaySessionDetail = TrueNow 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.