RE: Trace file with tkprof

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 04 Sep 2002 19:08:21 -0800
Message-ID: <F001.004C8112.20020904190821@fatcity.com>

Content-Type: text/plain;
charset="iso-8859-1"

> -----Original Message-----
> From: Chuan Zhang [mailto:Chuan.Zhang_at_transact.com.au]
> 
> Is there any way to get the same execution statistics between 
> the finished sql statement and interrupted sql statement?
> 
> Supposed table A have ten million rows. 
> 
> If select A.a, A.b from A where ..., in sqlplus session, 
> actually the returned could be millions. I could not wait for 
> all the selected rows coming out. I have to interrupte it in
> the process. Could I still get the same execution statistics 
> in trace file?
> 
> The same happened to "set autotrace on" in sqlplus session. I 
> could only see the execution plan at the end of execution.

Would "set autotrace traceonly explain" or "set autotrace traceonly explain statistics" do what you need?
>From the SQL*Plus manual, SET command:
AUTOT[RACE] {ON|OFF|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] Displays a report on the execution of successful SQL DML statements (SELECT, INSERT, UPDATE or DELETE). The report can include execution statistics and the query execution path.

OFF does not display a trace report. ON displays a trace report. TRACEONLY displays a trace report, but does not print query data, if any. EXPLAIN shows the query execution path by performing an EXPLAIN PLAN. STATISTICS displays SQL statement statistics. Information about EXPLAIN PLAN is documented in the Oracle9i SQL Reference manual.

Using ON or TRACEONLY with no explicit options defaults to EXPLAIN STATISTICS. The TRACEONLY option may be useful to suppress the query data of large queries. If STATISTICS is specified, SQL*Plus still fetches the query data from the server, however, the data is not displayed.

The AUTOTRACE report is printed after the statement has successfully completed.

Information about Execution Plans and the statistics is documented in the Oracle9i Performance Guide and Reference manual.

When SQL*Plus produces a STATISTICS report, a second connection to the database is automatically created. This connection is closed when the STATISTICS option is set to OFF, or you log out of SQL*Plus.

------_=_NextPart_001_01C25481.0B7226E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2654.45">
<TITLE>RE: Trace file with tkprof</TITLE>
</HEAD>
<BODY>

> -----Original Message-----
 > From: Chuan Zhang [<A =
HREF=3D"mailto:Chuan.Zhang_at_transact.com.au">mailto:Chuan.Zhang_at_transact.= com.au</A>]
 > 
 > Is there any way to get the same execution =
statistics between 
 > the finished sql statement and interrupted sql =
statement?
 > 
 > Supposed table A have ten million rows. 
 > 
 > If select A.a, A.b from A where ..., in sqlplus =
session, 
 > actually the returned could be millions. I =
could not wait for 
 > all the selected rows coming out. I have to =
interrupte it in
 > the process. Could I still get the same =
execution statistics 
 > in trace file?
 > 
 > The same happened to "set autotrace =
on" in sqlplus session. I 
 > could only see the execution plan at the end of =
execution. 

 

Would "set autotrace traceonly explain" or =
"set autotrace traceonly explain statistics" do what you = need?
 From the SQL*Plus manual, SET command:
 AUTOT[RACE] {ON|OFF|TRACE[ONLY]} [EXP[LAIN]] =
[STAT[ISTICS]] 


Displays a report on the execution of successful SQL =
DML statements (SELECT, INSERT, UPDATE or DELETE). The report can = include execution statistics and the query execution path.

OFF does not display a trace report. ON displays a =
trace report. TRACEONLY displays a trace report, but does not print = query data, if any. EXPLAIN shows the query execution path by = performing an EXPLAIN PLAN. STATISTICS displays SQL statement = statistics. Information about EXPLAIN PLAN is documented in the = Oracle9i SQL Reference manual.

Using ON or TRACEONLY with no explicit options =
defaults to EXPLAIN STATISTICS. 


The TRACEONLY option may be useful to suppress the =
query data of large queries. If STATISTICS is specified, SQL*Plus still = fetches the query data from the server, however, the data is not = displayed.

The AUTOTRACE report is printed after the statement =
has successfully completed. 


Information about Execution Plans and the statistics =
is documented in the Oracle9i Performance Guide and Reference manual. =


When SQL*Plus produces a STATISTICS report, a second =
connection to the database is automatically created. This connection is = closed when the STATISTICS option is set to OFF, or you log out of = SQL*Plus.

</BODY>
</HTML>

------_=_NextPart_001_01C25481.0B7226E0--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

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: ListGuru_at_fatcity.com (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).

Received on Wed Sep 04 2002 - 22:08:21 CDT