Poor performance 0CO_OM_NWA_1 and 0CO_OM_NAE_1
Hi guys ,
Both extractors have very very poor performance during extraction to BW (7.0 but using 3.x DS). RSA3 has the same issue. without selection it takes about 8min to retrieve 90 records.
Typical package size during load is 5000-8000. AFAIK this size is set by SAP and can't be changed.
I think the problem is the r/3 AFVC table which is about 1.500.000 records big. SQL trace shows that a query on this table runs the longest.
An index is created to meet these selections but the costs for this query shrinks only by a small amount.
The query that takes the longest:
SELECT "OBJNR" , "VORNR" , "WERKS" , "WAERS" , "AUFPL" , "APLZL" , "PROJN" , "SUMNR" , "OWAER" FROM "AFVC" WHERE "MANDT" = :A0 AND "AUFPL" IN ( :A1 , :A2 , :A3 , :A4 , :A5 , :A6 , :A7 , :A8 , :A9 , :A10 , :A11 , :A12 , :A13 , :A14 , :A15 , :A16 , :A17 , :A18 , :A19 , :A20 , :A21 , :A22 , :A23 , :A24 , :A25 , :A26 , :A27 , :A28 , :A29 , :A30 , :A31 , :A32 , :A33 , :A34 , :A35 , :A36 , :A37 , :A38 , :A39 , :A40 , :A41 , :A42 , :A43 , :A44 , :A45 , :A46 , :A47 , :A48 , :A49 , :A50 , :A51 , :A52 , :A53 , :A54 , :A55 , :A56 , :A57 , :A58 , :A59 , :A60 , :A61 , :A62 , :A63 , :A64 , :A65 , :A66 , :A67 , :A68 , :A69 , :A70 , :A71 , :A72 , :A73 , :A74 , :A75 , :A76 , :A77 , :A78 , :A79 , :A80 , :A81 , :A82 , :A83 , :A84 , :A85 , :A86 , :A87 , :A88 , :A89 , :A90 , :A91 , :A92 , :A93 , :A94 , :A95 , :A96 , :A97 , :A98 , :A99 , :A100 ) AND "SUMNR" = :A101 SELECT STATEMENT ( Estimated Costs = 105 , Estimated #Rows = 6 ) 5 3 INLIST ITERATOR 5 2 TABLE ACCESS BY INDEX ROWID AFVC ( Estim. Costs = 105 , Estim. #Rows = 6 ) Estim. CPU-Costs = 2.188.228 Estim. IO-Costs = 105 1 INDEX RANGE SCAN AFVC~Z01 ( Estim. Costs = 101 , Estim. #Rows = 6 ) Search Columns: 3 Estim. CPU-Costs = 2.137.832 Estim. IO-Costs = 101 Access Predicates
Setting in table BWOM_SETTINGS are set to NOOBJPRESEL.
Does anyone has an idea what else I could try?
Edited by: Matthias Gamsjager on Sep 19, 2008 10:30 AM
I was working on simillar cases. recommendation provided in SAP Note 821763, mostly resolve the issue. Please have a look into the following SAP Notes as well. Apply the notes if any of the this note is applicable in your case.
Test it in your test environment and let us know the result. If you are still facing performance issue with these loading process create OSS message in 'SV-BO-BI' for further analysis.
Hope this helps