RE: Multiple Buffer Pool

From: Trassens Christian <trassenc_at_TELEFONICA.COM.AR>
Date: Wed, 19 Jul 2000 12:28:41 -0300
Message-Id: <10563.112420@fatcity.com>

There is something that it doesn't sound good. When you pin packages, = you do
it on the shared pool. When you keep tables, you do it on the buffer = cache.
More specific: code, as PL/SQL, procedures, and SQL, runs using the = shared
pool as a cache. The data blocks goes to the buffer. Through 8 you can specify two areas in the buffer cache, known as buffer keep and buffer recycle.=20

Talking deeply about those things. From my experience and my tests I = can
advise you testing with small tables and indexes used for application. = Add
their blocks. And with that in mind set the value of buffer_keep. At = least
include an overhead of 20%. Also estimate the amount of lru_latches for = that
buffer. BUT REMEMBER THAT IS TEST AND TRY with these new features. With = my
advice I can tell you that I always gain some percent of performance. However your case is different, so test it.

About the shared_pool I always pin packages when I notice shared pool fragmentation problems. Before this I usually set = shared_pool_reserved_size
to a value near 10%/15% of the shared_pool_size. And when you realise = about
that kind of problems: looking at x$kghlu and v$sgastat and at last = when you
receive a 4031 error.

Regards.

NOTE: Remember that before setting the value of buffer keep or recycle, = you
must issue the alter table....buffer <keep/recycle> stmt on the table.

> -----Mensaje original-----
> De: Gautam_Reddy_at_Dell.com [SMTP:Gautam_Reddy_at_Dell.com]
> Enviado el: Mi=E9rcoles 19 de Julio de 2000 12:39
> Para: Multiple recipients of list ORACLE-L
> Asunto: Multiple Buffer Pool

>=20

> Hi
> =20
> Just want to how many of you pin system packages in the keep pool. If =
so
> what kind of performance improvement have you seen. I have been doing =
some
> test on pinning some of the system packages and I have not seen much =
of
> improvement. I even pinned my lookup(static) tables into the keep =
pool to
> and I have not seen any performance gain.=20
> =20
> Any other suggestions on how to optimal set keep,recycle pool are =
also
> welcomed.=20
> =20
> Thx
> Gautam Reddy
> 728-3656(512)
> gautam_reddy_at_dell.com <mailto:gautam_reddy_at_dell.com>
Received on Wed Jul 19 2000 - 10:28:41 CDT