Db Buffer cache
Reader, September 17, 2004 - 1:55 pm UTC
Dear Tom,
I find your site the best way to clear confusions.
Please correct me if I'm wrong:
1. You mean theres no need to specify thier sizes until we use it for
appropriate object.
2. Theoritically Db Buffer cache= DB_CACHE_SIZE + DB_KEEP_CACHE_SIZE + DB_RECYCLE_CACHE_SIZE. they are seperate parameters. Right?
3. If I need DB_KEEP_CACHE_SIZE or DB_RECYCLE_CACHE_SIZE I do have to consider the db_buffer_cache size (which should be a total of all the
three caches)?
Thank you.
September 17, 2004 - 2:17 pm UTC
1) right, unless you've identified a need to use them, don't
2) literraly the amount of storage in the buffer cache is the sum, yes. is it confusing to have an equation:
x = x+y+z
in this case -- so I would say
storage_dedicated_to_caching_blocks =
db_cache_size+db_keep+db_recycle+db_32k+db16k......
3) they are "accumulated", you add all of the db%cache_size parameters up:
ops$tkyte@ORA9IR2> show parameter db%cache_size
NAME TYPE
------------------------------------ -----------
db_16k_cache_size big integer
db_2k_cache_size big integer
db_32k_cache_size big integer
db_4k_cache_size big integer
db_8k_cache_size big integer
db_cache_size big integer
db_keep_cache_size big integer
db_recycle_cache_size big integer
the SUM of these is the size of the memory area you've set aside for caching blocks.
DB cache size
Reader, September 18, 2004 - 1:21 am UTC
Hi,
1) "the SUM of these is the size of the memory area you've set aside for caching blocks". Is it the DB buffer cache memory area in SGA for caching blocks?
2) which parameter size shall I consider in case of setting non-standard blocksize? I mean which param size should be increased to set db_nk_cache_size (the x's size according to you)?
3) there is a prob to set db_nk_cache_size. my db blocksize is 8kb and I'm using 9i Release 1.
the error says "insufficient memory to grow cache size"
Pls. tell me how to do it and what is the exact command?
Thanks in advance.
September 18, 2004 - 10:19 am UTC
1) the db buffer cache<b>S</b> (plural) are the memory area<b>S</b> in the SGA for caching blocks.
plural
2) ummm, you tell me. which non-standard size did you want to use? you would of course set *that* one.
3) just means your sga_max_size was either
a) not set large enough to grow the SGA by the amount you want to grow it be
b) left to default to the start up size, which is the same as a) really
either
a) set the sga_max_size larger and restart or
b) take away a couple of meg from some other cache (shared pool, db cache, whatever) and then you can set it.
ops$tkyte@ORA9IR2> show parameter cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 33554432
db_keep_cache_size big integer 33554432
db_recycle_cache_size big integer 0
ops$tkyte@ORA9IR2> alter system set db_2k_cache_size = 16m;
alter system set db_2k_cache_size = 16m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache
ops$tkyte@ORA9IR2> alter system set db_keep_cache_size = 16m;
System altered.
ops$tkyte@ORA9IR2> alter system set db_2k_cache_size = 16m;
System altered.
ops$tkyte@ORA9IR2> show parameter cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 16777216
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 33554432
db_keep_cache_size big integer 16777216
db_recycle_cache_size big integer 0
ops$tkyte@ORA9IR2>
like that.
DB BUFFER SIZE/DB_KEEP_BUFFER_SIZE/`DB_RECYCLE_BUFFER
Peter, September 18, 2004 - 11:13 pm UTC
Tom,
The general belief is if all data is in memory, it should be faster.Therefore, can DB BUFFER SIZE be very large to take the entire set of tables and indexes?
Our DBA says it may not be.He shows a hit ratio on cache.
Your guidance requested
September 19, 2004 - 10:21 am UTC
it'll be faster if you tune you queries and design properly too.
No, it is not practical to cache an entire database, not unless it fits on a floppy disk anyway.
A cache works by making it so that frequently requested data (low hanging fruit) is available and physical IO is only done when necessary.
db_nk_cache_size
Reader, September 19, 2004 - 9:04 am UTC
SQL> select * from v$sgastat where pool like 'java%';
POOL NAME BYTES
----------- -------------------------- ----------
java pool free memory 28311552
java pool memory in use 5242880
SQL> sho parameter java
NAME TYPE VALUE
------------------------------------ ----------- ---------------------
java_max_sessionspace_size integer 0
java_pool_size string 33554432
java_soft_sessionspace_limit integer 0
SQL> alter system set java_pool_size=16m;
alter system set java_pool_size=16m
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> sho parameter shared_pool
NAME TYPE VALUE
------------------------------------ ----------- -----------------
shared_pool_reserved_size big integer 1677721
shared_pool_size big integer 33554432
SQL> alter system set shared_pool_size=30m;
System altered.
SQL> alter system set db_2k_cache_size = 12m;
System altered.
1) I thought java is not in use and tried to reduce its size but
I cannot alter its size.
I think SGA components -'free memory' and 'memory in use' is summed up in one param java_pool_size, whereas in shared pool there r different params for reserved area and free area, is the reason why I can alter shared_pool_size and not java_pool_size. Am I right?
2) same error errupts while alteration of sga_max_size. Can u please explain why its happening?
3) Is there any way to alter java_pool_size and sga_max_size?
4) Which param size do u think is the best to reduce(which affects less on the normal operations) ?
5) I dont have db_keep_cache or db_recycle_cache size specified. Can I reduce the db_cache_size param? Is there any negative effect of doing so?
Thanks for your time.
September 19, 2004 - 10:33 am UTC
1) not all memory areas are online adjustable, just the ones documented to be.
that is the reason -- some are allowed to be, others are not.
2) sga_max_size is documented to be, well, the maximum size the sga can be. it is not changeable after you start (otherwise, well, it would be pedantic would it not be? I mean, why have an sga_max_size if you can just make it be anything you wanted? it would not have much sense as a parameter)
3) restart
4) only you, only only only you can answer that. You know your system -- what do you use, what do you make use of. Think about what you are asking here -- suppose I say "java pool" but you run tons of java -- not so good.
5) starting to give up here. think about what you are doing -- think about the obvious side effects
o reduce db_cache_size
-> less ram dedicated to caching
-> less cached
-> perhaps increased physical io
-> possible increased response time due to increased physical io
but again, ONLY YOU, knowing how your system works -- could really answer any of this.
A reader, September 20, 2004 - 9:29 am UTC
buffer cache
Reader, September 20, 2004 - 9:49 am UTC
Hi,
1) DB Buffer Cache has 3 different algorithms
-Pinning
-LRU Algorithm
-Db Buffer Pool -keep buffer pool (these three buffer
-recycle buffer pool pools r sub-caches
-default buffer pool of DB BUFFERCACHE)
(Pls correct me if I'm wrong).
2) Default buffer pool uses LRU algorithm...How this algorithm different from the lru algorithm mentioned in Q# 1?
3) What algorithms do keep and recycle buffer pool use?
4) what does pinning mean? Why this algorithm is used and where?
Please guide.
(pls. let me know your link regarding this or any documentation part, if possible to clear the details about LRU algorithm and Pinning algorithm)
Thanks in advance.
September 20, 2004 - 10:49 am UTC
"pinning" is not an algorith
we don't pin in the buffer cache, you can pin in the shared pool (it is like sticking a tack into the memory and making it so it cannot go away, that object is "pinned")
the concepts guide goes into this to some degree
as does the admin guide
and my book "Expert one on one Oracle" spends a couple of pages in the architecture sections on this topic. It is bigger than a breadbox.
RECYCLE with no pool
RobH, September 05, 2006 - 11:59 am UTC
I have an index that I've assigned to the RECYCLE buffer pool, yet one does not exist. I can't find in the docs how this would be handled, I'm *assuming* it will still be treated normally and assigned to the DEFAULT pool, but does it get placed in the LRU differently?
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
SQL> set lin 140
SQL> show parameter recyc
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
buffer_pool_recycle string
db_recycle_cache_size big integer 0
SQL> select index_name, buffer_pool from dba_indexes where buffer_pool != 'DEFAULT';
INDEX_NAME BUFFER_
------------------------------ -------
STATS$SEG_STAT_PK RECYCLE
Solved
RobH, September 06, 2006 - 2:07 pm UTC
I joined x$bh, x$kcbwds to v$buffer_pool using set_id between v$buffer_pool.lo_setid and v$buffer_pool.hi_setid and x$bh.set_ds = ws.addr
A Reader, January 15, 2009 - 12:27 am UTC
Hi Tom
Our DBA is in the process of setting up a 10g instance for testing migration from 9i to 10g.
I was looking at the SGA related parameters and here is what i find :
select a.name, a.value/(1024*1024), b.value/(1024*1024)
from v$parameter a full outer join v$parameter@gdwdm@gdwp b
on a.name = b.name
where a.name in
('sga_max_size',
'sga_target',
'shared_pool_size',
'large_pool_size',
'java_pool_size'
)
or a.name like 'db%cache_size'
NAME,A.VALUE/(1024*1024),B.VALUE/(1024*1024)
sga_max_size,6144
sga_target,6144
shared_pool_size,0
large_pool_size,0
java_pool_size,0
db_cache_size,0
db_keep_cache_size,4096
db_recycle_cache_size,0
db_2k_cache_size,0
db_4k_cache_size,0
db_8k_cache_size,0
db_16k_cache_size,0
db_32k_cache_size,0
My 2 q's are around db_keep_ccahe_size :
(a) The following query shows that none of the tables or indexes is allocated to the 'KEEP' Buffer Pool
select 'Tables',buffer_pool,count(*) from user_tables group by buffer_pool
union
select 'Indxes',buffer_pool,count(*) from user_indexes group by buffer_pool
'TABLES', BUFFER_POOL,COUNT(*)
Indxes, DEFAULT, 552
Indxes, , 1
Tables, DEFAULT, 204
so is there any point in allocating any db_keep_ccahe_size ? Let alone such a large value 4G. Is this entire space being wated ?
(b) This is just a calrification. Oracle Manual says
<quote>
There are a few SGA components whose sizes are not automatically adjusted. The administrator needs to specify the sizes of these components explicitly, if needed by the application. Such components are:
Keep/Recycle buffer caches (controlled by DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE)
...
The memory consumed by manually sized components reduces the amount of memory available for automatic adjustment. For example, in the following configuration:
SGA_TARGET = 256M
DB_8K_CACHE_SIZE = 32M
STREAMS_POOL_SIZE = 24M
The instance has only 200 MB (256 - (32 + 24) remaining to be distributed among the automatically sized components
</quote>
So in our situations, even though sga_target is 6G, we are blocking 4G for db_keep_ccahe_size, so leaving just 2G to be available to Automatic SGA Allocation to other SGA Components.
(c) Finally ... are there some system views that i can query in the running system to identify these issues definitively.
Thanks in advance
January 16, 2009 - 5:04 pm UTC
you might ask your dba whey they are allocating 4gb to a memory segment that is not currently assigned any segments - sure. I'd be asking the person that did it "why are you doing that?"
you used the user_ views, that is not a very global picture of the database instance, it does not show that the keep pool is NOT used.
A reader, January 28, 2009 - 7:55 pm UTC
Hi, Is it possible that part of table's data I want to keep in buffer cache? I..e I have a big table but from that table only few records are going to use very frequently as a part of report but at the same time some batch job also running and as a result report is running very slow, Is it possible that part of data remains in buffer for long time, I do not want to create another small table with CACHE option because its not possible in the product and this table will also populate everyday. Or Is it possible to set priority of a query ?
January 30, 2009 - 12:27 pm UTC
that is exactly what a buffer cache does.
If you use it, it will be cached.
If you do not use it, it will not be.
If the data is not in the cache - then one of two things is happening:
a) the data wasn't in the cache when the batch started, it has to get there somehow, someone has to read it.
b) the data was in the cache, but the cache had to flush this data out because it ran out of space.
ALTER TABLE T CACHE; does not do what many people think, it doesn't actually cache a table. It simply modifies how blocks are placed in the cache during a full tablescan of a large table.
A reader, January 30, 2009 - 7:27 pm UTC
Thanks for quich reply, from one of yr answer I learnt that cache option just changing data dictinary and when we full scan this table then it will fetch data and keep in most recently used end. and so I have created one job which will full scan this table at every 15 minutes and so always those data/blocks available in buffer cache and that way now report is running fine, now i am testing with this change how much time increase in my batch job, thansk for your guidance.
Selecting Block size for keep/ recycle cache
Nilesh Kumar, December 04, 2012 - 6:20 am UTC
Hi Tom,
1. Can we use a different block size (other then default) for keep and recycle cache (if buffer cache is using default block size).
2. Say if my buffer cache is 200M, then setting keep/ recycle cache have any effect on buffer cache size (in case my SGA_TARGET and PGA_AGGREGATE_TARGET have sufficiently large enough to accommodate this change).
3. And what will be the effect if SGA_TARGET and PGA_AGGREGATE_TARGET are not large enough.
Also, right now i am on 10g, so just was curious to know if oracle plans to depricate these parameters.
December 07, 2012 - 5:08 am UTC
1) well, the 'buffer cache' always uses the default size - it is only the non-default caches that do not.
the keep/recycle pools use the default block size and you should only be using one blocksize in the first place UNLESS you are transporting data into your database (typically an 8k block size tablespace attached temporarily to a 16/32k warehouse for data loading).
And you probably don't need to use the keep/recycle pools either in most cases - they are for very fine tuning.
2) sure, if you don't have extra free memory in the first place - you'll not have the memory to add the pools without shrinking the buffer cache.
3) think about this, the answer is rather obvious? if your sga target isn't set large enough, you won't be able to allocate what you want.
I think you don't even want to consider playing with these things - if your buffer cache is only 200mb - you have a rather tiny database - just use the defaults.
How cache the big table in the recycle pool
junye, February 14, 2014 - 3:24 pm UTC
Hi,Tom
Now,i have a table,it contains 8 millin rows(13GB),every column is multi-valued,use full-text index query,but it is cost most time in first query. i want cache this table in recycle pool to improve query performance.
This way is correct? what will i do?
thanks!