How to Accelerate DSO Activation Performance?

[Siva]

Abstract: 

Though the Proliferation of Cloud Computing Technologies prevailed the paradigm shift of moving to Modern Data Warehouse Architecture and Technologies, this Blogpost still discusses one of the crucial task performance issues occurring in the Classical Data Warehouse, built on Non-HANA DB. The reason is that even 60 percent of customers (around 10000 customers) are still running their SAP BW Business on other than HANA DB.

Problem Statement: 

One of my clients faced a massive fluctuation in data load completion at the system level. In turn, it created critical escalation from the Business for inability to meet the SLA across different functional areas starting from HR, WRM, SCM, EPPM, and Finance Webi and AO report.

Observation: 

We observed that the system’s data load performance behaved weirdly for the last three months before the fix was applied in the system. It seems the data load performance will be good for one week, and all the areas will be completed comfortably before SLA. But, in the next week, the entire performance is like a topsy-turvier; all the areas breached the SLA. Again, the same pattern got repeated.

So, we did a profound investigation in all the spots in the process chain loads and finally found that the DSO activation behaves haphazardly. The “BI_PROCESS_ODSACTIVAT” activation process significantly influenced the weird system behavior. One day, DSO activation for the entire system took only 50K seconds; another day, it took 150K seconds, which was three times higher than the usual completion time.

Detailed Investigation:

The above metrics helped narrow our focus towards the DSO activation process. We have analyzed a few Info providers and observed the following.

The table below mentioned data targets activation took longer than usual. Activation is one of the significant activities for the DSO-type data targets in the BW. Also, the below table ensures that there is not much difference in the data volume.

The screenshot below shows that one of the info providers took 1.4 hours to complete the activation on 18th Oct, whereas the same activation got completed in 4 minutes on 14th Oct. The same case for others also.

DSO Activation Performance Comparison

 

BASIS Details:

The above screenshot shows that the fluctuation in the activation is the only cause of the system's performance issue. Hence, we requested the BASIS team investigate the system for the particular period and observe why some of the days' activation took longer than usual. The following are their findings.

The screenshot below shows the CPU performance at the DB server level. It shows the CPU contention state where it went to idle time.

CPU Performance Details from DB Server

 

Further, BASIS shared the top listed SQL order by CPU time. It is clearly interpreted that the CPU engaged its maximum time on the deleted statement of the RSODSACTDATA table.

Top Listed CPU Handled SQL Statements

Final Observation: 

The above-shared details helped us narrow down and pinpoint the issue further. The long-running activation is because the CPU is stuck in the deletion process to delete the entries of the RSODSACTDATA (The RSODSACTDATA table is a buffer table to keep the records temporarily during the activation process) records.

With the help of BASIS, we investigated further on table/ index definition applied in the RSODSACTDATA and observed that Index Scanning is defined with Fast Full Scan mode.

Current Definition of the Table Index

Solution Applied: 

Upon SAP suggestion, we have implemented the S-Note 1020260 that helps change the Index definition from the Fast Full Scan to Unique Scan. The change in Index definition, given remarkable performance improvement to the DSO activation process. Since then, we have completed the entire system DLM jobs on time.

The screenshot shows the Index Definition of post S-Note implemented.

Definitions of the Table Index Post Deployment of S-Note

System Behavior upon Postfix :

The screenshot below shows the CPU performance. It worked fine and didn’t find any CPU idle time.

Postfix - CPU Performance Details from DB Server

The screenshot below shows the DSO activation time comparison for a few DSOs. It is evident that the system is back on track, and all the loads are completed on time. Please refer to the column 11th Dec.

Postfix - DSO Activation Performance Comparison

 

Conclusion: 

Through this Blogpost, I concluded that table index definition is paramount for system performance behavior, particularly for the common process (Like DSO Activation) across the system, rendering the same table to complete the process. I hope this Blogpost may give some thought process to analyze the issue that occurs more broadly (across the system) and how to reach out to the root of the problem and fix the same.