Re: Buffer cache hits %

From: <michael_bialik_at_my-deja.com>
Date: Fri, 16 Jul 1999 14:30:46 GMT
Message-ID: <7mnfmm$grj$1@nnrp1.deja.com>

Hi.
It is not BAD, but it is not good either. You are supposed to get > 95% hit_ratio for tuned system.

Try :
  SELECT sqltext, executions, buffer_gets, disk_reads,    buffer_gets / executions, disk_reads / executions   FROM v$sqlarea
  WHERE execution > 0
  ORDER BY 5 desc;

It will list all executed SQL statements in order of their average number of buffer_gets per execution.
Usually it mean a scan of a lot of tables. Try tuning these statements.

Good luck. Michael.

In article <O41ialrz#GA.309_at_nih2naae.prod2.compuserve.com>, "Kevin Gillins" <kevin_gillins_at_compuserve.com> wrote:
> This is a multi-part message in MIME format.
>
> ------=_NextPart_000_0015_01BECE98.2C81AF80
> Content-Type: text/plain;
> charset="iso-8859-1"
> Content-Transfer-Encoding: 7bit
>
> I have a question about the buffer cache hits ratio we are
experiencing.
>
> We are on 7.3.3.5 database using 10.7 oracle apps most all products.
Apx
> 100+ users.
> The database size is apx 40 gig. We are on HP 4 processors with 2.5
gig
> memory.
> There are 2 instances both the same size (prod and test). The shared
pool
> is set
> at 250,000,000 running 6 db writers. The production instance is on
EMC
> disks.
>
> My main question is the buffer cache hit% is sitting at 90% in
general. Is
> this good, bad or
> real bad. I want to get a feel. I have tried increasing the shared
pool
> and db_block_buffers.
>
> What other settings if any would improve this statistic. What stats
could I
> check to see
> what contributes to the 10% miss?
>
> Your suggestions and answers are appreciated.
>
> --
>
> Kevin Gillins
> Management Information Consulting
> Boston Office
> 978-887-3300
>
> ------=_NextPart_000_0015_01BECE98.2C81AF80
> Content-Type: text/html;
> charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META content=3D"text/html; charset=3Diso-8859-1" =
> http-equiv=3DContent-Type>
> <META content=3D"MSHTML 5.00.2314.1000" name=3DGENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY bgColor=3D#ffffff>
> <DIV><FONT size=3D2>I have a question about the buffer cache hits
ratio =
> we are=20
> experiencing.</FONT></DIV>
> <DIV> </DIV>
> <DIV><FONT size=3D2>We are on 7.3.3.5 database using 10.7 oracle apps
=
> most all=20
> products.  Apx 100+ users.</FONT></DIV>
> <DIV><FONT size=3D2>The database size is apx 40 gig.  We are on =
> HP 4=20
> processors with 2.5 gig memory.</FONT></DIV>
> <DIV><FONT size=3D2>There are 2 instances both the same size (prod
and=20
> test).  The shared pool is set</FONT></DIV>
> <DIV><FONT size=3D2>at 250,000,000 running 6 db writers.  The =
> production=20
> instance is on EMC disks.</FONT></DIV>
> <DIV> </DIV>
> <DIV><FONT size=3D2>My main question is the buffer cache hit% is
sitting =
> at 90% in=20
> general.  Is this good, bad or</FONT></DIV>
> <DIV><FONT size=3D2>real bad.  I want to get a feel.  I
have =
> tried=20
> increasing the shared pool and db_block_buffers.</FONT></DIV>
> <DIV> </DIV>
> <DIV><FONT size=3D2>What other settings if any would improve this =
> statistic. =20
> What stats could I check to see </FONT></DIV>
> <DIV><FONT size=3D2>what contributes to the 10% miss?</FONT></DIV>
> <DIV> </DIV>
> <DIV><FONT size=3D2>Your suggestions and answers are =
> appreciated.</FONT></DIV>
> <DIV><FONT size=3D2><BR>-- <BR><BR>Kevin Gillins<BR>Management =
> Information=20
> Consulting<BR>Boston Office<BR>978-887-3300</FONT></DIV>
> <DIV> </DIV>
> <DIV><FONT size=3D2></FONT> </DIV></BODY></HTML>
>
> ------=_NextPart_000_0015_01BECE98.2C81AF80--
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Jul 16 1999 - 09:30:46 CDT