RE: STAT from trace
No little minds at this place, maybe just hobgoblins. Certainly no consistency. I tried again this morning and everything worked. (of course I don't know what has changed behind the scenes:Oracle patches, OS patches???). Jane, stop this crazy thing. Thanks for the suggestions. Next time I'll spool my commands immediately so they'll be some record. Henry -Original Message- Cary Millsap Sent: Wednesday, October 01, 2003 6:14 PM To: Multiple recipients of list ORACLE-L If you exited the Oracle session without deactivating the trace, then you should probably see a XCTEND as well, right? If you see the XCTEND but no STAT lines, then I think you've found a bug. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Henry Poras Sent: Wednesday, October 01, 2003 4:45 PM To: Multiple recipients of list ORACLE-L Wolfgang, I'll give level 9 a try. Yes I get PARSE, EXEC, FETCH, WAIT. My platform is AIX 5.1 For now I've run sql_trace = true and 10046 level 8 and looked at two seperate trace files. I've had this working before, but I can't remember if it was on 5.1 or when we still were at AIX 4. Thanks for the suggestions. Henry -Original Message- Wolfgang Breitling Sent: Wednesday, October 01, 2003 3:45 PM To: Multiple recipients of list ORACLE-L Do you get the PARSE, EXEC and FETCH entries for the sql? You gave us your Oracle version, but what is your platform? Would you care to try the following: since sql_trace = true corresponds to event 10046, level 1, why not set event 10046 at level 9. Maybe that'll do the trick. At 12:59 PM 10/1/2003, you wrote: Tried that. Also queried on open_cursors in a parallel session. Henry -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Daniel Fink Sent: Wednesday, October 01, 2003 1:59 PM To: Multiple recipients of list ORACLE-L Subject: Re: STAT from trace Henry, What happens if you issue another query after the query of interest? (something like select 1 from dual;) STAT should be emitted when the cursor is closed. Daniel Henry Poras wrote: Thanks. I tried both disabling the trace and quitting from the session. No luck with 10046, just sql_trace.Henry -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]mailto:[EMAIL PROTECTED] Behalf Of Jamadagni, Rajendra Sent: Wednesday, October 01, 2003 10:25 AM To: Multiple recipients of list ORACLE-L Subject: RE: STAT from trace I get STAT lines no matter how I enable the trace. Make sure you wither close the session or stop the trace so that all pending STAT lines will be written to the file. Raj -Original Message- From: Henry Poras [mailto:[EMAIL PROTECTED]mailto:[EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 9:34 AM To: Multiple recipients of list ORACLE-L Subject: STAT from trace I was creating some trace files yesterday and came across one of these problems that shows up occasionally (then I forget about it). When I ran my query using ALTER SESSION SET sql_trace=true (sqlplus; 8.1.7.4), I got the STAT line in the trace and the associated 'row source' information after running tkprof. When I ran my query using ALTER SESSION SET EVENTS '10046 trace name context forever, level 8' (or with dbms_system.set_ev), there were no STAT entries for my query. There were some for some of the recursive queries. Everything else was identical. What's up? Henry Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Henry Poras INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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
RE: STAT from trace
Title: RE: STAT from trace I get STAT lines no matter how I enable the trace. Make sure you wither close the session or stop the trace so that all pending STAT lines will be written to the file. Raj -Original Message- From: Henry Poras [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 01, 2003 9:34 AM To: Multiple recipients of list ORACLE-L Subject: STAT from trace I was creating some trace files yesterday and came across one of these problems that shows up occasionally (then I forget about it). When I ran my query using ALTER SESSION SET sql_trace=true (sqlplus; 8.1.7.4), I got the STAT line in the trace and the associated 'row source' information after running tkprof. When I ran my query using ALTER SESSION SET EVENTS '10046 trace name context forever, level 8' (or with dbms_system.set_ev), there were no STAT entries for my query. There were some for some of the recursive queries. Everything else was identical. What's up? Henry 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.*2
Re: STAT from trace
Hi! This is the problem, that everything else was identical. If you executed exactly the same query again, it didn't get hard parsed anymore, thus no STAT lines were generated. Either flush shared pool or just add some bogus comment using /* */ into your query to get parsing and STAT lines. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 4:34 PM I was creating some trace files yesterday and came across one of these problems that shows up occasionally (then I forget about it). When I ran my query using ALTER SESSION SET sql_trace=true (sqlplus; 8.1.7.4), I got the STAT line in the trace and the associated 'row source' information after running tkprof. When I ran my query using ALTER SESSION SET EVENTS '10046 trace name context forever, level 8' (or with dbms_system.set_ev), there were no STAT entries for my query. There were some for some of the recursive queries. Everything else was identical. What's up? Henry -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Henry Poras INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Tanel Poder INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: STAT from trace
Title: RE: STAT from trace Thanks. I tried both disabling the trace and quitting from the session. No luck with 10046, just sql_trace. Henry -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Jamadagni, RajendraSent: Wednesday, October 01, 2003 10:25 AMTo: Multiple recipients of list ORACLE-LSubject: RE: STAT from trace I get STAT lines no matter how I enable the trace. Make sure you wither close the session or stop the trace so that all pending STAT lines will be written to the file. Raj -Original Message- From: Henry Poras [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 01, 2003 9:34 AM To: Multiple recipients of list ORACLE-L Subject: STAT from trace I was creating some trace files yesterday and came across one of these problems that shows up occasionally (then I forget about it). When I ran my query using ALTER SESSION SET sql_trace=true (sqlplus; 8.1.7.4), I got the STAT line in the trace and the associated 'row source' information after running tkprof. When I ran my query using ALTER SESSION SET EVENTS '10046 trace name context forever, level 8' (or with dbms_system.set_ev), there were no STAT entries for my query. There were some for some of the recursive queries. Everything else was identical. What's up? Henry
Re: STAT from trace
Henry, What happens if you issue another query after the query of interest? (something like "select 1 from dual;") STATshould be emitted when the cursor is closed. Daniel Henry Poras wrote: Thanks. I tried both disabling the trace and quitting from the session. No luck with 10046, just sql_trace.Henry -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jamadagni, Rajendra Sent: Wednesday, October 01, 2003 10:25 AM To: Multiple recipients of list ORACLE-L Subject: RE: STAT from trace I get STAT lines no matter how I enable the trace. Make sure you wither close the session or stop the trace so that all pending STAT lines will be written to the file. Raj -Original Message- From: Henry Poras [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 01, 2003 9:34 AM To: Multiple recipients of list ORACLE-L Subject: STAT from trace I was creating some trace files yesterday and came across one of these problems that shows up occasionally (then I forget about it). When I ran my query using ALTER SESSION SET sql_trace=true (sqlplus; 8.1.7.4), I got the STAT line in the trace and the associated 'row source' information after running tkprof. When I ran my query using ALTER SESSION SET EVENTS '10046 trace name context forever, level 8' (or with dbms_system.set_ev), there were no STAT entries for my query. There were some for some of the recursive queries. Everything else was identical. What's up? Henry begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard
Re: STAT from trace
That's not true. The STAT lines in the 10046 trace have nothing to do with parsing. But you must close the cursor for them to be written to the trace. If you are using sqlplus, either close the trace or the session, otherwise sqlplus keeps the cursor for the most recent sql open. Here is an exctract from a trace where I execute the same sql twice in a row. You can even see the alter session command. PARSING IN CURSOR #1 len=84 dep=0 uid=31 oct=3 lid=31 tim=926359 hv=1785040157 ad='313afe4' select t1.d1, t1.d2, t2.d1, t2.d2 from t1, t2 where t2.fk1=t1.pk1 and t2.d2 = 499 END OF STMT PARSE #1:c=200,e=258,p=25,cr=401,cu=6,mis=1,r=0,dep=0,og=4,tim=926361 EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=926362 FETCH #1:c=0,e=2,p=2,cr=2,cu=0,mis=0,r=0,dep=0,og=4,tim=926364 STAT #1 id=1 cnt=0 pid=0 pos=0 obj=0 op='HASH JOIN ' STAT #1 id=2 cnt=0 pid=1 pos=1 obj=134295 op='TABLE ACCESS BY INDEX ROWID T2 ' STAT #1 id=3 cnt=1 pid=2 pos=1 obj=134372 op='INDEX RANGE SCAN ' STAT #1 id=4 cnt=0 pid=1 pos=2 obj=134293 op='TABLE ACCESS FULL T1 ' = PARSING IN CURSOR #1 len=53 dep=0 uid=31 oct=47 lid=31 tim=926366 hv=3759542639 ad='316aac4' BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END; END OF STMT PARSE #1:c=1,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=926367 APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240 EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=926368 *** 2003-10-01 09:39:52.448 = PARSING IN CURSOR #1 len=69 dep=0 uid=31 oct=42 lid=31 tim=928350 hv=589283212 ad='30f70b8' alter session set events '10046 trace name context forever, level 8' END OF STMT PARSE #1:c=2,e=2,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=928351 EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=928351 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0 *** 2003-10-01 09:40:02.682 WAIT #1: nam='SQL*Net message from client' ela= 1021 p1=1413697536 p2=1 p3=0 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 = PARSING IN CURSOR #1 len=53 dep=0 uid=31 oct=47 lid=31 tim=929375 hv=3759542639 ad='316aac4' BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END; END OF STMT PARSE #1:c=2,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=929376 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 APPNAME mod='01@ c:\tmp\tst.sql' mh=764572216 act='' ah=4029777240 EXEC #1:c=1,e=1,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=929377 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 = PARSING IN CURSOR #1 len=84 dep=0 uid=31 oct=3 lid=31 tim=929380 hv=1785040157 ad='313afe4' select t1.d1, t1.d2, t2.d1, t2.d2 from t1, t2 where t2.fk1=t1.pk1 and t2.d2 = 499 END OF STMT PARSE #1:c=3,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=929382 EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=929383 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0 FETCH #1:c=1,e=1,p=0,cr=2,cu=0,mis=0,r=0,dep=0,og=4,tim=929384 WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 STAT #1 id=1 cnt=0 pid=0 pos=0 obj=0 op='HASH JOIN ' STAT #1 id=2 cnt=0 pid=1 pos=1 obj=134295 op='TABLE ACCESS BY INDEX ROWID T2 ' STAT #1 id=3 cnt=1 pid=2 pos=1 obj=134372 op='INDEX RANGE SCAN ' STAT #1 id=4 cnt=0 pid=1 pos=2 obj=134293 op='TABLE ACCESS FULL T1 ' WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 = PARSING IN CURSOR #1 len=53 dep=0 uid=31 oct=47 lid=31 tim=929388 hv=3759542639 ad='316aac4' BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END; END OF STMT PARSE #1:c=2,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=929389 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240 EXEC #1:c=2,e=2,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=929391 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0 *** 2003-10-01 09:40:14.219 WAIT #1: nam='SQL*Net message from client' ela= 1135 p1=1413697536 p2=1 p3=0 = PARSING IN CURSOR #1 len=57 dep=0 uid=31 oct=42 lid=31 tim=930528 hv=1975444627 ad='30f59a0' alter session set events '10046 trace name context off' END OF STMT PARSE #1:c=2,e=2,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=930530 EXEC
RE: STAT from trace
Tried that. Also queried on open_cursors in a parallel session. Henry -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Daniel FinkSent: Wednesday, October 01, 2003 1:59 PMTo: Multiple recipients of list ORACLE-LSubject: Re: STAT from traceHenry, What happens if you issue another query after the query of interest? (something like "select 1 from dual;") STATshould be emitted when the cursor is closed. Daniel Henry Poras wrote: Thanks. I tried both disabling the trace and quitting from the session. No luck with 10046, just sql_trace.Henry -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jamadagni, Rajendra Sent: Wednesday, October 01, 2003 10:25 AM To: Multiple recipients of list ORACLE-L Subject: RE: STAT from trace I get STAT lines no matter how I enable the trace. Make sure you wither close the session or stop the trace so that all pending STAT lines will be written to the file. Raj -Original Message- From: Henry Poras [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 01, 2003 9:34 AM To: Multiple recipients of list ORACLE-L Subject: STAT from trace I was creating some trace files yesterday and came across one of these problems that shows up occasionally (then I forget about it). When I ran my query using ALTER SESSION SET sql_trace=true (sqlplus; 8.1.7.4), I got the STAT line in the trace and the associated 'row source' information after running tkprof. When I ran my query using ALTER SESSION SET EVENTS '10046 trace name context forever, level 8' (or with dbms_system.set_ev), there were no STAT entries for my query. There were some for some of the recursive queries. Everything else was identical. What's up? Henry
RE: STAT from trace
Do you get the PARSE, EXEC and FETCH entries for the sql? You gave us your Oracle version, but what is your platform? Would you care to try the following: since sql_trace = true corresponds to event 10046, level 1, why not set event 10046 at level 9. Maybe that'll do the trick. At 12:59 PM 10/1/2003, you wrote: Tried that. Also queried on open_cursors in a parallel session. Henry -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Daniel Fink Sent: Wednesday, October 01, 2003 1:59 PM To: Multiple recipients of list ORACLE-L Subject: Re: STAT from trace Henry, What happens if you issue another query after the query of interest? (something like select 1 from dual;) STAT should be emitted when the cursor is closed. Daniel Henry Poras wrote: Thanks. I tried both disabling the trace and quitting from the session. No luck with 10046, just sql_trace.Henry -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]mailto:[EMAIL PROTECTED] Behalf Of Jamadagni, Rajendra Sent: Wednesday, October 01, 2003 10:25 AM To: Multiple recipients of list ORACLE-L Subject: RE: STAT from trace I get STAT lines no matter how I enable the trace. Make sure you wither close the session or stop the trace so that all pending STAT lines will be written to the file. Raj -Original Message- From: Henry Poras [mailto:[EMAIL PROTECTED]mailto:[EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 9:34 AM To: Multiple recipients of list ORACLE-L Subject: STAT from trace I was creating some trace files yesterday and came across one of these problems that shows up occasionally (then I forget about it). When I ran my query using ALTER SESSION SET sql_trace=true (sqlplus; 8.1.7.4), I got the STAT line in the trace and the associated 'row source' information after running tkprof. When I ran my query using ALTER SESSION SET EVENTS '10046 trace name context forever, level 8' (or with dbms_system.set_ev), there were no STAT entries for my query. There were some for some of the recursive queries. Everything else was identical. What's up? Henry Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: STAT from trace
Tanel, that's not correct. 10046 10053. To get the STATs lines the trace buffer has to be flushed i.e. the cursor has to be closed and the next statement is processed (or user closes the session) -- depends on the nature of the application, types of opened cursors and instance/session settings. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Tanel Poder wrote: Hi! This is the problem, that everything else was identical. If you executed exactly the same query again, it didn't get hard parsed anymore, thus no STAT lines were generated. Either flush shared pool or just add some bogus comment using /* */ into your query to get parsing and STAT lines. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 4:34 PM I was creating some trace files yesterday and came across one of these problems that shows up occasionally (then I forget about it). When I ran my query using ALTER SESSION SET sql_trace=true (sqlplus; 8.1.7.4), I got the STAT line in the trace and the associated 'row source' information after running tkprof. When I ran my query using ALTER SESSION SET EVENTS '10046 trace name context forever, level 8' (or with dbms_system.set_ev), there were no STAT entries for my query. There were some for some of the recursive queries. Everything else was identical. What's up? Henry -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: STAT from trace
Wolfgang, Vladimir, Thanks for pointing this out, I must have been thinking with some other part of my body than head. If it would depend on hard parsing, it wouldn't be so simple to get execution plans for sql for a session in instance that has been running for a while (assuming bind variables are used, of course). So, I understand the STAT information is written to trace on cursor close? Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 7:49 PM That's not true. The STAT lines in the 10046 trace have nothing to do with parsing. But you must close the cursor for them to be written to the trace. If you are using sqlplus, either close the trace or the session, otherwise sqlplus keeps the cursor for the most recent sql open. Here is an exctract from a trace where I execute the same sql twice in a row. You can even see the alter session command. PARSING IN CURSOR #1 len=84 dep=0 uid=31 oct=3 lid=31 tim=926359 hv=1785040157 ad='313afe4' select t1.d1, t1.d2, t2.d1, t2.d2 from t1, t2 where t2.fk1=t1.pk1 and t2.d2 = 499 END OF STMT PARSE #1:c=200,e=258,p=25,cr=401,cu=6,mis=1,r=0,dep=0,og=4,tim=926361 EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=926362 FETCH #1:c=0,e=2,p=2,cr=2,cu=0,mis=0,r=0,dep=0,og=4,tim=926364 STAT #1 id=1 cnt=0 pid=0 pos=0 obj=0 op='HASH JOIN ' STAT #1 id=2 cnt=0 pid=1 pos=1 obj=134295 op='TABLE ACCESS BY INDEX ROWID T2 ' STAT #1 id=3 cnt=1 pid=2 pos=1 obj=134372 op='INDEX RANGE SCAN ' STAT #1 id=4 cnt=0 pid=1 pos=2 obj=134293 op='TABLE ACCESS FULL T1 ' = PARSING IN CURSOR #1 len=53 dep=0 uid=31 oct=47 lid=31 tim=926366 hv=3759542639 ad='316aac4' BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END; END OF STMT PARSE #1:c=1,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=926367 APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240 EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=926368 *** 2003-10-01 09:39:52.448 = PARSING IN CURSOR #1 len=69 dep=0 uid=31 oct=42 lid=31 tim=928350 hv=589283212 ad='30f70b8' alter session set events '10046 trace name context forever, level 8' END OF STMT PARSE #1:c=2,e=2,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=928351 EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=928351 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0 *** 2003-10-01 09:40:02.682 WAIT #1: nam='SQL*Net message from client' ela= 1021 p1=1413697536 p2=1 p3=0 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 = PARSING IN CURSOR #1 len=53 dep=0 uid=31 oct=47 lid=31 tim=929375 hv=3759542639 ad='316aac4' BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END; END OF STMT PARSE #1:c=2,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=929376 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 APPNAME mod='01@ c:\tmp\tst.sql' mh=764572216 act='' ah=4029777240 EXEC #1:c=1,e=1,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=929377 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 = PARSING IN CURSOR #1 len=84 dep=0 uid=31 oct=3 lid=31 tim=929380 hv=1785040157 ad='313afe4' select t1.d1, t1.d2, t2.d1, t2.d2 from t1, t2 where t2.fk1=t1.pk1 and t2.d2 = 499 END OF STMT PARSE #1:c=3,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=929382 EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=929383 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0 FETCH #1:c=1,e=1,p=0,cr=2,cu=0,mis=0,r=0,dep=0,og=4,tim=929384 WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 STAT #1 id=1 cnt=0 pid=0 pos=0 obj=0 op='HASH JOIN ' STAT #1 id=2 cnt=0 pid=1 pos=1 obj=134295 op='TABLE ACCESS BY INDEX ROWID T2 ' STAT #1 id=3 cnt=1 pid=2 pos=1 obj=134372 op='INDEX RANGE SCAN ' STAT #1 id=4 cnt=0 pid=1 pos=2 obj=134293 op='TABLE ACCESS FULL T1 ' WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 = PARSING IN CURSOR #1 len=53 dep=0 uid=31 oct=47 lid=31 tim=929388 hv=3759542639 ad='316aac4' BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END; END OF STMT PARSE #1:c=2,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=929389 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240 EXEC #1:c=2,e=2,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=929391 WAIT #1:
RE: STAT from trace
Wolfgang, I'll give level 9 a try. Yes I get PARSE, EXEC, FETCH, WAIT. My platform is AIX 5.1 For now I've run sql_trace = true and 10046 level 8 and looked at two seperate trace files. I've had this working before, but I can't remember if it was on 5.1 or when we still were at AIX 4. Thanks for the suggestions. Henry -Original Message- Wolfgang Breitling Sent: Wednesday, October 01, 2003 3:45 PM To: Multiple recipients of list ORACLE-L Do you get the PARSE, EXEC and FETCH entries for the sql? You gave us your Oracle version, but what is your platform? Would you care to try the following: since sql_trace = true corresponds to event 10046, level 1, why not set event 10046 at level 9. Maybe that'll do the trick. At 12:59 PM 10/1/2003, you wrote: Tried that. Also queried on open_cursors in a parallel session. Henry -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Daniel Fink Sent: Wednesday, October 01, 2003 1:59 PM To: Multiple recipients of list ORACLE-L Subject: Re: STAT from trace Henry, What happens if you issue another query after the query of interest? (something like select 1 from dual;) STAT should be emitted when the cursor is closed. Daniel Henry Poras wrote: Thanks. I tried both disabling the trace and quitting from the session. No luck with 10046, just sql_trace.Henry -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]mailto:[EMAIL PROTECTED] Behalf Of Jamadagni, Rajendra Sent: Wednesday, October 01, 2003 10:25 AM To: Multiple recipients of list ORACLE-L Subject: RE: STAT from trace I get STAT lines no matter how I enable the trace. Make sure you wither close the session or stop the trace so that all pending STAT lines will be written to the file. Raj -Original Message- From: Henry Poras [mailto:[EMAIL PROTECTED]mailto:[EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 9:34 AM To: Multiple recipients of list ORACLE-L Subject: STAT from trace I was creating some trace files yesterday and came across one of these problems that shows up occasionally (then I forget about it). When I ran my query using ALTER SESSION SET sql_trace=true (sqlplus; 8.1.7.4), I got the STAT line in the trace and the associated 'row source' information after running tkprof. When I ran my query using ALTER SESSION SET EVENTS '10046 trace name context forever, level 8' (or with dbms_system.set_ev), there were no STAT entries for my query. There were some for some of the recursive queries. Everything else was identical. What's up? Henry Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Henry Poras INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: STAT from trace
If you exited the Oracle session without deactivating the trace, then you should probably see a XCTEND as well, right? If you see the XCTEND but no STAT lines, then I think you've found a bug. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Henry Poras Sent: Wednesday, October 01, 2003 4:45 PM To: Multiple recipients of list ORACLE-L Wolfgang, I'll give level 9 a try. Yes I get PARSE, EXEC, FETCH, WAIT. My platform is AIX 5.1 For now I've run sql_trace = true and 10046 level 8 and looked at two seperate trace files. I've had this working before, but I can't remember if it was on 5.1 or when we still were at AIX 4. Thanks for the suggestions. Henry -Original Message- Wolfgang Breitling Sent: Wednesday, October 01, 2003 3:45 PM To: Multiple recipients of list ORACLE-L Do you get the PARSE, EXEC and FETCH entries for the sql? You gave us your Oracle version, but what is your platform? Would you care to try the following: since sql_trace = true corresponds to event 10046, level 1, why not set event 10046 at level 9. Maybe that'll do the trick. At 12:59 PM 10/1/2003, you wrote: Tried that. Also queried on open_cursors in a parallel session. Henry -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Daniel Fink Sent: Wednesday, October 01, 2003 1:59 PM To: Multiple recipients of list ORACLE-L Subject: Re: STAT from trace Henry, What happens if you issue another query after the query of interest? (something like select 1 from dual;) STAT should be emitted when the cursor is closed. Daniel Henry Poras wrote: Thanks. I tried both disabling the trace and quitting from the session. No luck with 10046, just sql_trace.Henry -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]mailto:[EMAIL PROTECTED] Behalf Of Jamadagni, Rajendra Sent: Wednesday, October 01, 2003 10:25 AM To: Multiple recipients of list ORACLE-L Subject: RE: STAT from trace I get STAT lines no matter how I enable the trace. Make sure you wither close the session or stop the trace so that all pending STAT lines will be written to the file. Raj -Original Message- From: Henry Poras [mailto:[EMAIL PROTECTED]mailto:[EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 9:34 AM To: Multiple recipients of list ORACLE-L Subject: STAT from trace I was creating some trace files yesterday and came across one of these problems that shows up occasionally (then I forget about it). When I ran my query using ALTER SESSION SET sql_trace=true (sqlplus; 8.1.7.4), I got the STAT line in the trace and the associated 'row source' information after running tkprof. When I ran my query using ALTER SESSION SET EVENTS '10046 trace name context forever, level 8' (or with dbms_system.set_ev), there were no STAT entries for my query. There were some for some of the recursive queries. Everything else was identical. What's up? Henry Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Henry Poras INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message
