68 lines
2.2 KiB
MySQL
68 lines
2.2 KiB
MySQL
@@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
|
|
;
|
|
|