Re: optimizer_index_cost_adj and optimizer_index_caching

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Sat, 06 Mar 2004 07:52:11 -0700
Message-Id: <6.0.3.0.2.20040306074208.0298b2e0@pop.centrexcc.com>

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_at_freelists.org 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
-----------------------------------------------------------------

Received on Sat Mar 06 2004 - 08:49:20 CST