Hi Branimir,
Two Suggestions:
1. This looks like having an issue with foreign key indexes. You can check for foreign
keys and
their indexes for tables having objects-id 17786 (457a)and 17775 (4571). If any
of the columns
of these two tables have foreign key relationship with other tables then probably
you need to
create one index on that column.
2. Another suggestion is to check initrans/maxtrans and PCTFREE parameter. If these
are very
low and number of concurrent updates are higher then you need to increase these
values.
I know you are not considering this as an option, but this might help in this
case. In fact
you can't change these parameter for existing data blocks. So you can re-load your
data with
these parameters in effect.
Of course we have Gurus here who can add more inputs to this and correct me as
well if found
to be wrong.
HTH
Rajesh Dayal
Senior Oracle DBA (OCP 8,8i,9i)
International Information Technology Company LLC
-----Original Message-----
Sent: Tuesday, November 04, 2003 1:39 AM
To: Multiple recipients of list ORACLE-L
Subject: Fwd: [ORA-000060: Deadlock detected] Finding BOTH pieces of code that
I realize ORACLE-L could not be the best place to ask this question but
(googling/metalink-ing did not help overly)...
ORA-000060 happens in our own application in the worst of possible places
- at the customer's site. It happens intermittently. All I can do is look
at the alert log for errors and follow the trail to the extremely verbose
trace dumps and wonder. Clearly - I am over my head here as I have very
little clue of what am I actually looking at.
The question is how to interpret what trace dumps tries to tell (should I
be scratching my head with it or is there some kind of magic behind TAR
that would save the day)?
For instance - what is the meaning of this trace snippet (coming from
8.1.7.x):
Deadlock graph:
---------Blocker(s)--------
---------Waiter(s)---------
Resource Name process session holds waits process session holds
waits
TM-0000457a-00000000 24 37 SX 17 14
SSX
TM-00004571-00000000 17 14 SSX 24 37
SX
session 37: DID 0001-0018-00000002 session 14: DID 0001-0011-00000002
session 14: DID 0001-0011-00000002 session 37: DID 0001-0018-00000002
Rows waited on:
Session 14: no row
Session 37: no row
On the assumption that the source of the above problem has nothing
to do with INITTRANS/PCTFREE combo (as per metalink thread # 247579.999)
but the culprit really is the broken app - what would be the 'proper' way
to find which two pieces of code deadlocked each other?
What comes to my mind ain't a pretty sight - turning on sql tracing on the
database level (and suffering worsened performance), then once error happens
finding out exact time from alert log, converting it to matching TIC number
in 3 sec range (if I understand correctly Oracle will detect and break
deadlock
after 3 seconds). Once this range is known for 3 sec. window, digging
through
session traces would reveal what were other sessions doing at the time may
be
narrowing down the search to few possible sources of contention.
Problem with this approach is way too much work to set up then 'dig' through
all (tons of) trace files without any real guarantees source of deadlocking
would be obvious after all this effort. So the question is - how to help
duhveloper(s) by pointing closer to the 'root' of deadlocking?
Branimir
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Branimir Petrovic
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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.net
--
Author: Rajesh Dayal
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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).
