Re: Turn statement results off

In article <924a90f8.0106220545.2c51efee_at_posting.google.com>, awing_pilot_at_yahoo.com says...
>
>"andrew_webby at hotmail" <spam_at_no.thanks.com> wrote in message
>news:<993206880.1925.0.nnrp-12.c30bdde2_at_news.demon.co.uk>...
>> Look here:
>> SQL> set autotrace
>> Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
>> SQL>
>>
>> If you do set autotrace trace exp stat, it will run the query but not return
>> the results and show you the plan/time etc.
>>
>> "Joshua Goodstein" <awing_pilot_at_yahoo.com> wrote in message
>> news:924a90f8.0106211709.6796830c_at_posting.google.com...
>> > I remember a while a go we found a way to not display the results for
>> > a statement for tuning purposes.. IE. I want to see how long the
>> > execution takes not the returning of the rows. Does anyone have docs
>> > or know how on how this may be done?????? I thought it was a tag like
>> > echo off but i don't care if the results are 1 million or 1 row I want
>> > performance info on the statement execution itself
>> >
>> >
>> >
>> > Joshua Goodstein
>
>
>Heh, yeah i was using explain plan and autotrace but was under the
>immpression that the stats included stats on the time to actually
>execute and return the results not just execute... am i wrong? Thanks
>for your help

Well, unless you actually FETCH the rows -- the time to execute a select is very very small (a regular select -- not a select for update).

If you query:

select * from one_billion_row_table;

the execute phase will be hardly measurable - the FETCH phase is where the time will be spent.

An insert/update/delete/select FOR UPDATE -- they do most of their work in the EXECUTE phase.

A select -- does hardly any work in the execute phase, it does all of the work in the FETCH phase.

You need to retrieve the rows to evaluate the performance of the query. AUTOTRACE with TRACEONLY does this. You time the time to execute and retrieve the results -- but not the time to print them on screen.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp