Home » RDBMS Server » Server Administration » Setting db_keep_cache_size (9.2.0.7, HP -UX)
Setting db_keep_cache_size [message #331056] Wed, 02 July 2008 04:19 Go to next message
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 #331074 is a reply to message #331056] Wed, 02 July 2008 04:53 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams051.htm#i1126478

Babu
Re: Setting db_keep_cache_size [message #331077 is a reply to message #331056] Wed, 02 July 2008 04:58 Go to previous messageGo to next message
Michel Cadot
Messages: 66680
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You are limited by the parameter SGA_MAX_SIZE.
Decrease DB_CACHE_SIZE then you can add KEEP (iirc).

Regards
Michel
Re: Setting db_keep_cache_size [message #331079 is a reply to message #331056] Wed, 02 July 2008 05:01 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #331113 is a reply to message #331056] Wed, 02 July 2008 05:53 Go to previous messageGo to next message
nazbrian
Messages: 36
Registered: July 2008
Member
Thanks Arju,

So, What about increasing SGA_MAX_SIZE and then setting "db_keep_cache_size".

Brian
Re: Setting db_keep_cache_size [message #331118 is a reply to message #331113] Wed, 02 July 2008 05:54 Go to previous messageGo to next message
Michel Cadot
Messages: 66680
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't dynamically increase SGA_MAX_SIZE.
You have to restart.

Regards
Michel
Re: Setting db_keep_cache_size [message #331135 is a reply to message #331056] Wed, 02 July 2008 06:05 Go to previous messageGo to next message
VenkatMethuku
Messages: 8
Registered: June 2008
Junior Member
also chk out the max memory available in the server.
Re: Setting db_keep_cache_size [message #331138 is a reply to message #331056] Wed, 02 July 2008 06:12 Go to previous messageGo to next message
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 #331142 is a reply to message #331138] Wed, 02 July 2008 06:17 Go to previous messageGo to next message
Michel Cadot
Messages: 66680
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unless you have specific and precise reasons and are an expert in Oracle caches, use only default cache.

Regards
Michel
Re: Setting db_keep_cache_size [message #331146 is a reply to message #331056] Wed, 02 July 2008 06:40 Go to previous messageGo to next message
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 #331174 is a reply to message #331146] Wed, 02 July 2008 08:22 Go to previous messageGo to next message
Michel Cadot
Messages: 66680
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From my knowledge, you don't need keep buffer pool.

Regards
Michel
Re: Setting db_keep_cache_size [message #331617 is a reply to message #331174] Fri, 04 July 2008 04:19 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #676382 is a reply to message #676381] Thu, 06 June 2019 01:39 Go to previous messageGo to next message
Michel Cadot
Messages: 66680
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Database Performance Tuning Guide

Re: Setting db_keep_cache_size [message #676383 is a reply to message #676381] Thu, 06 June 2019 01:58 Go to previous messageGo to next message
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 #676384 is a reply to message #676383] Thu, 06 June 2019 02:16 Go to previous messageGo to next message
Michel Cadot
Messages: 66680
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you set it, Oracle will modify (not reset) your value to fulfill the formula it gives.
The sentence in the documentation is misleading, the value is not "the granule size * number of processor groups, or 4 MB * number of CPUs, whichever is greater" but the least multiple of this value greater than the value you give.
For example, if you have a granule of 4MB and 1 CPU and set a 10MB buffer size, Oracle will take 12MB.

Re: Setting db_keep_cache_size [message #676385 is a reply to message #676384] Thu, 06 June 2019 02:52 Go to previous messageGo to next message
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 #676386 is a reply to message #676385] Thu, 06 June 2019 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 66680
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The behavior of caches and SGA is explained in details in the document I pointed you to.

Re: Setting db_keep_cache_size [message #676387 is a reply to message #676381] Thu, 06 June 2019 05:47 Go to previous message
John Watson
Messages: 8046
Registered: January 2010
Location: Global Village
Senior Member
dba4oracle wrote on Thu, 06 June 2019 07:22
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
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.
Previous Topic: FRA & DIAG (CM merged 2)
Next Topic: Question about - Implement Oracle RAC
Goto Forum:
  


Current Time: Tue Nov 12 00:40:47 CST 2019