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.

/wp-content/uploads/2015/09/r1_784653.jpg

Monitor Infopackage screen appears as shown in the figure below

/wp-content/uploads/2015/09/r2_784654.jpg

Expand the selection as shown in the figure below

/wp-content/uploads/2015/09/r3_784670.jpg

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.

/wp-content/uploads/2015/09/r4_784672.jpg

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 .

/wp-content/uploads/2015/09/r5_784682.jpg

/wp-content/uploads/2015/09/r6_784683.jpg

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.

r15.JPG

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

/wp-content/uploads/2015/09/r7_784684.jpg

After getting PID from tx code sm50, double click the entry and the new screen appeared as shown in the figure below

/wp-content/uploads/2015/09/r8_784688.jpg

Click On Explain button. The new screen appeared as shown in the figure  below

/wp-content/uploads/2015/09/r9_784689.jpg

Scroll down the screen and then click on BALHDR~4 index.  it contains only one field “ALLDATE”.

/wp-content/uploads/2015/09/r10_784690.jpg

As shown above, database optimizer wrongly picked up BALHDR~4 index.

/wp-content/uploads/2015/09/r11_784691.jpg

/wp-content/uploads/2015/09/r12_784692.jpg

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.

/wp-content/uploads/2015/09/r13_784693.jpg

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 .

/wp-content/uploads/2015/09/r14_784697.jpg

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.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply