in a R/3 4.7 on Aix 5.3 ML11 with Oracle 18.104.22.168 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:
"MANDT" = :A0
"MANDT" , "KAPPL" , "KSCHL" , "VKORG" , "KUNNR" , "KONDA" , "MATNR" , "KFRST" , "DATBI"
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:
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
UNIQUE Index A977~0
Column Name #Distinct
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.
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.
>Can you check the statistics for your tables AXXX in db20
Oggetto database A977
Tipo oggetto DB 01
Intestatario oggetto DB
Tipo utilizzo A
Indicatore attivo U
Data di modifica 22.05.2009
Metodo analisi E
Dim. campione P10
Flag 'Storico' X
Data modifica TODO 10.02.2001
Tempo es. stim. 00:00:00
DBSTACT for A977 table..
Database object A977
DB object type 01
Owner of DB object
Type of usage A
Active Flag U
Changed on 22.05.2009
Analysis method E
Sample Size P10
History flag X
TODO change date 10.02.2001
Estimated runtime 00:00:00
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.
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 ...)