You can check the trace file created in udump when there is a deadlock and it will show you the tables involved in the deadlock (you'll have to convert hex to decimal to find the object_id then look it up in dba_objects). If the deadlock is on TM type locks, it is likely the result of unindexed FKs, e.g.:
(From the top of one of my deadlock trace files in udump)
*** 2005-01-12 08:56:11.192 *** SESSION ID:(23.22614) 2005-01-12 08:56:11.156 DEADLOCK DETECTED Current SQL statement for this session: UPDATE AR_CUSTOMER_MASTER SET TOT_ORD_AMT=NVL(TOT_ORD_AMT,0) + :b1 - :b2 ,LAST _ORD_AMT=:b1 - :b2 + :b5 ,NO_TIMES_ORDERED=NO_TIMES_ ORDERED + :b6 ,LAST_ORD_DATE=:b7 WHERE CUSTOMER_NO = :b8 AND ENTITY_ALL = :b9 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 TM-00000f45-00000000 18 23 SX 21 61 S TM-00000d0f-00000000 21 61 S 18 23 SX
Example from above TM lock:
0f45 = 3909 (you can just use your Windows standard calculator to do the conversion) 0d0f = 3343
SELECT object_name from dba_objects where object_id in (3909,3343); <- this will give you the tables involved in the deadlock
