Oracle Scratchpad

December 11, 2006

Cost is Time

Filed under: CBO,Execution plans — Jonathan Lewis @ 7:02 pm BST Dec 11,2006

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	... 
;   

10 Comments »

  1. Hey – is this column useful? Tom Kyte pretty much says if you are using cost to tune different queries, you’re barking up the wrong tree. Cost is not reliable. You agree? Maybe to a different extent?

    Comment by Doug C — December 12, 2006 @ 5:40 am BST Dec 12,2006 | Reply

  2. Doug, In many cases, cost is not “very reliable” as an indicator; but if you (a) know what it means, (b) know what makes the optimizer go wrong, and (c) don’t mess too much with poorly understood parameters – then it makes sense to take note of what it is telling you.

    Comment by Jonathan Lewis — December 12, 2006 @ 7:12 am BST Dec 12,2006 | Reply

  3. I was working on your article but I couldn’t match the time value from the cost as you mentioned, I hope you find time to help me sir, thank you.

    Here is the example on Oracle XE;

    SELECT pname, pval1 FROM sys.aux_stats$ WHERE sname = ‘SYSSTATS_MAIN';

    PNAME PVAL1
    —————————— ———-
    CPUSPEED 515
    CPUSPEEDNW 506,097
    IOSEEKTIM 12,242
    IOTFRSPEED 21258,578
    MAXTHR 6489088
    MBRC 78
    MREADTIM 34,979
    SLAVETHR
    SREADTIM 7,798

    9 rows selected.

    execute dbms_random.seed(0)
    drop table t1 purge;
    create table t1 as select
    rownum id,
    trunc(dbms_random.value(1,50)) n1,
    rpad(‘x’,100) padding
    from all_objects where rownum user,
    tabname =>’T1′,
    cascade => true,
    estimate_percent => null,
    granularity => ‘default’,
    method_opt => ‘for all columns size 1′ );
    end;
    /
    variable m_n1 number
    execute :m_n1 := 10;
    explain plan set statement_id ‘xyz’ for
    select n1, padding from t1 where id = :m_n1;
    select * from table(dbms_xplan.display(‘plan_table’,’xyz’));
    ..
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ..
    | 0 | SELECT STATEMENT | | 1 | 107 | 2 (0)| 00:00:01 |
    ..
    HR on 12/12/2006 at XE > select round(2 * 7.798 / 1000, 3) time from dual ;

    TIME
    ———-
    ,016

    Kind regards,
    Tonguç

    Comment by Tonguç — December 12, 2006 @ 10:47 am BST Dec 12,2006 | Reply

  4. Tonguc, the answer is in rounding errors. Oracle got as close as it could because it’s reporting to the second – and it’s rounding up, rather than rounding to the nearest second.

    Comment by Jonathan Lewis — December 12, 2006 @ 7:34 pm BST Dec 12,2006 | Reply

  5. [...] But when you run parallel, you calculate the resource requirement for the query then share the resource load across the parallel execution slaves. Assuming the slaves don’t interfere with each other the resources handled by each of N slaves will, in the simplest cases, be 1/N’th of the total resource requirement – which means the response time should be 1/N’th of the serial response time. Hence for parallel execution, you need to show the cost implied by the resource requirement (Resc) and the cost implied by the response time (Resp) of a single slave as it handles its shared of the resources.  (Remember: cost is time). [...]

    Pingback by Resc/Resp « Oracle Scratchpad — January 28, 2007 @ 11:33 pm BST Jan 28,2007 | Reply

  6. [...] Cost: The “cost” column of an execution plan is the optimizer’s way of expressing the amount of time it will take to complete the query. Unfortunately there are defects and deficiencies in the optimizer’s cost model that mean the calculations may fail to produce a reasonable estimate. Because of this it is possible for two queries to have the same cost but hugely different execution times; similarly you can have a “low-cost” query that run for ages and a “high-cost” query that completes almost instantly. Note – also that the calculation and interpretation of “cost” are harder to deal with when the optimizer is using “”first_k_rows (fkr)” optimisation.  Technically, though “cost is time”. [...]

    Pingback by Glossary « Oracle Scratchpad — September 11, 2009 @ 9:55 am BST Sep 11,2009 | Reply

  7. [...] on AskTom several years ago – but it’s wrong. As I’ve pointed out in the past, “Cost is Time”. The cost of a query represents the optimizer’s estimate of how long it will take that query [...]

    Pingback by Cost – again « Oracle Scratchpad — January 10, 2011 @ 6:50 pm BST Jan 10,2011 | Reply

  8. [...] cost estimate generated by the Cost-Based Optimizer (CBO) has always been a time estimate, although expressed in a slightly obscure unit, which is number of single block [...]

    Pingback by Cost Is Time: Next Generation « Ukrainian Oracle User Group — July 28, 2011 @ 2:36 pm BST Jul 28,2011 | Reply

  9. […] settings. It is possible to even write your own SQL script to generate execution plans. This post by Jonathan Lewis gives you a head […]

    Pingback by Understanding execution plan and cost in Oracle — September 15, 2013 @ 2:51 pm BST Sep 15,2013 | Reply

  10. […] years that the optimizer’s cost figure represents the predicted time to run: (see, for example, “Cost is time”). If you take the cost figure from the execution plan output, multiply it by the single block read […]

    Pingback by Execution Plans part 8: Cost, time, etc. – All Things Oracle — August 19, 2014 @ 12:01 pm BST Aug 19,2014 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,100 other followers