If you are having performance issues, of if you have done intensive data archiving or technical cleanup in your system running on Oracle, you need to consider the Oracle statistics. Without proper statistics the performance of your system will be sub-optimal or even bad.
Questions that will be answered in the blog are:
- How to run Oracle index statistics update?
- How to use the update as part of technical cleanup?
Oracle index statistics update
To run the Oracle index statistics update, go to transaction SE38 and start program RSANAORA:
To redo the statistics of an index fill out the table and index in the format: table~index. In this example the main index (0) of table BALDAT is chosen: BALDAT~0.
Run the update and wait for it to be finished:
Repeat this for every index defined in your system (lookup in SE11, button Indexes).
Runtime will depend on the amount of entries in the table and the type of infrastructure. Test on acceptance system before running in productive server.
Index run for almost empty tables
If your table is empty or almost empty you can also run RSANAORA with the option Alter index rebuild online. This will speed up read performance for that table index.
Regular runs
If you have setup regular technical clean up jobs as explained in this blog, you can opt to schedule a last step in the clean up job the update of the statistics program RSANAORA. This will ensure best performance while it will hardly cost you time.
Index monitoring
Oracle has a function called index monitoring to check if indexes are used at all. You can use it to delete non-used indexes. See OSS note 105047 – Support for Oracle functions in the SAP environment.
One thought on “Oracle statistics update”