Re: an easier way to time a sql statement?

DaLoverhino wrote:
> Hello. If I am writing a select statement and I am concerned about the
> performance impact, I'll try to time the sql using the following
> 'technique':
>
> DECLARE
> v_begin_time NUMBER;
> v_end_time NUMBER;
> BEGIN
> v_begin_time := DBMS_UTILITY.GET_TIME;
> EXECUTE IMMEDIATE '<put select statement here';
> v_end_time := DBMS_UTILITY.GET_TIME;
>
> DBMS_OUTPUT.PUT_LINE( 'Finished in: ' || TO_CHAR( (v_end_time -
> v_begin_time) / 100 ) || ' seconds.' );
> END;
>
>
> I might play around more with the sql statement, and then submit the
> anonymous block, and do this over again, if need be. I'm wondering if
> there is an easier way to time a sql statement, since this can be error
> prone. I was hoping something like:
>
> SELECT /*+ stopwatch */ ...... ;
>
> thanks.
>

In SQLPLUS enter "set timing on" before executing the sql.

You might also want to do "set autotrace on". It'll give you some useful tuning information like the execution plan, number of gets, sorts, etc.

SQL>select count(*) from mytable;

  COUNT(*)

     16789

Elapsed: 00:00:00.02

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 SORT (AGGREGATE)
   2 1 TABLE ACCESS (FULL) OF 'MYTABLE' Statistics

          0  recursive calls
          0  db block gets
         71  consistent gets
         68  physical reads
          0  redo size
        207  bytes sent via SQL*Net to client
        344  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed