Nope, there is no bitmap index ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, December 24, 2002 3:48 PM
> Bitmap indexes? > > joe > > > Shaleen wrote: > > >Jonathan, > > > >What do you make out of following deadlock graph. This is happenning when 2 > >instance of same batch process are running. We are absolutely certain that > >these batch processes are not working on same set of records (although > >records can be in same block). object f9d5 is wcu_po_line table. I am unable > >to understand why the update statements are requesting SSX lock on the > >table. This is not a case of primary/forign key issue with a missing index > >in child table where primary key is change in master table because master > >table is not being updated. > > > >Thanks > >Shaleen > > > >Deadlock graph: > ---------Blocker(s)-------- ---------Waiter(s)------ > >--- > >Resource Name process session holds waits process session holds > >waits > >TM-0000f9d5-00000000 390 503 SX SSX 290 597 SX > >SSX > >TM-0000f9d5-00000000 290 597 SX SSX 390 503 SX > >SSX > >session 503: DID 0001-0186-00000002 session 597: DID 0001-0122-00000002 > >session 597: DID 0001-0122-00000002 session 503: DID 0001-0186-00000002 > >Rows waited on: > >Session 597: obj - rowid = 000098A5 - AAAAAAADFAAAGCsAAA > > (dictionary objn - 39077, file - 197, block - 24748, slot - 0) > >Session 503: no row > >SQL statements executed by the waiting sessions: > >Session 597: > >UPDATE wcu_po_line > > SET po_no = :b21, > > po_line = :b20, > > item_price = :b19, > > po_qty = :b18, > > invoice_shipped_qty = 0, --invoice_shipped_qty > > distributor_item_no = :b17, > > current_status = :b16, > > created_dtm = SYSDATE, > > status_change_dtm = SYSDATE, > > --created_dtm > > return_id = NULL, --return_id_in, > > return_line_no = NULL, --return_line_no_in, > > min_qty = :b15, > > wrap_code = :b14, > > invoice_id = :b13, > > gift_wrap_UPC = :b12, > > gift_wrap_price = :b11, > > wrap_to_label = :b10, > > wrap_from_label = :b9, > > item_cost = nvl(:b7,:b6), > > xml_po_line = :b8, > > wmc_item_cost = nvl(:b7,:b6), > > distributor_id = :b5, > > po_type = :b4 > > WHERE po_no = :b3 > > AND co_order_no = :b2 > > AND co_line_no = :b1 > >=================================================== > >----- Original Message ----- > >To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > >Sent: Friday, December 20, 2002 3:33 PM > > > > > > > > > >>There is a deadlock here - but I confused the > >>issue by making complete garbage of the last > >>phrase. Instead of: > >> > >> > >> > >>>>both X and Y might end up waiting for A. > >>>> > >>>> > >>I should have said > >> > >> > >> > >>>>both Y and Z might end up waiting for X > >>>> > >>>> > >> (which is when you won't get the deadlock) > >> > >>The critical point comes in the previous > >>paragraph though: > >> > >> > >> > >>>>With a little luck, Y will be waiting for Z > >>>>and Z will be waiting for Y (i.e. DEADLOCK) > >>>> > >>>> > >>For Oracle 9, I have only introduced the X > >>session to take out one ITL slot from each > >>of the two blocks because Oracle 9 forces > >>a minimum value of 2 entries per ITL. > >> > >>This really is a deadlock - which will show a > >>deadlock graph with holders in mode 6 and > >>waiters in mode 4. (X and S if I've got the > >>letters right - personally I prefer numbers). > >> > >> > >>Regards > >> > >>Jonathan Lewis > >>http://www.jlcomp.demon.co.uk > >> > >>Coming soon a new one-day tutorial: > >>Cost Based Optimisation > >>(see http://www.jlcomp.demon.co.uk/tutorial.html ) > >> > >>Next Seminar dates: > >>(see http://www.jlcomp.demon.co.uk/seminar.html ) > >> > >>____England______January 21/23 > >> > >> > >>The Co-operative Oracle Users' FAQ > >>http://www.jlcomp.demon.co.uk/faq/ind_faq.html > >> > >> > >> > >> > >> > >>-----Original Message----- > >>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > >>Date: 20 December 2002 22:45 > >> > >> > >> > >> > >>>Jonathon, > >>> > >>>This produces ITL waits for sessions Y and Z; but this is not > >>> > >>> > >>deadlock. The > >> > >> > >>>deadlock occurs due to a situation where the Session 1 waits for > >>> > >>> > >>something > >> > >> > >>>to finish in Session 2, which in turn waits for Session 1 AND, this > >>> > >>> > >>is > >> > >> > >>>important, Oracle detects it and kills one of them, rolling back the > >>>changes, making a deadlock detected error. Is this not the true error > >>>message that occured in the original thread? > >>> > >>>In your example, sessions Y and Z will wait indefinitely until X > >>> > >>> > >>commits or > >> > >> > >>>rolls back. This is not going to be detected by Oracle nor killed by > >>> > >>> > >>it. So > >> > >> > >>>you wouldn't see a message DEADLOCK DETECTED in alert log. Therefore > >>> > >>> > >>setting > >> > >> > >>>INITRANS higher is not going to help at all. Rather the application > >>> > >>> > >>logic > >> > >> > >>>should be checked to remove a real locking conflict. > >>> > >>>Am I correct, or am I missing something here? > >>> > >>>Arup Nanda > >>> > >>> > >>> > >>-- > >>Please see the official ORACLE-L FAQ: http://www.orafaq.net > >>-- > >>Author: Jonathan Lewis > >> INET: [EMAIL PROTECTED] > >> > >>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: [EMAIL PROTECTED] (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). > >> > >> > >> > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Joe Testa > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: garg INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).