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
And why wouldn't every developer have PLUSTRACE?
A DBA that won't install and grant it should be treated the a lead pipe.
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Wed Dec 07 2005 - 17:55:08 CST
