OracleTextSearch PGA Size Grows Uncontrollably When Using Custom AUTO_LEXER Configuration (Doc ID 1297910.1)

Last updated on MAY 18, 2017

Applies to:

Oracle WebCenter Content - Version 10.1.3.3.0 and later
Information in this document applies to any platform.

Symptoms

When performing a (quick) search on a UCM instance that is configured using OracleTextSearch with the AUTO_LEXER on an Oracle RDBMS 11.2.0.1, the Oracle database process memory increases to several hundreds of megabytes and does not decrease. Subsequent searches will continue to allocate more memory on the database host until all available memory is consumed.

On windows, you may notice that the oracle.exe grows to consume all available memory and grow to several gigabytes.

After running the search, the following SQL statement shows that the "total PGA inuse" and "total PGA allocated" increases significantly, while only a limited amount of PGA memory is marked "freeable":

SQL> select * from v$pgastat;

NAME VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter 209715200 bytes
aggregate PGA auto target 13107200 bytes
global memory bound 41943040 bytes
total PGA inuse 3810291712 bytes
total PGA allocated 3857059840 bytes
maximum PGA allocated 3857059840 bytes
total freeable PGA memory 32440320 bytes
process count 49
max processes count 56
PGA memory freed back to OS 50.724.864 bytes
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 15223808 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 1992704 bytes
....


The following SQL will show memory usage per session. You will notice that with every search, the used, allocated and maximum will increase for one particular session while freeable memory remains low.

COLUMN alme HEADING "Allocated MB" FORMAT 99999D9
COLUMN usme HEADING "Used MB" FORMAT 99999D9
COLUMN frme HEADING "Freeable MB" FORMAT 99999D9
COLUMN mame HEADING "Max MB" FORMAT 99999D9
COLUMN username FORMAT a20
COLUMN program FORMAT a22
COLUMN sid FORMAT a5
COLUMN spid FORMAT a8
SET LINESIZE 300
SELECT s.username, SUBSTR(s.sid,1,5) sid, p.spid, logon_time,
SUBSTR(s.program,1,22) program , s.process pid_remote,
ROUND(pga_used_mem/1024/1024) usme,
ROUND(pga_alloc_mem/1024/1024) alme,
ROUND(pga_freeable_mem/1024/1024) frme,
ROUND(pga_max_mem/1024/1024) mame
FROM v$session s,v$process p
WHERE p.addr=s.paddr
ORDER BY pga_max_mem desc,logon_time ;


NOTE: If you are not using the AUTO_LEXER (or are on the 11.2.0.2 patch set and still observe excessive memory allocation) then please take a look at the issue described in <Document 1065852.1> "OracleTextSearch Database's PGA Size Grows Uncontrollably".

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