The first area of misrepresentation is because of inefficient data collection tools. While this may not be significant, or even measurable, during times of low CPU utilization, when the database server's CPU subsystem activity becomes extremely intense, executing collection-related commands take a relatively long time. If it takes 5 seconds to ask the database, "What time is it?" that 5 seconds can throw off your analysis. So be aware that during extremely high CPU utilization (90% and greater) reporting error could be significant.

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.