Oracle Database Time Model Viewer in Excel 2

1 03 2011

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


Actions

Information

2 responses

1 03 2011
Ignacio Ruiz

This is a great example, thanks for sharing and inspire!

1 03 2011
Charles Hooper

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:

'        intDisplaySessionDetail = True

Change that line to show the following (we will need to change this back later):

        intDisplaySessionDetail = True

Now what happens when the UserForm is displayed?

Second, updating the TreeView control can be a slow process, so it is often suggested to set the Visible property of that control to False before updating the control, and then set that property to True when finished updating the control. Locate the following two lines in the code (the two lines are not adjacent to one another):

'tvTimeModel.Visible = False

'tvTimeModel.Visible = True

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 139 other followers

%d bloggers like this: