RE: No Rows Deadlock and PCTFREE, INITRANS on 8.1.7.2

My guess there is no triggers or referential constraints since it's a Data Warehouse.

More details will help,

Regards

Waleed

-----Original Message-----
Sent: Thursday, April 25, 2002 12:53 PM
To: Multiple recipients of list ORACLE-L

Is it a TX mode 4 (Share) deadlock ?

Can you post a section of the deadlock graph from the trace file.

If you really have only two streams running, then you need only set initrans to 2 at a cost of (typically) 24 bytes per block to guarantee that the problem goes away.

There are several other reasons for deadlocks - in particular issues relating to parent/child conflicts. e.g.

    session 1 deletes child for parent A     session 2 inserts child for parent A     session 1 tries to delete parent A

At this point session 1 waits for session 2 to commit - showing TX/4 and no rows

If your batches are sufficiently tangled that the same sequence could have happened
in the opposite order (possibly through triggers) on other parent/child rows, then you'll get a TX/4 deadlock showing no rows.

An alternative thought - did you upgrade by doing an export/import ? Going from 8.0 to 8.1 you may find that some of the 8.0 indexes were deemed redundant by the 8.1 import, so you may have invisibly dropped an index supporting a foreign key.

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to 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 <ORACLE-L_at_fatcity.com> Date: 25 April 2002 16:11

|
|We are testing an upgrade of our warehouse from 8.0.4 to 8.1.7.2.
|
|While testing our nightly load job that runs in two simultaneous
|streams (ascending and descending), we have been getting deadlock
|errors. The trace file shows the the rows waited on are no rows.
|
|In response to a TAR I opened with Oracle, Oracle responded that
|the application needs to be rewritten so that there is only a single

|stream. However our developers aren't inclined to do so because
|our nightly window isn't long enough for a single job and because
|this application worked fine as is on 8.0.4.
|
|Our developers have discovered an old article (perhaps from 7.3.4
times)
|by Roger Snowdent "The Deadly Embrace (Oracle Locking Strategies)"
|www.dbdomain.com/120197.htm
|that indicates this deadlock error with no rows is an indication of
an
|"insidious table".
|
|The article states that the the INITRANS and PCTFREE parameters
|may be set too low for the table in use.
|
|Has anyone encountered this deadlock with norows indicated error
|before? If yes, how did you resolve it?
|
|In our current 8.0.4 datawarehouse database, this partitioned table
|has PCTFREE of 10 and INI_TRANS of 1. The block size is
|8k, we are using Sun Solaris 2.6 and Veritas Volume Manager.
|As I said, we are preparing to upgrade this to 8.1.7.
|
|What would be an appropriate setting for PCTFREE and INI_TRANS
|for this large warehouse partitioned table in these circumstances?
|
|What are our options if we want to change the PCTFREE and INI_TRANS
|for new data that gets loaded? Can we change PCTFREE and INI_TRANS
|and then just keep loading more data into an existing partition?
Can
|we change these values in new partitions and leave them the same in
|existing partitions? What would be the drawbacks of having some
|partitions
|with one value for these storage parameters and other partitions with
|different
|values?
|
|Also, the developers want to know if there is any utility that could
be run
|to
|determine whether other tables might be susceptible to this deadlock
|condition.
|Something like dbverify or analyze with some validation option?
|
|Thanks in advance for your feedback.
|
|Cherie Machler
|Oracle DBA
|Gelco Information Network

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).