How to troubleshoot high pga memory consumption

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem:    How to troubleshoot high pga memory consumption

Solution:

If one of the processes started consuming high pga, then execute the following steps during that time and provide the results.

execute the following set of queries once again, when the memory used by Oracle is very high

select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') from dual;
select sum(pga_used_mem)/(1024*1024),sum(pga_alloc_mem)/(1024*1024) from v$process;
select status,count(*) from v$session group by status;
select count(*) from v$process;
select max(pga_used_mem) from v$process;
select username,serial#,sid from v$session where paddr=(select addr from v$process where pga_used_mem=(select max(pga_used_mem) from v$process));

set pages500 lines110 trims on
clear col
col name format a30
col username format a20
break on username nodup skip 1

select username,serial#,sid from v$session where paddr=(select addr from v$process where pga_used_mem=(select max(pga_used_mem) from v$process));

select vses.username||':'||vsst.sid||','||vses.serial# username, vstt.name, max(vsst.value) value from v$sesstat vsst, v$statname vstt, v$session vses where vstt.statistic# = vsst.statistic# and vsst.sid = vses.sid and vstt.name in ('session pga memory','session pga memory max','session uga
memory','session uga memory max', 'session cursor cache count', 'session cursor cache hits','session stored procedure space', 'opened cursors current','opened cursors cumulative') and vses.username is not null group by vses.username, vsst.sid, vses.serial#, vstt.name order by vses.username, vsst.sid, vses.serial#, vstt.name;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For example:

PATROL:307,17 opened cursors current 9
session cursor cache count 100
session cursor cache hits 906284
session pga memory 2125192
session stored procedure space 0
session uga memory max 11289080

If above output is the highest memory using session, then we need to take a heapdump for this session.
In "PATROL:307,17" PATROL is the sesion user, and 307 is the SID and 17 is the serial#. Based onthe above values identify the PID and SPID using the following SQL.

select pid,spid from v$process p, v$session s where s.sid=307 and s.serial#=17 and p.ADDR=s.paddr; 

PID SPID
---------- ------------
14 17874

Using the above output you can take the heapdump using any of the following command:
-- By using Oracle processid 14
SQL> ORADEBUG SETORAPID 14
---- OR By using OS processid 17874
SQL> ORADEBUG SETOSPID 17874
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


SQL> oradebug setorapid <Orapid from query above or p.pid from first based on your query output>
SQL> oradebug unlimit
SQL> oradebug dump errorstack 10
SQL> oradebug dump heapdump 536870941
SQL> oradebug tracefile_name (shows the path and filename information)
SQL> oradebug close_trace (closes the trace file)
SQL>exit

Get the query outputs and heapdump trace file for further investigation.

Get Oracle Certifications for all Exams
Free Online Exams.com