Home » RDBMS Server » Performance Tuning » recursive relationships
recursive relationships [message #146488] Thu, 10 November 2005 16:20 Go to next message
toakes1
Messages: 4
Registered: November 2005
Junior Member
I have some questions about a trace file. I've copied only the PARSING IN CURSOR lines and the database calls to this email. There are several dep=2 calls related to cursor # 3. Notice that db call 26 and 27 are dep=2, but the db call just below 26 and 27 has a dep=0, so which db call of dep=1 is the parent of the dep=2 call for db calls 26 and 27?

PARSING IN CURSOR #1 len=69 dep=0 uid=0 oct=42 lid=0 tim=9350097676 hv=2004533713 ad='6897db84'
alter session set events '10046 trace name context forever, level 12'
END OF STMT

1 EXEC #1:c=0,e=74,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=9350070989

PARSING IN CURSOR #2 len=132 dep=1 uid=0 oct=3 lid=0 tim=9350117864 hv=2954231783 ad='68a01568'
select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece from idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piece#
END OF STMT

2 PARSE #2:c=0,e=3185,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=9350117854
3 EXEC #2:c=30043,e=25562,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=9350154385
4 FETCH #2:c=20029,e=38580,p=2,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=9350195572
5 FETCH #2:c=0,e=41,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=9350198425
6 FETCH #2:c=0,e=20,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=9350201095

PARSING IN CURSOR #3 len=116 dep=2 uid=0 oct=3 lid=0 tim=9350205641 hv=431456802 ad='68b6f7c4'
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

7 PARSE #3:c=0,e=1660,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=0,tim=9350205632
8 EXEC #3:c=10014,e=12169,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=9350232043
9 FETCH #3:c=0,e=53,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=4,tim=9350235041

PARSING IN CURSOR #2 len=132 dep=1 uid=0 oct=3 lid=0 tim=9350247554 hv=986338823 ad='689f4b40'
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece#
END OF STMT

10 PARSE #2:c=0,e=3210,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=9350247543
11 EXEC #2:c=40058,e=32404,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=9350295375
12 FETCH #2:c=0,e=22459,p=2,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=9350321057
13 FETCH #2:c=0,e=23,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=9350325492
14 EXEC #3:c=20029,e=16728,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=9350346442
15 FETCH #3:c=0,e=52,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=4,tim=9350350625

PARSING IN CURSOR #2 len=135 dep=1 uid=0 oct=3 lid=0 tim=9350366423 hv=336764478 ad='689f3f70'
select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece from idl_char$ where obj#=:1 and part=:2 and version=:3 order by piece#
END OF STMT

16 PARSE #2:c=10014,e=3490,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=9350366413
17 EXEC #2:c=40057,e=41446,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=9350428410
18 FETCH #2:c=10015,e=16862,p=2,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=9350449302
19 FETCH #2:c=0,e=20,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=9350453380
20 EXEC #3:c=10015,e=16143,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=9350473763
21 FETCH #3:c=0,e=52,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=4,tim=9350477712

PARSING IN CURSOR #2 len=132 dep=1 uid=0 oct=3 lid=0 tim=9350492791 hv=386388955 ad='689ece3c'
select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece#
END OF STMT

22 PARSE #2:c=0,e=3272,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=9350492781
23 EXEC #2:c=50072,e=45004,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=9350557304
24 FETCH #2:c=10015,e=37819,p=2,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=9350599076
25 FETCH #2:c=0,e=40,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=9350603113
26 EXEC #3:c=10014,e=15588,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=9350622893
27 FETCH #3:c=0,e=45,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=4,tim=9350626907

PARSING IN CURSOR #1 len=139 dep=0 uid=0 oct=47 lid=0 tim=9350646350 hv=1781481595 ad='6897bb24'
DECLARE
v_name v$database.name%type;
v_count number := 0;
BEGIN
FOR i in 1..2000000 LOOP
v_count := v_count + 1;
END LOOP;
END;
END OF STMT

28 PARSE #1:c=460663,e=532956,p=8,cr=37,cu=0,mis=1,r=0,dep=0,og=4,tim=9350646339
29 EXEC #1:c=620893,e=620934,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=9351314014

PARSING IN CURSOR #2 len=198 dep=1 uid=0 oct=3 lid=0 tim=9351321314 hv=2703824309 ad='68b6cb20'
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
END OF STMT

30 PARSE #2:c=0,e=1837,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=9351321308
31 EXEC #2:c=0,e=341,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=9351321762
32 FETCH #2:c=0,e=35,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=9351321817

PARSING IN CURSOR #1 len=27 dep=0 uid=0 oct=3 lid=0 tim=9351322272 hv=3789792959 ad='68965268'
select name from v$database
END OF STMT

33 PARSE #1:c=0,e=5173,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=4,tim=9351322269
34 EXEC #1:c=0,e=34,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=9351322350
35 FETCH #1:c=0,e=1509,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=9351323893
36 FETCH #1:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=9351324135

PARSING IN CURSOR #1 len=55 dep=0 uid=0 oct=42 lid=0 tim=9351325055 hv=4110456808 ad='68947a48'
alter session set events '10046 trace name context off'
END OF STMT

37 PARSE #1:c=0,e=141,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=9351325051
38 EXEC #1:c=0,e=45,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=9351325131

Report message to a moderator

Re: recursive relationships [message #146895 is a reply to message #146488] Mon, 14 November 2005 08:55 Go to previous messageGo to next message
toakes1
Messages: 4
Registered: November 2005
Junior Member
Can anybody help with this one?

Report message to a moderator

Re: recursive relationships [message #146898 is a reply to message #146488] Mon, 14 November 2005 09:00 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Sorry, I don't know enough about parsing the raw trace files and would have to re-read the appropriate chapters in Millsap's book. I assume you have his optimizing book right?

Report message to a moderator

Re: recursive relationships [message #146901 is a reply to message #146898] Mon, 14 November 2005 09:16 Go to previous messageGo to next message
toakes1
Messages: 4
Registered: November 2005
Junior Member
I've read the books serveral times, but it doesn't indicate what to do in a situation like this. According to the book, there should be a dep=1 database call emitted after the dep=2 db call, but the db call below the dep=2 db calls is the dep=0 parent of all the previous database calls. I only see this happening when the code that is being traced is pl/sql.

Report message to a moderator

Re: recursive relationships [message #146930 is a reply to message #146488] Mon, 14 November 2005 15:15 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
You might consider emailing him and asking. Provide your short sample, your exact version and patch information, maybe even small bit of code if you can get it down to the smallest needed to show what happened. He and his company write tools to parse the files, and focus on them, so he a) might already know and be able to tell you b) point you to one of his products that does it for you or c) take it under advisement as a new situation that needs to be solved.

Report message to a moderator

Re: recursive relationships [message #146976 is a reply to message #146930] Tue, 15 November 2005 00:48 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
... and keep us informed please (in addition to Scot's advice)

Report message to a moderator

Re: recursive relationships [message #147052 is a reply to message #146488] Tue, 15 November 2005 09:01 Go to previous messageGo to next message
toakes1
Messages: 4
Registered: November 2005
Junior Member
I sent an email to the hotsos support group, and I'll keep everybody updated.

Can somebody else trace the following pl/sql to see if the results are the same as mine?

DECLARE
v_name v$database.name%type;
v_count number := 0;
BEGIN
FOR i in 1..2000000 LOOP
v_count := v_count + 1;
END LOOP;
END;
/

Report message to a moderator

Re: recursive relationships [message #147054 is a reply to message #146488] Tue, 15 November 2005 09:31 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
This is what I ran in sqlplus to create the attached trace file:

MYDBA > start trace_start;

Session altered.

MYDBA > start recursive_trace;

PL/SQL procedure successfully completed.

MYDBA > start trace_end;

Session altered.


FILENAME
------------------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP/orcl_ora_3004.trc

trace_start:

alter session set events '10046 trace name context forever, level 12';

trace_end:

alter session set events '10046 trace name context off';

select value || '/' || instance_name || '_ora_' || spid || '.trc' filename
from v$process, v$session, v$instance, v$parameter
where v$parameter.name = 'user_dump_dest'
and v$session.paddr = v$process.addr
and v$session.audsid = sys_context('userenv','sessionid');

exit;

Report message to a moderator

Previous Topic: Need help on tuning
Next Topic: Difference in Toad & Oracle Report Builder
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Nov 23 17:37:15 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.