Hi,

I need to take the elapsed time / response time / query execution time
alone of a particular query. For example, if i try to take the
response time of the following query :

set timing on
select * from table_name;
..
..
8000 rows selected
elapsed time : 2 mins

so my log gets filled up with 8000 rows and when i try for more
queries, the log if going like 1GB in size. but i need to check only
the elapsed time of the query and not the query output. Is there
anyway to do so?
Can anyone please help me out on this? Thanks in Advance.

With Regards,
Raja.

raja wrote:

Using PL/SQL?

hr@> declare
2 junk owa_text.vc_arr ;
3 begin
4 select object_name bulk collect into junk from all_objects ;
5 end ;
6 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.43

thanks for the response. but i am having more than 5 queries like :

set timing on
select * from table1 where a=20;
select * from table2 where b=20 and a not in ('A');
select * from table3;
select * from table4;
select * from table5;

i dont want the output of these queries but only the elapsed time.

sorry i missed one point : its oracle 10gR2 and i am running these
queries from sqlplus :-)

set timing on
set arraysize {something fairly big - maybe 1000}
set autotrace traceonly statistics
{queries}

Oracle fetches the data, but dumps it straight into /dev/null.
No time wasted on formatting and filing.
The times you get are query and network traffic only

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com


"raja" <dextersunil (AT) gmail (DOT) com> wrote

so, set autotrace traceonly statistics, will execute the query and
just gives the elapsed time of the query and not the data ???

On Mar 15, 8:48*pm, raja <dextersu... (AT) gmail (DOT) com> wrote:

as Jonathan says,
get the elapsed time and the statistics captured during the execution
of the query

Here's an example of

SQL> create table t as
2 select rownum as c from dual connect by level <= 10000;

Tabella creata.

SQL> select count(*) from t;

COUNT(*)
----------
10000

SQL> set timing on;
SQL> set arraysize 1000
SQL> set autotrace traceonly statistics
SQL> select * from t;

Selezionate 10000 righe.

Passati: 00:00:00.28

Statistiche
----------------------------------------------------------
8 recursive calls
0 db block gets
36 consistent gets
0 physical reads
0 redo size
90442 bytes sent via SQL*Net to client
323 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed

On Mar 15, 8:48*pm, raja <dextersu... (AT) gmail (DOT) com> wrote:
as Jonathan says,
get the elapsed time and the statistics captured during the execution
of the query

Here's an example of

SQL> create table t as
2 select rownum as c from dual connect by level <= 10000;

Tabella creata.

SQL> select count(*) from t;

COUNT(*)
----------
10000

SQL> set timing on;
SQL> set arraysize 1000
SQL> set autotrace traceonly statistics
SQL> select * from t;

Selezionate 10000 righe.

Passati: 00:00:00.28

Statistiche
----------------------------------------------------------
8 recursive calls
0 db block gets
36 consistent gets
0 physical reads
0 redo size
90442 bytes sent via SQL*Net to client
323 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed

On Mar 16, 6:28*am, Donatello Settembrino
<donatello.settembr... (AT) gmail (DOT) com> wrote:

I would add that by not doing the formating or transferring the data
to something useful - like an application or file - your timings are
going to be off. So, it really depends on what is your real goal here.