Home » RDBMS Server » Performance Tuning » db_cache_size (Oracle 10.2.3, SunOS sun11 5.9)
db_cache_size [message #309781] Fri, 28 March 2008 15:07 Go to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
Hello, we are on Oracle 10.2.3. Back in 8i (we actually used it in 9i as well) the size of the SGA was controlled by the init parameter db_block_buffers. My understanding is that this parameter is depricated since 9i, as Oracle recommends to use db_cache_size parameter. How does db_x_cache_size relate to that parameter? Does anyone know if we do not implement Automatic Shared Memory Management, what is the guideline to set db_cache_size? Our db_block_sizeis set to 8192. Also, is there a way to monitor how this setting would affect performance in 10g? Thank you!

[Updated on: Fri, 28 March 2008 15:22]

Report message to a moderator

Re: db_cache_size [message #309904 is a reply to message #309781] Sat, 29 March 2008 13:42 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
db_cache_size parameter defines the size for the database buffer cache in the SGA.
WIth manual mode
db_cache_size= no. of buffers*db_block_size.

db_block_size represents a standard block size in your case 8k.

If you plan to use multiple block sizes in your database ,you specify db_x_cache_size parameter.

Oracle occupies separate space for these caches in SGA.
The default value is 0.

Re: db_cache_size [message #310171 is a reply to message #309904] Mon, 31 March 2008 08:17 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
Do you know if there is a way to monitor how db_cache_size will affect performace? Thank you!
Re: db_cache_size [message #310173 is a reply to message #310171] Mon, 31 March 2008 08:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
V$DB_CACHE_ADVICE

Regards
Michel
Re: db_cache_size [message #310202 is a reply to message #310173] Mon, 31 March 2008 09:57 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
Ok, got it. So DB_CACHE_SIZE should be set insead of DB_BLOCK_BUFFERS. V$DB_CACHE_ADVISE view along with V$BH and X$BH views will provide info on how many buffers to add/take away. I read the following:

Do not explicitly specify the DB_BLOCK_BUFFERS parameter in Oracle9i Database, or you will eliminate the ability of the database to dynamically reset buffer sizes.

http://www.dba-oracle.com/art_ault_optimization_parameters.htm

Are they talking about ASSM? Does ASSM automatically size db_cache_size as well?

Thank you!

[Updated on: Mon, 31 March 2008 09:58]

Report message to a moderator

Re: db_cache_size [message #310211 is a reply to message #310202] Mon, 31 March 2008 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't read the link you posted and so can't say about this article but I warn you that many informations on this site are wrong and/or misleading.
Find your informations in Oracle doc or sites like otn or asktom, or J. Lewis site or scratchpad...

Regards
Michel

[Updated on: Mon, 31 March 2008 12:02]

Report message to a moderator

Re: db_cache_size [message #310232 is a reply to message #310211] Mon, 31 March 2008 11:33 Go to previous message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
ok, thank you!
Previous Topic: Statspack
Next Topic: SQL Query behaving weirdly in 10g
Goto Forum:
  


Current Time: Sat Dec 10 12:59:23 CST 2016

Total time taken to generate the page: 0.10274 seconds