Working with Oracle’s Time Model Data 2

14 01 2010

January 14, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

So, how is it possible to transform this:

Into something like this:

A fairly long VBS script connects to the Oracle database, performs a lot of calculations on the data returned from the database, and then outputs the formatted result to a web page in Internet Explorer.  The VBS script continues to control the web page once the page is built, automatically refreshing the web page after a specified number of seconds, and responding to button clicks on the web page.  Easy, right?  Because this is done using a VBS script, the client computer must be running on Windows, while the server may run Unix, Linux, or Windows (I suggest not running this script directly on the server, instead run it from another computer).  The Show Detail button acts like a toggle to either show or hide the session details (on the yellow lines) that contributed to the system-wide statistic values (by default, the sessions must contribute to at least 10% of the total to be included in the session-level output).  Clicking the Re-Query button causes the script to update the page with the latest statistic delta values prior to the automatic refresh timer expiring.

So, where is the code?  Note that there may be bugs in the code – don’t step on them.  Also, the code is mostly written in a very verbose syntax so that it is easy to follow along with the script logic.

Const adCmdText = 1

Dim i
Dim j
Dim k
Dim strSQL
Dim strUsername
Dim strPassword
Dim strDatabase
Dim intCheckIterations              'Number of times to check the instances
Dim intDelayIterations              'Number of seconds to delay between iterations
Dim sglSessionMinimumPercent        'Minimum percent of the total required for the session to be included in the report detail
Dim dteLastLoopStart                'Time of the last loop start
Dim intDataChanged                  'Indicates whether or not the data to be displayed on the web page has changed
Dim intDisplaySessionDetail         'Indicates whether or not to display the session level detail
Dim snpDataWait                     'ADO recordset used to query V$SYSTEM_EVENT
Dim comDataWait                     'ADO command object used to retrieve data from V$SYSTEM_EVENT
Dim snpDataOSStat                   'ADO recordset used to query V$OSSTAT
Dim comDataOSStat                   'ADO command object used to retrieve data from V$OSSTAT
Dim snpDataSysTime                  'ADO recordset used to query V$SYS_TIME_MODEL
Dim comDataSysTime                  'ADO command object used to retrieve from V$SYS_TIME_MODEL
Dim snpDataSessTime                 'ADO recordset used to query V$SESS_TIME_MODEL
Dim comDataSessTime                 'ADO command object used to retrieve from V$SESS_TIME_MODEL
Dim dbDatabase                      'ADO database connection object

Dim strHTML                         'The raw HTML for the web page
Dim objIE                           'The Internet Explorer object
Dim strInd                          'Indent characters for the table
Dim intFlag                         'Loop control variable, allow to jump out of the loop early

Dim intNumCPUs                      'Number of CPUs
Dim dblIdleTime                     'Current value of idle time from V$OSSTAT
Dim dblBusyTime                     'Current value of busy time from V$OSSTAT
Dim dblUserTime                     'Current value of user time from V$OSSTAT
Dim dblSysTime                      'Current value of system/kernel mode time from V$OSSTAT
Dim dblIdleTimeLast                 'Previous value of idle time from V$OSSTAT
Dim dblBusyTimeLast                 'Previous value of busy time from V$OSSTAT
Dim dblUserTimeLast                 'Previous value of user time from V$OSSTAT
Dim dblSysTimeLast                  'Previous value of system/kernel mode time from V$OSSTAT

Dim dblDBCPU                        'Current value of DB CPU from V$SYS_TIME_MODEL
Dim dblDBTime                       'Current value of DB time from V$SYS_TIME_MODEL
Dim dblJavaTime                     'Current value of Java execution elapsed time from V$SYS_TIME_MODEL
Dim dblPLSQLCompile                 'Current value of PL/SQL compilation elapsed time from V$SYS_TIME_MODEL
Dim dblPLSQLExecution               'Current value of PL/SQL execution elapsed time from V$SYS_TIME_MODEL
Dim dblRMANCPU                      'Current value of RMAN cpu time (backup/restore) from V$SYS_TIME_MODEL
Dim dblBackgroundCPU                'Current value of background cpu time from V$SYS_TIME_MODEL
Dim dblBackgroundElapsed            'Current value of background elapsed time from V$SYS_TIME_MODEL
Dim dblConnectMgmt                  'Current value of connection management call elapsed time from V$SYS_TIME_MODEL
Dim dblFailedParseMemory            'Current value of failed parse (out of shared memory) elapsed time from V$SYS_TIME_MODEL
Dim dblFailedParseElapsed           'Current value of failed parse elapsed time from V$SYS_TIME_MODEL
Dim dblHardParseBind                'Current value of hard parse (bind mismatch) elapsed time from V$SYS_TIME_MODEL
Dim dblHardParseSharing             'Current value of hard parse (sharing criteria) elapsed time from V$SYS_TIME_MODEL
Dim dblHardParseElapsed             'Current value of hard parse elapsed time from V$SYS_TIME_MODEL
Dim dblInboundPLSQL                 'Current value of inbound PL/SQL rpc elapsed time from V$SYS_TIME_MODEL
Dim dblParseTimeElapsed             'Current value of parse time elapsed from V$SYS_TIME_MODEL
Dim dblRepeatedBind                 'Current value of repeated bind elapsed time from V$SYS_TIME_MODEL
Dim dblSequenceLoad                 'Current value of sequence load elapsed time from V$SYS_TIME_MODEL
Dim dblSQLExecuteTime               'Current value of sql execute elapsed time from V$SYS_TIME_MODEL

Dim dblDBCPULast                    'Last value of DB CPU from V$SYS_TIME_MODEL
Dim dblDBTimeLast                   'Last value of DB time from V$SYS_TIME_MODEL
Dim dblJavaTimeLast                 'Last value of Java execution elapsed time from V$SYS_TIME_MODEL
Dim dblPLSQLCompileLast             'Last value of PL/SQL compilation elapsed time from V$SYS_TIME_MODEL
Dim dblPLSQLExecutionLast           'Last value of PL/SQL execution elapsed time from V$SYS_TIME_MODEL
Dim dblRMANCPULast                  'Last value of RMAN cpu time (backup/restore) from V$SYS_TIME_MODEL
Dim dblBackgroundCPULast            'Last value of background cpu time from V$SYS_TIME_MODEL
Dim dblBackgroundElapsedLast        'Last value of background elapsed time from V$SYS_TIME_MODEL
Dim dblConnectMgmtLast              'Last value of connection management call elapsed time from V$SYS_TIME_MODEL
Dim dblFailedParseMemoryLast        'Last value of failed parse (out of shared memory) elapsed time from V$SYS_TIME_MODEL
Dim dblFailedParseElapsedLast       'Last value of failed parse elapsed time from V$SYS_TIME_MODEL
Dim dblHardParseBindLast            'Last value of hard parse (bind mismatch) elapsed time from V$SYS_TIME_MODEL
Dim dblHardParseSharingLast         'Last value of hard parse (sharing criteria) elapsed time from V$SYS_TIME_MODEL
Dim dblHardParseElapsedLast         'Last value of hard parse elapsed time from V$SYS_TIME_MODEL
Dim dblInboundPLSQLLast             'Last value of inbound PL/SQL rpc elapsed time from V$SYS_TIME_MODEL
Dim dblParseTimeElapsedLast         'Last value of parse time elapsed from V$SYS_TIME_MODEL
Dim dblRepeatedBindLast             'Last value of repeated bind elapsed time from V$SYS_TIME_MODEL
Dim dblSequenceLoadLast             'Last value of sequence load elapsed time from V$SYS_TIME_MODEL
Dim dblSQLExecuteTimeLast           'Last value of sql execute elapsed time from V$SYS_TIME_MODEL

