0

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.

Improve this question
7
  • If you have "abusive" queries in these instances, then you have to resolve them. Otherwise the problem will just eventually come back, even if you add more memory. If problem queries aren't an issue, then the only recommended solution to this error is to add more memory to the affected instances - they're being asked to do more than they have the resources to handle. Your reduction of sga_target likely just aggravated the problem, either way. There is no best practice that involves ignoring the root cause.
    – pmdba
    Mar 3 at 19:15
  • What you've articulated is exactly what my assumption has been all these years, hence resort to bouncing. My post is putting out feeders to see if anyone has discovered something most of us DBAs don't know about that could help broadly. Again, in dealing with the thousands of DBs and even more thousands of apps, I can't chase down every abusive query on every dev/test DB out there.
    – Paul W
    Mar 3 at 19:18
  • 1
    Hmmm. Yeah, not an enviable position. Not sure how I'd handle that either, except possibly to restart (as you've been doing) and prioritize the most frequent offenders to see if there's a common problem, or problem developer. Not much else you can do in a Dev environment if the users (or their code) can't "behave". Force enough restarts and maybe(?) they'll resolve to fix their own problems rather than deal with unplanned downtime? You're right in saying that you can't fix all of those problems yourself.
    – pmdba
    Mar 3 at 19:29
  • You could check the SQL Area for questions not using BIND variables and see i there are many "similar looking" ones only differing in literals (like "SELECT name FROM customers WHERE id=:1", only that instead of the :1 there 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_sharing for the corresponding sessions).
    – Izzy
    Mar 6 at 21:57
  • 1
    Yes, I know, but when you have thousands of databases and even more thousands of apps, there isn't enough time to chase down individual SQL issues. I know how to do that, but can't do so on such a large scale. My post is soliciting ideas that can apply across all Oracle instances.
    – Paul W
    Mar 6 at 22:17

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.