Re: Oracle 10g "Ghost" SID Eating up CPU
From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Wed, 2 Jun 2010 04:27:52 -0700 (PDT)
Message-ID: <535563.84969.qm_at_web65406.mail.ac4.yahoo.com>
Date: Wed, 2 Jun 2010 04:27:52 -0700 (PDT)
Message-ID: <535563.84969.qm_at_web65406.mail.ac4.yahoo.com>
Unfortunately cycling the database is your only option since there is no parent session nor any O/S process to kill. David Fitzjarrell ________________________________ From: Kellyn Pedersen <kjped1313_at_yahoo.com> To: oracle Freelists <oracle-l_at_freelists.org> Sent: Tue, June 1, 2010 2:25:39 PM Subject: Oracle 10g "Ghost" SID Eating up CPU The last two weekends, due to some new code, my main datawarehouse/OLTP, (yes, I know it's an oxy-moron and it's 10.2.0.4 on Linux, 64bit with a number of one off patches for parallel bugs...) has been overwhelmed by 32 CTAS concurrently running, all requesting 4 parallel on large table selects. Parallel was downgraded a number of times 75% or more during this step in their package. This is the second time I've come back in after the occurrance to find one parallel coordinator session running- on it's own, no other producers/consumers, no parent SID, just this one process eating up CPU- SID SERIAL# STATUS OSUSER PROCESS MACHINE PROGRAM ROW_WAIT_OBJ# PDDL_STATUS PQ_STATUS EVENT P1TEXT SECONDS_IN_WAIT 540 20564 ACTIVE sdev_user 31988 appmachine prodmachine (P039) 2815532 ENABLED ENABLED PX Deq: Execution sleeptime/senderid 141170 If you try to search for the OS Process, (31988), it doesn't exist, the SQL_ID is unknown but I can see it was sitting on the primary key for a particular table, (although different one than the last time this ghost was present last week!) What I believe happened is that the parallel query died, but the coorindator is still out there. ERROR at line 1: ORA-12805: parallel query server died unexpectedly I found a couple of these errors, (12805) in trace files from the times that parallel was downgraded. The process doesn't exist on the app server, I don't have an OS PID to kill and I can't kill it at the Oracle session level, (ORA-00030: User session ID does not exist.) Last time we had a maintenance window and solved the problem quickly with a database cycle, but here I am again- HOW DO I GET RID of this thing!?!? It's starting to eat up CPU and won't die... :( SID PID Coordinator SPID Group Set Degree Req Degree Wait Event 540 134 540 31988 PX Deq: Execution Msg Anybody have any ideas? I actually have two P039 processes in my database right now! This cannot be good! :( Kellyn Pedersen Sr. Database Administrator I-Behavior Inc. http://www.linkedin.com/in/kellynpedersen www.dbakevlar.blogspot.com "Go away before I replace you with a very small and efficient shell script..."
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jun 02 2010 - 06:27:52 CDT