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:
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.
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