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.


Actions

Information

12 responses

14 01 2010
coskan

Unbelievable helpful. I am currently reading choosing a performance optimization method chapter of your book did not think that it would be this good.

Thank you very much for sharing.

14 01 2010
Charles Hooper

The script in this article ties in nicely with several of the chapters in the “Expert Oracle Practices” book.

You might also enjoy this script (portions of the script require that Oracle Database run on a Windows server):

http://hoopercharles.wordpress.com/2010/01/09/drilling-into-session-detail-from-the-operating-system-on-the-windows-platform-2/

Thanks for the feedback regarding the second chapter that Randolf and I wrote. What is your opinion of the first chapter, “Understanding Performance Optimization Methods”? There were several significant changes made to both chapters between the time the alpha copy was captured and the final copy was submitted, so hopefully you were able to download the final version.

14 01 2010
coskan

I downloaded it on 22th of December,
I could not see any mistake so far but I hope you submitted the right by that time one otherwise I have to read 100 pages again :)

I did not go through the first chapter yet because this was initially to take a look at the chapter and I could not stop reading. I will definitely write my opinions about the chapters both here and my blog.

Thank you again.

14 01 2010
Charles Hooper

The final version of the chapter was made available on the Apress website on January 4, at around 7 PM UTC time (I do not know the reason for the delay, the final proof copies were due a couple of weeks earlier). Prior to that, Apress only offered the version of the chapters as of roughly October 1, which is just after the Apress lead editor split our chapter into 2 chapters and did his best to shrink the length of the chapter. We rewrote several sections of the two chapters based on the lead editor’s comments, adding a bit more detail in a couple of the sections.

I am very happy to hear that you are finding that the chapter is helpful/useful.

14 01 2010
coskan

I think I was already reading the Understanding Performance Optimization Methods :). I just downloaded the whole book and realized that the chapter name changed on top of the pages. It was labeled choosing a performance optimization method for the alpha download and I just looked at the top of the page while I wrote here :)

I think the chapter is very definitive and overall system tuning information gathering, plus how to go through a certain tracefiles was very educational for me.

2 02 2010
Blogroll Report 08/01/2009 – 15/01/2010 « Coskan’s Approach to Oracle

[...] Charles Hooper-Working with Oracle’s Time Model Data 2 [...]

7 02 2010
Anand

Hi Charles,

Its Awesome…no other word :)

If we run this for a remote database, then we need to provide the tns_alias name fro the source db in the strDatabase = “MyDB” ??? Am i right

Regards,
Anand

7 02 2010
Charles Hooper

I am happy that you like this example.

Yes, that is correct – it uses the entries found in tnsnames.ora.

While the example is very good, here are some ideas for expanding the example (someone else feel free to make the changes):
* In theory, it would be possible to read the tnsnames.ora file, extract the aliases, and create a drop-down list with all of the different databases that are defined – you could then use that list to select the database of interest to monitor.

* You could also write the retrieved statistics to a text file, Excel file, or database table for later analysis.

* I think that I also posted a VBS example where the computer speaks – if you find one session that is consuming 50% of the server capacity, you could have the script announce that USER1 is possibly suffering performance problems – it could be fun to listen to this for possibly 5 minutes.

* Add explain plan capability or a SQL monitor.

* Create charts in Excel that show performance history.

* And the list goes on…

4 10 2012
Karthik

Hi Charles,

i have copied the script and prvide the below details
when i run it open a blank screen

strUsername = “1333815″
strPassword = “Karthik2015″
strDatabase = “SSTORM_TEST”

what else do i need to do?

4 10 2012
Karthik

HI charles,

its working.. thanks

im just a junior DBA can you please refer some good books and sorry for my ignorance..

4 10 2012
Charles Hooper

Karthik,

Good that you were able to fix the problem with the script.

Regarding the books, are you asking about books for writing VBS scripts, Oracle Database performance books, or general Oracle Database books?

20 08 2014
Rajesh

Hi Charles,
Do you have this script (for above report) for DB On Linux Server?

Thanks

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 148 other followers

%d bloggers like this: