2095707 - SAP Work Manager SAP Mobile Add-On for ERP - Bad Performance after Activation of BC set

• Issue with: SMERP 610_700 Support Package 03
• After activation of BC sets of SAP Mobile Add-On for ERP, the system performance has been serverly degraded
• It created a lot of High Cost CPU SQL Statements (CPU-Costs = 106,806,177,066) and this is affecting the server drastically

The SQL statement is:

SELECT T_00."AUFNR"
FROM "AFKO" T_00 INNER JOIN "AFIH" T_01 ON T_01."MANDT"=:A0
AND T_01."AUFNR"=T_00."AUFNR" INNER JOIN "A FVC" T_02 ON T_02."MANDT"=:A1 AND T_02."AUFPL"=T_00."AUFPL"
WHERE T_00."MANDT"=:A2 AND T_01."IPHAS"=:A3 AND T_02."EQUNR"=:A4

When processing the SQL statement, it returned and scan the full table of 23 million records. It also peform many join commands.

~~~~~~~~~~~~Performance Issue SQL Query~~~~~~~~~~~~~~~~~~~~~

SQL Statement
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT
  T_00."AUFNR"
FROM
  "AFKO" T_00 INNER JOIN "AFIH" T_01 ON T_01."MANDT"=:A0 AND T_01."AUFNR"=T_00."AUFNR" INNER JOIN "A
  FVC" T_02 ON T_02."MANDT"=:A1 AND T_02."AUFPL"=T_00."AUFPL"
WHERE
  T_00."MANDT"=:A2 AND T_01."IPHAS"=:A3 AND T_02."EQUNR"=:A4


Execution Plan

Explain from gv$sql_plan: Address: 0700001214DA0A58 Hash_value:  2075311349 Child_number:  0 Instance_ID: 2
Sql_id: dh5uz7pxv5g7p  Parse Timestamp: 20140824   16:52:46

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 SELECT STATEMENT ( Estimated Costs = 304,453 , Estimated #Rows = 0 )
  |
  ---   9 HASH JOIN
      |   ( Estim. Costs = 304,452 , Estim. #Rows = 6,175,290 )
      |   Estim. CPU-Costs = 124,320,409,230 Estim. IO-Costs = 272,056
      |   Memory Used KB: 13,014,016
      |   Access Predicates
      |
      |--   7 HASH JOIN
      |   |   ( Estim. Costs = 81,466 , Estim. #Rows = 3,231,488 )
      |   |   Estim. CPU-Costs = 11,825,774,728 Estim. IO-Costs = 78,384
      |   |   Memory Used KB: 11,035,648
      |   |   Access Predicates
      |   |
      |   |--   2 TABLE ACCESS BY INDEX ROWID AFIH
      |   |   |   ( Estim. Costs = 26,909 , Estim. #Rows = 3,231,488 )
      |   |   |   Estim. CPU-Costs = 1,316,484,366 Estim. IO-Costs = 26,566
      |   |   |
      |   |   ------1 INDEX SKIP SCAN AFIH~K
      |   |           ( Estim. Costs = 3,405 , Estim. #Rows = 3,231,488 )
      |   |           Search Columns: 2
      |   |           Estim. CPU-Costs = 652,812,434 Estim. IO-Costs = 3,235
      |   |           Access Predicates Filter Predicates
      |   |
      |   ---   6 VIEW index$_join$_001
      |       |   ( Estim. Costs = 41,897 , Estim. #Rows = 14,282,700 )
      |       |   Estim. CPU-Costs = 6,829,834,553 Estim. IO-Costs = 40,118
      |       |
      |       ---   5 HASH JOIN
      |           |   Memory Used KB: 1,058,370,560
      |           |   Access Predicates
      |           |
      |           |-----3 INDEX RANGE SCAN AFKO~0
      |           |       ( Estim. Costs = 11,485 , Estim. #Rows = 14,282,700 )
      |           |       Search Columns: 1
      |           |       Estim. CPU-Costs = 2,558,198,998 Estim. IO-Costs = 10,818
      |           |       Access Predicates
      |           ------4 INDEX FAST FULL SCAN AFKO~1
      |                   ( Estim. Costs = 13,195 , Estim. #Rows = 14,282,700 )
      |                   Estim. CPU-Costs = 819,165,934 Estim. IO-Costs = 12,982
      |                   Filter Predicates
      |
      ------8 TABLE ACCESS FULL AFVC
              ( Estim. Costs = 206,154 , Estim. #Rows = 23,803,210 )
              Estim. CPU-Costs = 106,806,177,006 Estim. IO-Costs = 178,322
              Filter Predicates

~~~~~~~~~~~~~~~~~~end~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The SQL is triggered by a report with a namespace which starts with /SYCLO/C********.

• SAP Work Manager 6.0
• SAP Work Manager 6.1
• SAP Mobile ERP Add On (SMERP 610_700 Support Package 03)

"SAP Work Manager performance issue after activating BC set", "SMERP mobile add-on performance issue", "SAP note to fix SAP Work Manager performance" , KBA , MOB-SYC-SAP , Syclo Mobility for SAP backend , Problem