My Oracle Support Banner

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

Last updated on APRIL 26, 2018

Applies to:

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


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:

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:

---------- ---------- ------------------------
------------------------------------------------ -------- ------------
------------- ---------------- -----------

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


To view full details, 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 a vibrant support community of peers and Oracle experts.