I have an oracle server 11G with 12GB RAM with the following SGA settings:
Total System Global Area 5027385344 bytes
Fixed Size 2237008 bytes
Variable Size 3523218864 bytes
Database Buffers 1493172224 bytes
Redo Buffers 8757248 bytes
The database has a schema using a tablespace of 45GB, were the biggest table is 3GB in size.
The user experiences 'performance problems' now and then. I have seen that when the query is taking to much time, it is doing reads directly from disk with a full table scan. Tuning the query is quite difficult, I didn't manage to avoid full tables scan all the time.
I was thinking of increasing the RAM on the server to 64GB so that the whole schema can be buffered and reads shouldn't be done anymore from disk. Although the oracle advisor predict that the impact is very low.
select sga_size,sga_size_factor,estd_db_time from v$sga_target_advice;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME
---------- --------------- ------------
2352 .75 3164056
3136 1 2859259
3920 1.25 2752609
4704 1.5 2707718
5488 1.75 2702286
6272 2 2701428
What do you think I can do best?