Table MSEG has two indexes containing a single key. Why? Can I drop them?

[Former Member]

I'm a novice to the SAP world but an old hand at managing Oracle databases.

I'm looking to improve the performance of our large ERP system running SAP 4.6C and have found several indexes that have a cardinality of 1. The worst is the MSEG table which has indexes MSEG_{P and MSEG}R each containing a single key (candinality of 1). The table has 110 million rows so, these level 3 indexes take up several GB of space on disk and in the buffer pool - all to store 110 million occurances of the same 1 value. This configurtion is a waste of resources and I'd like to drop them, but would really like to know why they exist in the first place.

Can anyone help?

[stefan_koehler · Accepted solution]

Hello Chip,

i had a look at our very old 4.6c ERP system and have found only the MSEG~R index.

This index is for the reservations of materials and it includes the fields:

MANDT and RSNUM.

If i execute the following query to determine the selectivity in our test system (with really not many data):

SQL> select count(*) from SAPSR3.MSEG;
  COUNT(*)
----------
     98948

SQL> select DISTINCT(RSNUM) from SAPSR3.MSEG;
....
....
422 rows selected.

select count(*) from SAPSR3.MSEG group by RSNUM;
=> 97604

So one value is existing 97604 times and the field has 422 different values.. maybe some histograms are useful... but the index itself looks "logical good".

So i don't really understand your problem... how did you determine the cardinality?

Is the index really so big or are there many freed blocks (for example deleted leaf entries,etc..)?

Have you made an ANALYZE to check the structure (but be careful .. not really an online command)?

ANALYZE INDEX <SAPSID>."MSEG~R" validate structure offline;
select * from index_stats;

or maybe an index btree dump is helpful.

Regards

Stefan

[Former Member · Accepted solution]

Yes, my client is running an ancient version of SAP and is in the process of replatforming and upgrading the hardware in preparation for upgrading the software to Netweaver. I am doing my best to aid their efforts using my database expertise to help tune where it appears slow, but I am a novice when it comes to SAP.

I determined that that the MSEG_{P index (on columns MANDT, PS_PSP_PNR,MAT_PSPNR) and the MSEG}R index on columns (MANDT, RSNUM) have a single key (have cardinality of 1) by selecting a count of the distinct values. e.g.

SELECT mandt ,rsnum ,COUNT(*)

FROM sapr3.mseg

GROUP BY mandt, rsnum;

This query (and the one like it for MSEG_{P) returned a single value with over 110 million rows. The current optimizer stats (in dba_indexes) show MSEG}P to be a 3 level Btree with 783740 leaf blocks. The MSEG~R index is a 3 level Btree with 658530 leaf blocks.

When I did a single threaded full table scan on this table, it took about 1 hour. The ANALYZE INDEX VALIDATE STRUCTURE statement will acquire an exclusive table lock and I really don't want to lock the table on this 7x24 system.

Since these poor quality indexes can do nothing to improve the performance of a query and will only slow DML while consuming disk space and polluting the buffer pool, I suggested dropping these pigs. The Basis manager suggested that I enquire into why these indexes would exist if they were of such low quality - hence this posting. I suspect that my client's specific implementation simply does not have the variability in the data elements represented in these indexes to make them worthwhile for <u>this</u> site. Perhaps other installations use a different data mix that would make them valuable.

Insight into your experience with these indexes is appreciated and seems to reinforce my suspicions on data variability. It also appears that your MSEG table is about 3 orders of magnatude smaller than ours. Perhaps this size differnece points to an opportunity as well. Any additional insight you can share will also be appreciated.

[stefan_koehler · Accepted solution]

Hello Chip,

>> The ANALYZE INDEX VALIDATE STRUCTURE statement will acquire an exclusive table lock and I really don't want to lock the table on this 7x24 system.

I know - this is why i said please do an index btree dump - this is not locking the table and you will see how many leaf entries are used.

I can only make statements to the MSEG~R index, because i don't have any ~P index in my system.

>> Insight into your experience with these indexes is appreciated and seems to reinforce my suspicions on data variability. It also appears that your MSEG table is about 3 orders of magnatude smaller than ours. Perhaps this size differnece points to an opportunity as well

As i said this was our testsystem.. in our productive system this is one of the biggest tables.

But please let's clarify something about the index and the table:

1) How often is the index used? (please query V$SEGMENT_STATISTICS)

2) Please do the index btree dump to have a closer look at the index tree

3) Please have a look at sapnote #921165

4) Have you some performance problems with SAP standard transactions according to that index? (what does the explain plan show)

5) If the index is used in MB51 for example, then create histograms on that columns.

Regards

Stefan

[Former Member · Accepted solution]

Hi Chip

In addition to Stefans good comments just my to cents on your case. I think you are completely right on the cardinality issue. I observed similar issues in our systems. It is mostly overlooked that these indexes are a waste of space and slowing down dml operations on tables. Even worse they might lead to wrong execution plans in extreme scenarios.

