0

all

My team is trying to resolve an oracle issue we're seeing sporadically that is really causing us trouble.

We have an update process that imports .dmp files via a package that utilizes an Apply job to do so. (not our choice to implement it this way...)

The problem is, we're getting this apply job aborted with an error of 4031 unable to allocate <100 bytes (various values, usually from 60 to 90) from memory, with the shared pool marked as the culprit. ASMM is on. The server is provisioned ~500 MB of memory to run on (these are old Server 2003 environments so memory is in high demand). I am concerned about this error - it seems to me that the error should always report multiples of 4k, as that is our granule size, but perhaps I am mis-understanding the process of freeing up memory. I am also concerned that the server is not freeing up packages as needed.

I believe that the fix is to use dbms_shared_pool.keep("PACKAGE NAME") to pin the package used to run the apply job, so that it never has to allocate the memory again, but we need to test this fix before we deploy it, for various organizational reasons. We also plan to allocate more memory to the oracle database. Setting the minimum size is not a solution, as we've seen this issue when the size of the pool is above the minimum, with ASMM on. We've also tried setting the sga maximum size to a higher value, and pinning the package in question, but again, with no way to reproduce the issue, it is a waiting game to see if it ever happens again.

Our main problem is in reliably reproducing the problem - even when disabling ASMM and setting the shared pool as small as possible, we're not able to reliably reproduce the issue. We've gotten the shared pool small enough that the web interface can't even pull image tags out of the database, or query the login information from the appropriate table, yet the apply job still runs reliably.

We do not have an in-house DBA so naturally we're probably missing some obvious steps.

This issue is present sporadically on 2-3 servers in a 8 server environment. These individual servers all see similar use by different groups. I am unclear as to why only some of the servers encounter this problem, but have not been able to identify any smoking gun cause.

I am hoping for input in either reliably avoiding this issue entirely, or in reliably forcing the shared pool to be unable to allocate bytes to this job, so that we can confirm these fixes actually work.

Please bear in mind that I have read most of the 4031 posts on SO at this point - this problem has been under investigation for weeks. It is likely NOT appropriate to mark this question as duplicate and direct me to another question - I've read it already. It did not answer my question.This is a cross post from plain old Stackoverflow

I have examined the trace files for one of the servers but it was less than enlightening - I am unsure of what to look for.

We've tried flushing the shared pool, but again, with no way to reproduce the issue it turns into an "wait and see" fix, which isn't the answer that our funder is really interested in.

6
  • While I am working on an answer, I will let you know that ORA-4031 is an extremely difficult error to reliably reproduce. As I'm sure you well know, it's when the instance's shared pool is exhausted that this error occurs. This can easily explain why it's hard to pin down and why it happens across your 8 server cluster to "random" nodes. It's just a luck of the draw when it comes to connection management. You mention that you have a 500m memory server, which is incredibly small. How much do you have allocated to shared_pool_size? Trivially, you could try to increase that paramter. Dec 29, 2016 at 16:24
  • We typically have ~250mb allocated to the shared pool by ASMM. I agree that it's exceedingly small. On the plus side, these servers each see low usage - they're not publicly facing. We plan to allocate a bit more memory (maybe 1G total to SGA) but given that the servers can only index 4GB total, its a tricky balancing act to allow the server to run the webserver, oracle database, etc and not strangle any one thing. To clarify, they're not clustered - they're independent servers with different data, they each perform the exact same function for different customers, is all. Dec 29, 2016 at 16:33
  • I'd recommend specifying the size of db_cache_size and shared_pool_size independently instead of arbitrarily increasing SGA. Your errors are being caused by only the shared pool exhausting, but your performance with current sized db_cache is fine. Increasing SGA target/max will "waste" memory on stuff you don't need. Dec 29, 2016 at 16:37
  • What would be the best way to provision more shared pool size without increasing SGA maximum? Or do you mean increase shared pool and increase sga_target and max size only by the increase over the current size of shared pool? Dec 29, 2016 at 16:44
  • I personally have never relied or trusted sga_maxsize or sga_target in the spfile. I let Oracle calculate that "on-the-fly" after defining the sizes I want each piece of the SGA to be. As it stands, I'd adjust your max/target size by the same amount you increase your shared_pool_size. If shared_pool_size goes up 250m (example), increase SGA target/max by 250m. Dec 29, 2016 at 16:48

1 Answer 1

0

This answer is based on the discussion had in the comments of the original question.

The primary cause of any ORA-4031 is that the shared pool is exhausted and is unable to allocate new connections to the database. This will cause the database to crash. I am not going to comment if the package that is running the import is just the last straw or if it is the entire problem, architecturally, I am just offering a workaround.

Due to the small nature of the server memory, space is extremely restricted. I would recommend assigning specific amounts of memory to the shared_pool_size parameter in the spfile and bumping up memory usage incrementally each time the issue occurs. This, of course, implies an increase to SGA_max_size and SGA_target_size. Initial sizing and increment size will be left for the author to discuss, but I'd imagine 50m jumps would be small enough to keep the server happy and also have reasonable chances of success to workaround the ORA-4031.

If memory of the server becomes exhausted before issues begin to resolve, then I would begin digging around the import package load to see if there are inefficiencies with how that procedure is using the shared_pool.

1
  • I'll accept this as the answer for the time being - obviously an answer like this won't necessarily have the "silver bullet" fix, but as it stands this is the best path that's been suggested to me thus far. I invite others to also submit any feedback or ideas that they have. Dec 29, 2016 at 17:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.