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
