Increasing db_cache_size

[BeefStu]

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

[stecal]

SGA is more than just the buffer cache.
Are you licensed for AWR? What does the SGA target advisory show?
SGA_MAX_SIZE is set by you.
http://download.oracle.com/docs/cd/B...htm#sthref1244

[BeefStu]

Here is my advisory report

Estd Phys Estd Phys
Cache Size (m) Buffers Read Factor Reads
---------------- - ----------- ----------- ----------------
96 11,874 1.27 4,490,645,591 << 10% of Current Size
192 23,748 1.18 4,174,301,538
288 35,622 1.13 4,008,719,045
384 47,496 1.10 3,892,860,349
480 59,370 1.08 3,808,793,078
576 71,244 1.06 3,742,975,772
672 83,118 1.04 3,688,397,488
768 94,992 1.03 3,641,786,016
864 106,866 1.02 3,599,416,785
960 118,740 1.01 3,557,367,731
1,008 124,677 1.00 3,539,057,565
1,056 130,614 1.00 3,522,122,142 << Current Size
1,152 142,488 .99 3,492,253,740
1,248 154,362 .98 3,465,373,356
1,344 166,236 .97 3,439,718,306
1,440 178,110 .97 3,416,458,246
1,536 189,984 .96 3,395,725,154
1,632 201,858 .95 3,376,433,866
1,728 213,732 .95 3,358,980,730
1,824 225,606 .94 3,342,605,606
1,920 237,480 .76 2,676,266,371 << 200% of Current Size

[stecal]

What does the SGA advisor show? For the buffer cache, you could increase its size and see some improvement (per what Oracle estimates).

http://download.oracle.com/docs/cd/B...ory.htm#i45097

[BeefStu]

That is my next step but I want to put a load on my system first. I will
update the numbers by Friday for those who are curious.

As per my question, I did not set the sga_max_size the only thing I increased
was the db_buffer_cache from 1G -> 2GB but the sga_max_size did grow 1GB
along with the total SGA, which I expected.

So it looks like it is calculated since I did not implicitly set it. Does that
seem correct? Does the sga_max_size only apply when using ASM? I mean
does it have to be set when using sga_target_size?

Thanks for your help Stecal, its greatly appreciated.

[stecal]

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.

[vnktummala]

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.

Thanks,

[BeefStu]

I agree using ASSM is the right thing to do and when I upgrade my DB to
11gr2 that's the approach I will be taking.

Right now, since we are running on 10.2.0.4 I am holding off becuase of some
negative articles I saw with ASSM and Oracle 10

[vnktummala]

as far as I know, there is no negative impact using ASMM in 10g. We have more than 50 big size databases in my shop and we are happy with ASMM.

Please note - ASSM is not related to memory management. It's for segment space management.

Thanks,