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" <[EMAIL PROTECTED]>
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
> [EMAIL PROTECTED]
>
> ___________________________________________________________
> 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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
