Waleed,
If there is not an uniqueue index, the statement is correct. However with an unique index and different processes inserting the same unique value, there is a hang. The problem is not the table, but the index and adding itl's won't help. Anjo. "Khedr, Waleed" wrote: > It does not look like an inittrans problem on the table. > I remember reading that Oracle will try to avoid contention on itl if it's > doing insert by adding new blocks to the free lists. The only conflict that > could happen on itl (inittrans) during inserts is for indexes. > > Could you have a primary key constraint that has some SYS_<..> index > associated with it and was forgotten to be dropped? > > Regards, > > Waleed > > -----Original Message----- > Sent: Thursday, April 25, 2002 3:04 PM > To: Multiple recipients of list ORACLE-L > > Waleed, > > There used to be a single process that loaded the data. It ran too long > and the application owners split the job out into two process. One > started at one end and loaded data in ascending order and the other started > at the other end and loaded in descending order. > > Both jobs run at the same time in the same database. They've run like this > for a couple of years in the current 8.0.4 database without any obvious > locking errors. > > Cherie Machler > > > > "Khedr, > > Waleed" To: Multiple recipients of list > ORACLE-L <[EMAIL PROTECTED]> > <Waleed.Khedr@ cc: > > FMR.COM> Subject: RE: No Rows Deadlock > and PCTFREE, INITRANS on 8.1.7.2 > Sent by: > > [EMAIL PROTECTED] > > om > > > > > > 04/25/02 01:33 > > PM > > Please respond > > to ORACLE-L > > > > > > Can you please give more details on: While testing our nightly load job > that > runs in two simultaneous > streams (ascending and descending). > > What do you mean by descending and ascending (simultaneous)? > > Thanks > > Waleed > > -----Original Message----- > Sent: Thursday, April 25, 2002 11:19 AM > To: Multiple recipients of list ORACLE-L > > 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). > -- > 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). > > -- > 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). > -- > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk 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).
