It is not often but can happen that a SAP transaction/process is suddenly running abnormal longer due to extracting data from database without known application changes such as volume, setting and logic. This type of performance issue is normally due to fact that database has switched to a suboptimal plan instead from a good execution plan. This is a known issue. This post would talk about several different solutions which I experienced in fixing suboptimal SQL execution plan issue and their pros and cons:
- Refresh table stats via SAP database tool,
- Restoring table stats via database tool,
- Manipulate table stats via database tool,
- Use database hints via ABAP program and
- Use Oracle plan management tool.
Refreshing table stats via SAP database tool
This tool is used only when you found that there are many changes since last stats updating on the table used in the long running SQL statement. You normally can refresh, then try to increase sample rate. Each time after table stats is refreshed, you can check via SQL command editor to see whether plan for the sql is switched back to the good one. You can use SAP transaction DB20 to check when a table stats was updated last time and an amount of changes happened on the table since then. You can use SAP program rsanaora to updates stats in SAP Oracle environment.
This approach is not always working. but it needs no development, it is a quick action which we can try to fix the issue and has no know negative impact.
Restoring table stats via database tool
If switching to suboptimal SQL execution plan is immediately happening after a table stats is changed. You can restore original table stats to bring back the good plan to fix the performance issue using database tool.
Apparently, this is a very quick and simple fix if it happens to be the case.
Manipulate stats via database tool
This way, you manipulate stats which are used by database to determine execution plan related to selection criteria used in the SQL statement. For example, you manually increase number of distinct values for a selection fields so database might opt for index or reduce number of distinct value for a field so database is more likely to use full table scan.
Manipulating the stats might impact other SQL statements which access the same table. However it is only one ABAP SQL related to the stats, then it is fine.
Use database hints via ABAP program
This way is an ABAP program changes which needs to go through change management process.
Developer will need to modify the long running SQL statement in the program to use specific database hints. You normally modify a program in a development box, move it to quality box then production box involving different level of testing .
The hints is at ABAP level. This means this is effect to all child SQL statements originated from the SQL. So you need to assess how many child SQL statement this SQL would have and whether this hints is true to all child SQL statement. For example, An ABAP program has a SQL statement like SELECT * FROM table1 FOR ALL ENTRIES IN int_table WHERE field1 = int_table.field1 and field2 = Int_table.field2. If ABAP internal table INT_TABLE is empty, the sql statement would have a child statement like “select * from table1”. So index hints is not good for this type of scenario of this SQL statement even it is good for other cases.
We also need to know this approach might not be possible to apply on standard SAP ABAP code. Even so, it would be gone after system upgrading. In the future, if you need to change or remove the hints, it is another ABAP program change.
Use Oracle plan management tool
If your SAP system is using Oracle database, you can use Oracle plan management tool to assign a specific execution plan to a specific SQL statement.
Using SQL plan management avoids changing ABAP code and the issue can be fixed quickly like in a half hour if you are familiar with below steps. You can move oracle execution plan from one box to another box. This means you can move good execution plan for the SQL from testing box to production box and use it to fix the production plan issue. Since changes is done at Oracle level, plan issue on standard SAP code can be fixed as well via this method. Also, this solution is applied at SQL_ID level, other child SQL statements with different SQL ID would not be impacted. If more than one child SQL statements has the same issue, you need to apply SQL plan management for each child SQL. If it is critical to restore the SQL performance as soon as possible, Using SQL plan management is a better than using ABAP hints. You can lock-in execution plan for the critical and important SQL statements or for those SQL statements you think they would change in a system/oracle upgrading.
However since oracle plan management is based on SQL-ID which depends on actual “SQL text”. So any change in the SQL text can lead to different SQL-ID. It happens even it is rare that an ABAP SQL statement has different SQL text which is mapped to different SQL ID after SAP/Oracle upgrading. When this happens, you need to update the Oracle plan management to reflect the change.
Following are six steps on how to use Oracle plan management to fix execution plan issue. Those are from Standard Oracle document. You can google this using key word “Oracle SQL plan management. For convenience, I put hereJ
Step 1 – Find the SQL_ID for the statement in the V$SQL view and use it to create a SQL plan baseline for the statement using DBMS_SPM.LOAD_PLAN_FROM_CURSOR_CACHE.
Step 2 – The plan that was captured is the sub-optimal plan and it will need to be disabled using
DBMS_SPM.ALTER_SQL_PLAN_BASELINE. The SQL_HANDLE & PLAN_NAME are required to disable the plan and can be found by looking in DBA_SQL_PLAN_BASELINE view.
Step 3 – Now you need to modify the SQL statement using the necessary hints & execute the modified.
Step 4 – Find the SQL_ID and PLAN_HASH_VALUE for the hinted SQL statement in the V$SQL view.
Step 5 – Using the SQL_ID and PLAN_HASH_VALUE for the modified plan, create a new accepted plan for original SQL statement by associating the modified plan to the original statement’s
Step 6 – Now if you query DBA_SQL_PLAN_BASELINES you will see two plans in the SQL plan baseline, the original non-hinted plan and the new hinted plan. Only the hinted plan is
accepted, so this will be the plan chosen next time the SQL statement is executed.