Re: an easier way to time a sql statement?

Chuck wrote:
> 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

Unfortunately this only works when granted the PLUSTRC role.

It is a useful tool, however.

David Fitzjarrell Received on Wed Dec 07 2005 - 14:37:54 CST