RE: Interpretation of TKPROF output
Wow! This is a completely new view on the TKPROF. So far, I've been inclined to look upon the TKPROF output as on the holly scripture. Mentioning the tkprof bugs is like discovering Kumran rolls! > -Original Message- > From: Cary Millsap [mailto:[EMAIL PROTECTED]] > Sent: Monday, October 14, 2002 1:32 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Interpretation of TKPROF output > > > Actually two things can cause differences between tkprof output and > "explain plan" output: > > 1. Schema changes between when the trace output was collected and when > the tkprof was executed. > > 2. tkprof bugs. Tkprof often produces incorrect execution > plans from the > STAT lines in the trace file. You can tell by viewing the STAT lines > yourself. A line's pid is that row source operation's parent > row source > id. Children at the same level are listed in id order. > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Hotsos Clinic, Oct 15-17 Dallas, Dec 9-11 Honolulu > - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas > - Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas > > > -Original Message- > Bobak > Sent: Sunday, October 13, 2002 7:53 PM > To: Multiple recipients of list ORACLE-L > > Peter, > > "Row Source Operation" is what Oracle actually did, or, at least what > was recorded in the trace file. "Execution Plan" is what > TkProf did in > response to you specifying "explain=" option. They can > differ if things > changed from the time the trace was done to the time the explain was > executed. Particularly, if stats changed, or session modifiable > parameters differed between the session being traced and the session > that was established at TkProf execution time. > > When they differ, the "Row Source Operation" is a better bet > as to what > actually happened. > > Hope that helps, > > -Mark > > On Sun, 2002-10-13 at 16:03, Schauss, Peter wrote: > > I am running tkprof on some trace files with the following options: > > (Oracle 8.1.7) > > > > explain=/ sort=exeqry,fchqry,execu,fchcu > > > > Output after each query includes the following two tables: > > > > Rows Row Source Operation > > --- --- > > 36 INLIST ITERATOR > > 36 TABLE ACCESS BY INDEX ROWID PART_LIST > > 72INDEX RANGE SCAN (object id 3451) > > > > > > Rows Execution Plan > > --- --- > > 0 SELECT STATEMENT GOAL: CHOOSE > > 36 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PART_LIST' > > > > The "Execution Plan" table corresponds to the Explain Plan section > > of the Performance manual. There is no mention, however, of the > > "Row Source Operation" table. I note particularly, that in > some cases > > the first table seems to agree with the second. In others, however, > such as > > the example here, the Row Source table shows use of an > index while the > > Execution Plan shows a Table Access (Full table scan?). > > > > Am I interpreting this correctly? If so, what is going on? > > > > Thanks, > > > > Peter Schauss > > Northrop Grumman Corporation > > [EMAIL PROTECTED] > -- > -- > Mark J. Bobak > Oracle DBA > [EMAIL PROTECTED] > "It is not enough to have a good mind. The main thing is to use it > well." > -- Rene Descartes > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Mark J. Bobak > 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.com > -- > Author: Cary Millsap > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hostin
RE: Interpretation of TKPROF output
Actually two things can cause differences between tkprof output and "explain plan" output: 1. Schema changes between when the trace output was collected and when the tkprof was executed. 2. tkprof bugs. Tkprof often produces incorrect execution plans from the STAT lines in the trace file. You can tell by viewing the STAT lines yourself. A line's pid is that row source operation's parent row source id. Children at the same level are listed in id order. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 15-17 Dallas, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas -Original Message- Bobak Sent: Sunday, October 13, 2002 7:53 PM To: Multiple recipients of list ORACLE-L Peter, "Row Source Operation" is what Oracle actually did, or, at least what was recorded in the trace file. "Execution Plan" is what TkProf did in response to you specifying "explain=" option. They can differ if things changed from the time the trace was done to the time the explain was executed. Particularly, if stats changed, or session modifiable parameters differed between the session being traced and the session that was established at TkProf execution time. When they differ, the "Row Source Operation" is a better bet as to what actually happened. Hope that helps, -Mark On Sun, 2002-10-13 at 16:03, Schauss, Peter wrote: > I am running tkprof on some trace files with the following options: > (Oracle 8.1.7) > > explain=/ sort=exeqry,fchqry,execu,fchcu > > Output after each query includes the following two tables: > > Rows Row Source Operation > --- --- > 36 INLIST ITERATOR > 36 TABLE ACCESS BY INDEX ROWID PART_LIST > 72INDEX RANGE SCAN (object id 3451) > > > Rows Execution Plan > --- --- > 0 SELECT STATEMENT GOAL: CHOOSE > 36 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PART_LIST' > > The "Execution Plan" table corresponds to the Explain Plan section > of the Performance manual. There is no mention, however, of the > "Row Source Operation" table. I note particularly, that in some cases > the first table seems to agree with the second. In others, however, such as > the example here, the Row Source table shows use of an index while the > Execution Plan shows a Table Access (Full table scan?). > > Am I interpreting this correctly? If so, what is going on? > > Thanks, > > Peter Schauss > Northrop Grumman Corporation > [EMAIL PROTECTED] -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] "It is not enough to have a good mind. The main thing is to use it well." -- Rene Descartes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark J. Bobak 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.com -- 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 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: Interpretation of TKPROF output
Peter, In the perfect world you would expect the two explain plans (don't worry about the different headings) to be identical. Someone can probably answer this precisely but the first one is created during the trace and the second one is provided because you supplied the "explain=user/pass" option to tkprof - it basically says "go back and get an explain plan for each query". Are you sure that both explain plans are generated using the same username on the same instance? Is one perhaps connecting to a different database such as development? In essence - if you run "sqlplus user/pass" from the same place you ran tkprof are you connected to the exact same location as the session that created the trace file? Unfortunately if you are connected to the same area then I cannot help much more. Perhaps try taking the statement and use autotrace in sqlplus to see which plan it provides. Perhaps row level security of something is kicking in and modifying the query (this caught us out once when explain plans changed because we were connected as a different user)? Regards, Mark. "Schauss, Peter" <[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> hgrum.com> cc: Sent by: Subject: Interpretation of TKPROF output [EMAIL PROTECTED] 14/10/02 06:03 Please respond to ORACLE-L I am running tkprof on some trace files with the following options: (Oracle 8.1.7) explain=/ sort=exeqry,fchqry,execu,fchcu Output after each query includes the following two tables: Rows Row Source Operation --- --- 36 INLIST ITERATOR 36 TABLE ACCESS BY INDEX ROWID PART_LIST 72INDEX RANGE SCAN (object id 3451) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 36 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PART_LIST' The "Execution Plan" table corresponds to the Explain Plan section of the Performance manual. There is no mention, however, of the "Row Source Operation" table. I note particularly, that in some cases the first table seems to agree with the second. In others, however, such as the example here, the Row Source table shows use of an index while the Execution Plan shows a Table Access (Full table scan?). Am I interpreting this correctly? If so, what is going on? Thanks, Peter Schauss Northrop Grumman Corporation [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Schauss, Peter 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). <<>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer d
Re: Interpretation of TKPROF output
Peter, "Row Source Operation" is what Oracle actually did, or, at least what was recorded in the trace file. "Execution Plan" is what TkProf did in response to you specifying "explain=" option. They can differ if things changed from the time the trace was done to the time the explain was executed. Particularly, if stats changed, or session modifiable parameters differed between the session being traced and the session that was established at TkProf execution time. When they differ, the "Row Source Operation" is a better bet as to what actually happened. Hope that helps, -Mark On Sun, 2002-10-13 at 16:03, Schauss, Peter wrote: > I am running tkprof on some trace files with the following options: > (Oracle 8.1.7) > > explain=/ sort=exeqry,fchqry,execu,fchcu > > Output after each query includes the following two tables: > > Rows Row Source Operation > --- --- > 36 INLIST ITERATOR > 36 TABLE ACCESS BY INDEX ROWID PART_LIST > 72INDEX RANGE SCAN (object id 3451) > > > Rows Execution Plan > --- --- > 0 SELECT STATEMENT GOAL: CHOOSE > 36 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PART_LIST' > > The "Execution Plan" table corresponds to the Explain Plan section > of the Performance manual. There is no mention, however, of the > "Row Source Operation" table. I note particularly, that in some cases > the first table seems to agree with the second. In others, however, such as > the example here, the Row Source table shows use of an index while the > Execution Plan shows a Table Access (Full table scan?). > > Am I interpreting this correctly? If so, what is going on? > > Thanks, > > Peter Schauss > Northrop Grumman Corporation > [EMAIL PROTECTED] -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] "It is not enough to have a good mind. The main thing is to use it well." -- Rene Descartes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark J. Bobak 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: Interpretation of TKPROF output
Hello; Somebody knows how to connect to a Oracle DB, In a Solaris 8 Server, and insert data With Visual Basic, through internet? Thank you. Francisco Murillo -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Francisco Murillo Montoya 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).
