|
When you are using automatic SGA management (by setting sga_target, sga_max_size or both in the init.ora or spfile) this pool is dynamically allocated; it only needs to have its cache configured and tables altered to use this pool rather than the DEFAULT buffer pool (the ... ummm ... 'default' if you don't specify which pool to use). Once used the blocks in the KEEP pool don't get aged out by time; of course if the cache isn't large enough to contain all of the tables configured to use it then blocks will be replaced and physical I/O will be necessary to replace them as the configured tables are queried -- Table A and Table B are both configured to use the KEEP pool, the keep cache is sized to hold either all of Table A or all of Table B, but not both, so queries against Table A will cause the cache to load with those blocks, and unfortunately if Table B is queried thereafter those blocks will replace those from Table A and basically result in no better performance than using the DEFAULT pool, which is not the intended result. The KEEP cache should be sized a bit larger than the sum of all of the tables configured to use it; the sizing is in blocks, so a simple query of the USER_TABLES view reporting the sum of the BLOCKS column for all tables using the KEEP pool should provide an excellent starting point: SQL> select sum(blocks)
SUM(BLOCKS)
Knowing the granule size is also important, as that will be the value by which the cache is incremented or decremented; dividing the granule size by the db_block_size produces the number of blocks each adjustment will consume: SQL> select granule_size/value
GRANULE_SIZE/VALUE
Setting the db_keep_cache_size to anything less than 512 blocks will automatically allocate 512 blocks since the memory allocations are made in granule-sized increments when using automatic SGA management. Each adjustment up or down will add or subtract multiples of 512 blocks (at least in this case) to the cache. On my system the minimum value to which I can set db_keep_cache_size is 32 MB; any smaller value I might use will automatically configure the cache at 32 MB. The minimum size is dependent upon how many CPUs are installed and the granule size for the database in question. The formula for sizing this pool is number of CPUs * granule size Looking at the block total for all tables configured to use the KEEP pool we see the figure is larger than one granule, so we should set this parameter to at least 1024 (or a value larger than 512 to allow Oracle to 'bump it up' automatically to the next granule). Of course Oracle will most likely 'bump this up' to the minimum value obtained by the above calculation, but you won't know that until you query V$BUFFER_POOL to obtain the current size. In any case you should have sufficient space in the KEEP pool using the default calculation. Oh, and any adjustment above the minimum will be added in granule-sized allocations, so attempting to increase this cache to 33 MB will result in a cache 36 MB in size.
Have a Oracle Question
Oracle Books
Oracle Application
Oracle Home
All the site contents are Copyright © www.erpgreat.com
and the content authors. All rights reserved.
|