Skip to Content
Author's profile photo Lars Breddemann

Quick help for ORA-60 deadlock in BW systems

Today I re-learned a nice option to overcome ORA-60 problems with parallel data loading scenarios.

As most of you will know (from reading note “84348 – Oracle-Deadlocks, ORA-00060” or Michael Braunsteins Blogs Chasing Oracle deadlocks part I – row level locks/Chasing Oracle deadlocks part II – block level locks or some other resource) the problem here is caused by Oracle space management on block level.

There is a specific block level storage setting that need to be changed (INI_TRANS) in order to guarantee multiple parallel transactions in a already used block.
The key point to this change is, that it’s not sufficient to run the ALTER TABLE/ALTER INDEX command, since this will only change the setting for new blocks. To also cover the blocks already in use, the table/index needs to be reorganized or rebuild.

Usually this would have been a two step procedure:
1) change the INI_TRANS value
2) perform the reorg with BRSPACE

The thing I re-lerned just five minutes ago is:
BRSPACE has a switch for this – so you can do it all at once.

The following command will reorganize table “/BIC/FMYTESTCUBE” into the same tablespace and set the INI_TRANS parameters for the indexes (-SII) and for the table (-STI) to 20.

brspace -u / -f tbreorg -t "/BIC/FMYTESTCUBE" -STI 20 -SII 20 

Nice, isn’t it? 

Assigned Tags

      6 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Bala Prabahar
      Bala Prabahar
      Good info for non-BW systems.
      I've seen deadlock on F table(your example) caused by parallel loading with indexes on. In some cases, I've also seen deadlocks during DSO activation when activation was kicked off with secondary(non-unique) indexes on. 
      Dropping indexes(RSA1 performance tab) and loading data into cube has always worked for me. Since deadlocks normally occur when the number of records being loaded is high, dropping indexes helped us in two ways:
        1) Loading is faster(With indexes on, loading will get exponentially slower due to non-current stats) and
        2) No deadlocks.

      Thanks,
      Bala

      Author's profile photo Lars Breddemann
      Lars Breddemann
      Blog Post Author
      Hello Baba,

      well, unfortunately just dropping the indexes does not always solve the problem. Sometimes an adaption of the table blocks is required to get around this.

      And that's what you do with the BRSPACE command 😉

      regards,
      Lars

      Author's profile photo Former Member
      Former Member
      Hello Lars,

      thanks for the interessing information and also
      for updating the note 84348 with this brspace
      option. A long time ago, I heard that rasing
      the INI_TRANS parameter could also help to fix
      (at least some of) the infamous "enq: TX - row
      lock contention" causes, but could in turn
      trigger ORA-00060 errors. So should we raise
      INI_TRANS and keep the fingers crossed?

      Regards,
      Mark

      Author's profile photo Lars Breddemann
      Lars Breddemann
      Blog Post Author
      Hi Mark,

      I would be surprised that changing the INI_TRANS value would help with the "row lock contention".

      Maybe you're remembering the effect of BITMAP indexes for INSERTS/UPDATES as mentioned in note 825653, Pnt 66 ...

      I wouldn't just raise the INI_TRANS without any indication to do so. After all, it still takes time to start the reorgnisation and to check afterwards that everything went OK...

      regards,
      Lars

      Author's profile photo Former Member
      Former Member
      Hi Lars,
      thanks very nice blog.
      cheers Peter
      Author's profile photo Lars Breddemann
      Lars Breddemann
      Blog Post Author
      Glad you liked it 🙂

      cheers,
      Lars