Re: good value for optimizer_index_cost_adj

Based on documentation, OPTIMIZER_INDEX_COST_ADJ seems to provide additional comparison information for the CBO in terms of the relative cost of different types of I/O. To make a long story short, I believe that guidance can come from examining timing statistics from the wait-events "db file scattered read" (associated with FULL table scans) and "db file sequential read" (associated with indexed scans) and looking at their respective average wait times:

    select event, average_wait from v$system_event where event like 'db file s%'

Not to use that dirty word "ratio" lightly in this forum :-), you should calculate the ratio of::

    (avg-wait-for-db-file-sequential-read / avg-wait-for-db-file-scattered-read) * 100

which can be considered as a possible setting for OPTIMIZER_INDEX_COST_ADJ.

 Of course, all of the common-sense caveats apply: don't adhere to this formula slavishly because there might be any number of anomalies in the AVERAGE_WAIT information from V$SYSTEM_EVENT due to low uptime, etc. Take several samples over time, if possible (i.e. the axiom of "measure twice, cut once" works as well in database administration as in carpentry). Test, test, test before implementing in production...

At IOUG-A, I heard discussion that the OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ were two separate approaches developed by different development teams within Oracle that had the exact same purpose. So, the argument was advanced that setting *either* one *or* the other was sufficient, but not *both*. Not having any access to the internal goings-on in Oracle ST Development, I'm sticking with the idea that these two parameters are addressing *different* and very specific issues, so they both should be considered and used independently of one another...

I have a paper on this topic at
http://www.EvDBT.com/SearchIntelligenceCBO.doc that discusses these issues in more depth...

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, April 24, 2002 8:58 AM

> Hi,
>
> Oracle 817/Solaris 8.
>
> Users are doing select joining using the PKs of 2
> partitionned tables. Partitionned key and the primary
> key are the same.
>
> The access plan is a nested loop with a full table
> scan on the first table which hold 700 000 rows.
> The block size is 16K, I assume that's why Oracle is
> doing FTS.
> By using optimizer_index_cost_adj, I can make Oracle
> use the PK of the first table. I've used 50 as a value
> for optimizer_index_cost_adj.
> Is that too much ?
> Where can I get some metrics on that parameter ?
>
> TIA
>
>
> =====
> Stéphane Paquette
> DBA Oracle, consultant entrepôt de données
> Oracle DBA, datawarehouse consultant
> stephane_paquette_at_yahoo.com
>
> ___________________________________________________________
> Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français !
> Yahoo! Mail : http://fr.mail.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: =?iso-8859-1?q?paquette=20stephane?=
> INET: stephane_paquette_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).