Jonathan,
Following is section of the trace file generated. It is a TX lock. I'm
not sure whether it is mode 4.
How can I tell?
We upgraded using the migration script, not export/import. During the
nightly loads we have
always dropped the indexes on the foreign keys on this large fact table and
never had any
problems at 8.0.4. Those foreign key indexes were dropped before this
load.
I got another recommendation from Anjo Kolk to increase INITRANS to 2.
The problem is
that this very large partitioned table cannot be exported/imported in it's
entirety during our
regular maintenance window. Is that a requirement for changing INITRANS?
Could we just do this on new partitions? It is not a problem with
existing rows but rather with
new rows being inserted (according to our developers).
Thanks for your helpful reply.
Cherie Machler
*** SESSION ID:(9.24642) 2002-01-07 09:49:54.992
DEADLOCK DETECTED
Current SQL statement for this session:
INSERT INTO EXP_LINE_ITEM_FACT (
CONFIRMATION_NUMBER,EXPENSE_REPORT_LINE_NUMBER,
STATUS_KEY,BATCH_WINDOW_DATE_KEY,ACCOUNT_KEY,EXPENSE_DATE_KEY,REP_KEY,EXPENSE_CO
ST_CENTER_KEY,SUBMIT_DATE_KEY,ARRIVAL_DATE_KEY,PAYMENT_INITIATED_DATE_KEY,TRANSA
CTION_INITIATED_DATE_KEY,EXPENSE_CATEGORY_KEY,PROJECT_KEY,PRODUCT_KEY,PAYMENT_ME
THOD_KEY,EXPENSE_VENDOR_KEY,EXPENSE_LOCATION_KEY,EXPENSE_REPORT_NUMBER,PERIOD_EN
D_DATE,EXPENSED_AMOUNT,PAID_AMOUNT,OVERRIDE_IND,RECEIPT_IND,OUT_OF_POLICY_IND,LI
NE_ITEM_REMARKS_IND,DATA_SOURCE_MOD_DATETIME,DATA_WAREHOUSE_MOD_DATETIME,DATA_MA
RT_MOD_DATETIME,LOAD_DATE_KEY ) VALUES
( :b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b
10,:b11,:b12,:b13,:b14,:b15,:b16,:b17,:b18,:b19,:b20,:b21,:b22,:b23,:b24,:b25,:b
26,:b27,:b28,:b29,:b30 )
----- PL/SQL Call Stack -----
object line object
handle number name
a2ddf49c 258 package body EDM_DBO.EXP_LINE_ITEM_FACT_COMMON_PKG
a2ddf49c 1173 package body EDM_DBO.EXP_LINE_ITEM_FACT_COMMON_PKG
a2cffa58 1107 package body EDM_DBO.ELIF_EXP_RPT_AMT_TYPE_PKG_ASC
a2cffa58 677 package body EDM_DBO.ELIF_EXP_RPT_AMT_TYPE_PKG_ASC
a2cffa58 393 package body EDM_DBO.ELIF_EXP_RPT_AMT_TYPE_PKG_ASC
a2125ddc 4 anonymous block
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)
---------
Resource Name process session holds waits process session holds
waits
TX-0007004b-0000d4e9 15 9 X 12 37
S
TX-0006001f-0000d1ac 12 37 X 15 9
S
session 9: DID 0001-000F-00000002 session 37: DID 0001-000C-00000002
session 37: DID 0001-000C-00000002 session 9: DID 0001-000F-00000002
Rows waited on:
Session 37: no row
Session 9: no row
===================================================
PROCESS STATE
-------------
"Jonathan Lewis"
<[EMAIL PROTECTED] To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
mon.co.uk> cc:
Sent by: Subject: Re: No Rows Deadlock and
PCTFREE, INITRANS on 8.1.7.2
[EMAIL PROTECTED]
04/25/02 11:53 AM
Please respond to
ORACLE-L
Is it a TX mode 4 (Share) deadlock ?
Can you post a section of the deadlock graph
from the trace file.
If you really have only two streams running,
then you need only set initrans to 2 at a cost
of (typically) 24 bytes per block to guarantee
that the problem goes away.
There are several other reasons for deadlocks -
in particular issues relating to parent/child
conflicts. e.g.
session 1 deletes child for parent A
session 2 inserts child for parent A
session 1 tries to delete parent A
At this point session 1 waits for session 2
to commit - showing TX/4 and no rows
If your batches are sufficiently tangled that
the same sequence could have happened
in the opposite order (possibly through triggers)
on other parent/child rows, then you'll get
a TX/4 deadlock showing no rows.
An alternative thought - did you upgrade by
doing an export/import ? Going from 8.0 to 8.1
you may find that some of the 8.0 indexes
were deemed redundant by the 8.1 import,
so you may have invisibly dropped an index
supporting a foreign key.
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-----Original Message-----
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 25 April 2002 16:11
|
|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: Jonathan Lewis
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).
