7 Replies Latest reply: Nov 17, 2010 9:42 PM by Ganimede Dignan RSS

VA01, degraded performance, severla seq read for AXXX table

Ganimede Dignan
Currently Being Moderated

Hi,

 

in a R/3 4.7 on Aix 5.3 ML11 with Oracle 9.2.0.8 there are some performance issue with VA01 tcode. We have tracerd, during sales order creation, all sql transaction. We see that lot of time is for sequential read for some AXXX table:

 

Exec time is first colum:

 

  11.921.706  TRANSP   A977    

   7.413.735  TRANSP   A904    

   2.526.080  TRANSP   A913    

     566.205  VIEW     ATP_RESB

     468.535  TRANSP   A675    

 

as example A977 has this explain:

 

SQL Statement

 

SELECT

  *

FROM

  "A977"

WHERE

  "MANDT" = :A0

ORDER BY

  "MANDT" , "KAPPL" , "KSCHL" , "VKORG" , "KUNNR" , "KONDA" , "MATNR" , "KFRST" , "DATBI"

 

 

Execution Plan

 

 

 

 

SELECT STATEMENT ( Estimated Costs = 24.712 , Estimated #Rows = 1.660.739 )

 

        2 SORT ORDER BY

          ( Estim. Costs = 24.711 , Estim. #Rows = 1.660.739 )

          Estim. CPU-Costs = 2.793.835.385 Estim. IO-Costs = 24.262

 

            1 TABLE ACCESS FULL A977

              ( Estim. Costs = 3.193 , Estim. #Rows = 1.660.739 )

              Estim. CPU-Costs = 840.936.182 Estim. IO-Costs = 3.058

 

 

Full Table scan... mhmmmm.... statistics are super updated:

 

 

Table   A977

 

Last statistics date                  16.11.2010

Analyze Method               Sample 126.643 Rows

Number of rows                         1.660.739

Number of blocks allocated                20.140

Number of empty blocks                       339

Average space                              1.473

Chain count                                    0

Average row length                            77

Partitioned                                   NO

 

 

UNIQUE     Index   A977~0

 

Column Name                     #Distinct

 

MANDT                                          1

KAPPL                                          2

KSCHL                                          5

VKORG                                         16

KUNNR                                      1.697

KONDA                                         34

MATNR                                     51.997

KFRST                                          1

DATBI                                        369

 

End the "where" is the same of index A977~0 fields... why it si a full table scan? How can I optimized it?

 

For each test we have stop/start R/3 istance and Oracle.

 

Regards.

Actions