VA01, degraded performance, severla seq read for AXXX table

[Ganimede-Dignan]

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.

[former_member204746 · Accepted solution]

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.

[fidel_vales]

Hi,

There is a little point that seem to be missing here.

The query you show seems to be a buffer reload.

If you go to SE11 I'm quite sure that you see that the table is fully buffered.

So the question stops being "Oracle" and pass more to the "basis" area.

How may times you see this query in the trace?

Why is being reloaded?

may be the table is too big

may be the buffer is too small

may be the table is being modified too many times.

Depending on those there may be different solutions (increase the buffer size, unbuffer the table or ...)

[Former Member]

According to the values of the primary index, column mandt contains just one distinct value.

So this select statement is basically requesting all entries from the table...

In that case a full table scan makes sense.

You could try to run a analyze index <index name> for all indexed columns. It could be that the statistics are not generated correctly.

Kind regards,

Mark

[Former Member]

First I suggest, you may aware but must plan for your oracle upgrade ASAP as 9.2 is not supported (only Sustaining Support).

Can you check the statistics for your tables AXXX in db20. When you restart your SAP and DB your cache will clear and you will have performance issues until your cache again fill.

[Ganimede-Dignan]

>Can you check the statistics for your tables AXXX in db20

Oggetto database A977

Tipo oggetto DB 01

Intestatario oggetto DB

Sistema DB

Tipo utilizzo A

Indicatore attivo U

Cluster D_PP

Data di modifica 22.05.2009

Significanza 3

Metodo analisi E

Dim. campione P10

Indic. TODO

Flag 'Storico' X

Data modifica TODO 10.02.2001

Tempo es. stim. 00:00:00

Indic. cliente

Regards

[Former Member]

can you please paste the log in English?

[Ganimede-Dignan]

DBSTACT for A977 table..

Database object A977

DB object type 01

Owner of DB object

Database System

Type of usage A

Active Flag U

Cluster D_PP

Changed on 22.05.2009

Significance 3

Analysis method E

Sample Size P10

TODO flag

History flag X

TODO change date 10.02.2001

Estimated runtime 00:00:00

Customer flag

.