Re: Perf tuning OPTIMIZER_INDEX_COST_ADJ

Hi Rich,

Have you got a heavy loaded system with hybrid loading (i.e. read + writes)? I can suppose that the problem is due to changing nature of the activity. If so, then the relative cost isn't approx. constant during the time and will be changed according
to
AutoRAID's decisions (RAID5 or RAID0+1). If it's possible try to separate 'heavy-write' and 'heavy-read' parts in your system.

Regards,
Ed

> After just migrating from 8.0.6 to 8.1.7 and RBO to CBO, I've been
> researching about perf tuning and have come across the init.ora parameter
> OPTIMIZER_INDEX_COST_ADJ. The guide, by Tim Gorman of evdbt.com, explained
> that it's default value is 100 (percent), but should probably be in the
> 10-50 range for OLTP systems. It recommended using the following query as a
> guideline to set this:
>
> SELECT event, average_wait
> FROM v$system_event
> WHERE event LIKE 'db file s%read';
>
> ...and to take the ratio of "db file sequential read" to "db file scattered
> read" as a value for OPTIMIZER_INDEX_COST_ADJ. That may be fine for most
> systems, but our ratio is around .08%. No, it's not 8% -- and it's not a
> decimal problem. I'm getting this:
>
> db file sequential read 1.83687542789106
> db file scattered read 2151.12743289383
>
> I'm reasonably certain that the seemingly outrageous "2151.127" is due to
> our use of HP's AutoRAID (SLOWWWWWW!) -- we won't be getting one in our next
> box.
>
> So, I'm a little skiddish about changing the default value from 100 to 1 or
> even 10, based on the above query alone. The scattered read average wait
> seems to be dropping steadily (it's now down to ~1900 over a period of 14
> hours), so I'm trying to provide a "good" value for this parm without
> adversely affecting the optimizer.
>
> Anyone have any ideas?
>
> TIA!
>
> Rich Jesse System/Database Administrator
> Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jesse, Rich
> INET: Rich.Jesse_at_qtiworld.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: Shevtsov, Eduard
  INET: EShevtsov_at_flagship.ru

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).