High Frequency Automatic Statistics Job

From Robs_Wiki
Revision as of 18:13, 26 October 2020 by Qadmin wiki (talk | contribs) (Created page with "<pre> Introduction ======= • The automatic optimizer statistics collection job which calls DBMS_STATS package runs in predefined maintenance windows and these maintenance wi...")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search
Introduction
=======
• 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. You can only turn it on and off for the whole database at the moment and the default is off. 

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).
The jobs only uses 1 CPU and on one instance only. It uses table SYS.STATS_TARGET$ to drive forward the processing. The table with the stalest statistics is analyzed first and this table is also used to coordinate processing of the regular gather stats job and the high frequence job, to make sure they do not analyze the same table at the same time. It also keeps a history of the stats collection.

Dangers of this job:
•	cursor invalidation due to collection statistics.
•	might increase parse overhead !
•	increased volume of statistics history and statistics history purge job.

There is limited control over the job:
•	Enable/disable the job (for the entire database)
•	set the execution frequency
•	set the maximum runtime
•	omit tables by locking statistics

exec dbms_stats.set_global_prefs('AUTO_TASK_STATUS','ON');
exec dbms_stats.set_global_prefs('AUTO_TASK_INTERVAL','900');
exec dbms_stats.set_global_prefs('MAX_RUN_TIME','3600');

Example
=====

--View the table sys.stats_target:
SQL> select * from (
select to_char(start_time,'DD-MON-YYYY HH24:MI:SS') start_time, to_char(end_time,'DD-MON-YYYY HH24:MI:SS' end_time, staleness, obj# from sys.stats_target$ order by start_time desc)
where rownum < 6;

START_TIME           END_TIME              STALENESS       OBJ#
-------------------- -------------------- ---------- ----------
26-OCT-2020 08:18:22 26-OCT-2020 08:18:26         .3      73263
26-OCT-2020 08:18:19 26-OCT-2020 08:18:19         .4        422
26-OCT-2020 08:18:14 26-OCT-2020 08:18:19         .5        685
26-OCT-2020 08:18:08 26-OCT-2020 08:18:14         .5       8700
26-OCT-2020 08:18:04 26-OCT-2020 08:18:08         .4        423

--Create a table with missing statistics
SQL> create table test2 as select * from test1;

SQL> EXEC DBMS_STATS.DELETE_TABLE_STATS(OWNNAME=>'PDBADMIN', TABNAME=>'TEST2');

SQL> select TABLE_NAME,NUM_ROWS,BLOCKS,AVG_ROW_LEN,LAST_ANALYZED,NOTES from DBA_TAB_STATISTICS where table_name='TEST2';

TABLE_NAME        NUM_ROWS     BLOCKS AVG_ROW_LEN LAST_ANALYZED        NOTES
--------------- ---------- ---------- ----------- -------------------- -----------------------
TEST2

--Check the current status for the high frequency analysis (default is off)
SQL> col AUTO_TASK_STATUS format a20
SQL> SELECT DBMS_STATS.GET_PREFS('AUTO_TASK_STATUS') AS auto_task_status from dual;

AUTO_TASK_STATUS
--------------------
OFF

--Turn on the job, to run every 5 minutes:
SQL> EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON');

SQL> SELECT DBMS_STATS.GET_PREFS('AUTO_TASK_STATUS') AS auto_task_status from dual;

AUTO_TASK_STATUS
--------------------
ON

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

--Check the status of the job:
SQL> col status format a15
SQL> col origin format a15
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
---------- --------------- --------------- -------------- -------------------- ---------- ---------
238 AUTO_TASK IN PROGRESS  10/10 14:17:07 10/10 14:17:07  1495         89         0          0
258 AUTO_TASK COMPLETED    11/10 14:31:17 11/10 15:02:08  644          1          0          0
338 AUTO_TASK COMPLETED    13/10 07:24:37 13/10 07:31:51  105          1          0          0
358 AUTO_TASK COMPLETED    14/10 08:30:55 14/10 08:38:27  102          1          0          0
. . .