Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: sql trace - recursive relationships

RE: sql trace - recursive relationships

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Thu, 27 Nov 2003 00:05:18 -0800
Message-ID: <F001.005D7EC0.20031127000518@fatcity.com> 





Boris, thanks for sending me your data. The following note pertains only
to the excerpt you sent me; I didn't look at the whole trace file.



Here's the excerpt you sent:



<excerpt>




PARSING IN CURSOR #1 len=94 dep=0 uid=83 oct=47 lid=83 tim=1614119418158
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=1177,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=1614119418146
BINDS #1:



 bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01
oacfl2=8000000100000000 size=48 offset=0


   bfp=800003fbc005f2c0 bln=22 avl=01 flg=05
   value=0


 bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01

oacfl2=8000000100000000 size=0 offset=24
   bfp=800003fbc005f2d8 bln=22 avl=00 flg=01
=====================




PARSING IN CURSOR #2 len=68 dep=1 uid=98 oct=3 lid=98 tim=1614119426242
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=676,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1614119426225
BINDS #2:



 bind 0: dty=2 mxl=22(21) mal=00 scl=00 pre=00 oacflg=03 oacfl2=1
size=24 offset=0


   bfp=800003fbc005f6f8 bln=22 avl=01 flg=05
   value=0

EXEC #2:c=0,e=1345,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1614119427797
WAIT #2: nam='db file sequential read' ela= 2899 p1=14 p2=119562 p3=1
WAIT #2: nam='db file sequential read' ela= 4290 p1=11 p2=28810 p3=1


FETCH #2:c=0,e=7381,p=2,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1614119435232
WAIT #1: nam='SQL*Net message to client' ela= 5 p1=1413697536 p2=1 p3=0
EXEC



#1:c=10000,e=17026,p=2,cr=2,cu=0,mis=0,r=1,dep=0,og=4,tim=1614119435461

WAIT #1: nam='SQL*Net message from client' ela= 15771 p1=1413697536 p2=1
p3=0




</excerpt>



Here's the stuff from the excerpt that is required to do this exercise.
The first step that most people mess up is the failure to ignore the
PARSING IN CURSOR sections during the mechanical step of determining the
recursive SQL relationships:



<abbreviated-excerpt>


PARSE #1:c=0,e=1177,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=1614119418146
PARSE #2:c=0,e=676,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1614119426225
EXEC #2:c=0,e=1345,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1614119427797
FETCH #2:c=0,e=7381,p=2,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1614119435232
EXEC



#1:c=10000,e=17026,p=2,cr=2,cu=0,mis=0,r=1,dep=0,og=4,tim=1614119435461

</abbreviated-excerpt>



I think your tree is as follows (it's late, and my eyes are beginning to
blur). I've used indentation to represent the parent-child relationships
(the number of tabs on a line equals the call's dep value), and the
number represents the sequence in which the line was encountered within
the trace file.
 


    
  
  1.   PARSE #1:e=1177
  
  2.   EXEC #1:3=17026

  
  3.   PARSE #2:e=676
  
  4.   EXEC #2:e=1345
  
  5.   FETCH #2:e=7381






When you use this method, it helps to leave a blank line for each level
by which the dep value of a call exceeds the prior line's dep value.
This leaves enough space into which you can later backpatch when you
find the recursive parent. In this case, the tree looked like this right
before I parsed line 5 of the abbreviated trace data:



    
  
  1.   PARSE #1:e=1177

  
  2.   PARSE #2:e=676
  
  3.   EXEC #2:e=1345
  
  4.   FETCH #2:e=7381






Then I plugged in the "5. EXEC #1" when I encountered the dep=0 dbcall
on line 5 that was the parent of all the outstanding dep=1 children.



This is a really convenient notation, by the way. I wish I had thought
of it in time for the book. It beats the heck out of trying to find a
super-wide sheet of paper and then drawing boxes all over it. I will
incorporate this into our PD101 course notes, though, so thanks for the
inspiration.



At this point, to derive meaning from the relationships we've charted,
we need now to pay attention to the PARSING IN CURSOR information. Here,
the PARSE, EXEC, and FETCH calls (P/E/F) upon cursor #2 are all
recursive children of the EXEC call upon cursor #1. Therefore, the P/E/F
operations upon the SELECT...FROM NAV_NODE statement are children of the
EXEC of the PL/SQL block.



I suspect that Raj and Dan are exactly right, but I don't have the
energy tonight to cross-check their notes with what I've said here.



Happy Thanksgiving, everyone.




Cary Millsap


Hotsos Enterprises, Ltd.


http://www.hotsos.com



Upcoming events:

- Performance Diagnosis 101: 12/16 Detroit, 1/27 Atlanta
- SQL Optimization 101: 12/8 Dallas, 2/16 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...






-----Original Message-----


Boris Dali


Sent: Wednesday, November 26, 2003 8:30 AM
To: Multiple recipients of list ORACLE-L



Thanks, Raj.



So yes, as I said in my other email - the rule stated
in the book seem to apply to EXEC db calls only (in
case of SQL fired from PL/SQL). I guess I
misinterpreted it the way that it applies to ALL db
calls for recursive cursors.



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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Cary Millsap
  INET: cary.millsap_at_hotsos.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 Thu Nov 27 2003 - 02:05:18 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US