Skip to Content
Author's profile photo Paulo Junior

Two Frequent Business Warehouse Issues – 2/2: ORA-00060

This is another issue we frequently receive complaints about. What is scary about it is  it always comes as a Dump, and may require a little attention to some specific points, before you can understand that it was actually “just” a deadlock in your database. +   Runtime Errors         DBIF_RSQL_SQL_ERROR+

+   Exception              CX_SY_OPEN_SQL_DB+


+   Database error text……..: “ORA-00060: deadlock detected while waiting for+

+    resource”+

+   Internal call code………: “[RSQL/INSR//BIC/FDDLK_CBE]”+

+   Please check the entries in the system log (Transaction SM21).+ I’ll try to be as simple as possible with this explanation, just to help you understand the basic and necessary principles. h3. What is a deadlock? A deadlock is a condition where two or more users are waiting for data locked by each other.  Imagine two processes are running in parallel. *Process A* is currently locking a resource called *Resource 1* and *Process B* is locking *Resource 2*. A *deadlock* would happen if, to continue, *Process A* needs *Resource 2* and *Process B* needs *Resource 1*.TX-00090004-00011000   43   39    *X *                      35     46          *S* TX-0006001a-0001397d   35   46    X                       43     39          S If you see the value ‘X’ in both columns “Hold waits”, you may start working in the BW system. If you don’t, you should first check your Oracle database. h4. Database Side When we first understand what a deadlock in the database is, we tend to think of two processes fighting for the same records. This isn’t always true. The lock may be related to another resource. In Oracle databases, a table contains various blocks of data. The locks are managed inside the blocks. For that purpose, they also contain a list of transactions that are interested in specific rows. This list is called *Interested Transactions List (ITL)*. The *ITL* is composed by transaction slots. Each slot will point out which transaction is interested in which row. For each table/partition, its *INITRANS* and *MAXTRAN* parameters will delimiter how this resource will be managed. *INITRANS* establishes the Initial Transaction Slots. This is the number of transaction slots a block will initially have. *MAXTRANS* defines the maximum number of slots that a block can allocate. According to the necessity, Oracle will dynamically increase the number of slots in the block until it reaches the value set in this parameter. Our recommendation usually concerns increasing *INITRANS*, as Oracle, by default, doesn’t reserve much space for it. Therefore, if there is high concurrency, the transaction list usually needs to be extended. This extension is a very expensive and time consuming task. In this situation, transactions may be seen waiting for a “shared not exclusive lock”. Therefore, it makes sense to always look for a higher and optimum value for *INITRANS* for all blocks, so the transactions won’t have to wait for the list to be extended. *SAP Note #84348* describes a few situations that may cause a deadlock in the database and lists a few suggestions on how to solve it. {code:html}84348 – Oracle deadlocks, ORA-00060.{code} h4. Application Side h5. Drop the Indices BITMAP indices are designed to be used only in Data Warehouse systems. BW creates BITMAP indices on the F-Tables, for example, to improve the reporting performance. However, this type of index is very susceptible to deadlocks, as each index row references various records, not just one. Any DML statement (INSERTS, UPDATES, etc.) will lock many rows within an index. Dropping the secondary indexes from the InfoCube before loading data is recommended to improve dataload performance, but it also prevents deadlocks. Since indices are defined both in the DataBase and in the Dictionary, you may drop it only in the database, and rebuild from the dictionary definition after the load has finished. If you are manually loading the data, you can drop and rebuild the index in RSA1: RSA1- Delete and Repair Indices Or, you may set a default option directly to perform this action automatically in before each load.  The following popup window appears after clicking on the button “DB Index Creation” Delete InfoCube Indexes in BW If you are loading via process chain, you may create variants do manipulate the index: Dropping Indexes in Process Chains h5. Reduce Parallelism Reducing the number of parallel processes or deactivating the parallelism is another way to avoid deadlocks. To change this setting in a DTP, display the object and choose the menu option “Goto -> “Settings for Batch Manager…”. DTP: Settings for Batch Manager A popup window will be displayed. You may change the number of parallel processes in the field “Number of Processes”.  If set to 1, the parallelism is deactivated and the load will be serial. Number of Parallel Processes for DTP Loads For an InfoPackage in a 3.X dataflow, you can load serially by choosing the processing option “PSA and then into Data Targets (Package by Package)”. Processing types in an infopackage

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member
      Please refer to OSS Note 1513323. This note suggests a different solution than what is mentioned here for the BW side.

      However, the solution in Note 1513323 did not work for us. The solution mentioned in this blog DID work.

      Perhaps the OSS note should be adjusted?


      Author's profile photo Paulo Junior
      Paulo Junior
      Blog Post Author
      Hi Steven,

      Thanks for this feedback! I'm glad the info here was helpful.

      The solution from the KBA 1513323 is just one of the solutions proposed in this post. Some Knowledge Base Articles may be very narrow to single solutions.

      Which suggestion from the blog post did you follow?

      If you just manually dropped the indices and it worked, I'll check if these settings proposed by the KBA are obsolete (definitely irrelevant or in some situations) and request for a change in the Article.

      Author's profile photo Former Member
      Former Member
      Yes. We manually dropped the indices and it worked. The settings mentioned in the OSS note did not solve the problem.