Performance issue with transaction MC50

[knighttommy]

I am not sure where to post this question. If I need to post it in another forum, please let me know.

We are having performance issues with transaction MC50. After reviewing SAP Note 457615 we created an index on mseg with the following fields: MANDT, MJAHR, MATNR, WERKS and SOBKZ.

When running an explain on the sql statement, the database is using a different index. This index has the following fields MANDT, MATNR, WERKS, LGORT, BWART and SOBKZ.

The sql statement is ( sql trace from ST05):

SELECT * from mseg WHERE "MANDT" = '400' AND "MJAHR" BETWEEN 2009 AND 2009 AND "MATNR" = '000000000054001121' AND "WERKS" = 'SAT' AND "SOBKZ" IN ( 'K' , 'V' , 'W' , 'O' , ' ' )

Is there any way to force the database to use the newly created index.

Thanks....Tommy

Edited by: Tommy Knight on Dec 8, 2009 2:24 PM

Edited by: Tommy Knight on Dec 8, 2009 3:07 PM

Edited by: Tommy Knight on Dec 8, 2009 3:08 PM

[volker_borowski2 · Accepted solution]

Hi,

MATNR is a high cardinality column on MSEG

and MJAHR is very low cardinality.

You specifiy MATNR with a single value and

MJAHR with a range, so it is ok for the optimizer to choose

the high cardinality column with EQUAL.

If this is your average selection (a single MATNR),

change the index to

MANDT,MATNR,WERKS,SOBKZ,MJAHR

which should go for three equal columns,

an inlist iteration on SOBKZ and a RANGE SCAN on the last column.

But first, try to specify a range for MATNR, if this does

suit your average selection more. Even

MATNR between '123' and '123' may help to change the plan.

Hope this helps

Volker

[knighttommy]

Changing the order of the field in the index resolved the issue. The order for our index needed to be MANDT, MATNR, WERKS, MJAHR and SOBKZ,.

Thanks for the help.

Tommy

[knighttommy]

The DBA ran statistics on the table and indexes after the index was created. We just completed running DB20. It had no effect. The explain of the sql is still pointing to the wrong index.

Thanks....Tommy

[stefan_koehler]

Hello Tommy,

at first your database release and patchset is missing.

If you are using Oracle 10g, the advice of Peter is useless, because of statistics are automatically collected by a CREATE INDEX.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_5010.htm#i2075657

COMPUTE STATISTICS
In earlier releases, you could use this clause to start or stop the collection of statistics on an index. This clause has been deprecated. Oracle Database now automatically collects statistics during index creation and rebuild. This clause is supported for backward compatibility and will not cause errors.

The other thing is - are you really sure that this SQL statements was executed with literals?

I know that on MSEG (with histograms) it is recommended sometimes, but i just want to be sure.

If you want us to help you - we need much more information .. please check sapnote #1257075 and upload the information to a webhosting platform, so that we can take a look at it.

Regards

Stefan

[knighttommy]

The dba ran the optimizer on the the table and index. Is this the same as running update statistics? The database is Oracle.

Edited by: Tommy Knight on Dec 8, 2009 4:03 PM

[peter_dzurov]

I would force creating statistics with tcode DB20, enter that newly created index name and click create statistics (CTRL + F2).

[peter_dzurov]

Have you done "update statistics" for that newly created index? Try to do it and then observe which index is used.