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 <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
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: [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.com
--
Author: Khedr, Waleed
INET: [EMAIL PROTECTED]
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: [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).
