I monitor thousands of databases that other DBA teams personally manage, and it seems every day there is a one or two databases, usually non-production, throwing "
ORA-04031 unable to allocate (n) bytes of shared memory" complaints about the shared pool, which breaks my monitoring queries because I can't parse. I have for years been in the habit of simply bouncing these instances and moving on because the task of trying to chase down exactly how Oracle got its memory management screwed up in each case is too great. There are too many databases across too many versions, from 8i to 19c.
Years ago I found that this really increased with ASMM (
sga_target). I tried setting
sga_target to somewhat less than
sga_max_size on the theory that if Oracle is computing pool sizes and making a calculation mistake, having some headroom would help it. But I found that didn't really help. So I've just been bouncing immediately upon encountering the problem (non-prod, of course) all these years.
Has anyone found a better way? You'd think Oracle would be better at its memory management calculations and not get itself into this situation so frequently. I know there are lots of causes that can lead to this... abusive queries generating excessively large and/or unsharable cursors, sudden massive PX pool demand, etc, etc, etc. I've solved many of these cases in the past, but it's too much to chase them down in every case. I'm asking if anyone has had experience with this and found a best-practices approach that minimizes this across an entire footprint.
sga_targetlikely just aggravated the problem, either way. There is no best practice that involves ignoring the root cause.
:1there are literal values and no other difference). If that's a hit, get the devs using BIND variables (and to mitigate until done, maybe play with
cursor_sharingfor the corresponding sessions).