Problems with data base access

Optimizing SQL statements only makes sense if the R/3 System and database system are correctly configured. This is the responsibility of the R/3 System administrator and the database administrator respectively. Technical problems may have an effect on the whole system (for example, wrongly set parameters, communication problems, old table and index statistics), or may only affect SQL statements on certain tables (for example, missing or fragmented indexes).

To avoid technical problems:

ŸFollow the R/3 installation instructions and refer to R/3 Notes

ŸImplement the recommendations given by the SAP standard services GoingLive and EarlyWatch

Communication problems may occur between the application server and database server. Parameters on the database interface may be wrongly set, and thus also cause problems (for example, incorrect value for rsdb/max_blocking_factor, see recommendations for SELECT FOR ALL ENTRIES).

On some databases (for example, Oracle and Informix), fragmented indexes may occur. The term fragmented indexes means that index blocks are less than 50% full. Fragmented indexes can occur if a large number of table records are deleted and then a large number of table records are inserted. If you discover that an index is suitable for an SQL statement, but that executing the SQL statement causes heavy database system load, your database administrator should check whether a fragmented index (database-specific) is causing the problem.

The R/3 Workload monitor gives you an overview of the general state of your R/3 System. To view the initial screen of the R/3 Workload monitor, from the SAP standard menu choose Tools >> CCMS >> Control/Monitoring >> Performance menu >> Workload >> Analysis, or use Transaction ST03.

From this screen, choose Performance database. In the dialog box that appears, first select the server to be checked, then the time period (for example, previous weeks) and the examination period (for example, previous week).

If the average database response time per transaction step is greater than 600ms, the cause is very probably a fundamental R/3 or database problem. If this is the case, the results of the SQL performance trace are not reliable. Your R/3 System administrator or database administrator should therefore first solve the problem, and then you can repeat the analysis.

Missing indexes

Database reorganization or deliberately deleting database objects at the operating system level can cause inconsistencies between the indexes in the ABAP Dictionary and the indexes on the database.

You can find these inconsistencies by using the database monitor. To view the initial screen of the database monitor, from the SAP standard menu choose Tools >> Administration >> Monitor >> Performance >> Database >> Activity >> Detail analysis menu >> State on disk, or use Transaction DB02. For an overview of indexes that are missing in the database or not found in the ABAP Dictionary, choose Missing indexes.

If you find indexes that are missing on the database, inform your database administrator. Database indexes can be activated from this screen or from the ABAP Dictionary, and thus created on the database. Database indexes that exist on the database but not in the ABAP Dictionary can be created in the ABAP Dictionary. They are transferred to the database when they are activated. The index that already exists on the database is then overwritten. After you have changed the index design for a table, you must create new index statistics for the changed indexes.

Only make changes to database indexes at times of low system activity, because the database locks the database table while the indexes are being created.

RELATED POST

DATA BASE JOINS PART TWO
SAP ABAP SAMPLE CODE FOR ALV BLOCK REPORT

SAP ABAP SAMPLE CODE ALV CHECK BOXES REPORT

SAP ABAP SAMPLE CODE INTERACTIVE REPORT

SAP ABAP SAMPLE CODE ALV DOUBLE CLICK REPORT

SAP ABAP SAMPLE CODE ALV SIMPLE REPORT