In analyzing a SAP program which spends most of run time in reading data from database, SQL execution plan is often a focus. This post would share my knowledges on SQL Execution plan in a SAP/Oracle environment. It covers following:
- Oracle SQL execution plan introduction,
- How to read an oracle SQL execution plan in SAP system,
- How to assess an Oracle SQL execution plan and
- Tips on trouble-shooting performance incident related to execution plan.
1 Oracle SQL execution plan introduction
- Oracle SQL execution plan is a series of steps which oracle takes to execute an SQL to retrieve expected data from database. In the plan, Oracle would tell which table is accessed via which method, in which sequence and how data from each table is joined (when applicable).
- Oracle query optimizer is responsible for generating the execution plans and choose the best plan which has the lowest cost. Oracle calculates cost of a plan according to predicted I/O, CPU and network resource utilization based on various stats – table stats and system stats and related Oracle parameters setting.
- Various stats used in cost calculation are dynamic and based on sample data. This means two things: the plan chosen by database might not be the best one occasionally and the execution plan for a SQL can be changed over time. When Database is switching to suboptimal execution plan from good one, the related business transaction/process would take more time to finish.
- Oracle query optimizer only generates a plan once and reuses it if no stats related to execution plan are changed and the plan is still in memory (Oracle Shared Pool).
2 How to read an Oracle execution plan
A SQL execution plan could be very complex with many steps. SAP makes it easier for you to read Oracle execution plan by numbering steps in the order which a step is executed similar to Figure 1. So you read a SQL execution plan starting with step 1, then step 2 and so on. You can display a SQL execution plan for an executed or being executed SQL via SAP transaction ST04.
Figure 1 How to read a SAP Oracle execution plan
In Figure 1, SAP would execute step 1 to read row-ids(data records) by doing index range scan on index TBTCO~8 via index field STRTDATE(A1) and filter predicates, Then execute step 2 to get needed dataset from Job overview table TBTCO via row-ids from step 1), Then it would get another data set from TBTCP table by executing step 3 and step4 prior to execute step 5 which is hash join on both data set to return the data needed by the SQL. Index range scan is used when more than one entry from index table meet the searching criteria such as STRTDATE (Job start date) field. Many jobs starts in one day typically. So when TBTCO table is searched by job start date etc, it would normally return more than 1 entry. Only Select SQL statement which is using all index fields of a primary index always returns 0 or 1 entry.
TBTCP~8 index is a standard SAP secondary index. Following screen shot shows its’s index fields
Figure 2 TBTCO~8 index
3 How to assess a SQL execution plan
There are two purposes of assessing a SQL Execution plan –
- Whether the execution plan used by database is correct in current environment and
- What can be done to improve the performance of the SQL.
Assessing an oracle execution plan could be a very tedious and very demanding task even for an experienced Oracle expert especially when the execution plan is complex. More steps and more tables a SQL execution plan has, more complex the plan is.
When I assess an oracle execution, I would normally check:
- Whether an index used in accessing table is correct,
- Whether table is read in right sequence and
- Whether table join methods used by database is correct.
A right plan does not necessary mean it is a “performance sound” plan. For example, it is using full table scan to get a few records from a big table when no index satisfies the SQL where-clause. The Full table scan is correct in this context but it is not efficient. Since the SQL only returns several entries from the big table, this indicates the fields used in the where-clause are very selective, creating an index using fields from the where-clause should be very efficient.
4 Tips on trouble-shooting performance incident related to execution plan
When there is a performance incident in a production box with a single SAP transaction/program in your system, you have done performance trouble-shooting and identified that program is spent most of time in executing one particular SQL Select statement, the SAP transaction/program has no technical/functional/volume change and other sap transactions/program is working as usual, this is normally result of SQL execution plan switch – suboptimal SQL execution plan is chosen by the database for whatever reason.
In this situation, I would normally do following checks to confirm the plan switching issue and identify right execution plan –
- Check execution plan history and timing of plan change to see how is that related to timing of performance incident. If yes, then this is likely a plan switch issue – Oracle can switch to suboptimal execution plan for various reasons like system upgrading, stats changes etc. You can use SAP program /SDF/RSORADLD_NEW to check plan history if you prefer or cannot use Oracle AWR.
- Check execution plan between production box and other non-production SAP Boxes or other SAP production boxes. For example, if production box and testing box has different execution plan and plan in testing box is more efficient, then it is an Oracle SQL execution plan switching issue. The plan in testing box is the expected plan.
- When above step cannot draw a conclusion on what is right plan, then you need to validate the plan according to those above mentioned checks.
- Test your idea – If you think one table access path, one table join method, and join sequence might be better, you can always test this out by executing the SQL with proper database hints. . Use this test only when no other ways to validate this. You can always use SAP St04 SQL command editor to execute a SQL in a SAP box even you have no direct-access of database SQL tools.
In my experience with SAP supply chain system, the common pitfalls are:
- Inappropriate index is used to access a table. Using index is good for performance. But using a wrong index is much worse than a full table scan.
- Full table scan is used while it is more efficient to do so via an index.
- One of nested-loop and hash-join operation is incorrectly preferred over the other. Nested-loop is for smaller amount of record from outer table with good join condition (like primary key) to the inner table. Otherwise, it would be hash join for large dataset. Sort merge join is used with large data sets and inequality join conditions.
The good news is that we normally do not need to pay attention to an oracle execution plan except following typical cases:
- The SQL is using too much resource like CPU time, high IO time overall in the system and each execution of the SQL is longer like more than seconds – like top expensive SQL in Oracle AWR report or ST04 SQL cache etc. Normally you do not need to assess an execution plan even it is in your top expensive SQLs list if each execution of the SQL is faster like in milliseconds. If a SQL shows up as the most expensive SQL and it is faster, you need to work with function owner to reduce resource usage to focus on reduction of frequency which the SQL is executed.
- Execution of the SQL is holding a lock which is blocking other business processes, this is especially true when the SQL is using “SELECT for update” command.
- A business transaction cannot meet business performance goal due to long execution of a SQL.
- A SQL is taking much more time suddenly than it was while other SQLs are performing normally.
- Preparing system upgrading.
Knowledge with different Oracle optimizer access paths is a precondition for an efficient plan review. Click Oracle Optimizer Access Path if you would like to know more.
If you wonder the general approach on performance trouble-shooting, you might find my post “SAP production performance issue trouble-shooting” helpful.