Dim intSessionCount                 'Number of sessions logged
Dim intSessionCurrent               'Index of the current session
Dim lngSIDLast                      'SID for the previous row from the database
Dim lngSerialLast                   'SERIAL# for the previous row
Dim intSessionExists(999)           'Used to determine if the session is still found in the system
Dim lngSID(999)                     'SID for session
Dim lngSerial(999)                  'SERIAL# for the session
Dim strSessionOther(999)            'USERNAME, MACHINE, PROGRAM
Dim dblDBCPUS(999)                  'Current value of DB CPU from V$SESS_TIME_MODEL
Dim dblDBTimeS(999)                 'Current value of DB time from V$SESS_TIME_MODEL
Dim dblJavaTimeS(999)               'Current value of Java execution elapsed time from V$SESS_TIME_MODEL
Dim dblPLSQLCompileS(999)           'Current value of PL/SQL compilation elapsed time from V$SESS_TIME_MODEL
Dim dblPLSQLExecutionS(999)         'Current value of PL/SQL execution elapsed time from V$SESS_TIME_MODEL
Dim dblRMANCPUS(999)                'Current value of RMAN cpu time (backup/restore) from V$SESS_TIME_MODEL
Dim dblBackgroundCPUS(999)          'Current value of background cpu time from V$SESS_TIME_MODEL
Dim dblBackgroundElapsedS(999)      'Current value of background elapsed time from V$SESS_TIME_MODEL
Dim dblConnectMgmtS(999)            'Current value of connection management call elapsed time from V$SESS_TIME_MODEL
Dim dblFailedParseMemoryS(999)      'Current value of failed parse (out of shared memory) elapsed time from V$SESS_TIME_MODEL
Dim dblFailedParseElapsedS(999)     'Current value of failed parse elapsed time from V$SESS_TIME_MODEL
Dim dblHardParseBindS(999)          'Current value of hard parse (bind mismatch) elapsed time from V$SESS_TIME_MODEL
Dim dblHardParseSharingS(999)       'Current value of hard parse (sharing criteria) elapsed time from V$SESS_TIME_MODEL
Dim dblHardParseElapsedS(999)       'Current value of hard parse elapsed time from V$SESS_TIME_MODEL
Dim dblInboundPLSQLS(999)           'Current value of inbound PL/SQL rpc elapsed time from V$SESS_TIME_MODEL
Dim dblParseTimeElapsedS(999)       'Current value of parse time elapsed from V$SESS_TIME_MODEL
Dim dblRepeatedBindS(999)           'Current value of repeated bind elapsed time from V$SESS_TIME_MODEL
Dim dblSequenceLoadS(999)           'Current value of sequence load elapsed time from V$SESS_TIME_MODEL
Dim dblSQLExecuteTimeS(999)         'Current value of sql execute elapsed time from V$SESS_TIME_MODEL

Dim dblDBCPUSLast(999)              'Last value of DB CPU from V$SESS_TIME_MODEL
Dim dblDBTimeSLast(999)             'Last value of DB time from V$SESS_TIME_MODEL
Dim dblJavaTimeSLast(999)           'Last value of Java execution elapsed time from V$SESS_TIME_MODEL
Dim dblPLSQLCompileSLast(999)       'Last value of PL/SQL compilation elapsed time from V$SESS_TIME_MODEL
Dim dblPLSQLExecutionSLast(999)     'Last value of PL/SQL execution elapsed time from V$SESS_TIME_MODEL
Dim dblRMANCPUSLast(999)            'Last value of RMAN cpu time (backup/restore) from V$SESS_TIME_MODEL
Dim dblBackgroundCPUSLast(999)      'Last value of background cpu time from V$SESS_TIME_MODEL
Dim dblBackgroundElapsedSLast(999)  'Last value of background elapsed time from V$SESS_TIME_MODEL
Dim dblConnectMgmtSLast(999)        'Last value of connection management call elapsed time from V$SESS_TIME_MODEL
Dim dblFailedParseMemorySLast(999)  'Last value of failed parse (out of shared memory) elapsed time from V$SESS_TIME_MODEL
Dim dblFailedParseElapsedSLast(999) 'Last value of failed parse elapsed time from V$SESS_TIME_MODEL
Dim dblHardParseBindSLast(999)      'Last value of hard parse (bind mismatch) elapsed time from V$SESS_TIME_MODEL
Dim dblHardParseSharingSLast(999)   'Last value of hard parse (sharing criteria) elapsed time from V$SESS_TIME_MODEL
Dim dblHardParseElapsedSLast(999)   '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                    'Number of wait events read from the database
Dim intWaitCurrent                  'Current index of the wait event
Dim strWaitEventName(1300)          'Name of the wait event
Dim dblWaitValue(1300)              'Current wait event total time
Dim dblWaitValueLast(1300)          'Previous wait event total time
Dim dblWaitWaitsValue(1300)         'Current wait event number of waits
Dim dblWaitWaitsValueLast(1300)     'Previous wait event number of waits
Dim dblWaitTOValue(1300)            'Current wait event number of timeouts
Dim dblWaitTOValueLast(1300)        'Previous wait event number of timeouts

