Basically the concurrency level = initrans (so if 2 jobs run -> set it to 2), however for indexes set it to conccurency + 1.
Anjo. [EMAIL PROTECTED] wrote: > 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: 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).
