Oracle 19c New Feature High-Frequency Statistics

The automatic optimizer statistics collection job which calls DBMS_STATS package runs in predefined maintenance windows and these maintenance windows are open once a day during which various jobs including the gathering of statistics is performed.

For volatile tables statistics can go stale between two consecutive executions of such automatic statistics collection jobs. The presence of stale statistics could potentially cause performance problems because the optimizer is choosing sub-optimal execution plans.

The new feature introduced in Oracle 19c called High-Frequency Automatic Optimizer Statistics Collection complements the standard automatic statistics collection job.

By default, the high-frequency statistics collection occurs every 15 minutes and as such there is less possibility of having stale statistics even for those tables where data is changing continuously.

The DBMS_STATS.SET_GLOBAL_PREFS procedure is used to enable and disable the high-frequency statistics gather task as well as change the execution interval (default 15 minutes) and the maximum run time (60 minutes).

Let us see an example of using this new Oracle 19c feature.

We can see that the statistics for the MYOBJECTS_19C table are stale and we now use the DBMS_STATS.SET_GLOBAL_PREFS procedure to enable the high-frequency statistics gathering at 5 minute intervals.
 

SQL> select stale_stats from user_tab_statistics where table_name='MYOBJECTS_19C';

STALE_S
-------
YES

SQL> EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_INTERVAL','300');

PL/SQL procedure successfully completed.

 
We can query the DBA_AUTO_STAT_EXECUTIONS data dictionary table to get information on the status of the daily standard automatic statistics execution job.We can see that during the week days the job runs during the maintenance window which is in the night and the weekend maintenance window is during the day instead.
 

SQL> SELECT OPID, ORIGIN, STATUS, TO_CHAR(START_TIME, 'DD/MM HH24:MI:SS' ) AS BEGIN_TIME,
       TO_CHAR(END_TIME, 'DD/MM HH24:MI:SS') AS END_TIME, COMPLETED, FAILED,
       TIMED_OUT AS TIMEOUT, IN_PROGRESS AS INPROG
FROM  DBA_AUTO_STAT_EXECUTIONS
ORDER BY OPID; 

 OPID ORIGIN		   STATUS      BEGIN_TIME     END_TIME	     COMPLETED FAILED TIMEOUT INPROG
----- -------------------- ----------- -------------- -------------- --------- ------ ------- ------
  659 AUTO_TASK 	   COMPLETED   10/06 23:00:50 10/06 23:02:02	   569	    2	    0	   0
  681 AUTO_TASK 	   COMPLETED   11/06 00:10:58 11/06 00:11:20	   296	    2	    0	   0
  684 AUTO_TASK 	   COMPLETED   11/06 00:20:59 11/06 00:21:11	    62	    2	    0	   0
  687 AUTO_TASK 	   COMPLETED   11/06 00:31:00 11/06 00:31:04	    43	    2	    0	   0
  690 AUTO_TASK 	   COMPLETED   11/06 00:41:01 11/06 00:41:05	    46	    2	    0	   0
  693 AUTO_TASK 	   COMPLETED   11/06 00:51:02 11/06 00:51:05	    44	    2	    0	   0
  699 AUTO_TASK 	   COMPLETED   11/06 01:01:04 11/06 01:01:12	   148	    2	    0	   0
  702 AUTO_TASK 	   COMPLETED   11/06 01:11:05 11/06 01:11:08	    43	    2	    0	   0
  705 AUTO_TASK 	   COMPLETED   11/06 01:21:06 11/06 01:21:08	    31	    2	    0	   0
  708 AUTO_TASK 	   COMPLETED   11/06 01:31:07 11/06 01:31:10	    39	    2	    0	   0
  711 AUTO_TASK 	   COMPLETED   11/06 01:41:09 11/06 01:41:12	    39	    2	    0	   0
 1045 AUTO_TASK 	   COMPLETED   12/06 22:00:09 12/06 22:02:47	   644	    1	    0	   0
 1085 AUTO_TASK 	   COMPLETED   13/06 22:00:03 13/06 22:02:09	   467	    1	    0	   0
 1125 AUTO_TASK 	   COMPLETED   15/06 08:23:50 15/06 08:25:46	   362	    1	    0	   0

14 rows selected.

 
After about 5 minutes have elapsed if we run the same query again, we can another ‘AUTO_TASK’ statistics job running and this is the high-frequency statistics gathering job.

We can also see that the table which earlier had statistics reported as stale has now had fresh statistics gathered.
 

SQL> SELECT OPID, ORIGIN, STATUS, TO_CHAR(START_TIME, 'DD/MM HH24:MI:SS' ) AS BEGIN_TIME,
       TO_CHAR(END_TIME, 'DD/MM HH24:MI:SS') AS END_TIME, COMPLETED, FAILED,
       TIMED_OUT AS TIMEOUT, IN_PROGRESS AS INPROG
FROM  DBA_AUTO_STAT_EXECUTIONS
ORDER BY OPID; 

 OPID ORIGIN		   STATUS      BEGIN_TIME     END_TIME	     COMPLETED FAILED TIMEOUT INPROG
----- -------------------- ----------- -------------- -------------- --------- ------ ------- ------
  659 AUTO_TASK 	   COMPLETED   10/06 23:00:50 10/06 23:02:02	   569	    2	    0	   0
  681 AUTO_TASK 	   COMPLETED   11/06 00:10:58 11/06 00:11:20	   296	    2	    0	   0
  684 AUTO_TASK 	   COMPLETED   11/06 00:20:59 11/06 00:21:11	    62	    2	    0	   0
  687 AUTO_TASK 	   COMPLETED   11/06 00:31:00 11/06 00:31:04	    43	    2	    0	   0
  690 AUTO_TASK 	   COMPLETED   11/06 00:41:01 11/06 00:41:05	    46	    2	    0	   0
  693 AUTO_TASK 	   COMPLETED   11/06 00:51:02 11/06 00:51:05	    44	    2	    0	   0
  699 AUTO_TASK 	   COMPLETED   11/06 01:01:04 11/06 01:01:12	   148	    2	    0	   0
  702 AUTO_TASK 	   COMPLETED   11/06 01:11:05 11/06 01:11:08	    43	    2	    0	   0
  705 AUTO_TASK 	   COMPLETED   11/06 01:21:06 11/06 01:21:08	    31	    2	    0	   0
  708 AUTO_TASK 	   COMPLETED   11/06 01:31:07 11/06 01:31:10	    39	    2	    0	   0
  711 AUTO_TASK 	   COMPLETED   11/06 01:41:09 11/06 01:41:12	    39	    2	    0	   0
 1045 AUTO_TASK 	   COMPLETED   12/06 22:00:09 12/06 22:02:47	   644	    1	    0	   0
 1085 AUTO_TASK 	   COMPLETED   13/06 22:00:03 13/06 22:02:09	   467	    1	    0	   0
 1125 AUTO_TASK 	   COMPLETED   15/06 08:23:50 15/06 08:25:46	   362	    1	    0	   0
 1287 AUTO_TASK 	   IN PROGRESS 15/06 17:38:25 15/06 17:38:25	    83	    0	    0	   1

15 rows selected.

SQL> 
SQL> select stale_stats from user_tab_statistics where table_name='MYOBJECTS_19C';

STALE_S
-------
NO