Working with Oracle’s Time Model Data

January 13, 2010

(Forward to the Next Post in the Series)

As you might be aware several interesting performance views were added in Oracle Database 10.1 that help determine why a session is processing slower than expected.  The performance views offer data far beyond that of V$SYSSTAT, V$SESSTAT and the wait event interface that has been a part of Oracle since version 7.  One such view is the instance-wide V$SYS_TIME_MODEL view, and its associated session level V$SESS_TIME_MODEL view.  Oracle Database 10.1 also added V$OSSTAT, which helps determine how busy the server’s CPUs are as a whole, rather than just within the database instance.

It is easy to query the V$SYS_TIME_MODEL view to output just the statistic name and the value of that statistic, as shown below:

However, there are a couple of problems with just querying the view:

• It is hard to see the relationship between the various statistics.  Some of the statistics act as “parent” statistics for other statistic, essentially further dividing the parent statistic’s value into multiple sub-statistics.
• The numbers are very large, making it easy to lose the scale of a particular problem.
• The numbers represent totals since the database was last started.

A much more logical way to analyze the statistics is to arrange those statistics into a tree structure, and then to divide the values by 1,000,000 to convert the values to seconds.  For example, something like this:

Making the above conversion could be a little time consuming if the task must be performed frequently.  Thus, we need to find a way to automate the process.

Maybe something like this that not only shows the output from V$SYS_TIME_MODEL, but also V$OSSTAT and V$SYSTEM_EVENT:

If you have read many of my blog articles, you will probably quickly realize that the above is not yet good enough.  The values shown still tell the accumulated statistic values since the database was brought online, which was probably a short time before these statistics were captured.  The above shows that the server’s CPUs were on average about 33% busy at the operating system, system-wide level, with most of that time spent executing user code, rather than operating system kernel code.  The elapsed time for Oracle’s background tasks was just over 53 seconds, with 5.29 of those seconds spent running on the CPU.  Let’s see, 6,029 seconds busy, 5.29 + 2,362 seconds in the database instance – something else is running on the server consuming CPU cycles.  The DB Time component, which captures the accumulated CPU usage and wait events for all user sessions, totalled 2,619 seconds, of which 2,362 seconds were spent actively running on the server’s CPUs.

Nice, but not good enough, to determine if there is a performance problem, we need to constrain the statistics to a specific time interval, possibly a minute, something like the following:

Notice in the above that the server’s CPUs in this time period were roughly 70% busy, suggesting that careful time scoping will make a significant difference.  Nice, but still not good enough.

We need to be able to drill into the session level detail to see which sessions are the largest contributors to the V$SYS_TIME_MODEL statistics if we want to do something useful with the statistics, something like this:

So, how was the above output created?  As you can probably tell, the output is displayed in Internet Explorer, which probably means that it was generated by a web server of some sort.  But, I have not yet written an article on this blog that shows how to query the database from a web server…

An explanation of how the web page was created will follow in a later blog post.