Oracle-L: PS enqueue waits

From: Olga Gurevich <gurelei_at_yahoo.com>
Date: Wed, 08 Oct 2003 10:59:32 -0800
Message-ID: <F001.005D26AE.20031008105932@fatcity.com>

Hi. I have a query that seems to be waiting for a PS enqueu for about 2 days. When querying the v$session_wait, I'm getting the following:

 SID EVENT                      S-I-W    T         P1 
    P2         P3
----- ------------------------- ------ ---- ----------
------- ----------
   48 enqueue                   ######    0 1347616774
      1         15
   15 direct path write              0   -1        304
 185925         16
   67 direct path write              3   -1        304
 326053         16
   23 direct path write              3   -1        304
 171333         16
   64 direct path write              3   -1        304
 159829         16

(sids 15,67,23 and 64 are parallel slaves of the SID
48).

I also see that this is a PS type enqueue via the following:

  1 select sid,
  2 chr(bitand(p1,-16777216)/16777215)|| chr(bitand(p1,16711680)/65535) enq,
  3 decode(
chr(bitand(p1,-16777216)/16777215)||chr(bitand(p1,16711680)/65535), 'TX','Transaction accessing a rbd', 'ST','Space mgt activity', 'SS','Sort segment activity', 'TM','DML ACtivity', 'UL','user defined',
chr(bitand(p1,-16777216)/16777215)||chr(bitand(p1,16711680)/65535)) edes,
  4
decode(bitand(p1,65535),1,'NULL',2,'SUB_SHARE',3,'SUB-exclusive',4,'share',5,'share or subexclusive',6,'exclusive','other') md,   5 p2,
  6 p3
  7 from gv$session_wait
  8* where event='enqueue'

 sid enq  enqueue name                   lock mode    
  P2         P3
----- ---- ------------------------------ ----------
------- ----------
   48 PS   PS                             exclusive   
    1         15

I'm not sure what P2 and P3 are referring to in this case. I have tried to kill the session, but it didn't go away and the parallel slaves are still holding a lot of space in the TEMP tablespace. What should I do now? any sugestions?

thanks

Do you Yahoo!?
The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Olga Gurevich
  INET: gurelei_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Oct 08 2003 - 13:59:32 CDT

PS enqueue waits - need help