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> </td><td colspan=""1""> </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> </td><td colspan=""1""> </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> </td><<td colspan=""1""> </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> </td><td colspan=""1""> </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> </td><td colspan=""1""> </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> </td><td colspan=""1""> </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> </td><td colspan=""1""> </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> </td><td colspan=""1""> </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> </td><td colspan=""1""> </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> </td><td colspan=""1""> </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> </td><td colspan=""1""> </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> </td><td colspan=""1""> </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> </td><td colspan=""1""> </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> </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> </td><td colspan=""1""> </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> </td><td colspan=""1""> </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> </td><td colspan=""1""> </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> </td><td colspan=""1""> </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> </td><td colspan=""1""> </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.
Charles Hooper's Oracle Notes 
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.
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):
https://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.
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.
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.
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.
[…] Charles Hooper-Working with Oracle’s Time Model Data 2 […]
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
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…
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?
HI charles,
its working.. thanks
im just a junior DBA can you please refer some good books and sorry for my ignorance..
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?
Hi Charles,
Do you have this script (for above report) for DB On Linux Server?
Thanks