Skip to Content
0
Former Member
Sep 18, 2008 at 01:43 PM

Poor performance 0CO_OM_NWA_1 and 0CO_OM_NAE_1

51 Views

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