Home » RDBMS Server » Performance Tuning » Oracle memory parameters (Oracle 9.2.0.8, Solaris 9)
Oracle memory parameters [message #570919] Sun, 18 November 2012 06:06 Go to next message
milicab
Messages: 1
Registered: November 2012
Junior Member
Hi!
I have a question regarding memory parameters in oracle database 9.2.0.8, 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?

Regards,
M

[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
BlackSwan
Messages: 21936
Registered: January 2009
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
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
Milicab,

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 9.2.0.5 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: Wed Apr 16 07:03:30 CDT 2014

Total time taken to generate the page: 0.16137 seconds