Oracle-L: sql trace - recursive relationships

From: Boris Dali <boris_dali_at_yahoo.ca>
Date: Mon, 24 Nov 2003 17:09:34 -0800
Message-ID: <F001.005D7A5F.20031124170934@fatcity.com>

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
BINDS #1:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0 size=48 offset=0

bfp=800003fbc005ff80 bln=22 avl=01 flg=05 value=0
bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00

oacflg=01 oacfl2=0 size=0 offset=24
   bfp=800003fbc005ff98 bln=22 avl=00 flg=01
=====================

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
BINDS #2:
bind 0: dty=2 mxl=22(21) mal=00 scl=00 pre=00 oacflg=03 oacfl2=4000000000000001 size=24 offset=0

bfp=800003fbc005f660 bln=22 avl=01 flg=05 value=0
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).

Received on Mon Nov 24 2003 - 19:09:34 CST