Help: Is cost of explain plan reliable?

From: Mike F <u518615722_at_spawnkill.ip-mobilphone.net>
Date: Fri, 19 Apr 2002 19:29:22 GMT
Message-ID: <l.1019244563.1055114746@[64.94.198.252]>

When we tune the sql statement, can we trust cost by the explan plan, or we should always set timing on and test ourselves?

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

Received on Fri Apr 19 2002 - 14:29:22 CDT