In the work process overview – SM50, you can see that session B is not responding during a direct read for table NRIV. R/3 work process 405 has sent a SELECT SINGLE for table NRIV to the database and is now waiting for the database to return the requested data.
In monitor for database locks – DB01, you can see why there is no response to the database request. Session B, which is run by WP 405, tries to create a lock on table NRIV, which is already locked by the R/3 work process 494. In the work process overview, you can see that work process 494 is processing session A.
To resolve the lock situation, session A must either release the lock or be cancelled.
The waiting session B causes SAP work process 405 to be blocked, which may cause wait situations for other users.
Situations like the one described are typically not caused by break points, but by lengthy processing of ABAP statements or SQL statements after they set a database lock. The lock on the database is released after the dialog step of session A is completed, and triggers a COMMIT or ROLLBACK.
Using R/3 enqueues and database locks.
Use enqueues to:
Explicitly exclude concurrent dialog access to data, thus preventing logical inconsistencies
A program using only read access does not need to use enqueues: Isolation levels can be used to achieve read consistency if necessary.
Database locks:
Are used in some special cases during the update phase
Should not be used in a dialog program
Exception: number ranges (SELECT single for update)
Locking and performance
Enqueues and database locks on central objects can serialize running transactions.
Avoid serialization:
In general, perform locks only when necessary
Keep locking times as short as possible:
For example, use array operations instead of single record insert/updates
For example, pre-aggregate data to enable one update instead of many
For number ranges: Fewer requirements cause less serialization
For available-to-promise quantities (ATP): additional logic can help to reduce locking times, especially over dialog steps.
For many enqueues, use bundling in a local enqueue container.
Use these two screens to detect high CPU usage on the database server.
Screen A, the operating system monitor – ST06, indicates a CPU utilization of 90% for user processes.
Screen B, shows the top CPU processes (ST06; Detailed analysis menu -> Top CPU processes). This screen reveals that 85.2% of the 2 CPU’s available are being used by the database process with PID 142.
High CPU usage of database processes for long periods typically indicates expensive SQL statements.
The database process monitor – ST04; oracle session can be used to analyze high CPU usage on the database server caused by expensive SQL statements. The Database process monitor enables you to display:
Database processes and the associated client processes (typically R/3 work processes).
The SQL statement currently being processed. To reveal the statement, dbl click the oracle process name. This is useful for statements that run for at least several seconds – short-running statements that are not expensive disappear too fast.
Screen A, showing the database process monitor, indicates that database process 296 is associated with the client process 494 on machine TWDFMX06.
After dbl clicking the line containing this oracle process, the database process monitor displays the expensive select on table ZLIPS: this statement is expensive because ZLIPS has many records and where clause is not supported by a suitable index. The EXPLAIN function for this SQL statement would indicate that a full table scan is being used.
Screen B, showing the work process overview – SM50 for application server TWDFMX06, identifies process 494 as an R/3 work process that is currently processing program ZZSELECT.
