Help - Search - Members - Calendar
Full Version: db_keep_cache (are new rows included)?
Oracle DBA Forums > Oracle > Oracle Forum
velacon
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.
CODE
select segment_name, sum(bytes)/(1024*1024) as mb  
  from  dba_segments
where owner = 'usr'
   and segment_name='tabname'
   group by segment_name
order by mb desc


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

3. I altered the storage (we have some partitions)
CODE
ALTER TABLE tabname modify partition Part1 STORAGE (BUFFER_POOL KEEP);
ALTER TABLE tabname modify partition Part2 STORAGE (BUFFER_POOL KEEP);


4. I preloaded the table
CODE
SELECT /*+ FULL(tabname) */ SUM(id) FROM tabname;


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
Steve
QUOTE (velacon @ Jun 27 2008, 07:01 AM) *
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?
velacon
Hello.

QUOTE
3. I'm not really sure what your statements to check the cache are. What are you doing?


Here my statement to check the cache:
CODE
SELECT o.OBJECT_NAME, o.OBJECT_TYPE, o.OWNER, COUNT(*) NUMBER_OF_BLOCKS
     FROM DBA_OBJECTS o, V$BH bh
    WHERE o.DATA_OBJECT_ID = bh.OBJD
        AND owner like 'USR%'      
    GROUP BY o.OBJECT_NAME, o.OWNER, o.OBJECT_TYPE
    ORDER BY COUNT(*) desc

Is the statement ok?
I have bought the book "Oracle Tuning The Definitive Reference" but can not find a better one.
nirnay jaiswal
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.
HAL9000
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';
;
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.