I came across this script to monitor free memory in SGA in Oracle wiki.
select POOL, round(bytes/1024/1024,0) FREE_MB
from v$sgastat
where name like '%free memory%';
POOL............FREE_MB
------------ ----------
shared pool.........293
large pool............7
java pool.............8
Had been looking for this one for some time.
Subscribe to:
Post Comments (Atom)
Posts
3 comments:
Just out of curiosity, what do you expect to see with that?
Hi Frits,
We occassionally get ORA-4031 error which is when sga runs out of memory. If we write a small program which periodically monitors and records SGA use, we can use that information for sizing our SGA correctly. It is good information for capacity planning.
- Vikram
I understand. But that's not what you get.
The shared pool has several important functions. It keeps states of several things, and functions for a great deal as cache.
Memory in the shared pool is allocated using linked lists. Needed memory is allocated from free space, or freed as necessary.
Getting an ORA-4031 means no more memory can be allocated nor freed with the requested size.
So, what does that mean?
If free memory is available in the shared pool during normal running, depending on the warmup time of the database (the time it takes to get in a steady working state), it means it is sized too big.
So what should be monitored?
Well, the shared pool must be large enough to have enough freeable memory for allocations.
And, (pre 10gr2 I believe) memory can fragment to little pieces, so large allocations (just beneath shared_pool_reserved_min_alloc) be done anymore.
Post a Comment