Question

Identify - Where the memory growing ?

Answer 1

Identify - Where the memory growing ?

These two views introduced in 10.2.0.4.0 to identify where the memory is growing.

v$process_memory
v$process_memory_detail

Step to identify the memory leak :

Step 1 : Idntify Process( V$session and V$process)
Step 2 : Memory Information (v$process_memory)
Step 3 : Collect Detaild Memory Infor.
Step 4 : Find memory Increase.

COLUMN alme HEADING "Allocated MB" FORMAT 99999D9
COLUMN usme HEADING "Used MB" FORMAT 99999D9
COLUMN frme HEADING "Freeable MB" FORMAT 99999D9
COLUMN mame HEADING "Max MB" FORMAT 99999D9
COLUMN username FORMAT a15
COLUMN program FORMAT a22
COLUMN sid FORMAT a5
COLUMN spid FORMAT a8
SET LINESIZE 300
SELECT s.username, SUBSTR(s.sid,1,5) sid, p.spid, logon_time,
SUBSTR(s.program,1,22) program , s.process pid_remote,
ROUND(pga_used_mem/1024/1024) usme,
ROUND(pga_alloc_mem/1024/1024) alme,
ROUND(pga_freeable_mem/1024/1024) frme,
ROUND(pga_max_mem/1024/1024) mame
FROM v$session s,v$process p
WHERE p.addr=s.paddr
AND p.background IS null
ORDER BY pga_max_mem,logon_time ;

USERNAME SID SPID LOGON_TIM PROGRAM PID_REMOTE Used MB Allocated MB Freeable MB Max MB
--------------- ----- -------- --------- ---------------------- ------------ -------- ------------ ----------- --------
SYS 159 13364 05-OCT-10 sqlplus@rtcsol1 (TNS V 13329 2.0 3.0 .0 3.0
TEST 148 13722 05-OCT-10 JDBC Thin Client 1.0 2.0 1.0 2.0
© 2010 Oracle Corporation – Proprietary and Confidential

USERNAME SID SPID LOGON_TIM PROGRAM PID_REMOTE Used MB Allocated MB Freeable MB Max MB
--------------- ----- -------- --------- ---------------------- ------------ -------- ------------ ----------- --------
SYS 159 13364 05-OCT-10 sqlplus@rtcsol1 (TNS V 13329 2.0 3.0 .0 3.0
TEST 148 13722 05-OCT-10 JDBC Thin Client 8.0 9.0 .0 9.0

Step 2 : Memory Information (v$process_memory)

COLUMN category HEADING "Category"
COLUMN allocated HEADING "Allocated bytes"
COLUMN used HEADING "Used bytes"
COLUMN max_allocated HEADING "Max allocated bytes"
SELECT pid, category, allocated, used, max_allocated
FROM v$process_memory
WHERE pid = (SELECT pid
FROM v$process
WHERE addr= (select paddr
FROM v$session
WHERE sid = 148));

Step 3 : Collect Detaild Memory Infor.

Step a:
alter session set events'immediate trace name PGA_DETAIL_GET level 16‘;
Step b:
CREATE TABLE tab1 AS
SELECT category, name, heap_name, bytes, allocation_count,
heap_descriptor, parent_heap_descriptor
FROM v$process_memory_detail
WHERE category = 'Other';

Step c:
alter session set events'immediate trace name PGA_DETAIL_GET level 16‘;

Step d:
CREATE TABLE tab2 AS
SELECT category, name, heap_name, bytes, allocation_count,
heap_descriptor, parent_heap_descriptor
FROM v$process_memory_detail
WHERE category = 'Other';

COLUMN category HEADING "Category"
COLUMN name HEADING "Name"
COLUMN heap_name HEADING "Heap name"
COLUMN q1 HEADING "Memory 1st" Format 999,999,999,999
COLUMN q2 HEADING "Memory 2nd" Format 999,999,999,999
COLUMN diff HEADING "Difference" Format S999,999,999,999
SET LINES 150
SELECT tab2.category, tab2.name, tab2.heap_name, tab1.bytes q1, tab2.bytes q2, tab2.bytes-tab1.bytes diff
FROM tab1, tab2
WHERE tab1.category = tab2.category
AND tab1.name = tab2.name
AND tab1.heap_name = tab2.heap_name
AND tab1.bytes <> tab2.bytes
ORDER BY 6 DESC;