Oracle Database Time Model Viewer in Excel 5

In the previous articles in this series we looked at ways to analyze the Oracle time model data at the system-wide level with drill-down into the session level detail, with cross references to a handful of statistics found in V$OSSTAT and V$SYSSTAT, and the system-wide wait events with drill-down into the session level wait event detail. There is a chance that some of the statistics might appear to be inconsistent between the various performance views, or possibly even between the start of the retrieval of the rows from a single performance view and the retrieval of the final row from the query of that performance view. You might even find in some cases, with some combinations of operating system platform and Oracle release version that the statistics in V$OSSTAT might not be in the unit of measure described in the Oracle Database documentation – a recent thread on the OTN forums contains an example of such a case (I completely overlooked the inconsistency that was pointed out in that thread). Is there a reason for the inconsistency? A quick browse through Metalink (MOS) finds the following articles:

Bug 7430365: INCORRECT VALUES FOR USER_TIME IN V$OSSTAT (3.79 hours per CPU per elapsed hour)Bug 3574504: INCORRECT STATISTICS IN V$OSSTAT IN HP-UXBug 5933195: NUM_CPUS VALUE IN V$OSSTAT IS WRONGBug 5639749: CPU_COUNT NOT SHOWN PROPERLY FROM THE DATABASEBug 10427553: HOW DOES V$OSSTAT GET IT’S INFORMATION ON AIXBug 9228541: CPU TIME REPORTED INCORRECTLY IN V$SYSMETRIC_HISTORY (3.75 hours per CPU per elapsed hour)Doc ID 889396.1: Very large value for OS_CPU_WAIT_TIME FROM V$OSSTAT / AWR ReportBug 7447648: OS_CPU_WAIT_TIME VALUE FROM V$OSSTAT IS INCORRECT

At the end of the previous article, we had produced a demonstration project that generated screen output similar to the following (note that the project code as of the end of part four in the series may be downloaded by using the link at the end of the fourth article):

Let’s continue adding features to the project. We will start by adding three new CommandButtons to the UserForm with the (Name) property set to the following (one name per CommandButton): cmdTraceSession, cmdStopTrace, and cmdShowExecutionPlan. Assign useful titles to the CommandButtons by setting appropriate values for the Caption property – due to limited available space on the UserForm I selected the Captions: Trace, Stop Trace, and XPLAN. The UserForm in my sample project currently appears as shown in the following screen capture:

Next, we need to add a little more code to the Initialize event of the UserForm to add the SQL statements that will allow the CommandButtons to function. Double-click in the background area of the UserForm to display the Initialize event code for the UserForm.

Scroll down through the code until you find the following comment section:

'More code will be copied here ' ' '

Add a couple of blank lines above that comment section and paste in the following code:

In the above, you might have noticed that I neglected to set up the two bind variables for the comTrace object (an object of type ADODB.Command), while I did set up the two bind variables for the comXPLAN object. The reason for this omission is that in previous testing I simply could not make the SQL code execute with bind variables, so I cheated a bit, and will simply replace the CommandText property value for the comTrace object with hardcoded values for the session’s SID and SERIAL# when the cmdTraceSession CommandButton is clicked.

View the UserForm again (find frmTimeModel under the Forms heading, right-click the UserForm’s name, and select View Object). Then double-click the cmdTraceSession CommandButton to show the Click event for that object. Change the Click event so that it includes the following code:

Private Sub cmdTraceSession_Click()
Dim i As Integer
Dim intSessionTrace As Integer
Dim strSQL As String
If intCurrentSessionIndex > -1 Then
strSQL = "DBMS_MONITOR.SESSION_TRACE_ENABLE(" & Format(lngSID(intCurrentSessionIndex)) & "," & Format(lngSerial(intCurrentSessionIndex)) & " ,TRUE,TRUE)"
comTrace.CommandText = strSQL
comTrace.Execute
End If
'Remember that we enabled trace for this session
intSessionTrace = 0
For i = 1 To 1000
If SessionTrace(i).lngSID = lngSID(intCurrentSessionIndex) Then
intSessionTrace = i
Exit For
End If
Next i
If intSessionTrace = 0 Then
'Find an unused entry
For i = 1 To 1000
If SessionTrace(i).lngSID = 0 Then
intSessionTrace = i
SessionTrace(i).lngSID = lngSID(intCurrentSessionIndex)
SessionTrace(i).lngSerial = lngSerial(intCurrentSessionIndex)
Exit For
End If
Next i
End If
SessionTrace(intSessionTrace).int10046Level = 12
cmdStopTrace.Enabled = True
cmdTraceSession.Enabled = False
tvTimeModel.SetFocus
End Sub

If you examine the code, you will see that we use the intCurrentSessionIndex variable’s value to know which session is the session of interest, and then set the SessionTrace object’s int10046Level property to 12 to indicate that we have enabled a level 12 trace for the session (this allows us to remember which sessions are being traced). But, we have a couple of problems: the intCurrentSessionIndex variable has a default value of 0 and so far we have not added code to assign a value to that variable; secondly, the SessionTrace object simply does not exist. Adding the SessionTrace object is easy, so we will start there. Scroll all the way up to the top of the project’s code and add the following code above everything else:

Private Type TraceDefinition
lngSID As Long
lngSerial As Long
int10046Level As Integer
int10053Level As Integer
int10032Level As Integer
int10033Level As Integer
int10104Level As Integer
End Type
Dim SessionTrace(1000) As TraceDefinition

The first of the above sections creates the definition of the object type TraceDefinition which is simply composed of seven variables.

While in that section of the project code, add a couple of more variable declarations that will be used later (first scroll down to where you see similar variable declarations):

Dim snpSQLStats As ADODB.Recordset 'ADO recordset object used to retrieve the statistics for the execution plan
Dim comSQLStats As ADODB.Command 'ADO command object used to retrieve the statistics for the execution plan

The second section (beginning with the word Dim) creates an array of 1,000 (actually 1,001) objects of type TraceDefinition.

Now for the hard part – how do we know which session is the current session – the one that is currently highlighted in the TreeView control? The key to this is in how we named the TreeView rows (by setting the Key property) as the rows were added in the UpdateDisplay subroutine. Each row that contains session-level detail has a Key property that begins with the value SESSION, so we are able to easily determine when a session-level detail row is clicked. Also part of the Key property is an underscore ( _ ) character that acts as a field delimiter between the rest of the name and a sequential number that points to additional information about that session. View the UserForm object again and double-click the TreeView control. Switch to the NodeClick event for the TreeView control (in the drop-down list at the right of the code window) and change the NodeClick event’s code to show the following:

Easy to understand so far? Let’s add the code to the cmdStopTrace CommandButton. View the UserForm and then double-click the cmdStopTrace CommandButton. Change the Click event’s code to show the following:

You might notice that the code to stop the trace for a session is very similar to the code to start the trace.

The code for the cmdShowExecutionPlan CommandButton could be quite tame, as it was in the original Time Model Viewer project, but that would be a bit boring. Let’s do something a little special. First, view the UserForm object and then double-click the cmdShowExecutionPlan CommandButton. Change the Click event for the CommandButton to show the following:

Note that in the above code, if you are running Windows Vista or Window 7 with User Access Control enabled, you will need to replace all instances of C:\ExcelTimeModelViewer in the code with a writeable file location.

Press the F5 key to start up the execution of the UserForm. You might need to toggle the chkPauseFresh CheckBox to allow the UserForm data to begin updating. Just as an experiment, I decided to take a look at the execution plan for the SQL_ID that was identified for my Excel session. The following is the output that was displayed on screen (in the Notepad application window):

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: