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
------------------------------
Original Message:
Sent: Fri November 24, 2023 01:03 AM
From: Roy Boxwell
Subject: How often runstats should be run
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
Original Message:
Sent: Wed November 22, 2023 08:59 AM
From: Matthias Gress
Subject: How often runstats should be run
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
------------------------------