Re: ORA-04031 yet 40% of 'free memory' in V$SGASTAT

My ISP news server seems to lose items at random, so I didn't see the initial post until I did a google search.

As Sybrand pointed out, this is (quite close to) the total of all free fragments in the shared pool.

When a session connects, it demands a chunk of about 20K under the heading of 'Session param v' or 'Parameters' (varies with version and where you look, possibly)

After a typical OLTP system has been running for a while, most of the memory has been fragmented into chunks of 4K or less, so when a new session connects, it may have to kick out a load of little chunks, before it can create a CONTIGUOUS piece of the required size. If it fails to make a contiguous section, you get a 4031.

That's why you have the "shared pool reserve", which is only supposed to be used for larger allocation, where the limit is set by a parameter called _shared_pool_reserved_min_alloc (default ca. 4K bytes). After the main pool is full, new allocations that are larger than the limit are first checked against the free space in the reserved pool before any space is cleared in the main pool. If you are seeing the problem regularly on logons, the solution may be to increase the size of the reserved pool - set parameter shared_pool_reserved_size to something larger than the default (which is 5% of the shared pool).

Note - There is a metalink note that suggests you can query x$ksmsp to see the detail of how the shared pool is being used. Don't run it, it can lock up a large, busy, system for minutes (literally). The equivalent query against x$ksmspr (shared pool reserved) is safer.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated March 9th 2005






<bdbafh_at_gmail.com> wrote in message 
news:1112305263.621952.211760_at_z14g2000cwz.googlegroups.com...


> Been in this exact situation, as recently as last week on 9.2.0.5.3.

>

> have you checked Metalink for articles on this?

> (They are helpful)

>