@@header

/*
*
*  Author    : Vishal Gupta
*  Purpose   : Display PGA Memory Details
*  Parameter : 1 - PID (From gv$process.pid )
*              2 - Instance Number (Defaults to 1)
*
*  Reference : Note 822527.1 - How To Find Where The Memory Is Growing For A Process
*              Note 199746.1 - How to Resolve ORA-4030 Errors on UNIX
*              
*
*  Notes     : To populate v$pga_memory_detail view, execute either of following command
*                using pid=1 enables/disables this event for all processes.            
*
*                - alter session set events 'immediate trace name PGA_DETAIL_GET <pid> ' ;
*                  or
*                - ORADEBUG setorapid  <pid>
*                  ORADEBUG DUMP PGA_DETAIL_GET <pid>
*
*              To dump pga memory details to trace file, execute either of the following command
*                - alter session set events 'immediate trace name PGA_DETAIL_DUMP <pid> ' ;
*                  or
*                - ORADEBUG setorapid  <pid>
*                  ORADEBUG DUMP PGA_DETAIL_DUMP    <pid>
*
*              To Cancel or turn off the event
*                - alter session set events 'immediate trace name PGA_DETAIL_CANCEL <pid> ' ;
*                  or
*                - ORADEBUG setorapid  <pid>
*                  ORADEBUG DUMP PGA_DETAIL_CANCEL <pid>
*           
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  27-Jun-12  Vishal Gupta  Created
*  
*/


/*
select * from v$process;
select * from v$process_group;
select * from v$process_memory where pid = 2; 
select * from v$process_memory_detail where pid = 23 ;
select * from v$process_memory_detail_prog;
select * from dba_hist_process_mem_summary;
*/

select p.pid
     , p.name
     , LPAD ('>',(level  -1 ) * 4,'|---' ) || p.heap_name
--     , p.heap_descriptor
--     , p.parent_heap_descriptor
     , p.bytes
     , p.category
     , p.allocation_count
from gv$process_memory_detail p
where p.pid like &&PID 
  AND p.inst_id LIKE &&INST_ID 
connect by PRIOR heap_descriptor = parent_heap_descriptor
start with parent_heap_descriptor = '00'
--ORDER BY level,p.parent_heap_descriptor || '.' || p.heap_descriptor
;