Memory Leak When Selecting From A Table With A VPD Defined. (Doc ID 1945845.1)

Last updated on NOVEMBER 10, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.

Symptoms

On : 11.2.0.4 version, Database Security

ACTUAL BEHAVIOR
---------------
The SQL statement with the view/table CS_CHARPENTIER_ALL_VL will run for several hours, until the PGA reaches between 4-12Gb.

EXPECTED BEHAVIOR
-----------------------
To not have the PGA To increase between 4-12 Gb

STEPS
-----------------------
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;


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