Re: optimizer_index_cost_adj and optimizer_index_caching


At 12:19 AM 3/6/2004, you wrote:

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

:
: I have not figured out yet if that can lead to differences in plan
: compositions or if it is guaranteed that the relative costs of  all plan
: components remain the same when comparing a plan stemming from having
: o_i_c_a=25 (i.e. single reads cost are 1/4 of multi read costs) vs. having
: system statistics where mreadtim = 4*sreadtim.
:

In theory it might - It would depend
on how Oracle handles the the "plus 1"
for tablescans in the new calculation.

I am not talking about that, but about join costs. The SM costs are (according to Oracle documentation):
cost of outer table access + cost of inner table access + sort cost

both table access costs get modified by the same factor - let's say 1/4 or 4 to keep with the example - but that is not true for the sort cost, so in the case of using o_i_c_a the sort costs would get exaggerated compared to the baseline, whereas in the case of system statistics they may get marginalized.

Things could get even more complicated for HA joins:

HA cost = (outer access cost * # hash partitions) + inner access cost

As I said, I have not thought or tested it through.

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com 

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: