2

I'm running Oracle 12.2.0.1 Standard edition on Windows 2012 RC2. I'm NOT running Oracle's Automatic Memory Management.

We are seeing these in the alert log:

ORA-04031: unable to allocate 4096 bytes of shared memory ("java pool","prv//////LBKLAGIMAAAAAAAA","JOXLE^aff7380e",":SGAClass")

First thing I thought to try was flushing the shared pool to resolve the immediate problem, while the dev team is fixing the SQL code, so I ran:

ALTER SYSTEM FLUSH SHARED_POOL;

Only then did I realise that the Java Pool isn't actually part of the shared pool. So I tried to take a look at the Java pool.

I think this tells me that my Java pool is roughly 32MB:

SQL> select * from v$javapool;
CATEGORY                                            MEMUSED     CON_ID
------------------------------------------------ ---------- ----------
:Intern                                              973440          0
:SGAClass                                          32103240          0
:SROState                                             81120          0

I ran this tuning tool, which if I can interpret correctly, says that there's not much benefit from increasing the Java pool size:

    select
       estd_lc_size,
       java_pool_size_for_estimate,
       java_pool_size_factor
    from
       v$java_pool_advice;
ESTD_LC_SIZE JAVA_POOL_SIZE_FOR_ESTIMATE JAVA_POOL_SIZE_FACTOR
------------ --------------------------- ---------------------
           8                          16                    .5
          24                          32                     1
          25                          48                   1.5
          25                          64                     2
          25                          80                   2.5
          25                          96                     3
          25                         112                   3.5
          25                         128                     4
          25                         144                   4.5
          25                         160                     5
          25                         176                   5.5

I ran the following query taken from the Oracle docs here and it seems to suggest that the SGA_TARGET value is fine!

SELECT (
   (SELECT SUM(value) FROM V$SGA) -
   (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)
   ) "SGA_TARGET"
FROM DUAL;
SGA_TARGET
----------
5167382528 = 4928 MB i.e. same as what it currently is!

I can see that:

    MEMORY_TARGET = 0
    SGA_MAX_SIZE = 4928M
    SGA_TARGET = 4928M
    JAVA_POOL_SIZE = 0
    SHARED_POOL_SIZE = 0
    DB_CACHE_SIZE = 0 
    DB_KEEP_CACHE_SIZE = 0
    MEMORY_MAX_TARGET = 0

...so I'm bit confused how to tune this when we're not using AMM.

So how to resolve this issue?

  1. is my best option to increase the Java pool size?
  2. if so, how to do this when not using AMM?
  • What values do you have for the other memory relevant settings? SHARED_POOL, DB_CACHE_SIZE, DB_KEEP_CACHE_SIZE and other memory parameters? Is anything set for MEMORY_MAX_TARGET? Add the information to your question by hitting the edit button. – John aka hot2use Apr 20 at 9:09
  • Nearly forgot: How much memory (RAM) does your system (laptop, desktop, server) have? – John aka hot2use Apr 20 at 9:14
  • Hi John, I've added those values. Let me know if there are other ones you would like to know. In general I can say that this system has never been tuned, and is using the Oracle defaults. Thanks! – TrojanName Apr 20 at 9:19
  • RAM on this server = 16GB – TrojanName Apr 20 at 9:20
1

AMM or ASMM?

There are two forms of Memory Management in an Oracle Database instance:

  1. Automatic Memory Management (AMM)
  2. Automatic Shared Memory Managemnt (ASMM)

If you were to have set values for the MEMORY_MAX_TARGET and MEMORY_TARGET then you would be in AMM mode. Your Oracle instances will assign memory up until the MEMORY_TARGET setting. If you were to notice a performance impact then you could increase the MEMORY_TARGET value up until the MEMORY_MAX_TARGET size without having to restart the instance.

If you haven't set the MEMORY_MAX_TARGETand the MEMORY_TARGET values for your instance, then we can assume that you are in ASMM mode, because you have set a value for the SGA_TARGET parameter.

When in ASMM mode the following parameters are automatically configured:

