ORACLE PGA IS CONSUMED MORE WITH XML TYPES

(Doc ID 970847.1)

Last updated on FEBRUARY 03, 2010

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.1 - Release: 11.1 to 11.2
Information in this document applies to any platform.

Symptoms

Customer is using a c++ application that is consuming too much PGA memory

About the application:
This is a C++ application running on Redhat Enterprise Linux. Along with our existing legacy data we are using sql statements to perform DML on our table which has a BinaryXML column. The table is defined as :

create table dps_testxml(id number, otp_spec xmltype) xmltype otp_spec store as securefile binary xml;

We created an index on the table using:
ALTER TABLE DPS_TESTXML
add CONSTRAINT id XML_pk PRIMARY KEY (ID);

We are going with the approach of passing raw XML in our SQL query and the database encodes the XML to BinaryXML. We are essentially using a char* with a length parameter to perform our operations viz read/write/update.
The sqls for read/write/update look like:

select x.otp_spec.getClobval() from odm_dev.dps_testxml x where x.id = :id

update odm_dev.dps_testxml set otp_spec = :xml where id = :id

insert into odm_dev.dps_testxml(id, otp_spec) values :id, :xml)

Memory is consumed under the following heap:

HEAP DUMP heap name="koh dur heap d"

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