Re: SQL Cost Compared To Elapsed Time
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 20 Apr 2006 08:48:36 +0100
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.
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@xxxxxxxxx> 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;