Optimizing....

[Ganimede-Dignan]

from Oracle session I see a seq read...

ACTIVE db file sequential read SAPR3 SELECT FROM "MKPF" T_00 , "MSEG" .......

If I see explain plan I see:

SELECT STATEMENT ( Estimated Costs = 59 , Estimated #Rows = 1 )

5 FILTER

5 NESTED LOOPS

5 NESTED LOOPS

5 MERGE JOIN

5 TABLE ACCESS BY INDEX ROWID MARC

INDEX RANGE SCAN MARC~Y

5 SORT JOIN

5 TABLE ACCESS BY INDEX ROWID T024D

INDEX RANGE SCAN T024D~0

5 TABLE ACCESS BY INDEX ROWID MSEG

INDEX RANGE SCAN MSEG~M

5 TABLE ACCESS BY INDEX ROWID MKPF

INDEX UNIQUE SCAN MKPF~0

The select is:

SELECT

T_00 . "BUDAT" , T_00 . "MBLNR" , T_00 . "MJAHR" , T_01 . "BWART" , T_01 . "LGORT" ,

T_01 . "MATNR" , T_01 . "MBLNR" , T_01 . "MEINS" , T_01 . "MENGE" , T_01 . "MJAHR" ,

T_01 . "WERKS" , T_02 . "DISPO" , T_02 . "MATNR" , T_02 . "WERKS" , T_03 . "DISPO" ,

T_03 . "DSNAM" , T_03 . "WERKS"

FROM

"MKPF" T_00 , "MSEG" T_01 , "MARC" T_02 , "T024D" T_03

WHERE

( T_01 . "MANDT" = :A0 AND T_01 . "MBLNR" = T_00 . "MBLNR" AND T_01 . "MJAHR" = T_00 . "MJAHR" )

AND ( T_02 . "MANDT" = :A1 AND T_02 . "MATNR" = T_01 . "MATNR" AND T_02 . "WERKS" = T_01 .

"WERKS" ) AND ( T_03 . "MANDT" = :A2 AND T_03 . "DISPO" = T_02 . "DISPO" AND T_03 . "WERKS" =

T_02 . "WERKS" ) AND T_00 . "MANDT" = :A3 AND T_00 . "BUDAT" = :A4 AND T_00 . "MJAHR" = :A5 AND

T_01 . "BWART" = :A6 AND T_01 . "LGORT" = :A7 AND NOT ( T_01 . "MATNR" LIKE :A8 OR T_01 . "MATNR"

LIKE :A9 ) AND T_01 . "WERKS" = :A10 AND T_02 . "DISPO" BETWEEN :A11 AND :A12 AND T_02 . "DISPO"

<> :A13#

Ho can I optmize .... in previus day it's no so slow... I chack statistics ra up-to-date with today.

Regards.

Ganimede Dignan.

[former_member251078]

Hello,

Could you please create the histogram as well as ensure that your DBSL patch level is upto date as given below.

Furthermore, you must create histograms with Transaction DB21 and method "EH" "ACTIV=U in DBSTATC" for the MKPF and MSEG database tables. For more information, see Note 106047.

Execute the BRCONNECT program with the following parameters:

brconnect -u / -c -f stats -t MKPF -f collect

brconnect -u / -c -f stats -t MSEG -f collect

To ensure that these hints work correctly, you must have the

following

patch levels:

Oracle lib_dbsl: 6.40, Patch >= 115 or

Oracle lib_dbsl: 7.00, Patch >= 47

For more information, see Note 902042.

If you have Release 6.40 or lower, you can ignore these

specifications.

Hope this helps

Regards

Dileep

[Former Member]

If it is SAP standard program, contact them, they will guide you. It is a good practice to run dbstats on all the tables using brconnect -f stats ... Check the documentation

Also you can change the Sampling in DBSTATC table. The higher the sampling, better the performance.

-AJ

[Former Member]

Hi Ganimede,

two comments from my side.

1.) Questions regarding optimization should be asked in the ABAP Performance forum instead of the Oracle forum.

2.) Are you sure that all statistics of all table (index and table statistics) are up to date on all four tables. I assume that at least the index on index MARC~Y is not up to date.

Best regards

Ralph Ganszky

[Ganimede-Dignan]

.

[fidel_vales]

Hi,

This is a good forum to ask about tuning a SQL statement (if the database is oracle, of course)

Refreshing statistics is a typical approach that could solve nothing.

It is important that statistics are representative, not that they are "new"

The higher the sampling, better the performance.

3 things could happen if this is done:

1) performance is better

2) performance is worse

3) performance does not change.

Given the current information, any suggestion is a wild guess.

You have a join between 4 tables, that you mention it comes from SAP code (but you provide the conditions, probably)

We do not know how many rows each of your tables have, we do not know any of the statistics of your indexes (used or not used)

We do not have any good information to do any proper analysis.

Calculating statistics could help, or not.

Next time, provide more information if you want proper help and not wild guesses.

[Former Member]

Hi!

Check the indexes for tables "MKPF", "MSEG", "MARC" and "T024D".

Is it possible to change an existing index or makes it sense to create a new index in order to optimize the execution of this SQL statement. But be careful with changing existing indexes. Other programs using that index may slow down ...

Regards,

Volker