Let's classify the service time based on the Oracle Database 10g Statspack report shown in Figures 5-8 and 5-9. The Statspack report (as does an AWR report) converts the Figure 5-8 time system model statistics into seconds, but the Figure 5-9 instance statistics need to be converted appropriately. For CPU-related time, the instance statistics, which are based on v$sysstat, are shown just as if they were queried directly from v$sysstat, which is in centiseconds. Based on the system time model statistics shown in Figure 5-8, the total CPU consumed by the Oracle instance during the 26-hour Statspack interval (I realize this is an unusually long report interval, but this is what one of my students sent me to help diagnose his system) is 48,126.3 seconds, which consists of DB CPU (Oracle server processes only) time of 45,551.6 seconds and background cpu time of 2,574.7 seconds. Based on the Statspack's instance activity report, the parse time CPU consumption (not the elapsed time) is 187.69 seconds (18,769 cs) and the recursive CPU consumption is 6,425.75 seconds (642,575 cs).

Notice that the instance CPU consumption figure based solely on v$sysstat is 33,020 seconds (statistic CPU used by this session), whereas based on the time system model, the CPU consumption is 48,126 seconds. Even with subtracting the time model's background CPU figure, the v$sysstat value is off by around 30%. This is a good example of why you want to use the time system model whenever possible.

The Oracle instance statistics shown in Figures 5-8 and 5-9 were gathered on a four-CPU core server over a large interval of 1,560.15 minutes. This means during this interval, the database server had the capacity to provide up to 374,436 seconds (4 cores _ 1560.15 minutes _ 60 s/1 m) of CPU power. Since Oracle consumed 48,126 seconds of CPU, Oracle used 12.9% of the available CPU. If this instance were the only instance on the database server and no other processes were consuming CPU, adding 10% operating system overhead, the database server would probably be around 23% busy. At 23% utilization, the queue time is not significant, and the average run queue will surely be less than the number of CPU cores. Since there are four CPU cores, we can surmise the average run queue is between zero and four. (If this is a surprise, please review the previous chapter, which covers the operating system.)