Re: Can DB_FILE_MULTIBLOCK_READ_COUNT be changed live?

From: David Kurtz <info_at_go-faster.co.uk>
Date: Thu, 13 Feb 2003 22:50:28 -0000
Message-ID: <sM%2a.2802$MD6.1317151@newsfep2-win.server.ntli.net>

Yes it can. I have done this in Oracle 8.0 in order to discourage the CBO from full scanning tables as often.
By reducing this value the full scan becomes more expensive and so is less likely to be chosen (depending on the costs of other available options).

However from Oracle 8i you should also consider the parameters OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING which control the calculation of the cost of using indexes. They are both dynamic and alterable at session level. I think it probably makes more sense to give these parameter realistic values (rather than the defaults) before you change the multiblock read count.

DB_FILE_MULTIBLOCK_READ_COUNT parameter defaults to 8. This means that Oracle will read 8 blocks in one operation. So a full table scan over 128 blocks will take 16 I/Os and will have a cost of 16. If you reduce it to say 5, the cost of the full scan goes up to 26.

---

David Kurtz
Go-Faster Consultancy Ltd.
web: www.go-faster.co.uk
sms: sms_at_go-faster.co.uk
PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

"Rhugga" <ccarson_NOBLEEPING_SPAM_at_syrrx.com> wrote in message news:3E4BCBE3.3020806_at_syrrx.com...
>
> I am using DBArtisan for Oracle management and it seems that the
> DB_FILE_MULTIBLOCK_READ_COUNT setting can be changed and active
> immediately while the database is up, without bouncing the instance. I
> was under the impression all init.ora params in Oracle 8i needed an
> instance restart.
>
> Can anyone shed some light?
>
> Thx,
> CC
>
>
>
> -----------== Posted via Newsfeed.Com - Uncensored Usenet News
==----------
> http://www.newsfeed.com The #1 Newsgroup Service in the World!
> -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers
=----- Received on Thu Feb 13 2003 - 16:50:28 CST