WOW! 200% SGA Usage!!

I'm running 8i under AIX 5.2.

We had an ORA-600 last Friday. Started with some 'unable to allocate 300 bytes in shared pool' messages, and then the 600 was thrown and the whole thing fell over. Not a pretty sight.

We've started scrutinizing SGA usage, because we are always trying to build the case to GET OFF of 8i. We've been trying for years, but sadly, those that make the decisions are several tiers separated from those that deal with the problems. Plus, there's been this 'plan' to move to 10g under Windows, and this plan is just draaaaagiinngg alloonnnng.... Not so much a priority for some as it is for others, obviously.

This environment was initially staged under 8.0.5. At that time, under AIX 5, there was (apparently) a hard upper limit for memory allocation. I believe that it was 2GB. Therefore, advice we've received to increase the SGA would not apply, because we are already 'maxed out' in terms of memory utilization.

I am attempting to confirm whether the same hard limit exists under 8.1.7.4.

Also: A recent Oracle report from our database is alarming:

Obj mem: 251348059 bytes
Shared sql: 249576779 bytes
Cursors: 862250 bytes
MTS session: 133880816 bytes
Free memory: 52497868 bytes (50.07M)
Shared pool utilization (total): 652323214 bytes (622.1M) Shared pool allocation (actual): 314572800 bytes (300M) Percentage Utilized: 207%

The utilization varies between 75% and 80% during the day. But the 207%  utilization report is unexpected. These numbers are arrived at as follows:

Shared pool utilization (total): *sum of*

select sum(sharable_mem) from v$db_object_cache;
select sum(sharable_mem) from v$sqlarea;
select sum(250*users_opening) from v$sqlarea;

Shared pool allocation (actual):
select value from v$parameter where name='shared_pool_size'

I had thought that Shared Pool allocation under 8 was static. Clearly it must grow as needed... or my reporting is wrong.

Anyway. Just thought some people might get a kick out of seeing a 200% SGA utilization.

BD Received on Thu Mar 23 2006 - 12:04:32 CST