PGA Allocation for sql
Hello , I have a question about pga allocation for a pretty simple sql.
ENV: Windows 2016R2 and Oracle 19.17
SELECT COUNT(*) FROM V$OPEN_CURSOR OC, V$SQL S, V$OBJECT_DEPENDENCY OD WHERE OC.SID = :2 AND OC.ADDRESS = S.ADDRESS AND OC.HASH_VALUE = S.HASH_VALUE AND LOWER(S.SQL_TEXT) LIKE :1 AND S.ADDRESS = OD.FROM_ADDRESS AND S.HASH_VALUE = OD.FROM_HASH AND OD.TO_TYPE IN (7, 8, 9, 11);
All internal views, in our application we spawn around 100 scheduler jobs running this sql.
What we see is that some of these jobs allocate 1100M of pga while some allocate 7M.