Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: No Rows Deadlock and PCTFREE, INITRANS on 8.1.7.2

RE: No Rows Deadlock and PCTFREE, INITRANS on 8.1.7.2

From: <Cherie_Machler_at_gelco.com>
Date: Thu, 25 Apr 2002 10:39:55 -0800
Message-ID: <F001.0044FB50.20020425103955@fatcity.com>

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 <ORACLE-L_at_fatcity.com>       
                    <sfaroult_at_oriol       cc:                                                                       
                    ecorp.com>            Subject:     RE: No Rows Deadlock and PCTFREE, INITRANS on 8.1.7.2        
                    Sent by:                                                                                        
                    root_at_fatcity.co                                                                                 
                    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: Cherie_Machler_at_gelco.com
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>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: 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).
>---------------------------------------------------
>-----------------

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: sfaroult_at_oriolecorp.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: 
  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).
Received on Thu Apr 25 2002 - 13:39:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US