Solved

Oracle Server Recommendations

  • 6 April 2021
  • 3 replies
  • 144 views
T
Rank
Userlevel 3
Badge +8

Under FSM6OnPremiseInstallation.pdf we can see the below recommendation. 
Oracle Server Recommendations
- Each month, run the script fsm_?_IndexRebuilder.sql where ? represents the current version.

 

We are running FSM 6.1 with an Oracle database. 

1) What are the benefits we can get by running it and what are the downsides if it is not run every month?
2) What are the precaution (or pre requisites) that should be  taken care of before running the script? Do we need some downtime before executing the script?
3)This script is also dropping the primary keys and recreating them. What are the risks?

icon

Best answer by Lee Pinchbeck 7 April 2021, 10:41

View original
This topic has been closed for comments

3 replies

Lee Pinchbeck
Rank
Userlevel 7
Badge +24

Hi @TDCSOURABH,

Rebuilding of indexes on fragmented data tables is a standard DB maintenance process that should be performed regularly.

This should be done when no-one is accessing the data so usually these are setup to run automatically overnight on a particular schedule.

When not run, fragmentation of the data tables will grow which will cause the speed of the database transactions to slow down. Eventually this can cause an unworkable state due to the slow down.

As this is a standard process there is little risk in running these, just make sure it is done at a time when no-one is accessing the data tables.

Kind regards,

Lee Pinchbeck

Currently reading: The Way of Kings - Brandon Sanderson
T
Rank
Userlevel 3
Badge +8

Thanks for the response. 

  1. Is there a way we can find out if the indexes are unworkable and needs rebuilding ? 
  1. We are running FSM almost 24/7 , there are always some TASKs created every hour (automatic/maintenance orders). So it is hard to get a timeslot where no one is accessing the data tables.  So in such a scenario executing this script on a running system can create inconsistent data (especially when the primary keys are dropped and recreated).
Lee Pinchbeck
Rank
Userlevel 7
Badge +24

Hi @TDCSOURABH,

Whilst we can give guidance on re-indexing with regards to its affect on FSM it is a database function and so this should be referred to Oracle for any specifics on how it can be monitored and rectified beyond what has been provided here.

The Oracle guidance on this is here: https://docs.oracle.com/cd/E57185_01/HFMAD/apbs09s06s04.html

One absolute is that you should not run this whilst users are in the system. You would need to schedule regular downtime for this is the system is running 24/7.

Kind regards,

Lee Pinchbeck

Currently reading: The Way of Kings - Brandon Sanderson
Terms & ConditionsCookie settings