One of our programmers made a new PL/SQL program that runs for 10/15 minutes and uses up all the db PGA memory.
When he runs it, I can watch in V$PGASTAT and see the numbers go up to the limit.
What type of items in his code should I look for? Expensive SQL? Arrays holding a lot of data? What are the general PL/SQL items that use up PGA?
Is there another database view that is useful in seeing WHAT exact sql/something-else that is using all the PGA.
19c db
from V$PGASTA:
aggregate PGA target parameter 0
aggregate PGA auto target 0
global memory bound 1024
total PGA inuse 21813
total PGA allocated 23512
maximum PGA allocated 26852
total freeable PGA memory 899
parameters:
pga_aggregate_limit big integer 0
pga_aggregate_target big integer 14976M