Too many plants existing in SAP causing db choose wrong index

[Former Member]

Hi Expert,

There're about 130 plants in our SAP. From last month, there're SQL running timeout dumps sometimes.

When I trace the log I found that the DB tends to choose index of 'WERKS'(plant) to find data.

for example,

select xxxxx

   from vbap

   inner join vbak on vbak~vbeln = vbap~vbeln

   into corresponding fields of table xxxxx

   where vbak~erdat in s_erdat

       and vbap~werks eq p_werks.

For there is SO data for about 5 years in our SAP. So choosing the index of vbak-erdat is preferable, what is the DB did before last month.

This month I found the DB tends to choose the index of vbap-werks so that causing SQL timeout.

Are there some solutions?

I cannot use the key work 'HINT', for there are mass number of reports, queries involved, from vbap-werks to ekpo-werks,

from marc-werks to mseg-werks.

What is worse, we may involve 40 more plants next year...

[Former Member]

Hi,

I guess very simple

VBAK is having the secondary index on ERDAT ( index name is : ERD ).

select VBELN FROM VBAK INTO TABLE IT_VBAK WHERE ERDAT IN S_ERDAT.

SELECT VBELN,XXXX from VBAP INTO TABLE IT_VBAP FOR ALL ENTRIES IN IT_VBAP

                 WHERE VBELN = IT_VBAK-VBELN AND

                               WERKS  = P_WERKS.

So, you will get the sales order relevant to the plant

Rg, Kiran

[nabheetscn]

Hi

First please check in Se11 what all various index you have

Nabheet

[Former Member]

Thanks nabheet

In my situation, SAP has both indexes for vbak-erdat and vbap-werks. The DB chooses to index vbap-werks and it comes out with SQL timeout.

[nabheetscn]

Hi Xu

If you go through this note 129385 - Database hints in Open SQL it clearly states that

"Complex database statements and/or a large number of very similar secondary indexes make it difficult for a database optimizer and the R/3 database interface to find the most efficient processing."

I believe HINT is the way or if at database level we can put i am not aware of.

Please read this note you will get more hints

Nabheet

[Former Member]

Hi nabheet,

I'm afraid that HINTS will not work in my situation, for there are large number of reports, queries involved.

Maybe this problem is beyond the scope of abap, but owe to the DB optimizer.

[kiran_k8]

Nabheet,

Can you please let me know what do you mean by HINT.

Thanks.

K.Kiran.

[nabheetscn]

Hi

Using hint syntax we are forcing the Database to use particular index while running the query. Please search on SCN you will find many links

Nabheet