Re: db_file_multiblock_read_count

I have always been a little humored by this parameter. db_file_multiblock_read_count attempts to specify a maximum value, as does the MBRC collected by system stats. However, that does not determine how many blocks are actually scooped up in a multiblock pass. One good example is to set event 10046 on a full table scan with waits (level 8 or 12) and look for db file scattered read waits, which will have the number of blocks read during the wait.

Anohter tricky part is that the CBO will use the value of mutiblock read count to determine if a table scan is cheaper than an index scan, even if the kernel does not actually read that many blocks (or that few blocks). Fun stuff. =)

On Mon, Sep 29, 2008 at 6:57 PM, Claudia Zeiler <czeiler_at_ecwise.com> wrote:

> One thing, I haven't done the bounce. I thought that this was a dynamic
> parameter.
>
> I will get permission to bounce this db in a sec.
>
> Btw, Allen, it is a posting from you in 2006 that I was following. I see
> that you had the same problem the. I guess that that makes you the expert
> on the topic.
> -Claudia
>
>
> -----Original Message-----
> From: Allen, Brandon [mailto:Brandon.Allen_at_OneNeck.com]
> Sent: Monday, September 29, 2008 4:47 PM
> To: Claudia Zeiler; oracle-l_at_freelists.org
> Subject: RE: db_file_multiblock_read_count
>
> How do you know you're "being ignored"? Just because it shows a value
> of 128 doesn't mean your "reset" isn't working - maybe 128 is the value
> that Oracle is coming up with? Try this:
>
> select isdefault from v$parameter where name =
> 'db_file_multiblock_read_count';
>
> If it's not the default, you might want to try this:
>
> alter system reset db_file_multiblock_read_count scope=spfile sid = '*';
>
> Then bounce your instance and check again.
>
> Regards,
> Brandon
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Charles Schultz

--
http://www.freelists.org/webpage/oracle-l