Oracle-L: SNP0...SNPx Memory Hogs

From: Jack C. Applewhite <japplewhite_at_inetprofit.com>
Date: Wed, 27 Feb 2002 13:20:07 -0800
Message-ID: <F001.0041A4AE.20020227132007@fatcity.com>

8.1.7.2.5 under Win2k Server

I just discovered that my four SNP processes were sitting around, doing no work, taking up about 500MB of RAM. Is this normal?

In Task Manager, the Oracle process was showing to be using about 1.4GB. After I issued "Alter System Set Job_Queue_Processes=0" it shrank to about 900MB. I then issued "Alter System Set Job_Queue_Processes=4", but the Mem Usage stayed the same.

None of those SNP processes had done any work (i.e., run any jobs) for about 10 hours. I would have expected the SNP processes to release memory when their jobs finish.

I've been trying to figure out what processes were chewing up RAM, ramping up over the course of 3 or 4 days. Some Java processes that we run were found to be hogs, but after I got the developers to disconnect/reconnect occasionally, those sessions were OK.

I finally found the culprits when I was checking sessions' "session pga memory". A couple of sessions were using about 200MB each. I checked to see what SQL they'd been running and found it to be dbms_ijob calls, which lead me to suspect the SNP processes - alas, Win2k doesn't let you look at individual Oracle processes like UNIX does.

Anyway, I can't find any mention anywhere that dormant SNP processes can be memory hogs. Am I missing something? For now I'll set Job_Queue_Processes to 0 and back to 4 right after the heavy work each night.

Any init parameters I'm missing? ...other suggestions?

Thanks.

Jack

Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
japplewhite_at_inetprofit.com
(512)327-9068

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: japplewhite_at_inetprofit.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may


also send the HELP command for other information (like subscribing).

Received on Wed Feb 27 2002 - 15:20:07 CST