MSEG Table performance issue

[Former Member]

hi Team,

We have 640 GB size of MSEG table running on oracle 11g database.

But this is taking longer time in fetch and having slow performance.Below is the SQL query being used in program

SELECT mjahr mblnr zeile budat_mkpf vgart_mkpf
werks lgort kokrs kostl
matnr bwart grund menge
meins dmbtr waers "SAP-5367
APPENDING CORRESPONDING FIELDS OF TABLE t_docitems
FROM mseg
FOR ALL ENTRIES IN it_matnrpack
WHERE matnr = it_matnrpack-matnr
AND budat_mkpf EQ wa_budmat-budat
AND werks EQ p_werks
AND bwart IN s_bwart
AND lgort IN s_lgort
AND kostl IN s_kostl.

Index is being used in this query but having fileds MANDT,MATNR,WERKS,GRUND and LIFNR.

Please suggest if any improvements is there which can be improve performance.

[raymond_giuseppi]

I hope you have checked that the internal table used in the FOR ALL ENTRIES clause is not empty.

Do you have the possibility to either

• Replace the FOR ALL ENTRIES with a JOIN or a SUBQUERY with the query that extracted the list of item numbers
• Split the list of items into smaller packages and either execute them in sequence (better use of memory) or in parallel (Select data in a RFC FM called DESTINATION 'NONE').
• Check with basis for performance of FOR ALL ENTRIES (Oracle hints in emergency situations) such as system parameters : rsdb/max_blocking_factor, rsdb/max_in_blocking_factor, rsdb/prefer_join, rsdb/prefer_union_all.
• Check if index MSEG~BUD is active and statistics up-to-date
• In very rare situations (many report have problems) creation of specific indexes.

Does your organization consider upgrading Oracle version, what's your SAP version ?