Re: SQL Cost Compared To Elapsed Time

To add to Wolfgang's note.

You should include, and join, on the
child_number, as the same statement
text could have many different child
cursors with different execution plans.
Probably include the address as well
to cater for the unlikely chance that you had two texts with the same hash_value.

I would also eliminate command_type = 47 (pl/sql) from v$sql; and executions = 0 (the plan has probably been flushed already).

It might also be worth highlighting cases where buffer_gets is much higher than cost, because cost is a measure of I/O time - but for OLTP systems particularly - I/O time is a function of I/O requests, and the optimizer thinks (to a very rough approximation) that all logical I/Os are physical I/Os.

Tuning for large systems might be an interesting exercise. But choosing predicates to eliminate rows from v$sql and then using a nested loop might be the best option.

It's important to point out that the method is only going to identify extreme cases - and will probably miss some that are important, and will probably report lots that are red herrings.

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

> Date: Wed, 19 Apr 2006 08:59:45 -0500
> From: "Ethan Post" <post.ethan_at_gmail.com>
> 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;
>

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