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.

Oracle's Statspack and AWR reports provide the detailed level response-time analysis data. However, the reports are clearly not focused on ORTA. In fact, part of the challenge of using Oracle's reports is to know what not to focus on, so you can avoid needless hours immersed in unproductive work. Once you do a few ORTAs based on these tools, you'll discover that performing such an analysis doesn't take all that long.