Re: Shared memory problem on Oracle 7.2.2

In article <30EB9F89.5A83_at_stavanger.Geco-Prakla.slb.com>, Per Eivind Solum <peres_at_stavanger.Geco-Prakla.slb.com> writes:
|> We are quite frequently getting error 4031 from
|> our Oracle 7.2.2 database:
|> GST0K1 > oerr ora 4031
|> 04031, 00000, "unable to allocate %s bytes of shared memory
|> (\"%s\",\"%s\",\"%s\")"
|> // *Cause: More shared memory is needed than was allocated in the shared
|> // pool.
|> // *Action: Either use the dbms_shared_pool package to pin large packages,
|> // reduce your use of shared memory, or increase the amount of
|> // available shared memory by increasing the value of the
|> // init.ora parameter "shared_pool_size".
|>
|>
|> The error occurs in an application that performs a large number of
|> relatively small insert transactions. The number of bytes that Oracle
|> is unable to allocate is typically in the range of 100 - 500 bytes.
|>
|> Our database server is a SparcStation 20 with 32Mb shared memory.
|> The Oracle database has 10 Mb shared memory (init.ora parameter shared_pool_size)
|>
|> I have verified that the application commits each transaction properly.
|> Does anyone know if this a known problem on Oracle 7.2.2?
|> Is there any workaround?
|>
At the start of December, 1995, I made several comments about this exact issue. ORACLE provided us with a patch for this specific problem, which then caused a massive performance degradation in our environment. The fixes so far have moved us to ORACLE 7.2.2.4.0 with a further patch for BUG 312953. At this point, MTS is still quite flaky and highly unreliable.

The workarounds for the bugs we were hit by are:

1. Use dedicated servers, not MTS.
2. On a crontab entry, we use sqldba to 'alter system flush shared_pool;' on a frequent basis. On some of our client servers, which still haven't applied the various patch kits, they have to run the crontab as frequently as every 4 minutes. There is still the occasional hit, but the odds are much lower. The SSA problem seems to occur in all releases after 7.0.16 up to, but not including 7.3 ... the calculation of hash addresses in the SSA changed from using the last 256 bits of the SQL statement to only using the last 64 bits. Bug fix 312953 puts it back to 256 bits. We went from 60-70 collisions in every library-cache bucket to only 2 to 3 collisions. This also really helps performance.

Bill Beaton

p.s. Enlarging the shared pool helps, but only in lengthening the period before the memory leak bug hits.

-- 
Names:	Bill Beaton			beatonb_at_cadvision.com
					Bill_Beaton_at_digitech.ab.ca
Phone:	(403) 295-3254 (Home)		(403) 266-8622 (work)