Re: SQL Cost Compared To Elapsed Time
- From: Tanel Põder <tanel.poder.003@xxxxxxx>
- To: "Oracle Discussion List" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 20 Apr 2006 20:15:47 +0900
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.
----- Original Message -----
From: Ethan Post
To: Oracle Discussion List
Sent: Wednesday, April 19, 2006 10:59 PM
Subject: SQL Cost Compared To Elapsed Time
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?
-- Tested on 9ir2
col address format a30
col cost format 99990.999
col elap_sec_per_cost format 99990.999
select a.address,
a. 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
Other related posts:
