RE: DB Block Buffers - Too Much ???

From: Alex Hillman <alex_hillman_at_physia.com>
Date: Wed, 21 Jun 2000 15:01:31 -0400
Message-Id: <10535.110055@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01BFDBB3.1CC43F80
Content-Type: text/plain;

        charset="iso-8859-1"

Paging and swopping does not influence hit ratio.

Alex Hillman

-----Original Message-----
From: Deepak Sharma [mailto:sharmakdeep_at_yahoo.com] Sent: Wednesday, June 21, 2000 3:36 PM
To: Multiple recipients of list ORACLE-L Subject: RE: DB Block Buffers - Too Much ???

No, the system has been running with activity for quite a while. I also checked 'vmstat 3 5' a few times and this is the o/p (showing slight swapping looking at pin and pout):

  procs    memory         pages                       
  intr        cpu      

  r w u act free wire fault cow zero react pin pout in sy cs us sy id
  7230 29 218K 2832 34K 261M 60M 60M 122K 58M 6824 637 1K 1K 13 6 82
  6230 30 220K 964 34K 736 11 683 0 13 0 67 5K 910 88 12 0
  6230 30 221K 193 34K 196 0 196 15 0 10 385 6K 1K 82 18 0
  7230 29 221K 128 34K 196 0 196 0 0 0 216 3K 1K 83 17 0
  7230 29 221K 128 34K 197 0 196 0 0 0 549 4K 2K 77 21 2

Also, I used 'swapon -s' and got this :

Total swap allocation:

    Allocated space:       768771 pages (6006MB)
    In-use space:           28802 pages (  3%)
    Available space:       739969 pages ( 96%)

showing 3% being used for swapping.

Somebody pls correct me interpreting this if i'm wrong, 'cause I'm not much of a Unix SA.

• Deepak
  • "Weaver, Walt" <wweaver_at_rightnowtech.com> wrote:
    > Just out of curiousity, did you measure hit ratio
    > soon after re-starting the
    > database? Wouldn't the hit ratio be misleadingly low
    > for a while, since
    > Oracle was probably busy reading disk and loading
    > stuff into the buffers?
    >
    > Other than that, I suppose you could monitor vmstat
    > or some other utility to
    > see if excessive memory swapping is going on.
    >
    > --Walt Weaver
    > Bozeman, Montana, USA
    >
    > -----Original Message-----
    > Sent: Wednesday, June 21, 2000 11:26 AM
    > To: Multiple recipients of list ORACLE-L
    >
    >
    > Recently on one of our test systems the Buffer Cache
    > Hit Ratio was showing 80%. I increased
    > db_block_buffers from 6000 to 16000, and the after
    > that the hit ratio has dropped to 55%. It seemed
    > strange to me that increasing db_block_buffers
    > should
    > actually decrease performance, until I read Oracle
    > tuning tips from Richard Niemiec, where he mentions
    > too much db_block_buffers is not good too, as it may
    > lead to swapping. My question is how do you
    > determine
    > whether the buffers are too low or too high ?
    >
    > SQL> select state, count(*) from x$bh group by
    > state;
    >
    > STATE COUNT(*)
    > ---------- ----------
    > 1 13047
    > 3 2953
    >
    > Does state = '1' mean this memory is not being used
    > and if so, should I decrease the buffers ?
    >
    > -- Deepak
    >
    > =====
    > Oracle DBA,
    > Minneapolis, MN
    > USA
    >
    > __________________________________________________
    > Do You Yahoo!?
    > Send instant messages with Yahoo! Messenger.
    > http://im.yahoo.com/
    > --
    > Author: Deepak Sharma
    > INET: sharmakdeep_at_yahoo.com
    >
    > Fat City Network Services -- (858) 538-5051 FAX:
    > (858) 538-5051
    > San Diego, California -- Public Internet
    > access / Mailing Lists
    >

    ---

    
    > 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).
    > --
    > Author: Weaver, Walt
    > INET: wweaver_at_rightnowtech.com
    >
    > Fat City Network Services -- (858) 538-5051 FAX:
    > (858) 538-5051
    > San Diego, California -- Public Internet
    > access / Mailing Lists
    >

    ---

    
    > 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).
    

    ---

Oracle DBA,
Minneapolis, MN
USA

---

Do You Yahoo!?
Send instant messages with Yahoo! Messenger. http://im.yahoo.com/

-- 
Author: Deepak Sharma
  INET: sharmakdeep_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).

------_=_NextPart_001_01BFDBB3.1CC43F80
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2448.0">
<TITLE>RE: DB Block Buffers - Too Much ???</TITLE>
</HEAD>
<BODY>

<P><FONT SIZE=3D2>Paging and swopping does not influence hit =
ratio.</FONT>
</P>

<P><FONT SIZE=3D2>Alex Hillman</FONT>
</P>

<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Deepak Sharma [<A =
HREF=3D"mailto:sharmakdeep_at_yahoo.com">mailto:sharmakdeep_at_yahoo.com</A>]<=
/FONT>
<BR><FONT SIZE=3D2>Sent: Wednesday, June 21, 2000 3:36 PM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: RE: DB Block Buffers - Too Much ???</FONT>
</P>
<BR>
<BR>

<P><FONT SIZE=3D2>No, the system has been running with activity =
for</FONT>
<BR><FONT SIZE=3D2>quite a while. I also checked 'vmstat 3 5' a few =
times</FONT>
<BR><FONT SIZE=3D2>and this is the o/p (showing slight swapping =
looking</FONT>
<BR><FONT SIZE=3D2>at pin and pout):</FONT>
</P>

<P><FONT SIZE=3D2>&nbsp; procs&nbsp;&nbsp;&nbsp; =
memory&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
pages&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
</FONT>
<BR><FONT SIZE=3D2>&nbsp; =
intr&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
cpu&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </FONT>
<BR><FONT SIZE=3D2>&nbsp; r&nbsp; w&nbsp; u&nbsp; act&nbsp; free wire =
fault cow zero react pin</FONT>
<BR><FONT SIZE=3D2>pout&nbsp; in&nbsp; sy&nbsp; cs&nbsp; us&nbsp; =
sy&nbsp; id</FONT>
<BR><FONT SIZE=3D2>&nbsp; 7230 29&nbsp; 218K 2832&nbsp; 34K 261M&nbsp; =
60M&nbsp; 60M 122K&nbsp; 58M</FONT>
<BR><FONT SIZE=3D2>6824 637&nbsp; 1K&nbsp; 1K&nbsp; 13&nbsp;&nbsp; =
6&nbsp; 82</FONT>
<BR><FONT SIZE=3D2>&nbsp; 6230 30&nbsp; 220K&nbsp; 964&nbsp; 34K&nbsp; =
736&nbsp;&nbsp; 11&nbsp; 683&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; =
13&nbsp;&nbsp; </FONT>
<BR><FONT SIZE=3D2>0&nbsp; 67&nbsp; 5K 910&nbsp; 88&nbsp; =
12&nbsp;&nbsp; 0</FONT>
<BR><FONT SIZE=3D2>&nbsp; 6230 30&nbsp; 221K&nbsp; 193&nbsp; 34K&nbsp; =
196&nbsp;&nbsp;&nbsp; 0&nbsp; 196&nbsp;&nbsp; 15&nbsp;&nbsp;&nbsp; =
0&nbsp; </FONT>
<BR><FONT SIZE=3D2>10 385&nbsp; 6K&nbsp; 1K&nbsp; 82&nbsp; =
18&nbsp;&nbsp; 0</FONT>
<BR><FONT SIZE=3D2>&nbsp; 7230 29&nbsp; 221K&nbsp; 128&nbsp; 34K&nbsp; =
196&nbsp;&nbsp;&nbsp; 0&nbsp; 196&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp; =
0&nbsp;&nbsp; </FONT>
<BR><FONT SIZE=3D2>0 216&nbsp; 3K&nbsp; 1K&nbsp; 83&nbsp; =
17&nbsp;&nbsp; 0</FONT>
<BR><FONT SIZE=3D2>&nbsp; 7230 29&nbsp; 221K&nbsp; 128&nbsp; 34K&nbsp; =
197&nbsp;&nbsp;&nbsp; 0&nbsp; 196&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp; =
0&nbsp;&nbsp; </FONT>
<BR><FONT SIZE=3D2>0 549&nbsp; 4K&nbsp; 2K&nbsp; 77&nbsp; =
21&nbsp;&nbsp; 2</FONT>
</P>

<P><FONT SIZE=3D2>Also, I used 'swapon -s' and got this :</FONT>
</P>

<P><FONT SIZE=3D2>Total swap allocation:</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp; Allocated =
space:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 768771 pages (6006MB)</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp; In-use =
space:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
28802 pages (&nbsp; 3%)</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp; Available =
space:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 739969 pages ( 96%)</FONT>
</P>

<P><FONT SIZE=3D2>showing 3% being used for swapping.</FONT>
</P>

<P><FONT SIZE=3D2>Somebody pls correct me interpreting this if =
i'm</FONT>
<BR><FONT SIZE=3D2>wrong, 'cause I'm not much of a Unix SA.</FONT>
</P>

<P><FONT SIZE=3D2>-- Deepak</FONT>
</P>
<BR>
<BR>

<P><FONT SIZE=3D2>--- &quot;Weaver, Walt&quot; =
&lt;wweaver_at_rightnowtech.com&gt; wrote:</FONT>
<BR><FONT SIZE=3D2>&gt; Just out of curiousity, did you measure hit =
ratio</FONT>
<BR><FONT SIZE=3D2>&gt; soon after re-starting the</FONT>
<BR><FONT SIZE=3D2>&gt; database? Wouldn't the hit ratio be =
misleadingly low</FONT>
<BR><FONT SIZE=3D2>&gt; for a while, since</FONT>
<BR><FONT SIZE=3D2>&gt; Oracle was probably busy reading disk and =
loading</FONT>
<BR><FONT SIZE=3D2>&gt; stuff into the buffers?</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Other than that, I suppose you could monitor =
vmstat</FONT>
<BR><FONT SIZE=3D2>&gt; or some other utility to</FONT>
<BR><FONT SIZE=3D2>&gt; see if excessive memory swapping is going =
on.</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; --Walt Weaver</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp; Bozeman, Montana, USA</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; -----Original Message-----</FONT>
<BR><FONT SIZE=3D2>&gt; Sent: Wednesday, June 21, 2000 11:26 AM</FONT>
<BR><FONT SIZE=3D2>&gt; To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Recently on one of our test systems the Buffer =
Cache</FONT>
<BR><FONT SIZE=3D2>&gt; Hit Ratio was showing 80%. I increased</FONT>
<BR><FONT SIZE=3D2>&gt; db_block_buffers from 6000 to 16000, and the =
after</FONT>
<BR><FONT SIZE=3D2>&gt; that the hit ratio has dropped to 55%. It =
seemed</FONT>
<BR><FONT SIZE=3D2>&gt; strange to me that increasing =
db_block_buffers</FONT>
<BR><FONT SIZE=3D2>&gt; should</FONT>
<BR><FONT SIZE=3D2>&gt; actually decrease performance, until I read =
Oracle</FONT>
<BR><FONT SIZE=3D2>&gt; tuning tips from Richard Niemiec, where he =
mentions</FONT>
<BR><FONT SIZE=3D2>&gt; too much db_block_buffers is not good too, as =
it may</FONT>
<BR><FONT SIZE=3D2>&gt; lead to swapping. My question is how do =
you</FONT>
<BR><FONT SIZE=3D2>&gt; determine</FONT>
<BR><FONT SIZE=3D2>&gt; whether the buffers are too low or too high =
?</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; SQL&gt; select state, count(*) from x$bh group =
by</FONT>
<BR><FONT SIZE=3D2>&gt; state;</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; STATE&nbsp;&nbsp; =
COUNT(*)</FONT>
<BR><FONT SIZE=3D2>&gt; ---------- ----------</FONT>
<BR><FONT =
SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13047</FONT>
<BR><FONT =
SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2953</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Does state =3D '1' mean this memory is not =
being used</FONT>
<BR><FONT SIZE=3D2>&gt; and if so, should I decrease the buffers =
?</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; -- Deepak</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; =3D=3D=3D=3D=3D</FONT>
<BR><FONT SIZE=3D2>&gt; Oracle DBA,</FONT>
<BR><FONT SIZE=3D2>&gt; Minneapolis, MN</FONT>
<BR><FONT SIZE=3D2>&gt; USA</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; =
__________________________________________________</FONT>
<BR><FONT SIZE=3D2>&gt; Do You Yahoo!?</FONT>
<BR><FONT SIZE=3D2>&gt; Send instant messages with Yahoo! =
Messenger.</FONT>
<BR><FONT SIZE=3D2>&gt; <A HREF=3D"http://im.yahoo.com/" =
TARGET=3D"_blank">http://im.yahoo.com/</A></FONT>
<BR><FONT SIZE=3D2>&gt; -- </FONT>
<BR><FONT SIZE=3D2>&gt; Author: Deepak Sharma</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp; INET: sharmakdeep_at_yahoo.com</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Fat City Network Services&nbsp;&nbsp;&nbsp; -- =
(858) 538-5051&nbsp; FAX:</FONT>
<BR><FONT SIZE=3D2>&gt; (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>&gt; San Diego, =
California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public =
Internet</FONT>
<BR><FONT SIZE=3D2>&gt; access / Mailing Lists</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------=
-----</FONT>
<BR><FONT SIZE=3D2>&gt; To REMOVE yourself from this mailing list, send =
an</FONT>
<BR><FONT SIZE=3D2>&gt; E-Mail message</FONT>
<BR><FONT SIZE=3D2>&gt; to: ListGuru_at_fatcity.com (note EXACT spelling =
of</FONT>
<BR><FONT SIZE=3D2>&gt; 'ListGuru') and in</FONT>
<BR><FONT SIZE=3D2>&gt; the message BODY, include a line containing: =
UNSUB</FONT>
<BR><FONT SIZE=3D2>&gt; ORACLE-L</FONT>
<BR><FONT SIZE=3D2>&gt; (or the name of mailing list you want to be =
removed</FONT>
<BR><FONT SIZE=3D2>&gt; from).&nbsp; You may</FONT>
<BR><FONT SIZE=3D2>&gt; also send the HELP command for other =
information</FONT>
<BR><FONT SIZE=3D2>&gt; (like subscribing).</FONT>
<BR><FONT SIZE=3D2>&gt; -- </FONT>
<BR><FONT SIZE=3D2>&gt; Author: Weaver, Walt</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp; INET: =
wweaver_at_rightnowtech.com</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Fat City Network Services&nbsp;&nbsp;&nbsp; -- =
(858) 538-5051&nbsp; FAX:</FONT>
<BR><FONT SIZE=3D2>&gt; (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>&gt; San Diego, =
California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public =
Internet</FONT>
<BR><FONT SIZE=3D2>&gt; access / Mailing Lists</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------=
-----</FONT>
<BR><FONT SIZE=3D2>&gt; To REMOVE yourself from this mailing list, send =
an</FONT>
<BR><FONT SIZE=3D2>&gt; E-Mail message</FONT>
<BR><FONT SIZE=3D2>&gt; to: ListGuru_at_fatcity.com (note EXACT spelling =
of</FONT>
<BR><FONT SIZE=3D2>&gt; 'ListGuru') and in</FONT>
<BR><FONT SIZE=3D2>&gt; the message BODY, include a line containing: =
UNSUB</FONT>
<BR><FONT SIZE=3D2>&gt; ORACLE-L</FONT>
<BR><FONT SIZE=3D2>&gt; (or the name of mailing list you want to be =
removed</FONT>
<BR><FONT SIZE=3D2>&gt; from).&nbsp; You may</FONT>
<BR><FONT SIZE=3D2>&gt; also send the HELP command for other =
information</FONT>
<BR><FONT SIZE=3D2>&gt; (like subscribing).</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>=3D=3D=3D=3D=3D</FONT>
<BR><FONT SIZE=3D2>Oracle DBA,</FONT>
<BR><FONT SIZE=3D2>Minneapolis, MN</FONT>
<BR><FONT SIZE=3D2>USA</FONT>
</P>

<P><FONT =
SIZE=3D2>__________________________________________________</FONT>
<BR><FONT SIZE=3D2>Do You Yahoo!?</FONT>
<BR><FONT SIZE=3D2>Send instant messages with Yahoo! Messenger.</FONT>
<BR><FONT SIZE=3D2><A HREF=3D"http://im.yahoo.com/" =
TARGET=3D"_blank">http://im.yahoo.com/</A></FONT>
<BR><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: Deepak Sharma</FONT>
<BR><FONT SIZE=3D2>&nbsp; INET: sharmakdeep_at_yahoo.com</FONT>
</P>

<P><FONT SIZE=3D2>Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) =
538-5051&nbsp; FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>San Diego, =
California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet =
access / Mailing Lists</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------=
-----</FONT>
<BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an =
E-Mail message</FONT>

Received on Wed Jun 21 2000 - 14:01:31 CDT