JDBC Application Querying LOB Expressions may lead to High Consumption Of PGA Memory (Doc ID 1346044.1)

Last updated on MARCH 08, 2017

Applies to:

JDBC - Version 11.2.0.2.0 and later
Information in this document applies to any platform.

Symptoms

A JDBC application performs a query of type :


SELECT XMLSERIALIZE(content x.OBJECT_VALUE as clob no indent) FROM ...



  A steadily increasing consumption of PGA is noticed on the database server as the rows are being retrieved by the JDBC application.

The following query is used on the server-side to monitor the increase in PGA memory:

select p.pid,s.sid,spid,s.program,s.status,
round(PGA_USED_MEM/(1024*1024)) as PGA_USED_MEM,
round(PGA_ALLOC_MEM/(1024*1024)) as PGA_ALLOC_MEM,
round(PGA_FREEABLE_MEM/(1024*1024)) as PGA_FREEABLE_MEM,
round(PGA_MAX_MEM/(1024*1024)) AS PGA_MAX_MEM
from v$process p, v$session s where s.paddr = p.addr(+)
--and p.pga_alloc_mem!= (select max(pga_alloc_mem) from v$process where program not like '%LGWR%')
and s.username='SCOTT'
and s.sid not in (select sid from v$mystat)
order by p.pga_alloc_mem;


example of results monitoring the JDBC query running in endless loop and running the PGA monitoring SQL every few minutes:

PID SID SPID
---------- ---------- ------------------------
PROGRAM STATUS PGA_USED_MEM
------------------------------------------------ -------- ------------
PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
------------- ---------------- -----------

26 130 16176
JDBC Thin Client INACTIVE 11
12 0 12



26 130 16176
JDBC Thin Client INACTIVE 13
15 0 15


26 130 16176
JDBC Thin Client INACTIVE 15
16 0 16


26 130 16176
JDBC Thin Client INACTIVE 38
39 0 39

26 130 16176
JDBC Thin Client INACTIVE 67
68 0 68

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