Re: No Rows Deadlock and PCTFREE, INITRANS on 8.1.7.2

Basically the concurrency level = initrans (so if 2 jobs run -> set it to 2), however for indexes set it to conccurency + 1.

Anjo.

Cherie_Machler_at_gelco.com 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: Cherie_Machler_at_gelco.com
>
> 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: ListGuru_at_fatcity.com (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: anjo_at_oraperf.com

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: ListGuru_at_fatcity.com (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).