Andrew Fraser DBA

Pre 9202/9014: Advanced queuing runs away with server memory.

Using the below memory listing script, I found two databases with large PGAs. That was strange since pga_aggregate_target was unset, sort_area_size was just 64k, and there were very few sessions. Checking PGA at session level showed the QMN sessions had high memory usage (over 200mb each). Which pointed to bug 2227360 “QMN process leaking memory”

It is fixed in 9.0.1.4/9.2.0.2 and above, but I was on 9.2.0.1. So the workaround is to kill the QMN (=AQ) sessions. Sounds drastic, but oracle restarts the processes apparently cleanly, with just a message in the alert log like:

Restarting dead background process QMN0
QMN0 started with pid=9

And that is the memory then freed up – for a while at least, you have to be prepared to kill those sessions every so often as the memory leaks build up, if, that is, you can’t patch up.

I used the below script to kill the sessions. I didn’t need to use the drastic “kill -9” on this occasion, but sometimes sessions take forever to go without that.

select 'alter system kill session '''||s.sid||','||s.serial#||''' ;',
'kill -9 '||p.spid
from v$session s, v$process p
where s.paddr = p.addr
and s.program like '%QMN%'
/

Reference: Metalink Note 233101.1.