Memory Leak When Selecting From A Table With A VPD Defined.
Last updated on NOVEMBER 10, 2016
Applies to:Oracle Database - Enterprise Edition - Version 184.108.40.206 and later
Information in this document applies to any platform.
On : 220.127.116.11 version, Database Security
The SQL statement with the view/table CS_CHARPENTIER_ALL_VL will run for several hours, until the PGA reaches between 4-12Gb.
To not have the PGA To increase between 4-12 Gb
The issue can be reproduced at will with the following steps:
1. Create a VPD policy on the view CS_CHARPENTIER_ALL_VL
2. run this query: SELECT INCIDENT_NUMBER , INCIDENT_ID from CS_CHARPENTIER_ALL_VL where INCIDENT_ID = :object_id and rownum = 1;
3. Monitor the PGA memory used by this query using:
select to_char(sysdate, 'DD-MON-YY HH24:MI:SS') date_time , s.sid, name, p.sql_id, round(value / (1024 * 1024)) Mb
from v$statname n, v$sesstat s, v$session p
where n.STATISTIC# = s.STATISTIC# and p.sid = s.sid and p.sql_id = 'a6czar4k99tdm' and name = 'session pga memory' -- 'session%memory%' order by 3 desc;
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