ORA-00060 Deadlock while waiting for resource

[former_member198905]

Hi Experts

We have only 12 hours per day for execute our data loading job in SAP BW. But

Every parallel job for data loading is stop with following error:

ORA-00060 Deadlock while waiting for resource

1st we try 8 parallel jobs with 50K packet size, it stop after 9.5 hours when 80% data load.

2nd we try 6 parallel jobs with 50K packet size, it also stop after 12 hours when 80% data load.

If we reduce parallel jobs then it may be take more then 12 hours which is not allowed by basis.

Our DBA doesn't figure out what is the solution. so what is you advice to rectify this issue??

Thanks

[Former Member]

Hi Imran,

Please refer to below note

1872040 - Handling ORA-00060 error - application deadlock issue

Thanks,

Avinash

[former_member198905]

Dear Avinash

I have no access of SAP ONE Support launchpad any other option?

Thanks

[JamesZ]

hi Imran,

First we need to find out if it is database deadlock or application deadlock. If it is database lock, then we need to adjust initrans, maxtrans.

If it is application deadlock, needs to involve BW consultant. For the details, please check SAP note 84348.

Best regards,
James

[former_member198905]

Hi James

According to SAP note 84348 the deadlock graph's last column that specifies the Waits as type 'S' is important. If an 'X' is displayed instead at this point in the graph, it is NOT an Oracle deadlock.

Following is our graph and its last column shows 'S' it mean it's a database deadlock:

Deadlock graph:    

                ---------Blocker(s)--------  ---------Waiter(s)---------

Resource Name          process session holds waits  process session holds waits

TX-000d0021-002deecd      63      92      X           26      322      S

TX-0006000b-001582a0      26     322      X           63      92       S

So how to adjust initrans, maxtrans??

[JamesZ]

Hi Imran,

For x type dealock, you don't need to tune initrans, maxtrans.

We need to find out the dealock sql from the trace file, then involve BW.

Best regards,
James

[former_member198905]

Hi James

Last column contain 'S' not 'X' as shown in my deadlock graph. so we need to adjust initrans, maxtrans but how??

[JamesZ]

Hi Irman,

Sorry, I only saw part of the graph.

In this case, you need to adjust initrans, maxtrans.

You can use brspace to do this, please see Lars doc:

Before changing it, you need to check its current values:

SELECT partition_name, ini_trans, max_trans FROM

      dba_tab_partitions

      WHERE table_name = '<TABLE NAME>'

      AND table_owner = '<OWNER>';

Or you can use sqlplus command to adjust them as mentioned in the SAP note 84348 , but afterwards you have to reorganize the table manually to take the new value effect.

Best regards,
James

[Reagan]

You should ask the DBA's to investigate the Oracle alert log and the traces files when this error appears.

[manish_singh13]

Hi Imran,

Please check below SAP Notes and see if solution provided in them helps out your issue:

1944924 - Deadlock ORA-00060 during parallel loading/activation of BW ODS object

1946893 - ORA-00060 Deadlock in Cube load

Thanks,

Manish