RE: sql trace - recursive relationships

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Wed, 26 Nov 2003 05:04:25 -0800
Message-ID: <F001.005D7E3F.20031126050425@fatcity.com>

Sorry about the last empty email ...

Cary is right, the EXEC at dep=0 is the database call you should be looking for, why? because until #1 is parsed, db has no way of finding what needs to do. And once it finds that "Oh I must run a SQL", the dep increases. So, I'd look for a subsequent EXEC instead of PARSE line.

I'll take a stab at this ... lines with --> are mine

---

PARSING IN CURSOR #1 len=94 dep=0 uid=83 oct=47 lid=83 tim=1617285502494 hv=1138148843 ad='605d0998'
--> Anonymous block

BEGIN nav_tree_pkg.get_nav_parent_node_id(

   :p_nodeid, :p_parentnodeid );
 END;
END OF STMT
--> anon block gets parsed, it probably contains a sql.
PARSE #1:c=0,e=141,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1617285502483
--> Found the sql, so oracle opened another cursor #1 which is dependent on cursor #1 so dep = 1
PARSING IN CURSOR #2 len=68 dep=1 uid=98 oct=3 lid=98 tim=1617285503241 hv=1778717541 ad='606795e8'
--> sql test

SELECT PARENT_NAV_NODE_ID FROM NAV_NODE WHERE NAV_NODE_ID = :b1 END OF STMT
--> Successful parsing of cursor #2

PARSE #2:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1617285503230
--> Executing cursor #2

EXEC #2:c=0,e=151,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1617285503563
--> Fetch cursor #2

FETCH #2:c=0,e=40,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1617285503648
--> Data returned to anon block

WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
--> Now the anon block executes. the e time includes the time for all actions of cursor #2
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

Now, I'll just wait for Cary to come along and tell me that I got it all wrong ...

Happy Thanksgiving (or Turky Day)
Raj

---

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 !

---

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.

**************************************************************************************4

-- 
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 - 07:04:25 CST