Oracle-L: Function calls, queries, and trace files

From: Paul Baumgartel <treegarden_at_yahoo.com>
Date: Tue, 04 Nov 2003 11:14:33 -0800
Message-ID: <F001.005D590C.20031104111433@fatcity.com>

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: treegarden_at_yahoo.com

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: ListGuru_at_fatcity.com (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).

Received on Tue Nov 04 2003 - 13:14:33 CST