Re: ORA-04031 and EM Express 12c

Hi Alessandro,

Here's what's likely happening:

1. EM Express uses XDB that uses shared servers.
2. Shared servers use large pool for session and cursor duration memory allocations.
3. Judging from the error message and allocation size your shared server session is trying to allocate 28 MB for *kxttIICDT* reason from *large* *pool *(not shared pool):

ORA-04031: unable to allocate *28049976* bytes of shared memory ("*large pool*","unknown object","*kxttIICDT*: 4","kxttAssignMemory: 10")

4) *KXTT* is about In-Memory Cursor-Duration Temporary tables:

SQL> _at_oddc <https://github.com/tanelpoder/tpt-oracle/blob/master/oddc.sql> kxtt
ORADEBUG DOC COMPONENT | grep -i kxtt

    ICDT_Exec                  In Memory CDT Execution (qes3t, kxtt)
    EXECUTE_TEMP_TABLE         Execute Temp Table  (kxtt)

The in-memory here does not mean Oracle's In-Memory Option, but just the fact that from 12.2 onwards temp table transformation can keep the materialized temporary data in session memory (as opposed to always writing it to TEMP).

5) And since in your case, with shared servers, the cursor-duration "private memory" is actually kept in large pool, you have this large allocation.

6) Try re-running this with *_in_memory_cdt = OFF* (you could create a logon trigger that only sets this for XDB/EM expression connections)

Incidentally I'll be talking about this stuff at my Advanced Oracle Troubleshooting training next month ;-)

--
Tanel Poder
https://blog.tanelpoder.com/seminar/




On Mon, May 13, 2019 at 10:35 AM Alessandro Vercelli <
dmarc-noreply_at_freelists.org> wrote:



> Hi All,


> after a long time (about 10 years), I started again to study Oracle DBMS

> so I tried 12c on a small KVM/libvirtd RHEL 6.4 (about 8 Gb RAM)

>

> The first problem I noticed after installation is a strange ORA-04031, I

> say "strange" because it pops up only when logged into EM Express webgui,

> here some details from alert and trace files:

>

> - alert file -

> Errors in file

> /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_s001_7290.trc

> (incident=31740):

> ORA-04031: unable to allocate 28049976 bytes of shared memory ("large

> pool","unknown object","kxttIICDT: 4","kxttAssignMemory: 10")

>

> - trace file -

> Trace file /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_s001_7290.trc

> Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit

> Production

> Build label:    RDBMS_12.2.0.1.0_LINUX.X64_170125

> ORACLE_HOME:    /u01/app/oracle/product/12.2.0/dbhome_1

> System name:    Linux

> Node name:      ora12db.network.local

> Release:        2.6.32-358.el6.x86_64

> Version:        #1 SMP Tue Jan 29 11:47:41 EST 2013

> Machine:        x86_64

> Instance name: ORCL

> Redo thread mounted by this instance: 1

> Oracle process number: 63

> Unix process pid: 7290, image: oracle_at_ora12db.network.local (S001)

>

>

>


--
http://www.freelists.org/webpage/oracle-l