Oracle invents time machine - optimizer now faster than light.
Look carefully at the following (genuine) extract from a 9.0.1.3 trace file, and examine the TIM= entries: PARSING IN CURSOR #2 len=210 dep=1 uid=0 oct=3 lid=0 tim=1019495629365212 hv=787810128 ad='6e0a278' select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2 END OF STMT PARSE #2:c=0,e=492,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,tim=1019495629365100 Note how Oracle has managed to finish parsing the statement 112 microseconds before it started. Now that's how to pass a benchmark ! Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Oracle invents time machine - optimizer now faster than light.
Hello Jonathan, isn't it soft parse (mis=0) ? regards... Jonathan Lewis wrote: Look carefully at the following (genuine) extract from a 9.0.1.3 trace file, and examine the TIM= entries: PARSING IN CURSOR #2 len=210 dep=1 uid=0 oct=3 lid=0 tim=1019495629365212 hv=787810128 ad='6e0a278' select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2 END OF STMT PARSE #2:c=0,e=492,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,tim=1019495629365100 Note how Oracle has managed to finish parsing the statement 112 microseconds before it started. Now that's how to pass a benchmark ! Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists 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). -- Danisment Gazi Unal http://www.ubTools.com
Re: Oracle invents time machine - optimizer now faster than light.
You mean a soft parse is allowed to finish before it starts ? Or maybe it has to be a soft parse, by SYS, on a recursive statement that uses the rule-based optimiser ;) Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 23 April 2002 13:02 light. |Hello Jonathan, | |isn't it soft parse (mis=0) ? | |regards... | |Jonathan Lewis wrote: | | Look carefully at the following (genuine) extract | from a 9.0.1.3 trace file, and examine the TIM= | entries: | | PARSING IN CURSOR #2 len=210 dep=1 uid=0 oct=3 lid=0 | tim=1019495629365212 hv=787810128 ad='6e0a278' | select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, | timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, | density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 | and intcol#=:2 | END OF STMT | PARSE | #2:c=0,e=492,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,tim=1019495629365100 | | Note how Oracle has managed to finish parsing the | statement 112 microseconds before it started. | | Now that's how to pass a benchmark ! | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Oracle invents time machine - optimizer now faster than light.
Do you have any more examples? and if so, is the second time always suffixed with '00'. I'm wondering if the concluding time is still centiseconds (or whatever precision is appropriate to cause the 'problem') Cheers Connor --- Jonathan Lewis [EMAIL PROTECTED] wrote: Look carefully at the following (genuine) extract from a 9.0.1.3 trace file, and examine the TIM= entries: PARSING IN CURSOR #2 len=210 dep=1 uid=0 oct=3 lid=0 tim=1019495629365212 hv=787810128 ad='6e0a278' select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2 END OF STMT PARSE #2:c=0,e=492,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,tim=1019495629365100 Note how Oracle has managed to finish parsing the statement 112 microseconds before it started. Now that's how to pass a benchmark ! Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Oracle invents time machine - optimizer now faster than light.
Don't lose any sleep on it, but here's another: PARSING IN CURSOR #1 len=40 dep=0 uid=54 oct=1 lid=54 tim=1019495629370923 hv=1851325355 ad='6cb3450' alter table TESTLONG modify ( text clob) END OF STMT PARSE #1:c=1,e=11122,p=1,cr=1,cu=0,mis=1,r=0,dep=0,og=4,tim=101949562937 0823 NB - not recursive, not SYS, and a hard parse to boot. But this one is much more interesting: PARSING IN CURSOR #1 len=68 dep=0 uid=54 oct=42 lid=54 tim=1019495629353714 hv=287842151 ad='6cba5e8' alter session set events '10046 trace name context forever, level 8' END OF STMT EXEC #1:c=0,e=13,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1019495629321662 Note the complete absence of PARSE #1, and the 'massive' 32,000 microsecond back-step. In general, however, the PARSE seems to be a fairly persistent 110 micro seconds out of step. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 23 April 2002 14:18 light. |Do you have any more examples? and if so, is the |second time always suffixed with '00'. I'm wondering |if the concluding time is still centiseconds (or |whatever precision is appropriate to cause the |'problem') | |Cheers |Connor | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Re: Oracle invents time machine - optimizer now faster than light.
Jonathan, beware, I *think* that the Time Travel Option (TTO) is licensed separately. Stephane Faroult Oriole Corporation Performance Tools Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Oracle invents time machine - optimizer now faster than light.
Hello Jonathan, I always think twice while talking to a guru. Here is my comment for your test case: 'tim' columns in these examples are not accurate. PARSING IN CURSOR #1 len=40 dep=0 uid=54 oct=1 lid=54 tim=1019495629370923 hv=1851325355 ad='6cb3450' alter table TESTLONG modify ( text clob) END OF STMT PARSE #1:c=1,e=11122,p=1,cr=1,cu=0,mis=1,r=0,dep=0,og=4,tim=101949562937 0823 Time difference : 1019495629370823 - 1019495629370923 = -100 microseconds But your elapsed time for PARSE call is 11122 microseconds. I think your second 'tim' should not be less than (1019495629370923 + 11122). Because, 'tim' column is obtained from V$TIMER when a line is written to dump. Most probably, this is a bug. I think "PARSING IN CURSOR"/"PARSE" statement doesn't always indicate when the statement is handled by Oracle. Because, there may be some recursive statements handled before the parent statements are actually handled. Statistics of these statements are not included in the parent statements. Also, as you stated in your last case, "alter session set events '10046.." or "alter session set sql_trace=true" don't include PARSE calls. If it's the first statement in raw trace file, no problem, but If not, or if a statement doesn't iclude a PARSE call, tkprof reports wrong results. Because, we know SOME statistics in raw trace files include SOME statistics of SOME recursive statements' statistics. tkprof substructs them by starting from PARSE call to current line. If tkprof can not find a PARSE call for a statement, it assumes substruction from the beginning of file to current line. This makes wrong reports. itrprof(Sorry Jared) reports an error code for this problem. I recommend the followings the tkprof users: - Enabling SQL and Event 10046 trace files from the other sessions may cause missing statistics in the trace files. - If this is not possible, enable the traces when the traced session is IDLE. regards... regards... Jonathan Lewis wrote: Don't lose any sleep on it, but here's another: PARSING IN CURSOR #1 len=40 dep=0 uid=54 oct=1 lid=54 tim=1019495629370923 hv=1851325355 ad='6cb3450' alter table TESTLONG modify ( text clob) END OF STMT PARSE #1:c=1,e=11122,p=1,cr=1,cu=0,mis=1,r=0,dep=0,og=4,tim=101949562937 0823 NB - not recursive, not SYS, and a hard parse to boot. But this one is much more interesting: PARSING IN CURSOR #1 len=68 dep=0 uid=54 oct=42 lid=54 tim=1019495629353714 hv=287842151 ad='6cba5e8' alter session set events '10046 trace name context forever, level 8' END OF STMT EXEC #1:c=0,e=13,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1019495629321662 Note the complete absence of PARSE #1, and the 'massive' 32,000 microsecond back-step. In general, however, the PARSE seems to be a fairly persistent 110 micro seconds out of step. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]> Date: 23 April 2002 14:18 light. |Do you have any more examples? and if so, is the |second time always suffixed with '00'. I'm wondering |if the concluding time is still centiseconds (or |whatever precision is appropriate to cause the |'problem') | |Cheers |Connor | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists 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). -- Danisment Gazi Unal http://www.ubTools.com
Re: Oracle invents time machine - optimizer now faster than light.
This reminds me of a (clean) limeric; There was a young lady from Bight, Whose speed was far faster than light. She went out one day in a relative way, And returned the previous night. :) Jared Jonathan Lewis [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/23/2002 02:58 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Oracle invents time machine - optimizer now faster than light. Look carefully at the following (genuine) extract from a 9.0.1.3 trace file, and examine the TIM= entries: PARSING IN CURSOR #2 len=210 dep=1 uid=0 oct=3 lid=0 tim=1019495629365212 hv=787810128 ad='6e0a278' select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2 END OF STMT PARSE #2:c=0,e=492,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,tim=1019495629365100 Note how Oracle has managed to finish parsing the statement 112 microseconds before it started. Now that's how to pass a benchmark ! Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
