Re: an easier way to time a sql statement?

DA Morgan wrote:
> fitzjarrell_at_cox.net wrote:
> > 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

>

Management.

> A DBA that won't install and grant it should be treated the a lead pipe.

There are situations where the DBA is not the source of the problem, as listed above. Their hands are tied by those who manage them, and in some shops the Dilbert principle is in full vigor.

> --
> Daniel A. Morgan
> http://www.psoug.org
> damorgan_at_x.washington.edu
> (replace x with u to respond)

David Fitzjarrell Received on Thu Dec 08 2005 - 11:11:06 CST