on 11-17-2010 11:19 AM
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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 ...)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
>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
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
.
User | Count |
---|---|
87 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.