Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: SQL Cost Compared To Elapsed Time

Re: SQL Cost Compared To Elapsed Time

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Thu, 20 Apr 2006 20:15:47 +0900
Message-ID: <07f701c6646b$c742f450$f50415ac@porgand>


Interesting idea :)

One thing is that if you find a query with high elapsed time to cost unit, it doesn't necessarily mean that the execution plan itself is suboptimal, maybe just other factors are affecting the execution, e.g. CBC latch contention, very little cached blocks etc..

Tanel.

  I posted this a while back with a lot more text and I don't think it ever posted, perhaps I was hitting some size limit. So here is the really short version. It occurred to me that a good way to find SQL which Oracle may be coming up with the wrong plan for is to compare the cost to the elapsed time. If the elapsed time per unit of cost is much higher than normal then Oracle might be using the wrong plan. The query below was my attempt to locate such SQL. Has anyone ever tried this?

  col address format a30
  col cost format 99990.999
  col elap_sec_per_cost format 99990.999

  select a.address,

  1. cost, round( b.elap_time_per_exe/100000/a.cost ,3) elap_sec_per_cost from
    (select address,sum(cost) cost from v$sql_plan
    where cost is not null group by address) a,
    (select address, decode(executions,0,0,elapsed_time/executions) elap_time_per_exe from v$sqlarea) b
    where a.address=b.address order by 3 desc;
  address                              cost elap_sec_per_cost
  ------------------------------ ---------- -----------------
  070000000DDC1EC0                    2.000             0.685
  070000000EF6ED50                    5.000             0.118
  070000000D9980C0                    4.000             0.105
  070000000E487980                    5.000             0.088
  070000000FAD18A8                    5.000             0.084
  070000000D50ED10                    4.000             0.070
   

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Apr 20 2006 - 06:15:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US