Set snpDataWait = CreateObject("ADODB.Recordset")
Set comDataWait = CreateObject("ADODB.Command")
Set snpDataOSStat = CreateObject("ADODB.Recordset")
Set comDataOSStat = CreateObject("ADODB.Command")
Set snpDataSysTime = CreateObject("ADODB.Recordset")
Set comDataSysTime = CreateObject("ADODB.Command")
Set snpDataSessTime = CreateObject("ADODB.Recordset")
Set comDataSessTime = CreateObject("ADODB.Command")

Set dbDatabase = CreateObject("ADODB.Connection")

strUsername = "MyUsername"
strPassword = "MyPassword"
strDatabase = "MyDB"

intCheckIterations = 20
intDelayIterations = 60
sglSessionMinimumPercent = 0.1  '10% of the total for the time period needed to be inccluded in the detail
strInd = "  "

dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
dbDatabase.Open

'Should verify that the connection attempt was successful, but I will leave that for someone else to code
On Error Resume Next  'Allow continuing the script if an error happens

With comDataWait
  strSQL = "SELECT" & vbCrLf
  strSQL = strSQL & "  EVENT," & vbCrLf
  strSQL = strSQL & "  TOTAL_WAITS," & vbCrLf
  strSQL = strSQL & "  TOTAL_TIMEOUTS," & vbCrLf
  strSQL = strSQL & "  TIME_WAITED" & vbCrLf
  strSQL = strSQL & "FROM" & vbCrLf
  strSQL = strSQL & "  V$SYSTEM_EVENT" & vbCrLf
  strSQL = strSQL & "WHERE" & vbCrLf
  strSQL = strSQL & "  WAIT_CLASS<>'Idle'" & vbCrLf
  strSQL = strSQL & "ORDER BY" & vbCrLf
  strSQL = strSQL & "  EVENT"

  .CommandText = strSQL
  .CommandType = adCmdText
  .CommandTimeout = 30
  .ActiveConnection = dbDatabase
End With

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 & "  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,' ') SQL_ID," & vbCrLf
  strSQL = strSQL & "  NVL(S.SQL_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

'Fire up Internet Explorer
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Left = 0
objIE.Top = 0
objIE.Width = 950
objIE.Height = 800
objIE.StatusBar = False
objIE.MenuBar = False
objIE.Toolbar = False

objIE.Navigate "about:blank"
objIE.Document.Title = "Charles Hooper's Time Model Data Viewer"
objIE.Visible = True

For i = 1 To intCheckIterations
  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 snpDataWait = comDataWait.Execute
  If Not (snpDataWait Is Nothing) Then
    Do While Not (snpDataWait.EOF)
      intWaitCurrent = intWaitCount + 1
      'Find the previous entry for this wait event
      For j = 1 To intWaitCount
        If strWaitEventName(j) = CStr(snpDataWait("event")) Then
          intWaitCurrent = j
          Exit For
        End If
      Next
      If intWaitCurrent = intWaitCount + 1 Then
        'New entry
        intWaitCount = intWaitCount + 1
        strWaitEventName(intWaitCurrent) = CStr(snpDataWait("event"))
      End If
      dblWaitValueLast(intWaitCurrent) = dblWaitValue(intWaitCurrent)
      dblWaitValue(intWaitCurrent) = CDbl(snpDataWait("time_waited"))
      dblWaitWaitsValueLast(intWaitCurrent) = dblWaitWaitsValue(intWaitCurrent)
      dblWaitWaitsValue(intWaitCurrent) = CDbl(snpDataWait("total_waits"))
      dblWaitTOValueLast(intWaitCurrent) = dblWaitTOValue(intWaitCurrent)
      dblWaitTOValue(intWaitCurrent) = CDbl(snpDataWait("total_timeouts"))

      snpDataWait.movenext
    Loop
  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
  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
        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"))
          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
  End If

  dteLastLoopStart = Now
  intDataChanged = True

  'Uncomment the following line if you would like for the session detail to be collapsed on each refresh
  'intDisplaySessionDetail = False

  Do While DateDiff("s", dteLastLoopStart, Now) < intDelayIterations
    'Remain in this loop until intDelayIterations seconds have elapsed
    intFlag = 0
    If intDataChanged = True Then
      'Update the web page
      strHTML = ""

      strHTML = strHTML & "<form name=""OracleTimeModel"">" & vbCrLf
      'strHTML = strHTML & "<input type=text id=divStatus name=divStatus value="" "" size=50 disabled=true><br />" & vbCrLf
      strHTML = strHTML & "<input type=hidden id=txtOK value="" "">" & vbCrLf
      strHTML = strHTML & "<input type=button value=""Re-Query"" id=cmdQuery onclick=""document.getElementById('txtOK').value='QUERY';"">" & vbCrLf
      strHTML = strHTML & "<input type=button value=""Show Detail"" id=cmdShowDetail onclick=""document.getElementById('txtOK').value='DETAIL';"">" & vbCrLf
      strHTML = strHTML & "<input type=button value=""Close"" id=cmdClose onclick=""document.getElementById('txtOK').value='CLOSE';"">" & vbCrLf
      strHTML = strHTML & "</form>" & vbCrLf

      strHTML = strHTML & "<table border=""1"" width=""500"" style=""font-family: Courier New; font-size: 8pt""" & vbCrLf
      strHTML = strHTML & "<tr><td bgcolor=""#11AAFF"">CPUs</td><td  bgcolor=""#11AAFF"">Busy Time</td><td bgcolor=""#11AAFF"">Idle Time</td>" & _
        "<td bgcolor=""#11AAFF"">User Mode</td><td bgcolor=""#11AAFF"">Kernel Mode</td></tr>" & vbCrLf
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber(intNumCPUs, 0) & "</td>"
      strHTML = strHTML & "<td><p align=""right"">" & FormatNumber((dblBusyTime - dblBusyTimeLast) / 100, 2) & "</td>"
      strHTML = strHTML & "<td><p align=""right"">" & FormatNumber((dblIdleTime - dblIdleTimeLast) / 100, 2) & "</td>"
      strHTML = strHTML & "<td><p align=""right"">" & FormatNumber((dblUserTime - dblUserTimeLast) / 100, 2) & "</td>"
      strHTML = strHTML & "<td><p align=""right"">" & FormatNumber((dblSysTime - dblSysTimeLast) / 100, 2) & "</td></tr>"
      strHTML = strHTML & "</table><p>" & vbCrLf

      strHTML = strHTML & "<table border=""1"" width=""900"" style=""font-family: Courier New; font-size: 8pt""" & vbCrLf
      strHTML = strHTML & "<tr><td bgcolor=""#11AAFF"">Value</td><td bgcolor=""#11AAFF"" colspan=""5"">Statistic Name</td></tr>" & vbCrLf
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblBackgroundElapsed - dblBackgroundElapsedLast) / 1000000, 2) & "</td><td colspan=""5"">Background Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblBackgroundElapsed - dblBackgroundElapsedLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblBackgroundElapsedS(j) - dblBackgroundElapsedSLast(j)) / (dblBackgroundElapsed - dblBackgroundElapsedLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblBackgroundElapsedS(j) - dblBackgroundElapsedSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblBackgroundCPU - dblBackgroundCPULast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "Background CPU Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblBackgroundCPU - dblBackgroundCPULast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblBackgroundCPUS(j) - dblBackgroundCPUSLast(j)) / (dblBackgroundCPU - dblBackgroundCPULast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblBackgroundCPUS(j) - dblBackgroundCPUSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblRMANCPU - dblRMANCPULast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & strInd & "RMAN CPU Time (Backup Restore)</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblRMANCPU - dblRMANCPULast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblRMANCPUS(j) - dblRMANCPUSLast(j)) / (dblRMANCPU - dblRMANCPULast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><<td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblRMANCPUS(j) - dblRMANCPUSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblDBTime - dblDBTimeLast) / 1000000, 2) & "</td><td colspan=""5"">DB Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblDBTime - dblDBTimeLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblDBTimeS(j) - dblDBTimeSLast(j)) / (dblDBTime - dblDBTimeLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblDBTimeS(j) - dblDBTimeSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblDBCPU - dblDBCPULast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "DB CPU</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblDBCPU - dblDBCPULast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblDBCPUS(j) - dblDBCPUSLast(j)) / (dblDBCPU - dblDBCPULast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblDBCPUS(j) - dblDBCPUSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblConnectMgmt - dblConnectMgmtLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "Connection Management Call Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblConnectMgmt - dblConnectMgmtLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblConnectMgmtS(j) - dblConnectMgmtSLast(j)) / (dblConnectMgmt - dblConnectMgmtLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblConnectMgmtS(j) - dblConnectMgmtSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblSequenceLoad - dblSequenceLoadLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "Sequence Load Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblSequenceLoad - dblSequenceLoadLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblSequenceLoadS(j) - dblSequenceLoadSLast(j)) / (dblSequenceLoad - dblSequenceLoadLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblSequenceLoadS(j) - dblSequenceLoadSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblSQLExecuteTime - dblSQLExecuteTimeLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "SQL Execute Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblSQLExecuteTime - dblSQLExecuteTimeLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblSQLExecuteTimeS(j) - dblSQLExecuteTimeSLast(j)) / (dblSQLExecuteTime - dblSQLExecuteTimeLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblSQLExecuteTimeS(j) - dblSQLExecuteTimeSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblParseTimeElapsed - dblParseTimeElapsedLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "Parse Time Elapsed</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblParseTimeElapsed - dblParseTimeElapsedLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblParseTimeElapsedS(j) - dblParseTimeElapsedSLast(j)) / (dblParseTimeElapsed - dblParseTimeElapsedLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblParseTimeElapsedS(j) - dblParseTimeElapsedSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblHardParseElapsed - dblHardParseElapsedLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & strInd & "Hard Parse Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblHardParseElapsed - dblHardParseElapsedLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblHardParseElapsedS(j) - dblHardParseElapsedSLast(j)) / (dblHardParseElapsed - dblHardParseElapsedLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblHardParseElapsedS(j) - dblHardParseElapsedSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblHardParseSharing - dblHardParseSharingLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & strInd & strInd & "Hard Parse (Sharing Criteria) Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblHardParseSharing - dblHardParseSharingLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblHardParseSharingS(j) - dblHardParseSharingSLast(j)) / (dblHardParseSharing - dblHardParseSharingLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblHardParseSharingS(j) - dblHardParseSharingSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblHardParseBind - dblHardParseBindLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & strInd & strInd & strInd & "Hard Parse (Bind Mismatch) Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblHardParseBind - dblHardParseBindLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblHardParseBindS(j) - dblHardParseBindSLast(j)) / (dblHardParseBind - dblHardParseBindLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblHardParseBindS(j) - dblHardParseBindSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblFailedParseElapsed - dblFailedParseElapsedLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & strInd & "Failed Parse Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblFailedParseElapsed - dblFailedParseElapsedLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblFailedParseElapsedS(j) - dblFailedParseElapsedSLast(j)) / (dblFailedParseElapsed - dblFailedParseElapsedLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblFailedParseElapsedS(j) - dblFailedParseElapsedSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblFailedParseMemory - dblFailedParseMemoryLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & strInd & strInd & "Failed Parse (Out of Shared Memory) Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblFailedParseMemory - dblFailedParseMemoryLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblFailedParseMemoryS(j) - dblFailedParseMemorySLast(j)) / (dblFailedParseMemory - dblFailedParseMemoryLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblFailedParseMemoryS(j) - dblFailedParseMemorySLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblPLSQLExecution - dblPLSQLExecutionLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "PL/SQL Execution Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblPLSQLExecution - dblPLSQLExecutionLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblPLSQLExecutionS(j) - dblPLSQLExecutionSLast(j)) / (dblPLSQLExecution - dblPLSQLExecutionLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblPLSQLExecutionS(j) - dblPLSQLExecutionSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblInboundPLSQL - dblInboundPLSQLLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "Inbound PL/SQL RPC Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblInboundPLSQL - dblInboundPLSQLLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblInboundPLSQLS(j) - dblInboundPLSQLSLast(j)) / (dblInboundPLSQL - dblInboundPLSQLLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblInboundPLSQLS(j) - dblInboundPLSQLSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblPLSQLCompile - dblPLSQLCompileLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "PL/SQL Compilation Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblPLSQLCompile - dblPLSQLCompileLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblPLSQLCompileS(j) - dblPLSQLCompileSLast(j)) / (dblPLSQLCompile - dblPLSQLCompileLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblPLSQLCompileS(j) - dblPLSQLCompileSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblJavaTime - dblJavaTimeLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "Java Execution Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblJavaTime - dblJavaTimeLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblJavaTimeS(j) - dblJavaTimeSLast(j)) / (dblJavaTime - dblJavaTimeLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblJavaTimeS(j) - dblJavaTimeSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblRepeatedBind - dblRepeatedBindLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "Repeated Bind Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblRepeatedBind - dblRepeatedBindLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblRepeatedBindS(j) - dblRepeatedBindSLast(j)) / (dblRepeatedBind - dblRepeatedBindLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblRepeatedBindS(j) - dblRepeatedBindSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "</table><p>" & vbCrLf
      strHTML = strHTML & "<table border=""1"" width=""500"" style=""font-family: Courier New; font-size: 8pt""" & vbCrLf
      strHTML = strHTML & "<tr><td bgcolor=""#11AAFF"">Wait Event Name</td><td  bgcolor=""#11AAFF"">Wait Time</td><td bgcolor=""#11AAFF"">Waits</td><td bgcolor=""#11AAFF"">Timeouts</td></tr>" & vbCrLf
      For j = 1 To intWaitCount
        If (dblWaitValue(j) - dblWaitValueLast(j)) <> 0 Then
          strHTML = strHTML & "<tr><td>" & strWaitEventName(j) & "</td>"
          strHTML = strHTML & "<td><p align=""right"">" & FormatNumber((dblWaitValue(j) - dblWaitValueLast(j)) / 100, 2) & "</td>"
          strHTML = strHTML & "<td><p align=""right"">" & FormatNumber((dblWaitWaitsValue(j) - dblWaitWaitsValueLast(j)), 0) & "</td>"
          strHTML = strHTML & "<td><p align=""right"">" & FormatNumber((dblWaitTOValue(j) - dblWaitTOValueLast(j)), 0) & "</td></tr>"
        End If
      Next
      strHTML = strHTML & "</table>" & vbCrLf

      objIE.Document.Body.InnerHTML = strHTML
      intDataChanged = False
    End If

    'Put the VBS script into suspend mode for 1/2 second to prevent hammering the CPUs
    Wscript.Sleep 500

    If objIE Is Nothing Then
      'User closed the Window
      intFlag = -1
    Else
      If objIE.Document.All.txtOK.Value <> " " Then
        Select Case objIE.Document.All.txtOK.Value
          Case "QUERY"
            intFlag = 1
            objIE.Document.All.txtOK.Value = " "
          Case "DETAIL"
            intFlag = 2
            If intDisplaySessionDetail = True Then
              intDisplaySessionDetail = False
            Else
              intDisplaySessionDetail = True
            End If
            intDataChanged = True
            objIE.Document.All.txtOK.Value = " "
          Case "CLOSE"
            intFlag = -1
            objIE.Document.All.txtOK.Value = " "
            objIE.Quit
        End Select
      End If
    End If

    If Abs(intFlag) = 1 Then
      Exit Do
    End If
  Loop

  If intFlag = -1 Then
    Exit For
  End If
Next

dbDatabase.Close

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 dbDatabase = Nothing
Set objIE = Nothing

Easy, right? 

There is no need to stop at this level – dig into the session-level wait events, enable a 10046 trace for sessions, set up the script to send an email to a user if the user consumes more than 10% of the server’s capacity, … and most important, have fun.








Follow

Get every new post delivered to your Inbox.

Join 143 other followers