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.

Figure 2-15 (shown in the next section) shows the total service time of 10 seconds. The service time classification is simply server process (SP) time (9 seconds), background process (BG) time (1 second), all parse CPU time (1 second), and all recursive SQL time (0 second). Most Oracle systems will consume CPU primarily in the server process (SP) category. Chapter 5 provides collection details and the associated service time classification math.