Skip to Content

BW Fine Tuning on Netweaver BW 730 – Part 2”

In “BW Fine Tuning on Netweaver BW 730 – Part 1” I provide the DSO activation performance improvement in BW730 based on my testing on both BW701 and BW730 environment.

In Part2 I will continue provide some DSO activation fine tuning tips based on the DSO activation testing on pure BW730 environment.

The test scenario is same as below:

  1. Load 1 million records to DSO object as delta request, which have no overlap with previous delta init request.
  2. Activate the single 1 million record delta request in both BW701 and BW730 environment with same DSO activation settings.

    a. DataPackage Size: 50,000

    b. Parallel Degree: 5

    c. Run Mode: Batch Mode

    d. No SID generation during activation

    e. No unique record

    f. Total Volume: 1 million records 

However in my next test approaches, DataPackage size and activation parallel degree is changed to compare the DSO activation runtime for different settings in BW730. Below is the test result for different DSO activation DataPackage size and parallel degree.

Test round

DataPacakge Size

ParallelDegree

1

20000

5

2

20000

8

3

50000

5

4

50000

8

Below is my DSO activation tests result

 

On first impress for the test result, I am quite surprised on DSO activation job performance regarding large DataPackage. With same parallel degree 5, test round 3 DSO activation job took 176 seconds for 50,000 DataPackage size. However for 20,000 Datapacakge size and same 5 parallel degree, DSO activation only used 160 seconds.

The main difference for test round 1 & 3 is on how DSO Active Queue extract main job run with writing child jobs. Take ABAP trace for detail explanation:

Test Round 1: DataPackage Size 50000, Parallel Degree 5

Child job ABAP trace

  

“Select …FOR ALL ENTRIES” Statement took 10 seconds to process 50000 records. Array insert into change log and active tables took 8 seconds.

Test Round 3: DataPacakge Size 20000, Parallel Degree 5

Child Job ABAP trace

  

“Select…FOR ALL ENTRIES” statement took 4.6 seconds to process 50000 record lookup. Array insert into change log and active table took 4.4 seconds.

From child job performance trace result, the child job SQL statement runtime is linearly increased with increased record number. The main difference for this 2 test running is on the behavior of Active queue main job.

Test Round 1 Main Job ABAP Trace

 

Test Round 3 Main Job ABAP Trace

 

Active queue table (/BIC/AZDSOTEST40) fetch throughput is similar for both tests running. The only difference is on the difference that main job waiting for child writing job to be released for next writing task. In ABAP trace, function “CALL ALERTS” runtime reflect this waiting difference for both test running.

With 20000 DataPackage size, main job spent 95 seconds on waiting free child jobs. With 50000 DataPackage size, main job spent 113 seconds on waiting free child jobs. Because for both tests running, parallel degree is only set with 5, which means at most 4 child jobs can be initiated for writing tasks.

On test round 2 & 4, when parallel degree increased to 8, more background work process can be used for child writing job, the DSO activation runtime reduced mainly on 50% less main job waiting time.

Based on above tests running, the DSO activation tuning approach should follow below sequence:

  1. Make sure normal tuning approaches have been applied. For example uncheck “generate SID during DSO activation”.
  2. Increase parallel degree as first priority to reduce DSO activation main job waiting time.
  3. If parallel degree increase still cannot improve DSO activation as expected, then reduce DataPackage size to let DSO activation child writing job take less runtime. In addition the DSO activation main job waiting time will also be reduced on waiting time slots.

Finally compared with BW701 same data volume DSO activation, the final DSO activation job reduced from 309 seconds to 139 seconds (55% improvement) with below changes:

  1. Utilize of SAP BW730 mass lookup for DSO active table. (43% improvement)
  2. Reduce DataPackage size from 50000 to 20000. (2% improvement)
  3. Increase parallel degree from 5 to 8. (10% improvement)

On large scale BW system with huge DSO data volume and more ABAP instances, DSO activation job performance can gain much more improvement with BW730 DSO new features.

 

At last you can also refer below SAP Notes to improve DSO activation job on DB6 database:

  • SAP Note 1515687: DB6: Performance optimization DSO data activation
  • SAP Note 1421011: DB6: Speed up selection from DSO activation queue (DB2 V9.1)

 

In general please follow below SAP Notes to calculate Datapacakge size and parallel degree:

  • SAP Note 1567541P27:DSO: Poor performance when you start request activation
  • SAP Note 1392715: DSO req. activation:collective perf. problem note
  • SAP Note 1599602: FAQ: BW system performance
To report this post you need to login first.

7 Comments

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

  1. Marc Bernard
    Nice blog, Xiaofan. Your next test should be BW on HANA, which activates DSO data in the memory of HANA instead of the ABAP application server. Would be great if we could get the same scenario tested for comparison.

    Best,
    Marc

    (0) 
    1. Xiaofan Wu Post author
      Hi Mark,

      In case I have change to play with HANA based BW system, I will run DSO comparison test to see how fast HANA system will looks like.

      Thank you for your comment.

      Michael Wu
      CoE Technology BW
      SAP America

      (0) 
        1. Bala Prabahar
          Hi Xiaofan,

          IMO-correct me if I’m wrong – BW on HANA that is going be available in Nov, is probably just another database. In other words – based on Juergen’s comment in one of blogs and your comment – the activities we’ll perform on HANA would not be any different from those we perform on non-HANA db. The performance may be slightly better due to all-memory processing; however ABAP layer caching etc would still be relevant; and other application design layers (PSA, ODS, CUBE, Aggregates) would also be relevant, correct?

          In addition, in BWA, we don’t need aggregates except for failover situation. However in HANA db, depending on business requirements, we may need to build aggregates, correct?

          May I say these are four(I’m sure there are more) differences between BWA and BW on HANA?

              1) Aggregates (I already explained the difference)
              2) BWA uses columnar whereas BW on HANA DB would use RDBMS format? or Hybrid?
              3) BWA needs another layer(TREX/Python?) for monitoring and administration whereas HANA doesn’t need another layer(Reduction of Layers?). Non-HANA DB layer would be replaced by HANA DB layer.
              4) BWA has additional options in RSA1; would look and feel of RSA1 be different between BW on non-HANA and BW on HANA? I guess look and feel of DB-centric tcodes such as St04 etc may be different.

          Thanks,
          Bala

          (0) 
          1. Xiaofan Wu Post author
            Hello Bala,

            Very good question!!!

            Based on current SAP BI/BW/HANA roadmap, I would say in year 2011 HANA in SAP BW landscape is working as database. So the HANA database in BW landscape will more work as a virtual (im memory) DataMart.All BW critical objects still build on HANA database as triditional way. In the future HANA road map, SAP HANA will be a new application to act as same as SAP BW from SAP Data Warehouse side. This should explain the question on current SAP HANA with SAP BW.

            In current SAP HANA/BW landscape, if BW build on  HANA database, then all traditional behavior has no change on logic side. But definitely everything will be much faster than BW on traditional database. For some BW ABAP related operation, for example BW OLAP runtime calculation and DTP start routine calculation, HANA database will not help since most runtime still at ABAP side.

            Regarding your questions on BWA and HANA,I would say in HANA 1.0 SAP only provide BWA as in-memory accelerate engine. In current  HANA 1.5 solution, there is no BWA box in BW landscape. From my understanding, BWA is replaced with HANA in-memory database or secretly “renamed” as HANA database since BWA 7.20 already support aggregate precalcualtion and DSO handling. I do not think aggregate still necessary for BW query since all Cube/DSO transaction records already in memory database.

            Regards,

            Michael Wu
            SAP America.

            (0) 

Leave a Reply