Re: Parameter to influence Oracle's Idea of IO Cost?

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Thu, 10 May 2007 10:45:49 +0200
Message-ID: <5ag4dfF2mocmqU4@mid.individual.net>

On 09.05.2007 17:59, Valentin Minzatu wrote:
> On May 9, 11:51 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:

>> On 09.05.2007 16:58, Valentin Minzatu wrote:
>>
>>
>>
>>
>>
>>> On May 9, 10:37 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:
>>>> On 09.05.2007 16:22, Valentin Minzatu wrote:
>>>>> On May 9, 6:48 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:
>>>>>> Hi,
>>>>>> this is on 10.2.0.1.0.  I think I remember that there is a parameter
>>>>>> that will affect how Oracle costs IO but can't seem to find it (searched
>>>>>> DB Reference, Performance Tuning Guide, PL/SQL Package Reference, Web).
>>>>>>   Does my memory fail me?
>>>>>> Do you know other ways to influence how Oracle costs single block reads
>>>>>> vs. multi block reads (apart from DB_FILE_MULTIBLOCK_READ_COUNT and
>>>>>> hinting that is)?  Thank you!
>>>>>> Kind regards
>>>>>>         robert
>>>>> What is the problem you try to address by chaging the optimizer
>>>>> behavior?
>>>> I'm trying to bias the CBO towards multi block reads.
>>>> Kind regards
>>>>         robert- Hide quoted text -
>>>> - Show quoted text -
>>> Why do you need to do that? (i.e. is this a DW system? )
>> Si.  And experiments have shown that plans with MBR's perform
>> significantly better than those without.  And I'd prefer to use DB
>> parameters or other means over hints.
>>

>
> You should have a very strong case when you make changes at the
> instance level as they impact everything else in ways that are not
> always predictable. The reason I asked you what are you trying to
> achieve was to see if there are no other ways to accomplish the same
> goal without impacting the whole (i.e. build another level on the top
> of the building without having to make changes to the foundation). If
> you are only to look at how much testing would involve validating the
> one change at the database level - that is a lot. Unless you have a
> single application against that database I would try to look into
> addressing the issue somehow else.

It's a single application but you are right: changing global parameters will require more testing to make sure there are no negative effects.

Thanks

        robert Received on Thu May 10 2007 - 03:45:49 CDT