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

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-l