INI_TRANS changes on BI system

[Ganimede-Dignan]

Hi,

several times we have some dump with ora-0600 in a our BI system (NW04s, Oracle 10.2 with last patches)

Database error text........: "ORA-00060: deadlock detected while waiting for

resource"

Internal call code.........: "[RSQL/178/XXXX ]"

When we change INIT_TRANS param for tables ... the interuped chains ends fine when we modify the parameter and restart the task.

We have recent SPs

SAP_ABA 700 0014 SAPKA70014

SAP_BASIS 700 0014 SAPKB70014

PI_BASIS 2006_1_700 0004 SAPKIPYM04

ST-PI 2005_1_700 0003 SAPKITLQI3

SAP_BW 700 0016 SAPKW70016

FINBASIS 600 0008 SAPK-60008INFINBASIS

SEM-BW 600 0008 SAPKGS6008

BI_CONT 703 0005 SAPKIBIIP5

ST-A/PI 01I_BCO700 0000 -

With this SPs the problem should be solved with this entry into RSADMIN table.

ORA_CUBEINDEX_INITRANS

ORA_ODS_TABLE_INITRANS

Have you got any idea ? ... we have a nice BI-developer but I prefered to do not leave access with SQLPLUS to server .... can I build a ABAP report to execute "ALTER TABLE "/BIC/FAFC_GL01" INITRANS 20;" to current Oracle istance ? have you got any example ?

Regards.

[lbreddemann]

Hi Ganimede,

the ALTER TABLE/ALTER INDEX statement is usually executed via SQLPLUS.

If you really want to use a SAP report for it - call db_execute_sql in SE37.

Anyhow: just altering the INITRANS storage parameter is NOT ENOUGH.

It's necessary to reorganize the tables/partitions afterwards.

The INITRANS storage parameter defines the storage setup for NEWLY ALLOCATED BLOCKS ONLY.

Blocks that are already used won't be affected.

To maintain the entries in the RSADMIN table why not use SE16/SE16N ?

KR Lars

[Ganimede-Dignan]

I refer to OSS note:

84348

831234

1044110

750033

[stefan_koehler]

Hello Ganimede,

there is a really nice blog article from Michael Braunstein about this topic:

/people/michael.braunstein/blog/2007/11/29/chasing-oracle-deadlocks-part-ii--block-level-locks

> When we change INIT_TRANS param for tables ... the interuped chains ends fine when we modify the parameter and restart the task.

Have you reorg the tables / table partitions after changing the INI_TRANS parameter?

Maybe the deadlocks are pointing to BITMAP indexes - have you checked the deadlock graph?

Regards

Stefan

[Ganimede-Dignan]

Hi,

- Create table on 18-Feb-2008 with name "MYTABLE"

- Change ini_trans to 20 of "MYTABLE" on 19-Feb-2008

- We add recordes until yesterday

- Today ORA-00060

- We have just check table.... and INI_TRANS is "1"

... have you got any idea ?

Regards.

Ganimede Dignan

[lbreddemann]

Hi Ganimede,

perhabs you did something wrong.

perhabs somebody changed the parameter back.

There's no way to know.

Is the table partitioned?

KR Lars

[Ganimede-Dignan]

Hi Ganimede,

>perhabs you did something wrong.

>perhabs somebody changed the parameter back.

mhmmmm it should no be possibile, no one have SQLPLUS access directly to Oracle.

>There's no way to know.

>Is the table partitioned?

Sure.

Thank you

Regards.

Ganimede Dignan.

[lbreddemann]

Hi Ganimede,

ok, let's check the INI_TRANS of the partitions:

select table_name, partition_name, ini_trans from dba_tab_partitions where owner='<SAPOWNER>' and table_name=<table_name>;

BTW: are you sure the ORA-60 you're facing now is due to the INI_TRANS issue? What does the lock-graph look like (see the tracefile)?

KR Lars

[Ganimede-Dignan]

Hi,

>ok, let's check the INI_TRANS of the partitions:

>select table_name, partition_name, ini_trans from dba_tab_partitions where owner='<SAPOWNER>' and >table_name=<table_name>;

We have already select on dba_tab_partitions and in dba_tab_partitions ini_trans of mytable now is "1" but we are really sure that was "20" because we have a print-screen wit select results.

>BTW: are you sure the ORA-60 you're facing now is due to the INI_TRANS issue? What does the lock-graph look like (see the >tracefile)?

I've just change init_trans and data load runs well.

This is a strategic SAP BI cube and it's updated every day.

Thank you for attention.

Regards.

Ganimede Dignan.

[lbreddemann]

>

> I've just change init_trans and data load runs well.

>

> This is a strategic SAP BI cube and it's updated every day.

Hm... I assume that for the now failing load request a new partition had been created - with the INI_TRANS 1.

Did you change the INI_TRANS setting for new objects in BW?

Still the question is open: how does the deadlock-graph looked like? Post it please.

KR Lars

[Ganimede-Dignan]

Hi,

thank you for collaboration, next time I post the Deadlock graph....

Regards.

[Ganimede-Dignan]

.