2
  1. Is there any frequency at which i should run REORG and RUNSTATS on my master tables?
  2. If i have a daily load of around 1,000,000 records into my table would it be suggestible to run a REORG and RUNSTATS everyday after the Load?
  3. Are there any drawbacks to REORG and RUNSTATS?

I understood what REORG and RUNSTATS does from the links here. But could not conclude on the Impact it might have on Overusing or Underusing it.

2

This is one of those where the correct answer is: it depends. It all depends on your environment, on your data. What you can and should do, is test how frequently you need to run them in lower environments. Play with them under load/stress. Figure out which options you may need to use.

But to give you something to start with, and not knowing the distribution of your data, I would go with RUNSTATS daily. Maybe REORG once a week. Now, depending on when your LOAD occurs, I would consider running it after the LOAD. LOAD bypasses a lot of things. You want to know the number of rows and the distribution of the data in the table(s) in order to have efficiently running queries. REORG is to clean up lost space and improve I/O. Perhaps less important after LOAD (depending on table fragmentation).

Both RUNSTATS and REORG acquire locks of sorts, so again, that is where you want to test in lower environments. If you can, run them after hours for zero impact. If you need to run them during the day, play with the options to see what you can do. The systems I happened to work on allowed for full RUNSTATS and full offline REORGs during the day. But we didn't have the volume of transactions others have.

Hopefully I've given you some thoughts to consider and a place to start with. The (final) answer differs for everyone.

  • I would suggest that you execute RUNSTATS when necessary: When a statistically significant portion of the data has changed. Loading 1M rows/day into a table likely does not necessitate daily RUNSTATS, especially after the table has over 10M rows. – Ian Bjorhovde Jan 4 '16 at 18:51
  • @IanBjorhovde - I would agree. However if you are a shop where you are small or are an "accidental/junior" dba without a lot of policies, I would fault on the side of RUNSTATS once a day (especially if you have a nightly maintenance window). Its better than not at all. But if you have the scripting skills to figure out when to run them, then I agree that it is best to run them only when necessary. – Chris Aldrich Jan 4 '16 at 19:19
  • Hence the question, Are there any Side Effects of running it daily ?? My Application has a downtime of 7-8 hours daily when the data is Pushed. Asides i don't notice much difference in performance if i run it daily but if it does not run for a few days(a week approx) there is an impact on the performance. – rrrohanb Jan 5 '16 at 4:12
  • those two things right there (7 - 8 hour outage window daily) and impact on performance if not run, tells me you should run them daily then. :) – Chris Aldrich Jan 5 '16 at 13:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.