Resizing the SGA

[Ping Ai]

Dear All,

The number of users will be doubling soon and I would really
appreciate some advice on the SGA sizing.

Database version: 9i
Operating System: AIX 4.3.3 ML 10

Shared Server configuration.
Currently...

Average 20 users.

SQLshow sga

Total System Global Area 336529160 bytes
Fixed Size 452360 bytes
Variable Size 268435456 bytes
Database Buffers 67108864 bytes
Redo Buffers 532480 bytes

NAME VALUE
hash_area_size 2097152
java_pool_size 117440512
large_pool_size 1048576
log_buffer 524288
sga_max_size 336529160
shared_pool_res erved_size 5872025
shared_pool_siz e 117440512
sort_area_size 1048576

The server has just been upgraded from 1GB to 2GB of RAM.

How can I calculate the approximate values when the extra users come
in?

Also the large pool size needs to be increased because of ORA-4031
error. But if I increase it, do I need to make any changes to the
other parameters like shared_pool_siz e?


Please advise.


Thank you

[Pratap]

Re: Resizing the SGA

I suggest you use workarea_size_p olicy = AUTO as you are using Oracle
9i. Oracle will then manage the sizes of all components of PGA like
hash_area_size, sort_area_size etc.

Also switch on the all "advices" in the database like db_cache_advice .

Once you start running the system check these views to adjust your
memory components -

V$DB_CACHE_ADVI CE
V$PGA_TARGET_AD VICE
V$SHARED_POOL_A DVICE

Pratap Deshmukh
Cognizant Technology Solutions, India

https://www.cognizant.com