Re: tuning db_multiblock_read_count

At 07:27 AM 5/2/2003 -0800, you wrote:
>We have a default block size of 8k on a Solaris v5 in Oracle 8173 which is
>too small, but we cant change it now.
>
>Id like to tunin the db_multiblock_read_count which is set to 16 which I
>believe is the default. Any suggestions on what to set it to? Or where I
>can find documentation on this?
>
>We do alot of large full table scans and that small block size is killing
us.

Before changing db_file_multiblock_read_count make sure that your extent sizes are multiples of the new setting * db_block_size, e.g. if you want to double it to 32 then all the extents of all tables or indexes that get full scanned should be in integral multiples of 32*8 = 256K or else it won't buy you anything.

Secondly, increasing db_file_multiblock_read_count will reduce the cost of FTS and FFS for the CBO, so the optimizer may change some plans to take advantage of these lower cost access paths. So be prepared. You can combat that to some degree by lowering the optimizer_index_cost_adj from its default of 100. But now you are already changing two init.ora parameters which can affect a multitude of sql, potentially not all for the better.

Things will be better in 9i where there are two values for multiblock read efficiency - one for the optimizer to make its decision and one for the execution engine so there you can jack up the second setting so that full scans, when they need to be done, are as efficient as possible but have the first at a value such that it does not deter the CBO from index access needlessly.
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited. If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately. We honour similar requests relating to the privacy of email communications.

Cette communication par courrier electronique est une communication privee a l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie. Les renseignements contenus dans ce courriel sont confidentiels et si vous n'etes pas le destinataire prevu, vous etes avise, par les presentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit. Si vous n'etes pas specifiquement autorise a recevoir ce courriel ou si vous croyez l'avoir recu par erreur, veuillez en aviser l'expediteur original immediatement. Nous respectons les demandes similaires qui touchent la confidentialite des communications par courrier electronique.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).