Cost is Time

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 the output from dbms_xplan.display() in 10g onwards. The table has a column called time that the function reports. It is the prediction of time that will be spent generating each row source once, 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	... 
;