(Forward to the Next Post in the Series)
Previously, I had written a couple of blog articles that showed how to build a reasonably usable Oracle Database Time Model Viewer using nothing more than a text file (containing a VBS script) and a dynamically generated web page that is displayed on a Windows client computer using Internet Explorer (see the three part series). It might be interesting to see what is possible in a more sophisticated programming environment, such as Microsoft Excel.
170.64 seconds of CPU time consumed in 60 seconds, meaning that on average the CPUs were 34.97% busy, 20.18 seconds of CPU time were consumed in kernel mode, meaning that 88.17% of the CPU time consumed was consumed by Oracle Database or other foregound processes. Interesting, but I could probably obtain roughly the same information from an operating system utility. Possibly the more important question is “What is happening in my database instance?” Before the time model statistics were introduced in Oracle Database 10.1, we could see by examining repeated samplings of V$SYSSTAT that 78.05 CPU seconds were consumed by the sessions (falling into the CPU used by session statistic), that 2.43 CPU seconds were needed parsing activity (falling into the parse time cpu statistic, and 2.90 CPU seconds were needed for recursive operations (falling into the recursive cpu usage statistic) while performing operations such as trigger execution and space management calls – we are able to drill down into the session level detail by comparing delta values of V$SESSTAT. Starting with Oracle Database 9.2 we could also monitor the CPU usage of SQL statements by repeatedly checking V$SQL (Oracle Database 10.2 and above permit accessing V$SQLSTATS as a less resource intensive method to access much of the same information using the SQL_ID and PLAN_HASH_VALUE). We could also check the various views (V$SYSTEM_EVENT, V$SESSION_EVENT, V$SESSION_WAIT, V$LOCK, etc.) that compose the wait event interface, but that information will not indicate the amount of CPU used, and why the CPU was used by a session.
Let’s see if it is possible to build an extended version of the Oracle Database Time Model Viewer in Excel, hopefully something like this – the screen capture includes a lot of cross-referenced information that extends well beyond the Oracle Database time model views (V$SYS_TIME_MODEL, V$SESS_TIME_MODEL):
If you look closely at the above picture, we also see that this Oracle Database instance accounted for 80.63 CPU seconds (Background CPU Time plus DB CPU), with SID 1230 consuming the greatest percentage of CPU time at 19.13 seconds. Looking at the bottom of the screen capture, we see wait events from V$SYSTEM_EVENT and V$SESSION_EVENT that are grouped into categories (Administrative, Application, Commit, Concurrency, Configuration, Idle, Network, Other, Scheduler, System I/O, and User I/O), a feature which first appeared in Oracle Database 10.2. The time model statistics show that session 1230 spent 24.98 seconds executing SQL statements, a portion of the time was spent running on the CPU (19.13 seconds), and a port of the time was spent sitting in wait events (6.47 seconds in the direct path read wait event). Interesting possibilities here.
Let’s look at another example. 793.46 seconds of CPU time consumed in (roughly) 60 seconds, meaning that on average the CPUs were just under 100.00% busy, 11.60 seconds of CPU time were consumed in kernel mode, meaning that 98.54% of the CPU time consumed was consumed by Oracle Database or other foregound processes. Fantastic, we are using 100% of the server’s CPUs (oh wait, that is not a good idea). Unlike the earlier screen capture, where DB Time (125.86 seconds) was roughly equal to DB CPU (80.61 seconds) plus the value for Total Non-Idle Wait Time (43.74 seconds); in this screen capture we find that DB Time (259.40 seconds) seems to have a bit of lost time because the DB CPU (131.66 seconds) plus Total Non-Idle Wait Time (34.72 seconds) is about 93 seconds short of the DB Time statistic – on a positive note, if we were only looking at the wait events, it would appear that the sessions waited 9.02 seconds less in this time interval than in the time interval of the previous screen capture – we would have missed that the missing time detail if we had we not been looking at more than just the wait events 🙂 . If we look at the delta values from V$OSSTAT (the statistics at the top of the screen captures), and compare those statistics to the DB CPU plus Background CPU Time time model statistics, we get a sense of what the problem might entail. The V$OSSTATS statistics indicate that the processes running on the server (and the kernel mode CPU usage that was provoked by those processes) consumed 793.46 seconds of CPU time, while we are only able to account for 131.65 seconds + 0.08 seconds, or roughly 16.6% of the total CPU usage within the database instance. A relevant question at this point is what process or processes consumed the other 83.4% of the CPU time?
Moving on to the third screen capture, we see that things have started calming down a a little, with the average CPU utilization for the 60 second time period at 89.24% busy – of course this probably means that there were periods of 100% utilization, and periods of 70% utilization (I cheated, I watched the CPU utilization in roughly 5 second intervals, but note that DB Time minus DB CPU minus Total Non-Idle Wait Time indicates that 36.86 seconds are apparently missing… or lost in the CPU run queue, or in an uninstrumented code path). In the following screen capture we are able to see that the database instance consumed 203.77 seconds plus 0.03 seconds of the 439.56 seconds of total server CPU time consumed in the interval (roughly 46.4% of the total server CPU consumed could be attributed to the database instance – what else is consuming the server’s CPU time, possibly another database instance?).
By the time of the fourth screen capture things are starting to settle down, with the server’s CPUs just 24.58% busy. Unfortunately, the database instance being monitored only accounted for 13.95% of that CPU usage, so some other process is still competing for the server’s CPU time. Lots of pretty colors and other information in the screen captures, but we will save the explanation for later.
Let’s start up Excel and begin building the project – you will probably need the 32 bit version of Excel for this exercise, even on a 64 bit computer. Right-click one of the worksheet tabs and click View Code.
From the Tools menu, select References. Find Microsoft ActiveX Data Objects 2.8 (or 6.0) Library, and place a check next to that item. This is the feature that will allow our macros to communicate with the Oracle Database. Click OK.
Next, we need to create a window for our application, and in Excel that window is called a UserForm. Right-click Microsoft Excel Objects, and then select Insert – UserForm from the menu.
From the View menu, select Properties Explorer. Change the (Name) property of the UserForm to frmTimeModel and feel free to change the other form properties as you see fit.
Now the potentially challenging part. We need a Microsoft Windows built-in 32 bit element (control) called TreeView. This 32 bit control is found in the MSCOMCTL.OCX, and on a 32 bit operating system the file should be found in the C:\Windows\System32 folder. On a 64 bit operating system the file should be found in the C:\Windows\SysWOW64 folder. This file might be installed by various installer programs, but can also be downloaded from Microsoft (this link might also work, but the file is much older). If you had to download the file, put it in the correct folder location and then “register” the file with Windows, using a command like this (on a 64 bit client computer):
REGSVR32 c:\windows\SysWOW64\MSCOMCTL.OCX
(Possible bad news, the TreeView control might not work without Visual Basic 5.0 or 6.0 installed, see this article – we might need to simulate the TreeView using an Excel worksheet.)
Once we verify that the TreeView control is on the computer and registered, switch back to the Visual Basic editor, and from the Tools menu, select Additional Controls… Locate one of the Microsoft TreeView Controls in the list and place a checkmark next to it. Then click the OK button.
Find the TreeView control in the Toolbox tools list, and click it. Draw a rectangle convering most of the UserForm. In the Properties window, change the (Name) property of the TreeView control to tvTimeModel and then double-click the word (Custom) in the Properties window. In the Properties Pages window, change the Line Style to 1 – tvwRootLines and then click the OK button. Back in the Properties window, change the font to Courier New with an 8 point font size (to do this, double-click the word Font in the properties list).
Now, let’s add a little code to the project. Earlier, when we added a UserForm to the project, we right-clicked Microsoft Excel Object. In the same area of the screen is an item titled ThisWorkbook – double click the word ThisWorkbook. Add the following code to the code window that appeared:
Private Sub Workbook_Open() 'Code to initialize sheets should be placed here lngTimerTriggerSeconds = 60 lngTimerEventCounter = lngTimerTriggerSeconds frmTimeModel.Show End Sub
The above code sets the values of a couple of variables that can be accessed throughout the Excel project and then tells Microsoft Excel to display the UserForm that we just created, every time this Excel spreadsheet workbook is opened.
Now let’s create a timer subroutine that will automatically run once a second. Right-click Microsoft Excel Objects, and then select Insert – Module from the menu. Add the following code to the new module:
Option Explicit
Public lngTimerEventCounter As Long
Public lngTimerTriggerSeconds As Long
Public intKillFlag As Integer
Public Sub TimerEvent()
Dim i As Integer
lngTimerEventCounter = lngTimerEventCounter + 1
If lngTimerTriggerSeconds <= lngTimerEventCounter Then
frmTimeModel.tvTimeModel.Nodes.Clear
frmTimeModel.tvTimeModel.Nodes.Add , , "BackgroundElapsedTime", "This is a test, the time is now " & Now
frmTimeModel.tvTimeModel.Nodes.Add "BackgroundElapsedTime", tvwChild, "BackgroundCPU", "The background is still in the background."
For i = 1 To frmTimeModel.tvTimeModel.Nodes.Count
'Force all of the nodes to appear expanded
frmTimeModel.tvTimeModel.Nodes(i).Expanded = True
Next i
frmTimeModel.tvTimeModel.Nodes(1).Selected = True
lngTimerEventCounter = 0
intKillFlag = intKillFlag + 1
End If
'Instruct Excel to execute the TimerEvent sub again in 1 second
If intKillFlag < 10 Then
Application.OnTime DateAdd("s", 1, Now), "TimerEvent"
End If
End Sub
The above creates a sort of recursive routine, where every time the TimerEvent subroutine executes, it instructs Excel to execute the subroutine again after waiting one second. Every 60 times the TimerEvent subroutine executes, it will update the TreeView control on the UserForm. But there is a catch – somehow we need to execute the TimerEvent the first time so that it will automatically execute once a second. Below where you previously found Microsoft Excel Objects you will see frmTimeModel. Right-click frmTimeModel and select View Code. Add the following code:
Private Sub UserForm_Initialize() TimerEvent End Sub
Now save the Excel spreadsheet workbook, exit Excel, and the open the spreadsheet workbook that we just created. If everything works as it should, you should see something like this (note that you may need to adjust the Macro Security Level to allow the execution of all macros):
We will continue from here in the next segment of the series.
Edit February 28, 2011:
The current Excel project file, it might be necessary to hit the F5 key on the keyboard for the macro to run correctly after the workbook opens: TimeModelViewerExcelArticle1
Charles Hooper's Oracle Notes 
Recent Comments