BW users complaint the poor performance on BW Production system. (BW 740 ABAP and oracle database 11.2.0.4.3).
They executed "Monitoring Infopackage" using transaction code rsmo and when clicking on "details" output not appeared on the screen. The process was running several hours.
I am asked to investigate the root cause of Infopackage Monitoring performance issue.
Step-by-Step problem reproduction is as follows
Enter transaction code rsmo as shown in the figure below.
Monitor Infopackage screen appears as shown in the figure below
Expand the selection as shown in the figure below
Double click on any entry (highlighted in yellow colour as shown in the figure above) i.e. select entry with less number of records too
Right side screen appears.
Click on the "Details" tab on the right side of the screen as shown in the figure below.
Pop-up screen appeared. Either click Yes or No.
It takes several hours more than 5 hours to get the output of Details or running indefinite loop irrespective number of records. This problem occurred after upgrade to NW740..
To investigate that process was running long time, first enter transaction code sm50 as shown in the figure below
database sequential Read was taking place on the table is BALHDR .
Executed transaction code st12 "Single Transaction Analysis" and then enabled trace for specific user. It was observed that based on the abap trace, more than 80% time spent on the database. This could be due to poor oracle optimizer database statistics or index for table BALHDR.
To check whether the issue was due to poor database statistics or index, executed transaction code st04, then click on "Session Monitor" as shown in the figure below
After getting PID from tx code sm50, double click the entry and the new screen appeared as shown in the figure below
Click On Explain button. The new screen appeared as shown in the figure below
Scroll down the screen and then click on BALHDR~4 index. it contains only one field "ALLDATE".
As shown above, database optimizer wrongly picked up BALHDR~4 index.
As shown above, fields after where clause such as MANDANT,EXTNUMBER,ALMODE and PROBCLASS were not present in BALHDR~4 index.
The workaround solution is to create Non-unique secondary index temporarily in the development system till SAP provide permanent fix correction solution. Although it is not good idea to create secondary index, we have no choice as we waited for many days from SAP to get us fix correction for permanent solution.
BW users were continuously complaining about poor performance in SAP BW production system and the month end period closing was approaching near.
Finally the IT Manager has agreed for creating the secondary index for table BALHDR in the development system.
Non-unique secondary index BALHDR~Z1 was created as shown in the figure above. After the index created and activate completed, execute transaction code rsmo .
Expand the selection as shown in the figure above and double click on the highlighted yellow colour, Then click "Details" tab on the right side of the screen, immediately the output was displayed in less than one second.
The transport request was imported into quality system and tested the transaction code rsmo by testers team. After getting the approval same transport request was transported to BW production system and tested successfully. BW users were able to execute transaction code rsmo and succeeded in getting output of "Details" screen in less than one second.
- SAP Managed Tags:
