HOW TO VALIDATE SQL PERFORMANCE TUNING VIA SAP ST04 SQL CACHE

Sometimes, you have to tune SQL to improve SAP program performance. After SAP SQL tuning is done, is new SQL statement having better performance than original SQL statement? This post would show you an example on how to verify this via SAP ST04 SQL cache. 1 The background ST04 SQL cache showed that one SQL was a top expensive SQL statement based on CPU utilization. 2 Tuning on the SQL in SAP ABAP program To improve the performance, SQL is tuned in the way that source table was swapped from /SAPAPO/MATKEY to /SAPAPO/MATMAP since primary key of /SAPAPO/MATKEY matches existing selection criteria and new table returns same result under the same selection criteria. Please refer to Figure 1 to know the new code and old code— Figure 1 Old code and new code The change is quite simple and done in SAP SCM system. So would the new SQL have better performance than the old SQL? 3 Performance validation on the tuned SQL via ST04 SQL cache Since all executed SQL statements would be kept SQL cache with many performance metrics like response time, CPU time etc. So both old SQL and new SQL execution history data is available in the system. That data is pulled and showed in Figure 2. Figure 2 ST04 SQL Cache Figure 2 shows that each execution of new SQL accessing /SAPAPO/MATMAP table is spending about 13,006,591 microseconds against 21,495,315+ microseconds spent by old SQL statements accessing /SAPAPO/MATKEY. That is at least 39% performance improvement on CPU time consumption per SQL execution. Total CPU time consumed by a SQL is the times of two numbers – CPU time per SQL execution and number of times which the SQL is executed. Improvement on individual SQL execution is not a guarantee for overall improvement if number of SQL execution is increased. In this particular case, Figure 1 shows the change is just replacing the old table with a new table without logic changes. So number of times which the SQL should be executed would not be changed after tuning. Also Figure 2 shows number of rows retrieved (column Rows/Exec) per SQL execution has no big difference between new SQL and old SQL and it also shows 43+% improvement on SQL elapsed time/execution. So the SQL/ABAP performance tuning is successful based on ST04 SQL cache performance analysis. So far, ST04 SQL cache performance analysis helped us to achieve our goal – whether the SQL/ABAP tuning is good/bad for performance. You might wonder why it helped to improve performance by a simple table swap? The developer replaced the old table with the new table since EXT_MATNR is a primary key of /SAPAPO/MATMAP (Figure 5). Reading a table via primary key is faster – is this the reason for the performance improvement? If you would like to know more, continue readingJ. 4 Why changes make a performance improvement A tuned SQL can make performance improvement due to many reasons – execution plan, table size etc. 4.1 Execution plan comparison Figure 3 is the SQL execution plan with original table before the change. Figure 4 is the SQL execution plan with the new table. Figure 3 SQL Execution plan before change Figure 4 SQL execution plan after change From the above execution plan, we can see that primary key is not used to read the new table. It is using the “Full table scan”. Index /SAPAPO/MATKEY~MAT has only “MATNR” field. Comparing cost of SQL execution plan between old table and new table, you can see “Estimated Costs” with the new table is 788 against 1,223 with old table. Figure 5 Table structure Based on the code and new table structure, the primary key could be used to read the table normally. Why not? We need table size information to understand this. 4.2 Review table size Using ST04 SQL command editor, We can enter SQL and execute it to get following information from Oracle table DBA_TABLES . Figure 6 – Number of table records So number of rows is similar but average row length is different – this is approved by the table size from DB02 transaction (Figure 4) Figure 7 – Table storage size So the new table is almost 1/3 of old table in size. 4.3 Performance improvement When an index is used to access a table data record, Oracle would need to read index block to get address for the data block and then it would fetch data block. When number of records needed reaches a threshold, it would be more efficient to bypass index and read data block directly. In this particular case, whenever the sql is executed, it would fetch 247K record out of 250K records. So the full table access is used by Oracle with the new table. So the performance improvement is coming from a much smaller table and full table access not due to primary index with the new table. 5 Other methods for validating a SQL performance tuning There are other ways to determine whether a SQL tuning in SAP program is good to performance. Based on program runtime difference – Get program runtime before and after changes against same testing data. Based on analysis of program execution statistics – statistics for program execution before changes and statistics for program execution after changes. Based on performance trace analysis – Do a before and after trace. Based on technical analysis like where-clause and table index. Each of above method has their own pros and cons. You need to choose the best one fit for your case. For example, when you measure the performance by program/job runtime or execution statistics, you need to be sure that runtime is not impacted by many factors like volume, application lock and database lock, resource contention etc. If you changed many SQL statements in a program to tune performance, it might be a tedious work to find out how each change is performing. Someone might notice that the cost in the execution plan with a new table is lower. You cannot say for sure that lower cost means faster access when different table are involved. Even with the same table, an execution plan with a “lower” cost can be a bad plan – that is why Oracle provides Oracle hints for program to influence execution plan. So using cost is not an error-proof method to measure performance of two different SQL statements. In this post, I have used ST04 SQL cache to validate performance improvement from SQL tuning. You can click How to run ST04 for more information. HOW TO VALIDATE SQL PERFORMANCE TUNING VIA SAP ST04 SQL CACHEVALIDATE SQL PERFORMANCE TUNING;SQL CHANGE PERFORMANCE ANALYSIS;SQL TUNING PERFORMANCE MEASUREMENT; ST04 SQL CACHE Post navigation PREVIOUS POST