Re: Oracle 10g "Ghost" SID Eating up CPU

From: David Fitzjarrell <>
Date: Wed, 2 Jun 2010 04:27:52 -0700 (PDT)
Message-ID: <>

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 <>
To: oracle Freelists <>
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 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-

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.
"Go away before I replace you with a very small and efficient shell script..."


Received on Wed Jun 02 2010 - 06:27:52 CDT

Original text of this message