Purpose of High-Frequency Automatic Optimizer Statistics Collection
AutoTask schedules tasks to run automatically in maintenance windows. By default, one window is scheduled for each day of the week. Automatic optimizer statistics collection (DBMS_STATS) runs in all predefined maintenance windows.
Statistics can go stale between two consecutive statistics collection tasks. If data changes frequently, the stale statistics could cause performance problems. For example, a brokerage company might receive tremendous data during trading hours, leading the optimizer to use stale statistics for queries executed during this period.
High-frequency automatic optimizer statistics collection complements the standard statistics collection job. By default, the collection occurs every 15 minutes, meaning that statistics have less time in which to be stale.
How High-Frequency Automatic Optimizer Statistics Collection Works
To enable and disable the high-frequency task, set the execution interval, and set the maximum run time, use the DBMS_STATS.SET_GLOBAL_PREFS procedure. The high-frequency task is “lightweight” and only gathers stale statistics. It does not perform actions such as purging statistics for non-existent objects or invoking Optimizer Statistics Advisor. The standard automated job performs these additional tasks.
Automatic statistics collection jobs that run in the maintenance window are not affected by the high-frequency jobs. The high-frequency task may execute in maintenance windows, but it will not execute while the maintenance window auto stats gathering job is executing. You can monitor the tasks by querying DBA_AUTO_STAT_EXECUTIONS.
Statistics gathering until 18c was on daily/weekly basis based on the maintenance tasks defined. Say 10PM every day with 8 hour window on weekdays and 24 hrs window on week ends. And how many times we have seen statistics have become stale between two collections and DBA's has to manually intervene and collect stats.
From 19c onwards, High-frequency automatic optimizer statistics collection complements the standard statistics collection job. By default, when set to on, the collection occurs every 15 minutes, meaning that statistics have less time in which to be stale.
To Enable high frequency stats collection
- EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON’);
For, Maximum Run duration of each run,
- EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_MAX_RUN_TIME','600’);
To Specify the frequency of auto stats collection in seconds
- EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_INTERVAL','240')
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 Oracle 19c new feature.
Turn on/ off : EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON’);
Change the default period : EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_INTERVAL','300’);
Monitor : DBA_AUTO_STAT_EXECUTIONS
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;
CONN demo/demo12345@//192.168.58.101:1521/srlabdb
The high frequency automatic statistics task will not start during the maintenance window
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 stale_stats from user_tab_statistics where table_name='TAB3';
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;
SQL> select stale_stats from user_tab_statistics where table_name='TAB3';
SQL> EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON’); <= Turn on High Frequency Auto Stats Collection
SQL> EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_MAX_RUN_TIME','900’); <= Run for up to 900 seconds (15 mins)
SQL> EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_INTERVAL','900’); <= Run every 900 seconds (15 mins)
SQL> create table tab1 (id number, code number, name varchar2(42)); <= Stale with no stats
SQL> insert into tab1 select rownum , rownum , 100)+1, 'Sachin Ramesh' from dual connect by level <= 100000;
SQL> commit;
SQL> create table tab2 (id number, code number, name varchar2(42));
SQL> insert into tab2 select rownum , rownum , 100)+1, 'Sachin Ramesh' from dual connect by level <= 100000;
SQL> commit;
SQL> exec dbms_stats.gather_table_stats ownname =>null, tabname =>'TAB2');
SQL> insert into tab2 select rownum+100000, mod(rownum , 100)+1, 'Ramesh Kumar' from dual connect by level <= 50000;
SQL> commit; <= Stale with outdated stats
SQL> create table tab3 (id number, code number, name varchar2(42));
SQL> insert into tab3 select rownum , 10, 'SACHIN RAMESH' from dual connect by level <= 1000000;
SQL> update tab3 set code = 9 where mod(id,3) = 0;
SQL> update tab3 set code = 1 where mod(id,2) = 0 and id between 1 and 20000;
SQL> update tab3 set code = 2 where mod(id,2) = 0 and id between 30001 and 40000;
SQL> update tab3 set code = 3 where mod(id,100) = 0 and id between 300001 and 400000;
SQL> update tab3 set code = 4 where mod(id,100) = 0 and id between 400001 and 500000;
SQL> update tab3 set code = 5 where mod(id,100) = 0 and id between 600001 and 700000;
SQL> update tab3 set code = 6 where mod(id,1000) = 0 and id between 700001 and 800000;
SQL> update tab3 set code = 7 where mod(id,1000) = 0 and id between 800001 and 900000;
SQL> update tab3 set code = 8 where mod(id,1000) = 0 and id between 900001 and 1000000;
SQL> commit;
SQL> exec dbms_stats.gather_table_stats ownname =>null, tabname =>'tab3', estimate_percent => 100);
<= Missing histogram statistics
SQL> select code, count(*) from demo.tab3 group by code order by code;
SQL> select * from tab3 where code=7;
Current table statistics:
-------------------------
SQL> select table_name , num_rows , stale_stats , notes from user_tab_statistics where table_name in ('TAB1', 'TAB2', 'TAB3');
SQL> select column_name , num_buckets , histogram from user_tab_col_statistics where table_name ='TAB3';
After the High Frequency Auto Stats task completes (approx. 15 minutes)
SQL> select table_name , num_rows , stale_stats from user_tab_statistics where table_name in ('TAB1', 'TAB2', 'TAB3');
SQL> select column_name , num_buckets , histogram from user_tab_col_statistics where table_name ='TAB3';
SQL> select * from tab3 where code=7;
No comments:
Post a Comment