Changing the index design of a table in the R/3 System can affect SQL statements other than the one you want to optimize. You must therefore ensure that other SQL statements are not negatively influenced by a new, changed or deleted index.
To do this, you must first establish which database views are referenced on the table. You can find this information in the where-used list in the ABAP Dictionary.
Next, perform an object-related shared SQL area analysis. This means that you restrict the analysis to the relevant tables or database views, rather than finding inefficient SQL statements in the whole system.
After that, change the index design (also activate the indexes, and update the table and index statistics). Immediately after you have changed the index design, reset the DB SQL cache (use the Reset pushbutton).
To establish in which database views the tables to be analyzed are used, go to the initial screen of the ABAP Dictionary. To do this, from the SAP standard menu choose Tools >> ABAP Workbench >> Dictionary, or use Transaction SE12. Enter the table name in the appropriate field, and choose Where-used list.
To perform an object-related DB SQL cache analysis, first go to the initial screen of the database monitor. To do this, from the SAP standard menu choose Tools >> Administration >> Monitor >> Performance >> Database >> Activity, or use Transaction ST04.
Choose Detail analysis menu >> SQL request. A dialog box appears, where you can limit the SQL statements displayed. Delete the entries in the fields Buffer Gets and Disk Reads. In the Table field, replace the * with the name of the table to be analyzed.
Download to a local file the list of SQL statements contained in the DB SQL cache for the table. Save the Explain SQL display for each SQL statement. Perform the analysis in the same way for all database views that use the table.
A few days after you changed the index design, perform the same analysis again, and for each SQL statement compare the values for logical and physical accesses per execution. This second analysis enables you to ensure that none of the SQL statements analyzed is negatively influenced by the change in index design. The change should affect only the SQL statement to be optimized.
No comments:
Post a Comment