Statistics are essential for query optimizer to generate an optimal access plan. The runstats utility collects necessary statistics and optimizer makes use of these statistics during query compilation. In this blog, I would like to briefly describe different ways in which a user can collect statistics.
User can execute the runstats command manually or through admin command with appropriate option to collect statistics on a particular table and/or index. Refer to the following link to know various options supported under runstats to gather table and index statistics.
Deciding which statistics to collect for a workload is complex, and maintaining up-to-date statistics is time consuming. Out-of-date or incomplete table or index statistics might lead the optimizer to select a sub optimal plan, which slows down query execution.
To overcome this situation in version V8.2 we introduced a concept called automatic table maintenance, in short we address this as auto runstats.This feature is enabled by default in which, database manager will take care of maintaining up-to-date statistics for tables automatically.
Auto runstats evaluation happens at every 2 hrs,5 min of time interval. It examines each table under active database to consider for statistics collection . If table statistics are missing or stale it will decide to collect statistics during the next maintenance window. By default the maintenance window will be set to 24x7. User has the privilege to set maintenance window depending on the system workload. One can also control statistics collection by setting maintenance policy. For example, If you want to exclude nicknames from automatic statistics collection process, then you can set a policy to accomplish the task.
User can always deactivate auto runstats by setting db cfg parameter AUTO_RUNSTATS=OFF.
RTS (Real Time Statistics):
This feature was first introduced in version v9.5. It will collect statistics synchronously at statement compilation time. This feature is enabled by default when you create a database. Query optimizer determines how statistics are collected based on query need and table activity (amount of update/insert/delete operation). RTS collects statistics during statement compilation there by providing more timely and accurate statistics for the optimizer which results in better query execution plan.
RTS collects statistics via synchronous/fabrication and make it available immediately to optimizer by updating it in statistics cache.
RTS daemon evaluate every 5 min of time interval and process any pending request from previous runstats activity Ex: Writing statistics cache value to disk or collecting statistics asynchronously.
User can always deactivate RTS by setting db cfg parameter AUTO_STMT_STATS=OFF
learn more about automatic table maintenance process (Auto runstats and RTS) in the following link.
Statistics collection via 'CREATE INDEX' command:
User can collect index statistics during 'CREATE INDEX' by specifying 'COLLECT STATISTICS' option.
Statistics collection via 'LOAD' command:
User can collect statistics during 'LOAD' by specifying 'STATISTICS USE PROFILE'. To collect statistics via LOAD, one has to create a profile using runstats command before executing LOAD. To know more on how to create runstats profile refer to the link mentioned under 'Manual runstats'.
Statistics collection via 'REDISTRIBUTE' command:
Similar to LOAD command, user can collect statistics via 'REDISTRIBUTE' by specifying 'STATISTICS USE PROFILE' option. User should first create a runstats profile before executing REDISTRIBUTE command to collect statistics.