Oracle-L: RE: sql trace - recursive relationships

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Wed, 26 Nov 2003 04:54:33 -0800
Message-ID: <F001.005D7E3C.20031126045433@fatcity.com>

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: boris_dali_at_yahoo.ca

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).


**************************************************************************************
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: Rajendra.Jamadagni_at_espn.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 Wed Nov 26 2003 - 06:54:33 CST