Re: Oracle Keep Pool
Date: Wed, 25 Nov 2009 12:55:19 -0800 (PST)
Message-ID: <8e7c6258-f8ea-4943-b314-5ebda7757a1d_at_c3g2000yqd.googlegroups.com>
On Nov 25, 12:41 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Nov 25, 4:47 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> > The KEEP pool, RECYCLE pool, and DEFAULT pool utilize different goals
> > (algorithms) for keeping blocks in memory. Any table or index block
>
> Do you have a reference for this? I was under the impression the
> only difference is whether you can use the cache keyword, the naming
> was purely arbitrary. I may be out of date, of course. But see the
> ask tom you quoted... the goal is just the desire of the DBA to keep
> segments with different access patterns segregated to avoid pushing
> out segments that should be kept.
>
> jg
Joel, thanks for asking for confirmation - sure beats letting someone repeat the same incorrect information multiple times.
Well, I might be remembering things wrong. It seems that there are differing opinions. It can definitely be said that there is a definite difference when the SGA_TARGET parameter is set, as the DEFAULT buffer pool may shrink as a result of hard parsing a large number of non-reusable SQL statements while the KEEP and RECYCLE pools remain the same size. But, that is not good enough.
Some quotes from the documentation:
http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/clauses009.htm
"KEEP: Specify KEEP to put blocks from the segment into the KEEP
buffer pool. Maintaining an appropriately sized KEEP buffer pool lets
Oracle retain the schema object in memory to avoid I/O operations.
KEEP takes precedence over any NOCACHE clause you specify for a table,
cluster, materialized view, or materialized view log.
RECYCLE: Specify RECYCLE to put blocks from the segment into the RECYCLE pool. An appropriately sized RECYCLE pool reduces the number of objects whose default pool is the RECYCLE pool from taking up unnecessary cache space.
DEFAULT: Specify DEFAULT to indicate the default buffer pool. This is the default for objects not assigned to KEEP or RECYCLE."
-- OK, so setting an object to use the KEEP buffer cache automatically over-rides the NOCACHE clause. OK, so where I am going with this? -- http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/memory.htm "Buffers and Full Table Scans: When buffers must be read from disk, the database inserts the buffers into the middle of the LRU list. In this way, hot blocks can remain in the cache so that they do not need to be read from disk again. A problem is posed by a full table scan, which sequentially reads all rows under the table high water mark (see "Segment Space and the High Water Mark"). Suppose that the total size of the blocks in a table segment is greater than the size of the buffer cache. A full scan of this table could clean out the buffer cache, preventing the database from maintaining a cache of frequently accessed blocks. Blocks read into the database cache as the result of a full scan of a large table are treated differently from other types of reads. The blocks are immediately available for reuse to prevent the scan from effectively cleaning out the buffer cache. In the rare case where the default behavior is not desired, you can change the CACHE attribute of the table. In this case, the database does not force or pin the blocks in the buffer cache, but ages them out of the cache in the same way as any other block. Use care when exercising this option because a full scan of a large table may clean most of the other blocks out of the cache." -- OK, so if a large table is read into the DEFAULT buffer cache, the blocks are brought into the cold end of the LRU, unless the CACHE attribute is set. -- This is an old thread, so it might not apply: http://www.mail-archive.com/oracle-l_at_fatcity.com/msg62169.html -- This thread starts out describing memory limits on 32 bit Windows and eventually switches to a discussion of the DEFAULT, KEEP, and RECYCLE pool - including a test case I provided a couple years ago (might be interesting to re-run the test case again to see if there are faults in the test case). http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/d701be8f06f77d83 -- In this thread, after prompting by Jonathan Lewis, I noticed that the number of blocks cached by the test table and index gradually decreased (when checked every 60 seconds) once the query completed (the database instance was otherwise idle) - might be interesting to see if the same behavior is present when the objects are located in the KEEP pool: http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/6c436cee329326ec If there are counter-points or counter test cases to the above I would like to see those test cases - I could certainly be wrong. Charles Hooper Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table" IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.Received on Wed Nov 25 2009 - 14:55:19 CST