Performance Improvement in BW Extractors – Setting up SQL Trace
Introduction: Extracting data is one of the most important areas in SAPBW. Any delay can cause delay in Business Reporting. Here I would try to explain the various issues that we normally have while extracting data from source system and what can be done in order to avoid those.
Would take some practical scenarios to explain the same.
Identify Expensive SQL statements on the source system during the processing of the extractor:
(I) Issue: We were experiencing lot of performance issues with respect to the extractor 0FI_GL_14 ultimately resulting in delaying in reporting. We checked the Job log in SM37 in source system but were not able to get complete information.
During a ST12 trace on extractor 0FI_GL_14 job we identify an expensive SQL statement on the table FLAGLFLEXA. We noticed the SQL statement is performing an expensive HASH JOIN due to an inappropriate index access on the table FLAGLFLEXA as explained below.
Recommendation: By doing this we were able to identify the Expensive SQL statement and were able to improve the same by creating secondary index on the FAGLFLEXA with the required fields i.e. RBUKS, RYEAR and POPER as these were not already involved. This certainly did improve the performance of the extractor.
(II) Similarly for one of other Z extractor we were facing the same performance issue. To extract 100,000 of records it was approximately taking more than 2 hours.
Issue: Data source taking more than 2 hours to extract 1000 of records resulting in delay in reporting.
Again by running the trace for the extractor job we detected a long running access to the table PRPS caused by a particular line of the code as shown below:
Note: The more the access to the database the worst the performance would be.
The code that is responsible for this access is shown below:
SELECT SINGLE * FROM prps WHERE objnr EQ lt_data-objnr.
This access causes the following access path where we can see that a full table scan is performed on the table PRPS.
We can see SQL ID 1f1xgnbhbx1tz does not have a suitable index to use and therefore must perform a full table scan.
From the below we can see below that the field OBJNR is a very selective field
When this SQL is running on the system it can be responsible for 20% of buffer gets on a hourly basis on the system as shown below.
Recommendation: (I) Select only necessary fields instead of select *.
(II) Created a Secondary Index on the Table PRPS with the field OBJNR.
Here, I am stressing the importance of setting up Traces in Extractor jobs to identify various sources of important and hidden information to work on the Extractor code.
Thanks for reading.