Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2013

    Unanswered: runstats and OLTP

    Hi All,
    Anyone has any idea how's runstats run in OLTP ? Cause there's no maintenance window for the runstats to run. any implication is auto_maint=ON and auto_runstats=ON ?

    For your information, the database using is in AIX 7.1


  2. #2
    Join Date
    May 2003
    You can do runstats while applications are running in an OLTP environment. Obviously, it is best to do it during non-peak hours, but it does not require an application outage. However, I cannot speak for auto-runstats since I don't use it and would not recommend it.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Feb 2012
    I'm also not in favor of automatic runstats. I like to be in control, but maybe that's my burden to bare ;-)
    At my company we used to collect statistics every day for all tables at non peak hours. We have a stored procedure that executes the runstats on all tables. But because it's quite useless to collect statistics when there has been no change to the table (insert, updates,deletes) I've extended the procedure to look at a 'data change percentage' and then (configurable) execute runstats or not. Currently I'm executing runstats when 15% of the table has changed. This improved the execution time of the runstats of all tables and collects statistics on those tables that can have different cardinality after the changes. Automatic runstats also uses such a threshold of data change percentage, I thought it uses about 20%. I've written a blog on this, to read it visit: Runstats on data change percentage - Berts Blog
    Runstats can be executed online without applications suffering much from it. It does have to scan the whole table (if you don't have any sampling options in your runstats command). For very large tables (> 50 mln row) I prefer sampling. Another thing I would like to point to you as a hint is when a table is changed a lot (huge inserts/updates/deletes) and it's in the same tablespace as a lot of other tables runstats execution can take up a lot of time. I used to have a table that contained about 50 mln records a was changed for about 60% every day. Runstats execution took about 1 hrs. I decided to move the table to a separate tablespace and the runstats execution went down to 30 seconds!

  4. #4
    Join Date
    Dec 2011
    Centurion, South Africa
    You can prepare a scell/perl script to do runstats on table(s) and schedule it during the non-peak hours :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts