Here is a little experimental script based on a great blog post by Stefan Koehler entitled Trace back to responsible SQL or PL/SQL code for a particular (PGA) memory request by intercepting process with DTrace and one of my old blog post Geeky PL/SQL tracer/profiler : First step
And here is what it does :
TEST ENV : oracle 220.127.116.11/OEL6/UEK4 /PLSQL_CODE_TYPE=INTERPRETED/systemtap
Source PL/SQL program doing huge memory allocation :
Script Output : (smem_detail.sql by Tanel Poder)
Basically this script will help trace back the PL/SQL source line responsible for particular memory allocation (memory leak) by specifying the memory allocation reason.
For more info please read the above blog posts 🙂