I gather that the SQL within functions that are called in top-level SQL
statement are not considered recursive, and therefore do not conform to
Cary's rule
"A database call with dep=n+1 is the recursive child of the first
subsequent dep=n database call listed in the SQL trace data stream."
But the "dep" value for such SQL is indeed 1 more than the "dep" value
of the caller, as in this excerpt:
PARSING IN CURSOR #1 len=124 dep=0 uid=149 oct=3 lid=149 tim=2988575045
hv=440952914 ad='3775ee70'
select
outstanding_vested_for_100K(hextoraw('0001E8A6F8A94B40B95BBE32AF120D95'),
to_date('28-AUG-03','DD-MON-YY'))
from dual
END OF STMT
PARSE #1:c=0,e=203,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2988540123
EXEC #1:c=0,e=396,p=0,cr=3,cu=0,mis=0,r=0,dep=0,og=4,tim=2988596994
WAIT #1: nam='SQL*Net message to client' ela= 10 p1=1413697536 p2=1
p3=0
=====================
PARSING IN CURSOR #12 len=365 dep=1 uid=149 oct=3 lid=149
tim=2988604429
hv=3990937121 ad='344ddb98'
SELECT GrantType.Behavior from
GrantType,PlanStockClassGrantType,IssueGrant,GrantParticipant
where GrantParticipant.GrantParticipant_pk=:b1 and
IssueGrant.IssueGrant_pk=GrantParticipant.IssueGrant_fk
and IssueGrant.PlanStockClassGrantType_fk =
PlanStockClassGrantType.PlanStockClassGrantType_pk
and PlanStockClassGrantType.GrantType_fk=GrantType.GrantType_pk
The first SQL is what I entered in SQL*Plus (immediately after turning
on 10046 tracing). The second is contained in the
outstanding_vested_for_100k function.
Why is the motivating SQL shown in the trace first?
Is the rule for determining "recursive" relationships in a case like
this simply to follow the increasing "dep" values (with detours for
"true" recursive SQL, such as data dictionary access, that follows
Cary's rule)?
TIA
=====
Paul Baumgartel
Transcentive, Inc.
www.transcentive.com
__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Paul Baumgartel
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