Table 6-2 Automatically Sized SGA Components and Corresponding Parameters
SHARE_POOL_SIZE
LARGE_POOL_SIZE
JAVA_POOL_SIZE
DB_CACHE_SIZE
STREAMS_POOL_SIZE

Reference: Table 6-2 (Oracle Docs)

There is also the following small note which indicates:

In addition to setting SGA_TARGET to a nonzero value, you must set to zero all initialization parameters listed in Table 6-2 to enable full automatic tuning of the automatically sized SGA components.

So there is no need to set the JAVA_POOL_SIZE to a non-zero value, unless you want to manually assign a minimum value for the parameter. Otherwise, Oracle will assign memory inside the boundaries of your SGA_TARGET size, which is currently slightly over 4 GB (4928M).

What is using SGA_TARGET?

Then there is the hint that:

(emphasis mine)

The manually sized parameters listed in Table 6-3, if they are set, take their memory from SGA_TARGET, leaving what is available for the components listed in Table 6-2.

The parameters in Table 6-3 are:

LOG_BUFFER
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
DB_nK_CACHE_SIZE

Reference: Table 6-3 (Oracle Docs)

Solution

If the components in table 6-3 are using too much memory, then there isn't enough memory left in the SGA_TARGET pool to automatically assign enough memory for the components in table 6-2 or in your case for the JAVA_POOL_SIZE parameter. You can the observe the error:

ORA-04031: unable to allocate 4096 bytes of shared memory ("java pool","prv//////LBKLAGIMAAAAAAAA","JOXLE^aff7380e",":SGAClass")

You might have to increase the SGA_TARGET parameter to a value higher than what you currently have, in order for the SGA Pool to have sufficient memory to automatically be able to tune the dynamic parameters.

Try 8192M and observe how your Oracle instance responds to these changes.

Reference Reading

| improve this answer | |
  • Thanks John. That makes more sense that it shouldn't be necessary to manually change the JAVA_POOL_SIZE. I'll increase both SGA_TARGET and SGA_MAX_SIZE to 8192M and see how it goes. Thank you! – TrojanName Apr 20 at 10:20
  • Thanks again. I just implemented that and it resolved the ORA-04031 issues with the java pool. – TrojanName Apr 20 at 16:27
  • 1
    Nice to hear. Glad I could provide you with an informative answer. – John aka hot2use Apr 20 at 16:53
1

With SGA_TARGET set and JAVA_POOL_SIZE=0 it you are at least using Automatic Shared Memory Management, so the Java Pool is already self-tuning. You can specify a JAVA_POOL_SIZE value that will become the minimum pool size and Oracle may choose to tune it higher if necessary. Changing the JAVA_POOL_SIZE will require a restart of the database. You may need to increase the SGA_TARGET value as well, as increasing the default JAVA_POOL_SIZE will take memory away from other SGA pools.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/JAVA_POOL_SIZE.html#GUID-F24C4770-0B7C-42BD-9AAD-0EC1E92A90F6

| improve this answer | |
  • What is the value of MEMORY_TARGET? – pmdba Apr 16 at 22:02
  • Thanks for responding! MEMORY_TARGET is 0. I have to admit I'm pretty confused. If the Java pool is self tuning, why am I getting the ORA-04031, and why do I need to change JAVA_POOL_SIZE? – TrojanName Apr 17 at 9:27
  • I forgot to ask, what is SGA_MAX_SIZE? Is it also 4920M? Oracle can only tune SGA components within the overall limit the SGA, so it most likely reached a point where it couldn't steal memory from another pool to grow the Java pool. You'll have to decide if you want to set a minimum size for the Java pool, or let Oracle continue to manage everything, but either way you'll need to grow the SGA max and target to make room for the new Java requirements. – pmdba Apr 17 at 10:52
  • According to this, Oracle recommends a minimum Java pool size of 50M. docs.oracle.com/database/121/JJDEV/chnine.htm#JJDEV13316 – pmdba Apr 17 at 10:57
  • yes, SGA_MAX_SIZE = 4928M too. I'm happy to let Oracle handle everything. So what's the recommendation here exactly? Increase both SGA_TARGET and SGA_MAX_SIZE and Oracle should handle the rest? Anything else needed? – TrojanName Apr 17 at 12:46

Your Answer

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

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