Hallo.

I have some questions about usage of keep_cache, preloading a table into and what about new/modified values (i know which table shoult preloaded and don't need to check the actual cache therefor).


1. I calculated the size of my table.


2. I have set the db_keep_cache to round 100MB.

3. I altered the storage (we have some partitions)


4. I preloaded the table


5. I try the performance and all looks like fine.

Now my questions:
1. If some rows from tabname will be modified? Will oracle inserts the new rows into the keep_cache automatic and what about the modifikations?

2. What about if the table tabname will be increased bigger than db_keep_chache? Can i write a script to count the blocksize from the table and copare it with the cached blockcount?

3. One more: I have run some Junits and saw, there only one tabname table partition cached? Are my staments to check the cache correct? Should i change the storage definition from the table too?

Thanks

Hello velacon,

1. Remember that changed data, in most cases, will go through the cache. If you have the keep cache storage, the caching of changed data should take care of itself pretty well.
2. That can be a problem. I could definitely recommend keeping an eye on BYTES and BLOCKS from DBA_SEGMENTS. If your table gets too large for the keep cache, you will experience physical reads
3. I'm not really sure what your statements to check the cache are. What are you doing?

Hello.



Here my statement to check the cache:

Is the statement ok?
I have bought the book "Oracle Tuning The Definitive Reference" but can not find a better one.

This is not going to help your purpose. This query will only give you the total number of the blocks which are in the buffer cache for that object at that moment. This can sometimes be even more than the size of the number of blocks of the table. The column STATUS of the view V$BH tells us whether the image of the block is current(xcur) or is the consistent read (cr) image of the block. So for the same table and same block Oracle keeps multiple versions of the same block in the buffer cache depending on the queries which are currently executing. For example when you use "SET TRANSACTION READ ONLY" in one session and update the same table from second session, Then two versions for the same block are kept in the buffer cache, One version have the current image of the block and the other one have the older image of the same block (which is usually generated by using undo information) to provide the read consistency image for the transaction happening in your First session.

When you use the BUFFER_POOL KEEP with your table defination you only ensure that all the blocks of this table will be kept in this pool only.

Tables in the KEEP pool will grow, and you want to adjust the KEEP pool size, to always have enough room for all objects that are assigned to the KEEP pool (with a 20% buffer):

http://www.rampant-books.com/t_oracle_keep..._assignment.htm

prompt The following will size your init.ora KEEP POOL,
prompt based on Oracle8 KEEP Pool assignment values
prompt
select
'ALTER SYSTEM SET BUFFER_POOL_KEEP = ('||sum(s.blocks)+((sum(s.blocks)*.2)||',2)'
from
dba_segments s
where
s.buffer_pool = 'KEEP';
;