asked on

Oracle

Hi,

I am having always a DeadLock error generated in my database always because of this statment : DELETE FROM APL_APP_LEVEL_DOC WHERE APL_ID=:APL_ID0, all the trace files generated contains the same SQL Statement for the current session, before the PROCESS STATE line.

- Do i have to increase the INITRANS for this table ?.
- Increasing the size of INITRANS does affect current data and the future ones ?

Here the first part of the trace file.

Windows thread id: 7016, image: ORACLE.EXE (SHAD)

*** 2011-08-04 08:52:54.549
*** SERVICE NAME:(mydatabase.company.CORP) 2011-08-04 08:52:54.533
*** SESSION ID:(3.40552) 2011-08-04 08:52:54.533
DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
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-0000eb68-00000000 96 3 SX SSX 86 46 SX SSX
TM-0000eb68-00000000 86 46 SX SSX 96 3 SX SSX
session 3: DID 0001-0060-000009AE session 46: DID 0001-0056-00002129
session 46: DID 0001-0056-00002129 session 3: DID 0001-0060-000009AE
Rows waited on:
Session 46: obj - rowid = 0000F029 - AAAPApAAGAAMXGjAAA
(dictionary objn - 61481, file - 6, block - 3240355, slot - 0)
Session 3: obj - rowid = 0000F62F - AAAPYvAAGAAVWKFAAA
(dictionary objn - 63023, file - 6, block - 5595781, slot - 0)
Information on the OTHER waiting sessions:
Session 46:
pid=86 serial=21228 audsid=1209103117 user: 55/SMARTSOLVE
O/S info: user: SYSTEM, term: USLZAPP006, ospid: 7064:8496, machine: FENWAL\USLZAPP006
program: SmartCommService.exe
application name: SmartCommService.exe, hash value=0
Current SQL Statement:
DELETE FROM APL_APP_LEVEL_DOC WHERE APL_ID=:APL_ID0
End of information on OTHER waiting sessions.
Current SQL statement for this session:
DELETE FROM APL_APP_LEVEL_DOC WHERE APL_ID=:APL_ID0
===================================================
PROCESS STATE

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

joe_echavarria

ASKER

Ok, is just that from the application side hasn't been any changing and this is a new error in the database.

I have been searching and the parameters INITRANS set low can cause deadlocks. I just wanted to make sure consulting with the experts.

johnsone

Have you read through this link:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6042872196732

There is some good information in there on how to spot an ITL deadlock.

no worries :-) no nothing

When INITRANS is set low you might get ORA-00060 "deadlock detected".
A max suggested value for INITRANS would be 100.

For ITL deadlocks - higher INITTRANS/PCTFREE values would allow for more space regarding the data blocks so that more transaction entries can be allocated when necessary.

joe_echavarria

ASKER

Hi
After executing the script below, does it means that i have to increase the value of the parameter initrans of these indexes ?

SQL> select OBJECT_NAME, SUBOBJECT_NAME, TABLESPACE_NAME, OBJECT_TYPE,
TATISTIC_NAME, VALUE from v$segment_statistics where statistic_name = 'ITL
waits' and value > 0 order by value desc;

OBJECT_NAME SUBOBJECT_ TABLESPACE OBJECT_TYP STATISTIC_ VALUE
--------------- ---------- ---------- ---------- ---------- -------
IX_AUDIT_TRAIL_ SMARTSOLVE INDEX ITL waits 206
FIELDS_2 _INDEX

IX_AUDIT_TRAIL_ SMARTSOLVE INDEX ITL waits 126
FIELDS_1 _INDEX

IX_AUDIT_TRAIL_ SMARTSOLVE INDEX ITL waits 76
1 _INDEX

IX_AUDIT_TRAIL_ SMARTSOLVE INDEX ITL waits 27
3 _INDEX

OBJECT_NAME SUBOBJECT_ TABLESPACE OBJECT_TYP STATISTIC_ VALUE
--------------- ---------- ---------- ---------- ---------- -------

IX_AUDIT_TRAIL_ SMARTSOLVE INDEX ITL waits 6
4 _INDEX

IX_AUDIT_TRAIL_ SMARTSOLVE INDEX ITL waits 4
2 _INDEX

IX_ACT_REQ_PROG SMARTSOLVE INDEX ITL waits 2
RESS_3 _INDEX

IX_OBTAINED_HIS SMARTSOLVE INDEX ITL waits 1

OBJECT_NAME SUBOBJECT_ TABLESPACE OBJECT_TYP STATISTIC_ VALUE
--------------- ---------- ---------- ---------- ---------- -------
TORY_1 _INDEX

IX_DOC_LIFE_CYC SMARTSOLVE INDEX ITL waits 1
LE _INDEX

IX_DOC_REFERENC SMARTSOLVE INDEX ITL waits 1
E_1 _INDEX

IX_ACT_REQ_PROG SMARTSOLVE INDEX ITL waits 1
RESS_2 _INDEX

11 rows selected.

joe_echavarria

ASKER

After executing the script below i guess i have 8 indexes that the INITRANS parameter must be increase ? Please also see the output of the preview question posted.

Which side should increase this parameter ?

SQL> select event, total_waits, time_waited, average_wait from v$system_event w
here event like 'enq: TX%' order by 2 desc;

EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------- ----------- ----------- ------------
enq: TX - 8445 1404640 166.33
row lock c
ontention

enq: TX - 1739 5371 3.09
index cont
ention

enq: TX - 11 90 8.19
contention

EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------- ----------- ----------- ------------
enq: TX - 8 36 4.5
allocate I
TL entry

joe_echavarria

ASKER

I have attached a pic with the output of 2 different queries. Acoording to this output does it means i have to increase the size of the INITRANS parameter of these 8 indexes? ,if so to which size u think ?

Regards,

initrans.jpg

no worries :-) no nothing

Usually for indexes it should be set to 4. (for tables -to 2 )

joe_echavarria

ASKER

So according to the image uploaded for thoses indexes the INITRANS parameter should be increase to 4 and the tables where those indexes where created should be se to 2 ?

Are those the reasons of the the deadlock i am having in the database ?

no worries :-) no nothing

"For ITL deadlocks - higher INITTRANS/PCTFREE values would allow for more space regarding the data blocks so that more transaction entries can be allocated when necessary."

Additionally here is some usefull reading: http://avdeo.com/2008/06/16/interested-transaction-list-itl/

joe_echavarria

ASKER

@georgekl

So the tables and indexes that need the changes on those parameter will be the ones in the picture uploaded ?