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: 64103
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: 64103
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: 64103
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: 64103
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 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.
Previous Topic: Information required: Migration to 10g from Oracle9i
Next Topic: ORA-00257: archiver error. Connect internal only, until freed.
Goto Forum:
  


Current Time: Sat Dec 03 06:12:24 CST 2016

Total time taken to generate the page: 0.05497 seconds