Working with Oracle’s Time Model Data

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.

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.

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: