This is really a simple one and you would be surprised how many companies large and small overlook this.
I have seen examples of sites with over 50 million rows in the fnd_concurrent_requests_u1 indexes; a huge waste of space! More importantly however is the problem of poor performance in such a core OEBS activity e.g. 10 concurrent manager processes running with a 30 second sleep time equates to 28,800 hits on your fnd_concurrent_requests table and its indexes (this does not include your users submitting requests and checking if their requests have completed). Get the picture why this is so important.
As part of regular maintenance practices you should rebuild your indexes on routinely purged objects on a regular basis, especially the indexes on fnd_concurrent_requests.
Want to know if you need to rebuild your indexes? The following article includes everything you need to know together with code and there is also a TOAD report. These can be found on the PIPER-Rx “tips” page: http://www.piper-rx.com/pages/tips.html
Rebuilding indexes in an OEBS environment - A guide to rebuilding indexes in an OEBS environment
View and Download associated TOAD report (TRD format) - Report id - FDBA002-10
Also remember some OEBS application monitors provide alerting to remind you when your indexes may need maintenance.
Happy Rebuilding J