Figures 2-15, 2-16, and 2-17 (shown below) are parts of an instance-level ORTA report based on a 120-second interval. Looking at the report in Figure 2-15, you can see the queue time is classified at different levels. While total queue time is 621 seconds, the next level is simply IO time (615 seconds) and non-IO time (6 seconds). If you work down the report and to the other figures, you'll notice the IO queue time classified as read time (586 seconds) and write time (30 seconds). Finally, all the queue time classifications result in the actual wait events with their associated time. Chapter 5 will give you a deeper understanding of queue time classification, including how being too focused on wait events can lead to a misdiagnosis.

While Oracle's wait time instrumentation is very comprehensive, this is not the case with service time; that is, CPU consumption or CPU time. For versions before Oracle Database 10g, service time is based on the v$sysstat and v$sesstat views, which indicate total consumption, as well as all parse time and recursive SQL CPU time. Systems based on Oracle Database 10g and later can take advantage of the v$sys_time_model and v$ses_time_model views, which provide more accurate timing and an additional background process CPU time category.

While Oracle does record CPU consumption related to parsing and recursive SQL, they can overlap each other, which muddies our timekeeping and reduces their usefulness. Parsing CPU consumption includes all parse time CPU consumption from all SQL, including server processes and background processes. Even parsing CPU consumption from recursive statements is included. Recursive CPU consumption also includes all recursive CPU consumption, regardless of whether the SQL originated from a server process or a background process. This also includes recursive CPU time involved with parsing. This means there is a crossover in the time accounting. As a result, we cannot classify CPU consumption (service time) into the clean and separate buckets of parse CPU time, recursive CPU time, and everything else (other).