Large PGA/UGA Utilization While Running Queries Against XML Fails ORA-4036 (Doc ID 1917615.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Standard Edition - Version 11.2.0.3 to 12.1.0.1 [Release 11.2 to 12.1]
Information in this document applies to any platform.

Symptoms

 Large PGA/UGA utilization while running queries against XML, give error ORA-4036 or ORA-4030.

Oracle 10.2.0.5:

USERNAME         Used MB Allocated MB Freeable MB   Max MB
--------------- -------- ------------ ----------- --------
TEST                 2.0          3.0         1.0      9.0



Oracle 11.2.0.3:

USERNAME         Used MB Allocated MB Freeable MB   Max MB
--------------- -------- ------------ ----------- --------
TEST                 320          326           1      326



Oracle 12.1.0.1:

USERNAME         Used MB Allocated MB Freeable MB   Max MB
--------------- -------- ------------ ----------- --------
TEST               316.0        330.0        10.0    330.0



Following the steps in <Note:822527.1>, How To Find Where The Memory Is Growing For A Process, was the highest memory allocation
in "kxs-heap-w" subheap for Oracle versions 11.2.0.3 and 12.1.0.1:

Category        Name                       Heap name             Memory 1st       Memory 2nd       Difference
--------------- -------------------------- --------------- ---------------- ---------------- ----------------
SQL             permanent memory           kxs-heap-w         1,371,334,232    2,698,213,800   +1,326,879,568

 

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