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).

A few words about recursive SQL are in order here. While I typically look at recursive SQL as anything that a DBA or application developer did not type, Oracle's internal definition is stricter and actually means that even application SQL can consist of a significant amount of recursive SQL. Oracle's internal definition of recursive SQL is that, when traced, the depth is greater than zero. If you look at a trace file, you will notice each statement has a reference to its depth, such as dep=0. A simple SQL statement you enter or a simple PL/SQL loop you enter into SQL*Plus will have a depth of zero. However, if you place a SQL statement within a PL/SQL loop that gets executed 500 times, those 500 executions will have a depth of one and will be considered recursive SQL. This is why the recursive figures can seem higher than you might expect.