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.

SQL> show 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_reserved_size 5872025
shared_pool_size 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_size?
Please advise.
Thank you

[Pratap]

I suggest you use workarea_size_policy = 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_ADVICE
V$PGA_TARGET_ADVICE
V$SHARED_POOL_ADVICE

Pratap Deshmukh
Cognizant Technology Solutions, India
www.cognizant.com