Oracle 19c - New Feature - High Frequency Optimizer and Automatic Stats Collection - Auto Stats Update with Demo!
Hi All.
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;
Cheers!
Ramesh.
Post a Comment: