Re: db_file_multiblock_read_count

  • From: "Charles Schultz" <sacrophyte@xxxxxxxxx>
  • To: czeiler@xxxxxxxxxx
  • Date: Mon, 29 Sep 2008 20:04:51 -0500

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@xxxxxxxxxx> 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@xxxxxxxxxxx]
> Sent: Monday, September 29, 2008 4:47 PM
> To: Claudia Zeiler; oracle-l@xxxxxxxxxxxxx
> 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
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>


-- 
Charles Schultz

Other related posts: