Re: Estimate a query time

"suri" <suriawan_at_gmx.de> wrote in message news:1144437234.395390.221720_at_u72g2000cwu.googlegroups.com...
> Thanks Steve. But in Oracle 9i the time estimate is not part of the
> execution plan. Seems there is no way in Oracle 9i to get the estimated
> time.
>
> Regards,
> Suri
>

If you are running 9i with system statistics (CPU costing) enabled, then the predicted time to completion is

    cost * "sreadtim"
(where sreadtim is the value pulled from sys.aux_stats$ by a call to dbms_stats.get_system_stats) That's all that 10g is doing to translate cost to time.

See also

    http://www.jlcomp.demon.co.uk/cbo_book/ch_01.html#Cost_is_time

If you are running without system statistics, then you might as well estimate that time = cost * N milliseconds where N is somewhere in the range 6 to 20, depending on how fast your discs are. It won't be very accurate because the underlying cost won't have allowed extra time for multiblock reads.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html