Shared Pool Utilization

[pcotten]

When we ran a script for shared pool utilization, here is the result we got. Anything that we should be troubled about? We are on Oracle 8.0.5.1. Our shared pool is 900MB and we run Oracle Applications 11.0.3.


Namespace Gets Ratio Pins Ratio Reloads Invalid.
--------------- ---------- ------- ---------- ------- ---------- ---------------------------------
SQL AREA 1998212 0.9320 169610825 0.9980 73742 23350
TABLE/PROCEDURE 2134026 0.9908 79514635 0.9994 9068 0
BODY 375777 0.9975 376273 0.9957 84 0
TRIGGER 430645 0.9993 430675 0.9983 272 0
INDEX 4404 0.4973 4408 0.4957 5 0
CLUSTER 4790 0.9985 4304 0.9972 2 0
OBJECT 0 1.0000 0 1.0000 0 0
PIPE 996916 0.9994 1006310 0.9994 1 0

[tamilselvan]

900 MB Shared POOL Size is too big. Do you really need 900MB unless you have more than 100,000 tables and 1M columns?

[gpsingh]

Do u see any high shared pool sleeps enteries in v$latch.
If yes , ur shared_pool_size could be very high.

[mbaliga]

Hi,

Check V$SGASTAT for free memory. If you are having lot of free memory all the time then your memory allocation is high. In that case you should look at tuning the latches. Beyond a certain limit, increasing shared pool will have negative impact on database operation.

Baliga