MON_GET_AUTO_RUNSTATS_QUEUE table function - Retrieve information about objects queued for evaluation

Syntax

>>-MON_GET_AUTO_RUNSTATS_QUEUE--()-----------------------------><

The schema is SYSPROC.

Authorization

Default PUBLIC privilege

None

Example

Display all objects that are currently under evaluation by automatic statistics collection in the currently connected database.

SELECT QUEUE_POSITION,
       OBJECT_TYPE,
       OBJECT_STATUS,
       VARCHAR(OBJECT_SCHEMA, 10) AS OBJECT_SCHEMA,
       VARCHAR(OBJECT_NAME, 10) AS OBJECT_NAME
FROM TABLE(MON_GET_AUTO_RUNSTATS_QUEUE()) AS T
ORDER BY QUEUE_POSITION ASC

The following is an example of output from this query.

QUEUE_POSITION OBJECT_TYPE OBJECT_STATUS        OBJECT_SCHEMA OBJECT_NAME
-------------- ----------- -------------------- ------------- -----------
             1 TABLE       JOB_SUBMITTED        TEST          EMPLOYEE
             2 TABLE       EVALUATION_PENDING   TEST          T1
             3 TABLE       EVALUATION_PENDING   TEST          BLAH

3 record(s) selected.

Usage notes

The MON_GET_AUTO_RUNSTATS_QUEUE table function returns information about all objects queued for evaluation by automatic statistics collection in the currently connected database. Objects queued for evaluation will be examined by automatic statistics collection to determine if a statistics update is required. When an object is found to require statistics update, a job is submitted to the automatic maintenance scheduler and evaluation temporarily blocks until the job completes execution. Use the MON_GET_AUTO_MAINT_QUEUE interface to monitor the progress of the statistics collection job. After an object has been evaluated by automatic statistics collection is removed from the evaluation queue, it will not be added to the queue again until the next automatic statistics collection evaluation interval. For a per-object history of evaluation, refer to the HEALTH_DB_HIC interface.

Each database has a separate automatic statistics collection evaluation queue. The MON_GET_AUTO_RUNSTATS_QUEUE reports the evaluation queue for the currently connected database.

The list reported by the MON_GET_AUTO_RUNSTATS_QUEUE interface may be empty if there are no objects currently queued for evaluation by automatic statistics collection. For example, if automatic statistics collection has processed all the tables that were queued for evaluation in the current evaluation interval, the list of objects queued for evaluation will be empty until the next evaluation interval. Automatic statistics collection evaluation intervals occur roughly every two hours.

The list reported by the MON_GET_AUTO_RUNSTATS_QUEUE interface is also empty if automatic statistics collection is not enabled.

Information returned