MRP performance - reorg of huge RESB table sorted on RESB~M index columns

[Former Member]

I received recommendation to do reorganisation of SAP table RESB with sort by index RESB~M leading columns(material_number MATNR and plant WERKS). RESB table is 16GB in size with approx 20 milion records.

It is true that most expensive SQL statements running against RESB table use RESB_{M index and I assume that these queries would benefit of doing it. I am concerned what negative impact it may have on other queries that are using RESB}0 index? Can you help me create test case that I can run in acceptance environment and use to sign off this recommendation before I implement it in production.

[stefan_koehler · Accepted solution]

Hello Andrija,

the answer to all your question is: It depends.

> It is true that most expensive SQL statements running against RESB table use RESB~M index and I assume that these queries would benefit of doing it.

Check the segment statistics to see the i/o part:

select OBJECT_NAME, STATISTIC_NAME, VALUE  from v$segment_statistics where OBJECT_NAME like 'RESB%' and STATISTIC_NAME in ('logical reads', 'physical reads');

The cpu, sort and other "expensive" things are only visible at statement or session level.

> I am concerned what negative impact it may have on other queries that are using RESB~0 index?

The clustering factor is maybe worse, so the optimizer maybe choose another execution plan and so on...

> Can you help me create test case that I can run in acceptance environment and use to sign off this recommendation before I implement it in production.

Copy the prod system to quality and test this with the queries that maybe benefit from (take a look at ST04).

If you receive that as a recommendation, then you can maybe ask for the queries that maybe take an advantage from that action and rerun that ones.

Regards

Stefan

[Former Member]

The bad clustering of index RESB~M and the related performance degradation is the most common clustering factor problem in SAP environments. So it is very likely that you will also benefit from an improvement of the clustering factor (although a closer look at V$SQL_PLAN is necessary to verify this assumption).