Skip to main content
Solved

Oracle Server Recommendations

T
Sidekick (Customer)
Forum|alt.badge.img+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?

Best answer by Lee Pinchbeck

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

View original
Did this topic help you find an answer to your question?
This topic has been closed for comments

3 replies

Lee Pinchbeck
Ultimate Hero
Forum|alt.badge.img+24
  • Lee Pinchbeck
  • Ultimate Hero
  • 1219 replies
  • Answer
  • April 7, 2021

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
Sidekick (Customer)
Forum|alt.badge.img+8
  • TDCSOURABH
  • Author
  • Sidekick (Customer)
  • 27 replies
  • April 7, 2021

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
Ultimate Hero
Forum|alt.badge.img+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

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings