The MON_GET_AUTO_RUNSTATS_QUEUE table function returns information about all objects which are currently queued for evaluation by automatic statistics collection in the currently connected database.
None
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.
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.
Column Name | Data Type | Description |
---|---|---|
OBJECT_TYPE | VARCHAR(8) | objtype - Object type monitor element |
OBJECT_SCHEMA | VARCHAR(128) | object_schema - Object schema monitor element |
OBJECT_NAME | VARCHAR(128) | object_name - Object name monitor element |
MEMBER | SMALLINT | member - Database member monitor element |
OBJECT_STATUS | VARCHAR(18) | Status of queued object. One of: EVALUATION_PENDING - Automatic statistics collection needs to look at the object to determine if a statistics update is required. JOB_SUBMITTED - Automatic statistics collection has determined that a statistics update is required for an object and has submitted a job to the automatic maintenance scheduler. Automatic statistics collection is waiting for the job to complete. |
JOB_SUBMIT_TIME | TIMESTAMP | If the status is JOB_SUBMITTED, the time at which the automatic statistics collection job was submitted to the automatic maintenance scheduler. NULL otherwise. |
QUEUE_POSITION | INTEGER | Indicates the position of the job in the automatic statistics collection evaluation queue. |
QUEUE_ENTRY_TIME | TIMESTAMP | Time that the job was added to the automatic statistics collection evaluation queue |