Recursive SQL in a events 10046 trace file
Date: Wed, 18 Feb 2004 06:54:26 -0600
Message-ID: <lvCdnUsdE9Zgwq7dRVn-sw_at_comcast.com>
Armed with my new O'Reilly book Optimizing Oracle Performance I have been
trying to get a better understanding of how Oracle works.
The book makes the statement, " A database cal with dep=n + 1 is the
recursive child of the first subsequent dep=n database call listed in the
SQL data stream. The book gives a few examples, and in trying it out it
seemed to work until I tried the following SQL. My question are why does
this not keep with the model? and how does one then account for this
situation:
Dump file c:\oracle\admin\to5\udump\to5_ora_296_2004-02-15a.trc
Sun Feb 15 20:55:22 2004
Redo thread mounted by this instance: 1
Oracle process number: 12
Windows thread id: 296, image: ORACLE.EXE
ORACLE V9.2.0.4.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Instance name: to5
hv=2004533713 ad='7157ec60'
alter session set events '10046 trace name context forever, level 12'
END OF STMT
EXEC #3:c=0,e=19923,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=43164321789
WAIT #3: nam='SQL*Net message to client' ela= 8 p1=1952673792 p2=1 p3=0
WAIT #3: nam='SQL*Net message from client' ela= 5029 p1=1952673792 p2=1 p3=0
PARSING IN CURSOR #3 len=69 dep=0 uid=60 oct=42 lid=60 tim=43164454230
PARSING IN CURSOR #3 len=238 dep=0 uid=60 oct=3 lid=60 tim=43164556233 hv=4211298066 ad='7157da00' select distinct c.name, s.name, s.salesperson_id from customer c join orders on c.cust_nbr = orders.cust_nbr join salesperson s on orders.salesperson_id = s.salesperson_id where c.name = 'Crimson Medical Inc.' order by s.name, c.NAME END OF STMT PARSE #3:c=0,e=2204,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=43164556217 BINDS #3: EXEC #3:c=10014,e=33290,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=43164688931 WAIT #3: nam='SQL*Net message to client' ela= 10 p1=1952673792 p2=1 p3=0 FETCH #3:c=70101,e=66839,p=0,cr=2900,cu=0,mis=0,r=1,dep=0,og=4,tim=43164785732 WAIT #3: nam='SQL*Net message from client' ela= 754 p1=1952673792 p2=1 p3=0 WAIT #3: nam='SQL*Net message to client' ela= 7 p1=1952673792 p2=1 p3=0 FETCH #3:c=0,e=13248,p=0,cr=0,cu=0,mis=0,r=3,dep=0,og=4,tim=43164823392 WAIT #3: nam='SQL*Net message from client' ela= 3669 p1=1952673792 p2=1 p3=0 STAT #3 id=1 cnt=4 pid=0 pos=1 obj=0 op='SORT UNIQUE (cr=2900 r=0 w=0 time=67115 us)' STAT #3 id=2 cnt=48 pid=1 pos=1 obj=0 op='NESTED LOOPS (cr=2900 r=0 w=0 time=66511 us)' STAT #3 id=3 cnt=1440 pid=2 pos=1 obj=0 op='NESTED LOOPS (cr=1458 r=0 w=0 time=40438 us)'
PARSING IN CURSOR #4 len=116 dep=1 uid=0 oct=3 lid=0 tim=43164909064 hv=431456802 ad='70797038' select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj#,o. flags from obj$ o where o.obj#=:1 END OF STMT PARSE #4:c=0,e=1018,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=43164909048 BINDS #4: bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0 bfp=093dbb5c bln=22 avl=04 flg=05 value=39030 EXEC #4:c=0,e=73894,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=43165043688 FETCH #4:c=0,e=117,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=43165056304 STAT #3 id=4 cnt=1440 pid=3 pos=1 obj=39030 op='TABLE ACCESS FULL ORDERS (cr=16 r=0 w=0 time=5866 us)' BINDS #4: bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0 bfp=093dbb5c bln=22 avl=04 flg=05 value=39034 EXEC #4:c=0,e=50695,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=43165132543 FETCH #4:c=0,e=116,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=43165145593 STAT #3 id=5 cnt=1440 pid=3 pos=2 obj=39034 op='TABLE ACCESS BY INDEX ROWID SALESPERSON (cr=1442 r=0 w=0 time=21449 us)' STAT #3 id=6 cnt=1440 pid=5 pos=1 obj=39035 op='INDEX UNIQUE SCAN SALESPERSON_PK (cr=2 r=0 w=0 time=7068 us)' BINDS #4: bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0 bfp=093dbb5c bln=22 avl=04 flg=05 value=39013 EXEC #4:c=0,e=53716,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=43165240373 FETCH #4:c=0,e=119,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=43165253961 STAT #3 id=7 cnt=48 pid=2 pos=2 obj=39013 op='TABLE ACCESS BY INDEX ROWID CUSTOMER (cr=1442 r=0 w=0 time=18732 us)' STAT #3 id=8 cnt=1440 pid=7 pos=1 obj=39014 op='INDEX UNIQUE SCAN CUSTOMER_PK (cr=2 r=0 w=0 time=7059 us)'
PARSING IN CURSOR #3 len=55 dep=0 uid=60 oct=42 lid=60 tim=43165395660 hv=4110456808 ad='71571ff0' alter session set events '10046 trace name context off' END OF STMT PARSE #3:c=0,e=520,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=43165395645 BINDS #3: EXEC #3:c=0,e=13838,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=43165477860 Received on Wed Feb 18 2004 - 13:54:26 CET