Re: Oracle process consumes all paging space on AIX

From: <bpetry_at_medplus.com>
Date: 10 Mar 2005 13:35:31 -0800
Message-ID: <1110490531.648690.83890@g14g2000cwa.googlegroups.com>

Support would like information listed below. The problem is this is a production box and this error comes on quickly and brings the box to its knees quickly. Customer is getting heat from users and not wanting to risk further downtime at this time.

Here's what support has asked us to obtain...

1. Identify the problem session
2. Run the following to demonstrate the memory growth:

select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') from dual; select value, n.name|| '('||s.statistic#||')' from v$sesstat s , v$statname n
where s.statistic# = n.statistic#
and n.name like '%ga memory%'
and sid= <sid of problem session>;

3. Get heapdump of the process leaking memory.
$ sqlplus

sql> connect internal
sql> oradebug setospid <pid>
sql> oradebug unlimit
sql> oradebug dump heapdump 5 <--do this 3 or more times (1-2 minutes

apart) while
process SZ increases

4. Get errorstack dumps while heapdump in step#3 are being taken. At least 1 errorstack dump should also be taken on the offending process to
determine what it is running:

$ sqlplus

sql> connect internal
sql> oradebug setospid <pid>
sql> oradebug unlimit
sql> oradebug dump errorstack 3 <-- do this 3x (1-2 minutes apart)

while heapdump above
is also being taken. Received on Thu Mar 10 2005 - 15:35:31 CST