Oracle Database Discussions

Hi all,

i have database performance problem, the server is slow because the RAm is used in 100%, taking statspack report i got the folowing output in instance efficiency:

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 14.15 In-memory Sort %: 99.98
Library Hit %: 98.60 Soft Parse %: 95.77
Execute to Parse %: 74.71 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 72.98 % Non-Parse CPU: 99.90

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 84.61 89.19
% SQL with executions>1: 4.97 5.09
% Memory for SQL w/exec>1: 14.23 16.09

The Buffer Hit is 14 which seems very slow, from statspack analyzer they are advising to resize the DB_CACHE_SIZE parameter following the DB_CACHE_ADVICE view output below:

80 10010 1.631 4847378181
160 20020 1.4917 4433425952
240 30030 1.4153 4206245775
320 40040 1.3496 4011022458
400 50050 1.2544 3727968097
480 60060 1.1851 3522007360
560 70070 1.1305 3359950649
640 80080 1.0732 3189644899
720 90090 1.0296 3060020775
768 96096 1 2972013360
800 100100 .9785 2908132320
880 110110 .8999 2674444052
960 120120 .8683 2580516085
1,040 130130 .8366 2486253576
1,120 140140 .6408 1904428818
1,200 150150 .569 1691134670
1,280 160160 .556 1652315333
1,360 170170 .5489 1631483768
1,440 180180 .5448 1619113935
1,520 190190 .5421 1611045498
1,600 200200 .5388 1601383512


can anybody help me on what is the right size of my db_cache_size as i have 4 Gb of RAM and the SGA_MAX_SIZE is set to 2Gb and pga_aggregate_target is set to 0.

thanks
Lucien