I want to increase the size of the parameter db_cache_size from 1008M to
2048M in order to get better hit ratio.
SQL> show parameter db_cache_size;
NAME TYPE VALUE
db_cache_size big integer 1008M
SQL> show sga
Total System Global Area 2147483648 bytes
Fixed Size 2085424 bytes
Variable Size 1073745360 bytes
Database Buffers 1056964608 bytes
Redo Buffers 14688256 bytes
SQL> show parameter sga
NAME TYPE VALUE
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 2G
sga_target big integer 0
My question is, if sga_max_size is set at 2G, would I be able to increase my db_cache_size to 2GB or do I need to first increase my sga_max_size first. Or to re-phrase, how is sga_max_size set, is
it calculated or does it need to be manually set. I'm thinking its calculated because I dont see this in my spfile.
I am running on a 64 bit AIX 5.3 machine on version 10.2.0.4 with 50GB
of memory on this machine. So raising this value 1GB would not cause my performance to degrade.
Thanks to all who answer
Last edited by BeefStu; 01-12-2011 at 01:26 PM.
Reason: fixed typo
How was is set initially? In 11g, for example, using DBCA, Oracle will target 40% of RAM for the memory_max_target.
SGA_TARGET (and potentially SGA_MAX_TARGET) can change based on administrator statements.
Dynamic SGA provides external controls for increasing and decreasing Oracle's use of physical memory. Together with the dynamic buffer cache, shared pool, and large pool, dynamic SGA allows the following:
The SGA can grow in response to a database administrator statement, up to an operating system specified maximum and the SGA_MAX_SIZE specification.
The SGA can shrink in response to a database administrator statement, to an Oracle prescribed minimum, usually an operating system preferred limit.
Both the buffer cache and the SGA pools can grow and shrink at runtime according to some internal, Oracle-managed policy.
thou this is not the answer to your question, I would like to suggest to use ASMM by setting the value for SGA_TARGET parameter instead of manually setting the values for different pools. You can see more information on ASMM in one of the links provided by Stecal.
for ex you can set 40% of the physical memory to SGA_MAX_SIZE (20 GB in your case) and based on DB size and requirement you can set SGA_TARGET value (you can start with 8GB and monitor). If the statistics shows the need of more memory then you can dynamically increase SGA_TARGET parameter value.
Try hard to get what you like OR you will be forced to like what you get.