Skip to Content
0
Nov 17, 2010 at 11:19 AM

VA01, degraded performance, severla seq read for AXXX table

70 Views

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.