| Setting db_keep_cache_size [message #331056] |
Wed, 02 July 2008 04:19  |
nazbrian
Messages: 36
Registered: July 2008
|
Member |
|
|
Hi,
SQL> select name, value from v$parameter
where name in ('db_cache_size','db_keep_cache_size','db_recycle_cache_size','shared_pool_size') ;
NAME VALUE
------------------------------ --------------------
shared_pool_size 318767104
db_keep_cache_size 0
db_recycle_cache_size 0
db_cache_size 637534208
SQL> alter system set db_keep_cache_size=100M;
alter system set db_keep_cache_size=100M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache
why I'm not able to set the parameter db_keep_cache_size having value 100M.
Regards.
[Updated on: Wed, 02 July 2008 04:51] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Setting db_keep_cache_size [message #331079 is a reply to message #331056] |
Wed, 02 July 2008 05:01  |
nazbrian
Messages: 36
Registered: July 2008
|
Member |
|
|
Hi Babu,
I have seen this doc, but this document does not mention anything abt the error i'm getting.
| Quote: | Range of values Minimum: 0 (values greater than zero are automatically modified to be either the user-specified-size rounded up to granule size or 4MB * number of CPUs * granule size, whichever is greater)
|
According to this it should have rounded up.
I've 20GB of memory in my system.
Brian
|
|
|
|
| Re: Setting db_keep_cache_size [message #331087 is a reply to message #331056] |
Wed, 02 July 2008 05:13 |
nazbrian
Messages: 36
Registered: July 2008
|
Member |
|
|
Hi michel,
SQL> l
1 select name, value, value/1024/1024 value_MB from v$parameter
2* where name in ('db_cache_size','db_keep_cache_size','db_recycle_cache_size','shared_pool_size','sga_max_size')
SQL> /
NAME VALUE VALUE_MB
------------------------------ -------------------- ----------
shared_pool_size 318767104 304
sga_max_size 1034907984 986.965164
db_keep_cache_size 0 0
db_recycle_cache_size 0 0
db_cache_size 637534208 608
In this case If I set db_keep_cache_size to 100M
db_cache_size + db_keep_cache_size < sga_max_size
Brian
|
|
|
|
| Re: Setting db_keep_cache_size [message #331093 is a reply to message #331056] |
Wed, 02 July 2008 05:27 |
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
|
Senior Member |
 
|
|
SGA_MAX_SIZE is the summation of DB_CACHE_SIZE(DB_BLOCK_SIZE)+LOG_BUFFER+SHARED_POOL_SIZE+LARGE_POOL_SIZE +JAVA_POOL_SIZE+ STREAMS_POOL_SIZE+ DB_nk_CACHE_SIZE+DB_KEEP_CACHE_SIZE+DB_RECYCLE_CACHE_SIZE.
In your example shared_pool_size+db_cache_size has passed 912M and your SGA_MAX_SIZE=986 so 986-912=74 around bytes left.
[Updated on: Wed, 02 July 2008 05:29] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Setting db_keep_cache_size [message #331138 is a reply to message #331056] |
Wed, 02 July 2008 06:12 |
nazbrian
Messages: 36
Registered: July 2008
|
Member |
|
|
Hi Michel,
I can restart db, Is there any limitations setting SGA_MAX_SIZE or is it limited to System memory?
I want to set db_keep_cache_size because in my statspack following entry has very big value
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
table scans (short tables) 4,334,847 1,204.8 1,776.6
After setting db_keep_cache_size, do I have to manually set buffer_pool keep for tables and indexes or i will take care automatically?
alter INDEX OWB_RUN.AE_PK storage (buffer_pool keep);
brian
|
|
|
|
|
|
| Re: Setting db_keep_cache_size [message #331146 is a reply to message #331056] |
Wed, 02 July 2008 06:40 |
nazbrian
Messages: 36
Registered: July 2008
|
Member |
|
|
Hi Michel,
For my knowledge,
Setting db_keep_cache_size suffice or along with this we have to give alter ... storage (buffer_pool keep); also for the objects we want to keep in KEEP cache ??
Brian
|
|
|
|
|
|
| Re: Setting db_keep_cache_size [message #331617 is a reply to message #331174] |
Fri, 04 July 2008 04:19 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The only use I have ever found for the Keep buffer pool is if the application has some processes that run during the day and access large amounts of data that only that process uses - this can have the effect of flushing a lot of the regularly accessed blocks out of the cache, and reloading them slows things down a bit.
Setting the Keep and Recycle pools is definitely 'Last 5%' tuning though - only worth doing when you've tuned all the SQL in the system to a point where you can't get any more improvement out of it.
|
|
|
|
| Re: Setting db_keep_cache_size [message #676381 is a reply to message #331056] |
Thu, 06 June 2019 01:22 |
dba4oracle
Messages: 100
Registered: June 2010
|
Senior Member |
|
|
Hi Guys,
On DB Oracle 12c,RHEL
planning to increase memory_max_target/memory_target to 64GB,want to utilize increased SGA as some of queries on tables are not performing
very well even after tuning,want to increase DB_KEEP_CACHE_SIZE
any indication for 32 GB memory_target/SGA,what should be ideal value
Thanks
|
|
|
|
|
|
| Re: Setting db_keep_cache_size [message #676383 is a reply to message #676381] |
Thu, 06 June 2019 01:58 |
dba4oracle
Messages: 100
Registered: June 2010
|
Senior Member |
|
|
This i found from oracle documentation
Range of values
Minimum: 0 (values greater than zero are automatically modified to be either the granule size * number of processor groups, or 4 MB * number of CPUs, whichever is greater)
so is it that DB_KEEP_CACHE_SIZE is automatically determined by oracle based on granule size and no of cpu and setting it manually not required
or will if modified manually will be reset automatically by oracle based on above calculations
|
|
|
|
|
|
| Re: Setting db_keep_cache_size [message #676385 is a reply to message #676384] |
Thu, 06 June 2019 02:52 |
dba4oracle
Messages: 100
Registered: June 2010
|
Senior Member |
|
|
say we have 32 GB of physical ram on db server, 8GB SGA,we set 1 GB to DB_KEEP_CACHE_SIZE,as i understand DB_KEEP_CACHE_SIZE is not automatically adjusted from SGA its
so other compnonets of SGA will be limited to use 7GB,1GB will be reserved for DB_KEEP_CACHE_SIZE no matter its fully used or not
and if usage go above 1GB for DB_KEEP_CACHE_SIZE ,like if we keep lot of tables /segments whose size 1GB,still only segment data upto 1GB will be stored
what if we try to keep just 1 table and its size is 2GB and DB_KEEP_CACHE_SIZE is set to 1GB,so this table is partially kept in cache or its
not kept at all
|
|
|
|
|
|
| Re: Setting db_keep_cache_size [message #676387 is a reply to message #676381] |
Thu, 06 June 2019 05:47 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
dba4oracle wrote on Thu, 06 June 2019 07:22Hi Guys,
On DB Oracle 12c,RHEL
planning to increase memory_max_target/memory_target to 64GB,want to utilize increased SGA as some of queries on tables are not performing
very well even after tuning,want to increase DB_KEEP_CACHE_SIZE
any indication for 32 GB memory_target/SGA,what should be ideal value
Thanks I believe that the keep and recycle pools are really only there for backward compatibility. The cache management algorithms have improved hugely in recent releases, and a default pool is almost certainly all you need. If you are convinced that it isn't right, consider using db_big_table_cache_percent_target to cache scans of big tables: it uses a much more intelligent algorithm than just nominating objects for a keep pool.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
