Stephane,
No, this is not an IOT table.
This is a TX type of lock on insert.
Thanks for your reply.
Cherie
"Stephane
Faroult" To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
<sfaroult@oriol cc:
ecorp.com> Subject: RE: No Rows Deadlock and
PCTFREE, INITRANS on 8.1.7.2
Sent by:
[EMAIL PROTECTED]
m
04/25/02 11:28
AM
Please respond
to ORACLE-L
You mention a partitioned table. Not an IOT I hope? Because SELECTs can
cause deadlocks on an IOT if you partition it in the process (deadlock on
DDL, as opposed to DML, locks). I used to have a trigger of death to check
it, but I wouldn't recommend it, since you have to delete SYS tables to get
rid of it :-). Bug encountered in 8.1.6 and 8.1.7, don't know about 9i, my
interest for bugs decreases strongly when I have switched to something
else, and my professional life is a succession of switches. BTW if the
deadlock occurs on DDL locks, INITRANS and PCTFREE are totally irrelevant.
>----- Original Message -----
>From: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L
><[EMAIL PROTECTED]>
>Sent: Thu, 25 Apr 2002 07:18:30
>
>
>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:
> 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).
>---------------------------------------------------
>-----------------
Stephane Faroult
Oriole Corporation
Performance Tools & Free Scripts
--------------------------------------------------------------
http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs
--------------------------------------------------------------
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroul
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:
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).
