Interesting SQL that displays time taken to do row count?

From: Snewber <snew_at_snew.com>
Date: Fri, 30 Sep 2005 16:36:30 +1000
Message-ID: <dhimde$236o$1@bunyip2.cc.uq.edu.au>

I can't get the correct value for t2 - t1 for the following query. Maybe adding t2 - t1 changes the execution plan and thus you get incorrect results? In that case what hints or changes are needed to fix it?

select

(dbms_utility.get_time ) t1,
(select count(*) from dba_source) row_count,
(dbms_utility.get_time) t2

from dual;

The following query seems to return the correct SECONDS_TO_EXECUTE, but not for the first column, DOES_NOT_WORK. When I remove the column, DOES_NOT_WORK, it makes the SECONDS_TO_EXECUTE column incorrect.

select

	hsecs - t1 DOES_NOT_WORK,
	row_count, round((t2 - t1)/100,5) SECONDS_TO_EXECUTE

from (
select

(select * from V$TIMER) t1,
(select count(*) from dba_source) row_count,
(select * from V$TIMER) t2

from dual
), v$timer;

Does anyone know how to improve the above query, or write a new query in one SQL statement, so that you can get the following:

ROW_COUNT SECONDS_TO_EXECUTE

---------   ------------------
99999       1.99

If you run the above query from SQLPlus with timing on, and timing on said 2.2 seconds, would that mean the difference , 2.22 - 1.99, was due to how long it took to send and receive the data? Received on Fri Sep 30 2005 - 01:36:30 CDT