When setting audit policy ACTIONS ALL, PGA size is increased continuously and ORA-4036 is caused

(Doc ID 2355260.1)

Last updated on APRIL 09, 2018

Applies to:

Oracle Database - Standard Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.

Symptoms

When setting audit policy ACTIONS ALL, PGA size is increased continuously and ORA-4036 is caused.

After setting auditing policy (ex: ACTIONS ALL) to user, pga size is increased according to executions of sql from this user.

When this is caused, the size of "alloc lob locator" in "koh-kghu sessi" is increased continuously.

The detail can be confirmed increased heap from PGA heapdump or v$process_memory_detail.

* please see Doc ID 822527.1 about v$process_memory_detail

How To Find Where The Memory Is Growing For A Process (Doc ID 822527.1)

ex)

SQL> SELECT tab2.pid,tab2.category, tab2.name, tab2.heap_name, tab1.bytes q1,
            tab2.bytes q2, tab2.bytes-tab1.bytes diff
       FROM tab1, tab2
      WHERE tab1.pid = tab2.pid
        AND tab1.category = tab2.category
        AND tab1.name = tab2.name
        AND tab1.heap_name = tab2.heap_name
        AND tab1.bytes <> tab2.bytes
       ORDER BY 7 DESC;

PID        CATEGORY  NAME               HEAP_NAME               Q1         Q2       DIFF
---------- --------- ------------------ --------------- ---------- ---------- ----------
58         Other     alloc lob locator  koh-kghu sessi  1351452232 1360987784    9535552
58         Other     free memory        kti call subhe       20504      41008      20504
58         Other     free memory        top call heap        30512       9928     -20584

 

 

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms