Database Reference
In-Depth Information
at recursive call depth 0. Should a SQL statement fire other statements, such as an
INSERT
state-
ment, which fires the execution of an insert trigger, then these other statements would be
executed at recursive call depth 1. A trigger body may then execute additional statements,
which may cause recursive SQL at the next higher recursive call depth. Following is an example
of an
INSERT
statement executed at recursive call depth 0. The
INSERT
statement fires a trigger.
Access to a sequence in the trigger body is at recursive call depth 1. Note how the execution
of the top level
INSERT
statement (
EXEC
#3
) is written to the trace file after the execution of the
dependent
SELECT
from the sequence has completed (
EXEC
#2
).
PARSING IN CURSOR #3 len=78
dep=0
uid=61 oct=2 lid=61 tim=771237502562 hv=3259110965
ad='6c5f86dc'
INSERT INTO customer(name, phone) VALUES (:name, :phone) RETURNING id INTO :id
END OF STMT
PARSE #3:c=0,e=1314,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=771237502553
=====================
PARSING IN CURSOR #2 len=40
dep=1
uid=61 oct=3 lid=61 tim=771237506650 hv=1168215557
ad='6c686178'
SELECT CUSTOMER_ID_SEQ.NEXTVAL FROM DUAL
END OF STMT
PARSE #2:c=0,e=1610,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=771237506643
EXEC #2:c=0,e=57,p=0,cr=0,cu=0,mis=0,r=0,
dep=1
,og=1,tim=771237507496
FETCH #2:c=0,e=54,p=0,cr=0,cu=0,mis=0,r=1,
dep=1
,og=1,tim=771237507740
EXEC #3:c=0,e=4584,p=0,cr=1,cu=3,mis=1,r=1,
dep=0
,og=1,tim=771237508046
When a client runs an anonymous PL/SQL block, the block itself is executed at recursive
call depth 0, but the statements inside the block will have recursive call depth 1. Another
example is auditing entries inserted into table
SYS.AUD$
. These are executed at one recursive
call depth higher than the statement that triggered the auditing.
Recursive parse, execute, and fetch operations are listed before the execution of the state-
ment that triggered the recursive operations. Statistics for SQL statements executed at recursive
call depth 0 contain the cost of dependent statements in terms of CPU time, elapsed time,
consistent reads, and so forth. This must be taken into consideration to avoid double counting
when evaluating SQL trace files.
Database Calls
The database call category consists of the three subcategories parsing, execution, and fetching.
Note that these entries correspond with the three stages of running dynamic SQL with the
package
DBMS_SQL
by calling the package subroutines
DBMS_SQL.PARSE
,
DBMS_SQL.EXECUTE
, and
DBMS_SQL
.
FETCH_ROWS
.
Among other metrics, database call entries represent the CPU and wall clock time (elapsed
time) a server process spends inside the ORACLE kernel on behalf of a database client. The
aggregated CPU and wall clock times from database calls in a trace file are closely related to the
session level statistics DB CPU and DB time in the dynamic performance view
V$SESS_TIME_MODEL
,
which is available in Oracle10
g
and subsequent releases.
