DomainKey-Signature: a=rsa-sha1; c=nofws;        d=gmail.com; s=gamma;        h=message-id:date:from:reply-to:to:subject:cc:in-reply-to         :mime-version:content-type:references;        b=vIps/uL+gCshg2ZTdZwbVTgOXvxpA6Y79wUZuLSsX56b8P3PuGmknLdp4M02k0nKdu         dn0ZmBmqTFh9hy/yavONJ/oBCzFhIOVDeG5Jknj/4gF36M1x08ayvNDpe/F4udF/881T         E1h8soZ1lNkRbjLC+WsP0ZIQwYJkd4IzQqG5U=
Message-ID: <7b8774110809291804w57fd4acfm132341a4fba5a601@mail.gmail.com>
Date: Mon, 29 Sep 2008 20:04:51 -0500
From: "Charles Schultz" <sacrophyte@gmail.com>
To: czeiler@ecwise.com
Subject: Re: db_file_multiblock_read_count
Cc: "Allen, Brandon" <Brandon.Allen@oneneck.com>, 	"oracle-l@freelists.org" <oracle-l@freelists.org>
In-Reply-To: <B47109E95A4F8C4AB42CC4D4169D997B14620B1AA4@ecsrm01.ecwise.local>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="----=_Part_2500_19991145.1222736691323"
References: <302232.68993.qm@web80605.mail.mud.yahoo.com>	 <B47109E95A4F8C4AB42CC4D4169D997B14620B1A9C@ecsrm01.ecwise.local>	 <04DDF147ED3A0D42B48A48A18D574C450D842C3D@NT15.oneneck.corp>	 <B47109E95A4F8C4AB42CC4D4169D997B14620B1AA4@ecsrm01.ecwise.local>
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: sacrophyte@gmail.com
------=_Part_2500_19991145.1222736691323
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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@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@OneNeck.com]
> Sent: Monday, September 29, 2008 4:47 PM
> To: Claudia Zeiler; oracle-l@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

------=_Part_2500_19991145.1222736691323
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<div dir="ltr">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.<br>
<br>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. =)<br>
<br><div class="gmail_quote">On Mon, Sep 29, 2008 at 6:57 PM, Claudia Zeiler <span dir="ltr">&lt;<a href="mailto:czeiler@ecwise.com">czeiler@ecwise.com</a>&gt;</span> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
One thing, I haven&#39;t done the bounce. I thought that this was a dynamic parameter.<br>
<br>
I will get permission to bounce this db in a sec.<br>
<br>
Btw, Allen, it is a posting from you in 2006 that I was following. &nbsp;I see that you had the same problem the. &nbsp;I guess that that makes you the expert on the topic.<br>
<div class="Ih2E3d">-Claudia<br>
<br>
<br>
-----Original Message-----<br>
From: Allen, Brandon [mailto:<a href="mailto:Brandon.Allen@OneNeck.com">Brandon.Allen@OneNeck.com</a>]<br>
Sent: Monday, September 29, 2008 4:47 PM<br>
To: Claudia Zeiler; <a href="mailto:oracle-l@freelists.org">oracle-l@freelists.org</a><br>
</div><div><div></div><div class="Wj3C7c">Subject: RE: db_file_multiblock_read_count<br>
<br>
How do you know you&#39;re &quot;being ignored&quot;? &nbsp;Just because it shows a value<br>
of 128 doesn&#39;t mean your &quot;reset&quot; isn&#39;t working - maybe 128 is the value<br>
that Oracle is coming up with? &nbsp;Try this:<br>
<br>
select isdefault from v$parameter where name =<br>
&#39;db_file_multiblock_read_count&#39;;<br>
<br>
If it&#39;s not the default, you might want to try this:<br>
<br>
alter system reset db_file_multiblock_read_count scope=spfile sid = &#39;*&#39;;<br>
<br>
Then bounce your instance and check again.<br>
<br>
Regards,<br>
Brandon<br>
<br>
--<br>
<a href="http://www.freelists.org/webpage/oracle-l" target="_blank">http://www.freelists.org/webpage/oracle-l</a><br>
<br>
<br>
</div></div></blockquote></div><br><br clear="all"><br>-- <br>Charles Schultz<br>
</div>

------=_Part_2500_19991145.1222736691323--
--
http://www.freelists.org/webpage/oracle-l