I agree.
When pinned, stored procs and packages are kept in the
shared pool. When specified, the tables and indexes
can be kept in 1 of the 3 buffer pools (kept,default
,recycle).
If performance isn't better after pinning the packages
and keeping some tables in the keep buffer pool, it
means that Oracle was already doing it and that your
system must not be stressed enough.
You can verify what objects are in the shared pool by
looking in v$db_object_cache.
You can verify what segments have blocks in cache by
looking at v$bh and dba_objects.
- Trassens Christian <trassenc_at_TELEFONICA.COM.AR> a
écrit : > 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.
>
> 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ércoles 19 de Julio de 2000 12:39
> > Para: Multiple recipients of list ORACLE-L
> > Asunto: Multiple Buffer Pool
> >
> > Hi
> >
> > 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.
> >
> > Any other suggestions on how to optimal set
> keep,recycle pool are also
> > welcomed.
> >
> > Thx
> > Gautam Reddy
> > 728-3656(512)
> > gautam_reddy_at_dell.com
> <mailto:gautam_reddy_at_dell.com>
> > www.dell.com <http://www.dell.com/>
> >
> >
> --
> Author: Trassens Christian
> INET: trassenc_at_TELEFONICA.COM.AR
>
> 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).
Stephane Paquette
DBA Oracle
stephane_paquette_at_yahoo.com
spaquette_at_houra.fr
(33) 01 53 93 06 50
Received on Thu Jul 20 2000 - 02:14:46 CDT
