The cost of a query was always supposed to be a representation of the execution time for that query – although expressed in an unusual unit.
If you doubt this, take a look at the plan_table, and output from dbms_xplan.display() in 10g. The table has a column called time, and the function reports it. It is the prediction of time that will be spent generating each row source, hence – for the top line of the plan – the predicted time of execution.
A simple check will show you that this time figure is derived as the cost column multiplied by the value of the sreadtim value from sys.auxstats$, divided by 1,000 to convert it to seconds.
So if you currently run 9i, and use your own SQL scripts to report execution plans (instead of using dbms_xplan.display()) you may want to add a time column to your output. The following pl/sql block is all you need to get going:
rem rem get_sreadtim.sql rem rem If there are no stats, Oracle raises ORA-20003 rem variable m_value number; variable m_status varchar2(64) declare m_start_date date; m_stop_date date; no_stats exception; pragma exception_init(no_stats, -20003); begin begin dbms_stats.get_system_stats( status => :m_status, dstart => m_start_date, dstop => m_stop_date, pname => 'SREADTIM', pvalue => :m_value ); exception when no_stats then :m_status := 'No Stats'; :m_value := 10.0; when others then raise; end; end; / column m_status format a20 print m_status print m_value
Once you’ve got the sreadtim (which I’ve given a default value of 10 milliseconds – you may want to adjust this to match your hardware), you can add one more column to your plan_table query, something like:
select ... round(cost * :m_value / 1000,3) time, ... from plan_table where ... ;