Re: 2GB limit of memory for Oracle on WIndows 32 bit

On Jul 2, 5:33 am, zigzag..._at_yahoo.com wrote:
> On Jul 2, 5:29 am, zigzag..._at_yahoo.com wrote:
> > On Jul 1, 10:47 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
>
> > > On Jul 1, 12:37 pm, zigzag..._at_yahoo.com wrote:
>
> > > > On Jul 1, 11:41 am, sybra..._at_hccnet.nl wrote:
> > > > > Apart from that cranking up the memory won't resolve your fundamental
> > > > > problem: apparently you never tuned your application and now you want
> > > > > to resolve that by throwing memory at the problem.
> > > > > This method has been demonstrated to be fundamentally wrong over and
> > > > > over again, and is one of the safest methods to end up in hell or at
> > > > > the unemployment office.
>
> > > > > --
> > > > > Sybrand Bakker
> > > > > Senior Oracle DBA-
>
> > > > FYI, keep and recycle pools work exactly the same way. keep and
> > > > recycle are just the names given by Oracle. Vendor sells the
> > > > application, I cannot do any tuning of application.
>
> > > The above comment is interesting. I recall reading in several places
> > > that the keep pool tries to keep blocks in memory, much like the
> > > default pool, while the recycle pool tries to quickly age blocks out
> > > of the pool. After a bit of a search, I found a bit of disagreement.
> > > The results of the search follow:

<snip>

> > > "Expert Oracle One on One" Page 80:
> > > "We also have the ability to carve out a space for segments in the
> > > buffer pool. This space is called the RECYCLE pool. Here, the aging
> > > of the blocks is done differently to the KEEP pool. In the KEEP pool,
> > > the goal is to keep 'warm' and 'hot' blocks cached for as long as
> > > possible. In the recycle pool, the goal is to age out a block as soon
> > > as it is no longer needed."
>
> > >http://www.jlcomp.demon.co.uk/faq/pin_table.html
> > > "NOTE: there is no difference in the behavior of the three buffer
> > > pools, the naming of the pools is merely for memo technically reasons
> > > and intended use."
>
> > >http://forums.oracle.com/forums/thread.jspa?threadID=524119-lookfor
> > > the posting by Jonathan Lewis for context:
> > > "You will rarely be able to beat Oracle's caching algorithms by
> > > playing with KEEP and RECYCLE pools - but there are a few special
> > > cases..."
>
> > > Charles Hooper
> > > IT Manager/Oracle DBA
> > > K&M Machine-Fabricating, Inc.
> > >> "NOTE: there is no difference in the behavior of the three buffer
>
> > pools, the naming of the pools is merely for memo technically reasons
> > and intended use."
>
> > This is exactly what I meant in my first post.>
>
> http://www.jlcomp.demon.co.uk/faq/pin_table.html
> "NOTE: there is no difference in the behavior of the three buffer
> pools, the naming of the pools is merely for memo technically reasons
> and intended use."
>
> This is exactly what I meant in my first post.-
> Just because you put a table in recycle pool instead of keep. Oracle
> is not going to treat it any differently.
>

A bit more searching in books finds two different opinions on how the different buffer caches operate:
"Expert Oracle Database 10g Administration" page 123: "Keep buffer pool: Keeps the data blocks in memory. You may have small tables that are frequently accessed, so to prevent them from being aged out of the database buffer cache, you can assign the tables to the keep buffer cache when they are created. Recycle buffer cache: Removes the data from the cache immediately after use. You need to use this buffer cache carefully, if you decide to use it at all. The recycle buffer cache will cycle out the object from the cache as soon as the transaction is over."

"Expert Oracle Database Architecture" page 141: "In fact, the three pools manage blocks in a mostly identical fashion; they do not have radically different algorithms for aging or caching blocks. The goal here was to give the DBA the ability to segregate segments to hot, warm, and do not care to cache areas."

The first of the above quotes seems to be in agreement with Tom Kyte's "Expert Oracle One on One" book and the Oracle 8 and Oracle 10g R2 Concepts manual. The second of the above quotes (from another book by Tom Kyte) seems to be in agreement with the article written by Svend Jensen on Jonathan Lewis's website.

It makes one wonder... is there a correct answer?

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Mon Jul 02 2007 - 07:49:20 CDT