There is one exeption when even such an index might be great. If you find querys with <i>where rsnum = X</i> when X does not exist. So you will be getting no rows very quickly. But this is rare.

You should not drop the index on the database without deactivating it in the Abap dictionary (transaction SE14).

Best regards, Michael

EDIT: one more add, you should speak with your basis/apps people about archiving (or deleting old data), if you don't do already, as far as i can remember MSEG is covered in archive objects MATBEL.

Message was edited by:

mho

[Former Member · Accepted solution]

another add: i just checked on one of our systems, it seems we have over 1000 indexes with only one distinct key :(((

Of course, most of them are empty and not doing any harm, but we also have some which certainly hurt us a lot. With this SQL you can list all indexes with only one key ordered by size.

SQL> select i.index_name, s.bytes/1024/1024 "SIZE_MB" from dba_indexes i, dba_segments s where i.index_name = s.segment_name and I.DISTINCT_KEYS = 1 order by SIZE_MB asc

INDEX_NAME                        SIZE_MB
------------------------------ ----------
EKKO~S                                264
MARC~NFM                        1171.3125
VBKD~LOC                             1222
EKPO~SRV                             2558
MARC~STD                             2695

1160 rows selected.

Regards Michael

[stefan_koehler · Accepted solution]

Hello Michael,

i would not rely on DISTINCT_KEYS of ALL_INDEXES or DBA_INDEXES - this value is collected if you gather statistics.

I have seen some situations on big tables, which were sampled with a low percentage value... so that this value does not really fit to the reality.

You are right in the most cases its near the reality but it is no 100 percentage guarantee.

Only for completion.

Regards

Stefan

[Former Member · Accepted solution]

Hi Stefan

Thanks, good point. I would never rely only on statistics data. But it is a quick way to scan for potential targets. Before i deactivate an index i would always:

- Check if there really is no other value at all times. Always recheck with apps/dev people as well.

- Check if there are no statements running against that index

However, even if one misses something here and things start to go wrong, the index can be recreated rather quickly then.

Regards, Michael

[Former Member]

mho;

I hadn't thought of the situation where the programs wants to find 0 rows quickly - that is a good catch. I will have to look for these tests for nonexistence - but to be a true test for non-existence it would also have to specify (and rsnum is not null).

The advise to deactivate the index in the Abap dictionary (transaction SE14) before dropping the index is most cogent.

The query:

SELECT mandt ,rsnum ,COUNT(*) 
FROM sapr3.mseg
GROUP BY mandt, rsnum;

returns 1 row containing a count over 110 million, so discussions of dumping the Btree for analysis are vacuous and a waste of time.

Creating histograms do not make the situation any better. With histograms, you would then have a very large Btree with a cardinality of 1 together with one hash bucket containing a count of all rows.

The reason you never want to force the use of this poor index (excepting the test for nonexistence of couse) is that you would be doing a full table scan the hard way - by ping ponging back and forth from index to table to index to table for the entire table. This approach causes good data to get pushed out of the buffer cache to make room for junk. This ping pong approach would yield 2Xrow_count+btree_depth logical reads. Additionally the time required to do all of this reading on a 110000000 row table in a busy system will ensure that much undo would need to be read in order to ensure read consistentcy, necessitatating yet more logical reads. A full table scan would use multiblock IO (db file scattered read) to scoop up several datablocks at a time, processing them one datablock at a time and flushingthem from the buffer pool as quickly as they get read.

So, if you average 200 rows/data block, then the index access of all of these rows would cost an average of 400 logical reads per datablock while the full scan access would be 1 logical read per datablock.

I really don't want the system to be full scanning this table regularly - either the easy way (full table scan) or the hard way (with the bad index).

The existence of bad indexes like these two pigs is actually a harder problem to solve (to both find and fix) than finding and fixing a "missing" index. Often developers and administrators don't really understand how indexes work and mistakenly believe that index access is always more efficient that a full table scan. This misunderstanding makes it difficult to sell the idea of dropping a bad index. If you propose such a heretical idea, they stare at you with a hairy eyeball and make rude gestures behind your back << grin >>

Thank you all for your insight and experience,

Chip

[stefan_koehler]

Hello Chip

at first ... have you checked the segment statistics? Is the index really used often?

You are making an analysis without knowing if this index is ever used in an execution (and in the execution plan) - are you facing performance problems with some transactions?

>> Creating histograms do not make the situation any better. With histograms, you would then have a very large Btree with a cardinality of 1 together with one hash bucket containing a count of all rows.

Have you read sapnote #921165 that i mentioned? Of course histograms would make the situation better (if the index is used in the execution plans).. SAP knows about this problem and has insert a literal hint in some of their standard programs to benefit of the histogram data.

Regards

Stefan