Re: Help: Is cost of explain plan reliable?

In article <l.1019244563.1055114746@[64.94.198.252]>, u518615722_at_spawnkill.ip-mobilphone.net says...
>
>When we tune the sql statement, can we trust cost by the explan plan,
>or we should always set timing on and test ourselves?
>

the *cost* has no bearing, no implication, no impact on the runtime.

We take a query.
We generate umpteen plans for it. Each plan is assigned a relative cost. We take the query with the least cost and run it.

You cannot really take the costs of two different queries and compare them, it is apples and oranges.

A query with a cost of 1 billion may return subsecond. A query with a cost of 1 might take a day.

It is just that in all of the plans generated -- for their respective queries -- they had the lowest cost in that set of equivalent plans.

>For instance,
>
>the following statement by Don Burleson
>
> select
> to_char(snap_time,'yyyy-mm-dd HH24:mi:ss') mydate,
> new.name pool,
> new.buffer_busy_wait-old.buffer_busy_wait buffer_busy_wait,
> senew.time_waited-seold.time_waited time_waited
> from
> perfstat.stats$system_event seold,
> perfstat.stats$system_event senew,
> perfstat.stats$buffer_pool_statistics old,
> perfstat.stats$buffer_pool_statistics new,
> perfstat.stats$snapshot sn
> where
> senew.snap_id = sn.snap_id
> and
> seold.snap_id = (select max(snap_id) from stats$snapshot where
>snap_id < senew.snap_id)
> and
> senew.event =seold.event
> and
> senew.event = 'buffer busy waits'
> and
> new.name = old.name
> and
> new.snap_id = sn.snap_id
> and
> old.snap_id = (select max(snap_id) from stats$snapshot where
>snap_id < new.snap_id)
> and
> new.buffer_busy_wait-old.buffer_busy_wait > 1
> group by
> senew.time_waited-seold.time_waited,
> to_char(snap_time,'yyyy-mm-dd HH24:mi:ss'),
> new.name,
> new.buffer_busy_wait-old.buffer_busy_wait
> order by to_char(snap_time,'yyyy-mm-dd HH24:mi:ss')
>
>in his book "high-perfomance Tuning with STATSPACK" to get the statistics about
>buffer_busy_wait every minutes.
>
>When I explain the plan, the cost is only 800, while it takes 5 minutes
>to run,
>
>I rewrite the sql
>
> select
> to_char(sn.snap_time,'yyyy-mm-dd HH24:mi:ss') mydate,
> t.pool,
> t.buffer_busy_wait,
> r.time_waited
> From perfstat.stats$snapshot sn ,
> (select /*+ ordered */
> new.snap_id,
> new.name pool,
> new.buffer_busy_wait-old.buffer_busy_wait buffer_busy_wait
> from
> perfstat.stats$buffer_pool_statistics old,
> perfstat.stats$buffer_pool_statistics new
> where
> old.snap_id =
> (select max(snap_id) from stats$buffer_pool_statistics where
>snap_id < new.snap_id)
> and new.buffer_busy_wait-old.buffer_busy_wait > 1
> and new.name = old.name
> group by new.snap_id,new.name, new.buffer_busy_wait-
>old.buffer_busy_wait ) t,
> (select senew.snap_id,
> senew.time_waited-seold.time_waited time_waited
> from perfstat.stats$snapshot snsenew,
> perfstat.stats$system_event senew,
> perfstat.stats$system_event seold
> where senew.snap_id = snsenew.snap_id
> and seold.snap_id =
> (select max(snap_id) from stats$snapshot where snap_id
><senew.snap_id)
> and senew.event = 'buffer busy waits'
> and seold.event = 'buffer busy waits'
> group by senew.snap_id,senew.time_waited-seold.time_waited) r
>where sn.snap_id = t.snap_id
> and sn.snap_id = r.snap_id
> and sn.snap_time > sysdate - 1
>
>using the inline view, the cost is 5112, but it only took 25 seconds,
> it makes me wonder should we trust cost by explain plan at all?
>
>
>Thanks for you comments
>
>
>
>
>
>
>
>--
>Sent by dbadba62 from hotmail element from com
>This is a spam protected message. Please answer with reference header.
>Posted via http://www.usenet-replayer.com/cgi/content/new

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp