Re: SQL Cost Compared To Elapsed Time

Interesting approach. A correction to your script, though. Cost in v$sql_plan (as in plan_table) is cumulative, so you should just take the top value (id=0) rather summing all the costs. Also, you should use v$sql rather than v$sqlarea (courtesy Jonathan L.):

col cost format 999990
col epc head 'sec per cost' for 9.9999EEEE col sql_text for a80 trunc

select * from (
select a.hash_value, a.cost, b.elapsed_time/1000000/greatest(b.executions,1) elapsed ,
decode(b.executions,0,0,b.elapsed_time/b.executions)/1000000/greatest(a.cost,1) epc , substr(b.sql_text,1,80) sql_text
from v$sql_plan a, v$sql b
where a.hash_value=b.hash_value
  and a.id=0
) where cost > 0 and elapsed > 0
order by epc desc

HASH_VALUE COST ELAPSED sec per cost SQL_TEXT

---------- ------- ----------- ------------ ------------------
 505823550       1       0.443   4.4313E-01 INSERT INTO PS_JRN
3615053301       1       0.209   2.0904E-01 UPDATE PSIBFOLOCK 
1595745989      15       2.519   1.6791E-01 SELECT DISTINCT B.
2714012042       1       0.163   1.6337E-01 UPDATE PSSERVERSTA
2563192654       9       1.139   1.2657E-01 SELECT DISTINCT BU
 360253819     139      15.078   1.0847E-01 SELECT B.BUSINESS_
2047190263       7       0.532   7.6050E-02 SELECT DISTINCT BA
3688728200       2       0.136   6.7860E-02 UPDATE PSIBFAILOVE
1155092526       7       0.335   4.7789E-02 SELECT DISTINCT BA
3576785461       1       0.038   3.8311E-02 INSERT INTO PS_JRN
2624601608       1       0.037   3.7127E-02 SELECT BANK_SETID,
3858222034       1       0.035   3.4707E-02 SELECT ACCESSID, A
2076559622       1       0.031   3.1435E-02 UPDATE PSSERVERSTA
 420564170       2       0.058   2.8951E-02 SELECT ORDER_NO, F
  52078002     367      10.429   2.8417E-02 DELETE FROM PS_TT_
 193245752       9       0.251   2.7890E-02 INSERT INTO PS_PG_
1650065160       1       0.027   2.6629E-02 SELECT 'X' FROM PS
 579328099      10       0.259   2.5898E-02 INSERT INTO PS_PG_
3021970989       9       0.232   2.5824E-02 select sum(tt_tota
3506061038       9       0.225   2.4963E-02 INSERT INTO PS_PG_
3092184773       9       0.198   2.1946E-02 select sum(tt_tota
3573616754       1       0.021   2.1323E-02 SELECT CDMTEXT FRO
  88490035       1       0.021   2.1305E-02 SELECT VAT_RPTG_CU
4135392545       1       0.020   2.0011E-02 SELECT TT_TAXABLE_
 492564730       6       0.113   1.8784E-02 INSERT INTO PS_PG_
1455883088     106       1.922   1.8129E-02 SELECT DISTINCT DE
3328179770       2       0.036   1.7933E-02 UPDATE PSAPMSGPUBI
3293728264       2       0.034   1.7059E-02 SELECT COUNT(*) FR
4224897240       2       0.034   1.6910E-02 SELECT SETID, DESC
2382590610       1       0.017   1.6867E-02 SELECT DIRECTIONAL
2025769128       1       0.016   1.6344E-02 SELECT 'X' , VAT_R
4118163282       6       0.097   1.6194E-02 INSERT INTO PS_PG_
4028469957       4       0.064   1.5982E-02 SELECT SETID, VEND
1319430867      19       0.301   1.5845E-02 SELECT BUSINESS_UN
2734179444       3       0.046   1.5412E-02 SELECT A.COUNTRY F

Quoting Ethan Post <post.ethan_at_gmail.com>:

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

-- 
regards

Wolfgang Breitling
Oracle 7,8,8i,9i OCP DBA
Centrex Consulting Corporation
www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l