Tuning Java Pool after getting ORA-04031

database-tuningoracleoracle-12c

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?

Best Answer

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

Related Question