The other area of misrepresentation has to do with time in the CPU run queue. When Oracle reports that a process has consumed 10 ms of CPU time, Oracle does not know if the process actually consumed 10 ms of CPU time or if the process first waited in the CPU run queue for 5 ms and then received 5 ms of CPU time.1 Oracle perceives only 10 ms of time. If this occurs, you will most likely notice it because the Oracle CPU time consumed will be greater than the CPU subsystem's capacity. For example, if you ran a response time-focused report over a duration of 60 seconds, and if the single-CPU core subsystem were heavily overworked, Oracle could report that Oracle processes consumed 65 seconds of CPU time. Yet, we clearly know the CPU subsystem is limited to supplying up to 60 seconds of CPU time and no more. And let's not forget that the operating system consumes CPU to manage the database server. So, even if Oracle is reported to consume 90% of the available CPU, it's a good guess that the CPUs are on average 100% busy. Again, this is rare, but on an extremely CPU bottlenecked system, when the CPU run queue is significantly larger than the number of CPU cores, it can happen.

Oracle has made CPU consumption details available for many years, at both the session and instance levels. It's just that CPU consumption has not been classified by DBAs and used in a response time-focused or OraPub 3-circle analyses. The benefits to using the instance statistic views (v$sesstat and v$sysstat) are that they are available on any Oracle release and are familiar to most DBAs. The negatives are that the statistics are not guaranteed to be updated until after a SQL statement completes, and when a session disconnects, all the session-level statistics (v$sesstat) vanish. Usually, this does not present a problem with a large multiuser Oracle system. However, with a batch-centric workload with only a few massive resource-consuming SQL statements running, it can muddy any Oracle analysis.

In the instance statistic views, Oracle provides only total CPU consumption. It does not differentiate between server process time and background process time.2 But if a background process is consuming an unusually large portion of the CPU, this can be easily seen from an operating system perspective. Figure 5-1 shows statistic 12, which is the total CPU consumption for the Oracle instance since instance startup. This includes all Oracle server processes and (we hope) all the background processes. The figure is in centiseconds. To convert to seconds, simply divide the queried value by 100. The example shown in Figure 5-1 has consumed 734.20 seconds (73420/100) of CPU time since it last started.