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).