Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

VA01, degraded performance, severla seq read for AXXX table

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.

replied

What do you expect with that SQL request?

SELECT * FROM "A977" WHERE "MANDT" = :A0

your only WHERE clause is the client (MANDT). It is normal that all the table will be read, especially if you only use 1 productive client. No index will be helpful in that case.

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question