--------------------------------------------------------------------------------
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !
-----Original Message-----
Sent: Monday, November 24, 2003 8:10 PM
To: Multiple recipients of list ORACLE-L
Reading Cary's "Optimizing Oracle Performance", page
91 it says:
"A database call with dep=n+1 is the recursive child
of the first SUBSEQUENT (empasis mine) dep=n database
call listed in the SQL trace data stream"
Does this apply to the SQL issued from PL/SQL?
I am looking at the simple packaged stored proc:
PACKAGE BODY nav_tree_pkg is
PROCEDURE GET_NAV_PARENT_NODE_ID
( p_NodeId IN NUMBER,
p_ParentNodeId OUT NUMBER)
IS
BEGIN
SELECT PARENT_NAV_NODE_ID INTO p_ParentNodeId
FROM NAV_NODE
WHERE NAV_NODE_ID = p_NodeId;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_ParentNodeId := -1 ;
END; -- Procedure
END;
... and here's what I see in the trace (sorry the
lines are probably wrapped):
=====================
PARSING IN CURSOR #1 len=94 dep=0 uid=83 oct=47 lid=83
tim=1617285502494 hv=1138148843 ad='605d0998'
BEGIN nav_tree_pkg.get_nav_parent_node_id(
:p_nodeid,
:p_parentnodeid
);
END;
END OF STMT
PARSE #1:c=0,e=141,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1617285502483
PARSING IN CURSOR #2 len=68 dep=1 uid=98 oct=3 lid=98 tim=1617285503241 hv=1778717541
ad='606795e8'
SELECT PARENT_NAV_NODE_ID FROM NAV_NODE WHERE NAV_NODE_ID = :b1 END OF STMT
PARSE #2:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1617285503230
EXEC #2:c=0,e=151,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1617285503563
FETCH #2:c=0,e=40,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1617285503648
WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
EXEC #1:c=0,e=1037,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=4,tim=1617285503786
WAIT #1: nam='SQL*Net message from client' ela= 2470 p1=1413697536 p2=1 p3=0
=====================
... Totaly different calls
=====================
So here it looks like the child CURSOR #2 with dep=1
is emitted AFTER the parent (CURSOR #1, dep=0)
Thanks,
Boris Dali.
______________________________________________________________________
Post your free ad now! http://personals.yahoo.ca
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Boris Dali
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).
**************************************************************************************
This e-mail message is confidential, intended only for the named recipient(s) above
and may contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000
and delete this e-mail message from your computer, Thank you.
**************************************************************************************5
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jamadagni, Rajendra
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).
