Database Reference
In-Depth Information
Database Call Statistics
without Recursive Statements
*****************************************************
Call Count Misses CPU Elapsed PIO LIO Consistent Current Rows
------- ----- ------ ----- ------- ------ ------ ---------- ------- ----
Parse 1 1 0.005 0.004 0 0 0 0 0
Execute 1 0 0.448 0.410 0 0 0 0 1
Fetch 0 0 0.000 0.000 0 0 0 0 0
------- ----- ------ ----- ------- ------ ------ ---------- ------- ----
Total 2 1 0.453 0.414 0 0 0 0 1
In this case, little time was spent by the current SQL statement according to the execution statistics. This is
also shown by the following resource usage profile. In fact, it shows that about 96 percent of the time was spent by
recursive SQL statements:
Component Duration [s] % Events Events [s]
--------------------------- ------------ ------- ------ ----------
recursive statements 12.271
96.437
n/a n/a
CPU 0.453 3.560 n/a n/a
SQL*Net message from client 0.000 0.003 1 0.000
SQL*Net message to client 0.000 0.000 1 0.000
--------------------------- ------------ -------
Total 12.724 100.000
To show which SQL statements these are, the resource usage profile is followed by a list of the recursive SQL
statements. From this list, you can see that SQL statement 2, which is a
SELECT
statement, was responsible for about
96 percent of the response time. Note that all other SQL statements except for SQL statement 3 were generated by the
database engine itself (for example, during the parse phase) and, therefore, are marked with the label
SYS recursive
:
7 recursive statements were executed.
Total
Statement ID Type Duration [s] %
------------ ---------------------- ------------ -------
#2 SELECT 12.234
96.150
#3 SELECT 0.033 0.263
#7 SELECT (SYS recursive) 0.003 0.022
#11 SELECT (SYS recursive) 0.000 0.001
#12 SELECT (SYS recursive) 0.000 0.001
#14 SELECT (SYS recursive) 0.000 0.001
#16 SELECT (SYS recursive) 0.000 0.000
------------ ---------------------- ------------ -------
Total 12.252 96.286
Because SQL statement 2 is responsible for most of the response time, you have to drill down further and get its
details. The structure is basically the same as for SQL statement 1. There is, however, additional information. In the
part that displays the execution environment, you can see the recursive level (remember, the SQL statements executed
by the application are at level 0) and the parent SQL statement identifier. This second piece of information is essential
in order to not lose the relationship between SQL statements (as TKPROF does!):
Session ID 156.29
Service Name SYS$USERS
Module Name SQL*Plus
