Home » RDBMS Server » Performance Tuning » Oracle memory parameters (Oracle, Solaris 9)
Oracle memory parameters [message #570919] Sun, 18 November 2012 06:06 Go to next message
Messages: 1
Registered: November 2012
Junior Member
I have a question regarding memory parameters in oracle database, especially sga_max_size and db_cache_size.
Database server has 32G of ram. Oracle parameter on server shmmax is set to 16G. Is reasonable to set sga_max_size to the same value, and db_cache_size to 80% of that size? Do you have any suggestions for these values?


[Updated on: Sun, 18 November 2012 06:29]

Report message to a moderator

Re: Oracle memory parameters [message #570921 is a reply to message #570919] Sun, 18 November 2012 11:44 Go to previous messageGo to next message
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Is 64-bit Oracle installed?
Ready, FIRE, Aim!
How will you determine the optimal values?
Re: Oracle memory parameters [message #571157 is a reply to message #570921] Wed, 21 November 2012 13:59 Go to previous message
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member

I have a high volume logical reads and physical writes database and I find that if I let shared_pool_size or db_cache_size grow to over 4 gigabytes I run into latching issues that slow down the database. I have the developers add bind variables to the reduce the shared_pool_size and I set db_keep_cache_size=3g and db_recycle_cache_size=3g and I cache heavy hit reads into the keep and heavy hit writes in the recycle. After Oracle does not really keep objects in the Keep cache. nor recycle the objects in the recycle cache. They differ from the db_keep_cache in the following hidden parameter.
VALUE Parameter               KSPPDESC
----- ----------------------- ---------------------------------------------

50    _db_percent_hot_default Percent of default buffer pool considered hot
0     _db_percent_hot_keep    Percent of keep buffer pool considered hot
0     _db_percent_hot_recycle Percent of recycle buffer pool considered hot
Previous Topic: Options for optimising SQL running against Wide tables
Next Topic: Inner join query tuning
Goto Forum:

Current Time: Mon May 27 20:47:31 CDT 2024