Db2

 View Only
  • 1.  How often runstats should be run

    Posted Thu November 23, 2023 10:33 AM

    Hello,

    suppose we have 900 million rows in our table. Each day about 200 000 rows wil be added. Their keys are unique, but not sequential. 

    How often should we ran runstats to keep statistics up to date?

    Regars

    Matthias



    ------------------------------
    Matthias Gress
    ------------------------------


  • 2.  RE: How often runstats should be run

    IBM Champion
    Posted Thu November 23, 2023 01:22 PM

    As you need it - when you need it, but:

    1. Consider impact (including locking on system catalog)
    2. Consider activity on the table
    3. Consider VOLATILE in table definition and implication of using VOLATILE
    4. Consider AUTOMATIC RUNSTATS and define maintenance windows in policy settings



    ------------------------------
    Jan Nelken
    ------------------------------



  • 3.  RE: How often runstats should be run

    Posted Mon November 27, 2023 03:25 PM

    Hello everyone,

    we have had good experiences with autorunstats, not least because of the number and size of tables we have to maintain.

    kind regards
    Matthias Engelland




  • 4.  RE: How often runstats should be run

    Posted Thu November 23, 2023 05:47 PM

    Howzit Matthias?

    I'd suggest rarely unless you are using RUNSTATS profiles and collecting column distribution stats

    I'd let DSNACCOX be your guide in terms of percentages but absolute values are meaningless with large tables like this. See Figure 6. DSNACCOX formula for recommending RUNSTATS on a table space
    From RTS use ((STATSINSERTS + STATSDELETES + STATSUPDATES) × 100) / TOTALROWS > SRTInsDelUpdPct -- IBM Recommendation is 20%

    We only collect stats on indexes when doing tablespace stats (and/or REORG) so they remain consistent i.e. REORG INDEX -- Do NOT collect stats!

    Cheers
    Bruce



    ------------------------------
    Bruce Williamson
    ------------------------------



  • 5.  RE: How often runstats should be run

    IBM Champion
    Posted Fri November 24, 2023 01:03 AM

    I am with Bruce here and even more radical really... Our Runstats Avoidance has rules about super size tables that states for over 100 Million rows a RUNSTATS is a real waste of CPU. After all, what are the chances of the Db2 optimizer choosing a different index or acces path with so much data? A table of that size is only being accessed by index anyway. In my personal experience about 95% of all RUNSTATS are not required. Naturally in-line stats for LOAD and REORG are different and also when DDL changes happen (ALTER ADD COLUMN, New Index etc etc) but your average run-of-the-mill RUNSTATS is mostly irrelevant these days.



    ------------------------------
    Roy Boxwell
    Senior Software Architect
    Software Engineering GmbH
    Duesseldorf
    +4921196149675
    ------------------------------



  • 6.  RE: How often runstats should be run

    IBM Champion
    Posted Mon November 27, 2023 03:29 PM

    Hi, My turn - I am with Bruce and Roy.  However, RUNSTATS plays a key role in several performance monitoring products in order to reflect performance for the current  database. It is also one of the main mechanisms for updating the Db2 Catalog, which the optimizer uses to choose access paths. 

    Let's look at a practical, but notable use case:

    An INDEX is added to a "large" table in a database to improve the performance of an SQL statement that reads it and RUNSTATS is not used nor any other Catalog update utility (for example, CATMAINT).  You knew this was a problem access path because you ran an APM tool that captured and reported on the execution of the resource consuming SQL statement.   The table is used in a batch job for production during the nightly batch process window.  Prior to the addition of the INDEX, the batch window was starting to jeopardize the start up of the on-line subsystems (CICS/Db2/IMS, etc.) because its SQL statement had extremely poor performance. The addition of the INDEX (done first in the TEST environment, of course) proved to significantly reduce the processing time for this table in the batch JOB.  

    If the Catalog is not updated, the optimizer will not choose the new INDEX (in our case) and the batch JOB will run as before, with an old access path, thus quasi nullifying the benefit of the new INDEX.  The APM tool is run with the batch JOB's execution, and also shows that the new INDEX was not used.  In the meantime, the DBA is feeling very comfortable because he/she "fixed" the issue. 

    Now most production systems are astute enough to "always" rerun RUNSTATS (or equivalent) when INDEXES are added to a table, so this means regardless of the size of the table, updating the Catalog is a requirement - so for this Matthias' question, RUNSTATS needs to be run, or an equivalent.  But for the shops that are not as careful as they should be, omitting the RUNSTATS because there is this HUGE table, would cause confusion at the very least (why isn't the new INDEX working - and the APM says so) and another long batch run cycle.

    So, if you can avoid running RUNSTATS but still update the Db2 Catalog, then take that route.  If not, then run RUNSTATS.



    ------------------------------
    Thomas Halinski
    ------------------------------



  • 7.  RE: How often runstats should be run

    IBM Champion
    Posted Fri November 24, 2023 06:01 AM

    Hi Matthias,

     

    Far back in the mists of ancient time, when I was a DBA, my "rule of thumb" was to run runstats each time the table grew by 10%.

    Now, you can just ask Db2 itself whether it thinks the table needs runstats by running the stored procedure DSNACCOX to query the real time stats tables SYSTABLESPACESTATS and SYSINDEXSPACESTATS.

     

    If you have BMC DASD Manager installed, you can build triggers that generate actions each time a threshold is reached, e.g. REORG after data set extents is reached, image copy after % rows changed, or stats after % rows added/deleted.

     

    Regards,

    Marcus Davage CEng CITP MBCS

    Lead Product Developer
    Intelligent Z Optimization and Transformation
    BMC Software Ltd
    Email marcus_davage@bmc.com

    signature_1567851823A pair of brown wings  Description automatically generated

     






  • 8.  RE: How often runstats should be run

    Posted Mon November 27, 2023 12:03 PM

    In my previous position, I had a transactional database that had multiple 100+Million row tables (several were 1billion+ rows). 
    We ran runstats once a week and never had problems. The key was to process them with sampling, rather than against the entire table. The runstats complete MUCH quicker with sampling and, in my case, zero issues with response times.



    ------------------------------
    Douglas Kostelnik
    ------------------------------



  • 9.  RE: How often runstats should be run

    Posted Tue November 28, 2023 06:22 PM

    Hi Douglas

    IMHO The frequency of your RUNSTATS is over the top and a waste of resources unless you have extremely volatile tables i.e. data changes exceeding 20% weekly. And if that is the case, then you're probably setting the VOLATILE flag on the table in the catalog and resetting statistics on tables/indexes unless you have multiple indexes/table.

    Do you use RUNSTATS PROFILEs and/or column distribution stats? How does sampling affect them?

    Cheers
    Bruce



    ------------------------------
    Bruce Williamson
    ------------------------------