Database buffer parameter

[Former Member]

Hello all,

I need to increase the DB buffer parameter to a new value.

I think I need to play with the values for db_cache_size parameter, am I right ?

My other question is, how can I check the physical memory available to be able to increase the db buffer size ?

Thanks,

J.

[former_member191911 · Accepted solution]

Hi Jordan,

You need indeed increase the value for db_cache_size.

Note 1431798 - Oracle 11.2.0: Database Parameter Settings states the following about this parameter:

You are allowed to optimize memory parameters and resource parameters such as DB_CACHE_SIZE or DB_WRITER_PROCESSES individually. Therefore, this note cannot give any general recommendations. However, you can determine options for optimization on the basis of a database performance analysis (see Notes 618868, 619188, 789011).

On how to check the available free memory, that depends on the operating system you are running. For Linux you can check with free -m, in Windows you can check the allocated memory in the task manager.

Kind regards,

Mark

[Former Member · Accepted solution]

Hello M.

Thanks for your fast answer,

I am running on AIX 6, and Oracle 10.2.5

Is it true that sga_max_size shows me the total of physical memory,from where db_cache_size is for the buffer memory ?

For example:

sga_max_size=35840M

db_cache_size=20480M, so 20480M from the total physical memory ( 35840M ) is for the buffers ?

Thanks!

[former_member191911 · Accepted solution]

To check the memory on AIX you can use the following command: svmon -G

The parameter sga_max_size sets the upper limit of the shared global area, which is the memory area that holds your buffers. So also the shared cursor cache.

I don't know if you have more databases or SAP systems running on this machine. If so, you should make a good calculation to distribute the memory across these instances.

Kind regards,

Mark

[Former Member · Accepted solution]

SGA_MAX_SIZE specifies the maximum size of the SGA for the lifetime of the instance not the total physical memory. You need to adjust SGA_MAX_SIZE with relation to physical memory installed. After that you can change db_cache_size and other sga memory parameters within SGA_MAX_SIZE.

Yes, db_cache_size is part of sga for the buffer memory.

To check the physical memory on AIX run

lsattr -El mem0 or

getconf REAL_MEMORY

Regards

Roman

[Former Member · Accepted solution]

Hi Jordan,

>> Is it true that sga_max_size shows me the total of physical memory,from where db_cache_size is for the buffer memory ?

db cache size is the part of the SGA. sga_max_size parameter does not indicate the physical memory on the server. It is the memory to be allocated on the system. Please note that the parameter value needs to be in the physical memory range.

I recommend you use dynamic SGA allocation, published by Oracle 9i, if you are not so much familiar with the Oracle. Check the note for the further information, below;

Note 617416 - Oracle9i: Dynamic SGA

Best regards,

Orkun Gedik