ORA-00060 Deadlock: Effects of increasing INITRANS parameter

[Former Member]

Hello Experts,

We have intermittently occurring ORA-00060 Deadlock errors in our ECC 6.0 EhP4 Production system (on Oracle 11 / RedHat Linux 4)

Background:

A custom program determines a number of documents from an input file. It then spawns multiple SAP sessions(100 documents per session) to post these documents onto the SAP system. the actual posting is done by the standard SAP program RFBIBL00. The deadlock issue occurs with this program running in parallel sessions.

Analysis:

Checking the Oracle traces, the deadlock errors crops up for differen tables (not a specific table). The deadlock graph from a previous error run is as below:

Deadlock graph:
                       -
Blocker(s)--
  -
Waiter(s)--
Resource Name          process session holds waits  process session holds waits
TX-004f001f-00000798       352    1542     X             76    1576           S
TX-00180021-0002045d        76    1576     X            352   1542           X
session 1542: DID 0001-0160-00000002	session 1576: DID 0001-004C-00000012
session 1576: DID 0001-004C-00000012	session 1542: DID 0001-0160-00000002
Rows waited on:
Session 1576: obj - rowid = 0000AB78 - AAAKt4ABDAAAtYOAAA
(dictionary objn - 43896, file - 67, block - 185870, slot - 0)
Session 1542: obj - rowid = 00009D94 - AAAJ2UABDAAFAg6AAG
(dictionary objn - 40340, file - 67, block - 1312826, slot - 6)
Information on the OTHER waiting sessions:
Session 1576:
 sid: 1576 ser: 5807 audsid: 1175093 user: 28/SAPSR3
  flags: (0x1100041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
  flags2: (0x8)
  pid: 76 O/S info: user: orasid, term: UNKNOWN, ospid: 8159
   image: oracle@sapr3dbhost
  O/S info: user: sidadm, term: , ospid: 5541, machine: sapappserver1
  program: dw.sapsid_Dxx@sapappserver1 (TNS V1-V3)
  client info: 0
  application name: SAPLKAUP, hash value=3582991603
  action name: 30536, hash value=2820519970
Current SQL Statement:
UPDATE "COSP" SET "WTG011" = "WTG011" + :A0 , "WOG011" = "WOG011" + :A1 , "WKG011" = "WKG011" + :A2 , "WKF011" = "WKF011" + :A3 , "PAG011" = "PAG011" + :A4 , "MEG011" = "MEG011" + :A5 , "MEF011" = "MEF011" + :A6 , "TIMESTMP" = :A7 WHERE "MANDT" = :A8 AND "LEDNR" = :A9 AND "OBJNR" = :A10 AND "GJAHR" = :A11 AND "WRTTP" = :A12 AND "VERSN" = :A13 AND "KSTAR" = :A14 AND "HRKFT" = :A15 AND "VRGNG" = :A16 AND "VBUND" = :A17 AND "PARGB" = :A18 AND "BEKNZ" = :A19 AND "TWAER" = :A20 AND "PERBL" = :A21 AND ( "TIMESTMP" <= :A22 OR "TIMESTMP" IS NULL )
End of information on OTHER waiting sessions.

We already checked the SAP Note 84348 and created a High prio message with SAP Support.

Planned Actions and Related Clarifications:

SAP recommends increasing the INITRANS parameter value for the affected tables to solve the deadlock issue. But we have the following clarification regarding this:

- Will it really solve the deadlock issue? The problem being there is no specific table for which this happens. As of now we encountered this error for tables COSP, NRIV, GLPCT and GLPCO.

- As the INITRANS change will affect only the newer blocks going ahead - will there be any performance issue with older and newer block existing in the same table? We cannot perform a table reorg(table size>60GB) as it would need a downtime of hours.

SAP says that customers who increased the INITRANS have not complained of any such performance issues. Hence we need your feedback from experience(if any). Unfortunately we cannot reproduce this issue on any of the test environments and hence are not able to simulate this change in test environment.

Any pointers will be extremely helpful.

Thanks and Regards,

Srikishan

[stefan_koehler · Accepted solution]

Hello Srikishan,

Will it really solve the deadlock issue?

The deadlock graph really looks like some ITL issue. You can also verify this by checking the oracle documentation and MOS note #62354.1.

-> http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/instance_tune.htm#PFGRF94503

-> http://download.oracle.com/docs/cd/E11882_01/server.112/e24448/dynviews_2027.htm#REFRN30121

will there be any performance issue with older and newer block existing in the same table?

Pre-allocated ITL slots will not drop the performance itself. If you got more ITL slots you will have "less space" for data, but this should be negligible in that case.

We cannot perform a table reorg(table size>60GB) as it would need a downtime of hours.

If you want to solve this issue you have to reorg the table with a higher INITRANS setting. The UPDATE statement will modify existing data in currently formatted blocks - so no additionally ITL slots will be created.

I have not investigated that behaviour if row migration kicks in, but from my logical understanding an ITL slot will also be needed in the "old" and "new" block. You could also perform an online reorganisation, so no downtime is needed.

If you want to be absolutely sure, if this is an ITL issue you can also try to query the AWR table DBA_HIST_ACTIVE_SESS_HISTORY and check for the corresponding wait event "enq: TX - allocate ITL entry".

-> http://download.oracle.com/docs/cd/E11882_01/server.112/e24448/statviews_3197.htm#REFRN23400

Regards

Stefan

[Former Member · Accepted solution]

Hi Stefan,

First, thanks for your helpful explanation.

We would try to confirm if it is an ITL issue via your suggestions.

Thanks for the online re-organization tip.

I further checked resources (http://www.dba-oracle.com/t_online_table_reorganization.htm) and as you stated the online reorg could run while the table accepts updates as well.

However, one more suggestion required would be regarding the runtime for this operation. For a table of 60GB size - we estimated a runtime of 8-10 hours. Would this be feasible, as per your experience? Also do see any risks related with this method as we plan to run this for the business critical tables during production time.

Thanks and Best Regards,

Srikishan

[stefan_koehler · Accepted solution]

Hello Srikishan,

Would this be feasible, as per your experience? 

There is no rule of thumb. This depends on your I/O subsystem, the amount of indexes and so on.

Also do see any risks related with this method as we plan to run this for the business critical tables during production time.

No - if you perform an online reorg and your I/O subsystem and other hardware is not used to capacity by normal system load you can do it without any "risk".

Here is the corresponding documentation of BR*Tools:

http://help.sap.com/saphelp_nw04/helpdata/en/1b/4e8f0d38a8f4419436d608a36b6581/content.htm

Regards

Stefan

[Former Member]

Hi Srikisan,

>> - Will it really solve the deadlock issue? The problem being there is no specific table for which this happens. As of now we encountered this error for tables COSP, NRIV, GLPCT and GLPCO.

I faced with this trouble on BI system while extracting the data into the infocubes. In short, I increased INITRANS parameter then have a temporary solution. To solve the problem we dropped all the indexes before data extraction and create the indexes from the scratch after the infocube created. But, note that I applied this on BI system, not an ERP like your situation.

>> - As the INITRANS change will affect only the newer blocks going ahead - will there be any performance issue with older and newer block existing in the same table? We cannot perform a table reorg(table size>60GB) as it would need a downtime of hours.

I didn't face with any performance issue, after I increase the value

You may check the note 831234 - Deadlock ORA-00060 during parallel loading into BW ODS

Best regards,

Orkun Gedik

[Former Member]

Hi Orkun,

Thanks for sharing your experience. We tried running the stats and re-creating indexes for the affected tables - but to no avail.

I guess the ERP tables are making it a bit more complex and bulky..:)

Regards,

Srikishan

[Former Member]

Hi Srikishan,

Some time I ago, I changed the initrans parameters for SNAP and VBHDR tables due to locking issues.

Ever since, we changed, had never faced any problems or negative performance.

And we didn't perform the reorg as it applies to the newly created extents.

Br,

Venky

[Former Member]

Hi Venky,

Thanks for your helpful response. As a thought, before you changed the INITRANS parameter, was the issue basically due to high number of ITL waits? Did your Oracle Deadlock graphs show an ORACLE issue?

I checked note 84348 and thread , where incase of an ITL issue - the Deadlock graph shows 'S' meaning an ORACLE issue. For these - the INITRANS could help. In our case I see both 'X' and 'S' in the graphs - hence the confusion.

Regards,

Srikishan