Lis most used indexes

[Former Member]

Dear all,

I would like to rebuild most used indexes from my server ( SAP on Oracle ). I am quite new in this, is there any way to check somehow most used indexes that can be rebuilt ?

Any advice is welcomed,

Thanks,

J.

[volker_borowski2 · Accepted solution]

Hi,

why would you like to do so?

I recommend to do a google search for several Articles/Blogs from Richard Foote concerning this topic.

Yes, rebuilding indexes can help in some cases, but you should not do this just for the fun of it, and

"most used" is certainly the worst criteria to be chosen, when to decide which one to rebuild.

There are several others that would be more relvant.

Volker

[Former Member · Accepted solution]

Hi Volker,

I did not know that most used indexes are not a criteria. Thanks for sharing that.

I just went to ST04 in SAP, and under Segments, I listed most used indexes and my selction criteria was to rebuild them.

I 'll try to read mr. Foote's articles. Also if you have any other ideas, do not hesitate to share them.

Thanks in advance,

J.

[volker_borowski2 · Accepted solution]

Hi,

Well these are not "my" ideas, and when doing the mentioned research you'll find quite good explanations. You will also find blogs descibing the complete oposite opinion. Evaluate the arguments on your own then (But I think Richard is ahead mostly):

- rebuilding indexes of tables that get frequent DML (update/insert) might be bad, because a rebuild will squeeze out required freespace to serve these operation. So theses operations might then require additional block-splits, thus suffering.

- rebuilding, when you did not delete quite couple of records from the table will not (or mostly not) reduce the blevel, thus a coalesce is less expensive to regain the freespace. Might be difficult to track when you are on the edge of getting to the next blevel anyway due to amount of data.

- Most likely rebuilding will lead to changed stats in the next stats-run (# of blocks, # avg. keys per leafblock, ...). This can lead to different execution plans which must not allways be what you want.

Example: I am doing service for a system that is doing massive parallel operation on ABAP Level. There are quite a couple of operations where I do not like to have "the best" plan for a DB statement, because when being executed on 100 workprocesses, you might get into trouble because of lack of ressources. This might be shortage on memory and/or TEMP when the DB decides for 100 hash joins instead of 100 nested loops. Yes, for the single statement the HJ is better (we measured), but when running on 100 WPs you can not do anything else in the system, and we have other operations utilizing additional procs in parallel. So we faked stats to turn the hashjoin into a nested loop. This might not be a common case, but it might be releavant in some cases. Rebuilding indexes related to this statement is always a nightmare, because it might turnover plans.

But there are no general rules. If you decide to rebuild, measure, and if it helps, ok, good job.

But do not rebuild without measureing and evaluating.

Volker

[Former Member · Accepted solution]

Thanks Volker,

Cleared my mind, and changed my plans I will let you know what have been done after the investigation.

Have a good day,

J,