Database Memory & 4030/4031 (MOSC)

MOSC Banner

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.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center