 | | | table with keep as buffer pool see much more physical reads than the number | table with keep as buffer pool see much more physical reads than the number 2007-11-04 - By qihua wu
Back
Hi,Mark,
1: there are only two tables in the KEEP buffer.
2: Each if there are block cleanouts(or update for the table), there is no
read from the disk(only write the data from buffer to disk), that means
number of "physical read" should not increaes for update or blcok cleanouts
For KEEP BUFFER, should the block be read three times before keep inside the
KEEP buffer without aging out? Isn't it kept in the buffer ONCE it is read?
Thanks,
Qihua
On 11/1/07, Mark W. Farnham < mwf@(protected)> wrote:
>
> 1) What else is in the KEEP buffer?
>
> 2) Do you only read this table?
>
> a. block cleanouts could cause some blocks to be updated (I think
> that would be a worst case of doubling.)
>
> b. if you're updating this table (or other tables sharing its
> cluster if clustered) then you are making the blocks candidates to be
> written out, and/or creating cached block clones serving read consistency,
> so at some point you might be reading those again, though sourced from
> rollback (UNDO)).
>
>
>
> If you're not updating this table at all, then table scanning it three
> times before your batch job might work some magic. (It is not really magic.
> It covers the delayed block cleanout and relatively heats your blocks for
> this table, so it will fare marginally better getting cleared out for other
> KEEPed (KEPT?) tables from pushing it out competitively.)
>
>
>
>
>
>
> -- ---- ---- ---- ---- ---- --
>
> *From:* oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)]
> *On Behalf Of *qihua wu
> *Sent:* Wednesday, October 31, 2007 11:26 PM
> *To:* oracle-l
> *Subject:* table with keep as buffer pool see much more physical reads
> than the number of blocks in the table
>
>
>
> Hi, everyone, the oracle I use is 10.2.0.2 on HPUX IA64
>
> Our job ran very slow and from AWR report I can see that a table named
ATTRIBUTE_VALUE
> consumes most of the physical IO (Physical read was more than 2.5M), as I
> know the blocks the table has is only 0.3M(block size is 8K), so I created
> a keep buffer and set the buffer pool of ATTRIBUTE_VALUE to "keep buffer".
> I was doing this to hope that once ATTRIBUTE_VALUE is read into the keep
> buffer, it will never be paged out so there the physical read will not
> exceed 0.3M (the blocks the table has). I already set the size of the keep
> buffer large enough (4G) to hold all the blocks.
>
> But after that change, ATTRIBUTE_VALUE was still read physically much more
> times than 0.3M (about 1.6M). Shouldn't it be only 0.3M if the table was
> "kept" in the buffer?
>
Hi,Mark,<br><br>1: there are only two tables in the KEEP buffer.<br>2: Each if
there are block cleanouts(or update for the table), there is no read from
the disk(only write the data from buffer to disk), that means number of "
;physical read" should not increaes for update or blcok cleanouts
<br><br>For KEEP BUFFER, should the block be read three times before keep
inside the KEEP buffer without aging out? Isn't it kept in the buffer ONCE
it is read?<br>Thanks,<br>Qihua<br><br><div><span class="gmail_quote">
On 11/1/07, <b class="gmail_sendername">
Mark W. Farnham</b> <<a href="mailto:mwf@(protected)" target="_blank" onclick=
"return top.js.OpenExtLink(window,event,this)">
mwf@(protected)</a>> wrote:</span><blockquote class="gmail_quote" style="border
-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left:
1ex;">
<div link="blue" vlink="blue" lang="EN-US">
<div>
<p style="margin-left: 0.5in; text-indent: -0.25in;"><font color="navy" face=
"Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;"
><span>1)<font face="Times New Roman" size="1"><span>
</span>
</font></span></span></font><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: navy;">What else is in the
KEEP buffer?</span></font></p>
<p style="margin-left: 0.5in; text-indent: -0.25in;"><font color="navy" face=
"Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;"
><span>2)<font face="Times New Roman" size="1"><span>
</span>
</font></span></span></font><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: navy;">Do you only read this
table?</span></font></p>
<p style="margin-left: 1in; text-indent: -0.25in;"><font color="navy" face=
"Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;"
><span>a.<font face="Times New Roman" size="1"><span>
</span>
</font></span></span></font><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: navy;">block cleanouts could
cause some blocks to be updated (I think that
would be a worst case of doubling.)</span></font></p>
<p style="margin-left: 1in; text-indent: -0.25in;"><font color="navy" face=
"Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;"
><span>b.<font face="Times New Roman" size="1"><span>
</span>
</font></span></span></font><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: navy;">if you're
updating this table (or other tables sharing its
cluster if clustered) then you are making the blocks candidates to be written
out, and/or creating cached block clones serving read consistency, so at some
point you might be reading those again, though sourced from rollback (UNDO)).<
/span></font></p>
<p><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font
-family: Arial; color: navy;"> </span></font></p>
<p><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font
-family: Arial; color: navy;">If you're not updating this table at
all, then table scanning it three times before your batch job might work some
magic. (It is not really magic. It covers the delayed block cleanout and
relatively heats your blocks for this table, so it will fare marginally better
getting cleared out for other KEEPed (KEPT?) tables from pushing it out
competitively.)</span></font></p>
<p><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font
-family: Arial; color: navy;"> </span></font></p>
<p><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font
-family: Arial; color: navy;"> </span></font></p>
<p><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font
-family: Arial; color: navy;"> </span></font></p>
<div>
<div style="text-align: center;" align="center"><font face="Times New Roman"
size="3"><span style="font-size: 12pt;">
<hr align="center" size="2" width="100%">
</span></font></div>
<p><b><font face="Tahoma" size="2"><span style="font-size: 10pt; font-family:
Tahoma; font-weight: bold;">From:</span></font></b><font face="Tahoma" size="2"
><span style="font-size: 10pt; font-family: Tahoma;">
<a href="mailto:oracle-l-bounce@(protected)" target="_blank" onclick="return
top.js.OpenExtLink(window,event,this)">oracle-l-bounce@(protected)</a>
[mailto:<a href="mailto:oracle-l-bounce@(protected)" target="_blank" onclick=
"return top.js.OpenExtLink(window,event,this)">
oracle-l-bounce@(protected)</a>] <b><span style="font-weight: bold;">On
Behalf Of </span></b>qihua wu<br>
<b><span style="font-weight: bold;">Sent:</span></b> Wednesday, October 31, 2007
11:26 PM<br>
<b><span style="font-weight: bold;">To:</span></b> oracle-l<br>
<b><span style="font-weight: bold;">Subject:</span></b> table with keep as
buffer
pool see much more physical reads than the number of blocks in the table</span>
</font></p>
</div><span>
<p><font face="Times New Roman" size="3"><span style="font-size: 12pt;"> <
/span></font></p>
<p style="margin-bottom: 12pt;"><font face="Times New Roman" size="3"><span
style="font-size: 12pt;">Hi, everyone, the oracle
I use is <a href="http://10.2.0.2" target="_blank" onclick="return top.js
.OpenExtLink(window,event,this)">10.2.0.2</a> on HPUX IA64<br>
<br>
Our job ran very slow and from AWR report I can see that a table named </span><
/font><font color="black" face="Arial" size="1"><span style="font-size: 8pt;
font-family: Arial; color: black;">ATTRIBUTE_VALUE </span></font>
consumes most of the physical IO
(Physical read was more than 2.5M), as I know the blocks the table has is only
0.3M(block size is 8K), so I created a keep buffer and set the buffer pool of
<font color="black" face="Arial" size="1"><span style="font-size: 8pt; font
-family: Arial; color: black;">ATTRIBUTE_VALUE </span></font>to "keep
buffer". I was
doing this to hope that once <font color="black" face="Arial" size="1"><span
style="font-size: 8pt; font-family: Arial; color: black;">ATTRIBUTE_VALUE <
/span></font>is
read into<font color="black" face="Arial" size="1"><span style="font-size: 8pt;
font-family: Arial; color: black;"> </span></font>the keep buffer, it will
never be
paged out so there the physical read will not exceed 0.3M (the blocks the table
has). I already set the size of the keep buffer large enough (4G) to hold all
the blocks.<br>
<br>
But after that change, <font color="black" face="Arial" size="1"><span style=
"font-size: 8pt; font-family: Arial; color: black;">ATTRIBUTE_VALUE </span><
/font>was
still read physically much more times than 0.3M (about 1.6M). Shouldn'
;t
it be only 0.3M if the table was "kept" in the buffer? </p>
</span></div>
</div>
</blockquote></div><br>
|
|
|