Since there was lot of hype of 19c new feature High frequency statistics thought of documenting few note .
Since main motto of Blog is High-Frequency Statistics have mentioned about it first But later also documented notes on our routine stats maintenance window
#########################
19c High-frequency automatic optimizer statistics collection. :
#########################
The new feature introduced in Oracle 19c called High-Frequency Automatic Optimizer Statistics Collection complements the standard automatic statistics collection job.
AutoTask schedules tasks to run automatically between 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.
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).
select
s.START_TIME, s.END_TIME
,s.STALENESS
,s.OSIZE
,s.OBJ#, name, NVL(subname,'<NULL>') subname
,s.TYPE#
,decode(s.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
'UNDEFINED') object_type
,s.FLAGS, s.STATUS
,s.SID, s.SERIAL#
,s.PART#, s.BO#
from sys.stats_target$ s
left outer join sys.obj$ o on o.obj# = s.obj#
Where start_time >= sysdate-10/1440
order by start_time
/
==> To Change Setting
EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON');
EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_MAX_RUN_TIME','600');
EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_INTERVAL','240');
==> to check current setting
SET LINESIZE 100 pages 99
COLUMN auto_task_status HEADING 'Auto Task|Status' FORMAT a10
COLUMN auto_task_max_run_time HEADING 'Auto Task|Max Run Time' FORMAT a15
COLUMN auto_task_interval HEADING 'Auto Task|Interval' FORMAT a10
SELECT DBMS_STATS.GET_PREFS('AUTO_TASK_STATUS') AS auto_task_status
, DBMS_STATS.GET_PREFS('AUTO_TASK_MAX_RUN_TIME') AS auto_task_max_run_time
, DBMS_STATS.GET_PREFS('AUTO_TASK_INTERVAL') AS auto_task_interval
FROM dual;
==> To Check execution history
COL OPID FORMAT 99999999
COL STATUS FORMAT a11
COL ORIGIN FORMAT a20
COL COMPLETED FORMAT 99999
COL FAILED FORMAT 99999
COL TIMEOUT FORMAT 99999
COL INPROG FORMAT 99999
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;
with x as (
SELECT x.*
, end_time-start_time diff
, start_time-(LAG(end_time,1) over (order by start_time)) start_lag
FROM DBA_AUTO_STAT_EXECUTIONS x
ORDER BY x.start_time
)
select opid, origin, status
, ((extract( day from start_lag )*24+
extract( hour from start_lag ))*60+
extract( minute from start_lag ))*60+
extract( second from start_lag ) start_lag
, start_time, end_time
, ((extract( day from diff )*24+
extract( hour from diff ))*60+
extract( minute from diff ))*60+
extract( second from diff ) secs
, completed, failed, timed_out, in_progress
from x
Where start_time >= sysdate-1/24
/
#########################
To Enable Or Disable old Auto task during daily maintenance window
#########################
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE (
client_name => 'auto optimizer stats collection'
, operation => NULL
, window_name => NULL
);
END;
/
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE (
client_name => 'auto optimizer stats collection'
, operation => NULL
, window_name => NULL
);
END;
/
-- disable sql tuning advisor on monday
BEGIN
dbms_auto_task_admin.disable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => 'MONDAY_WINDOW');
END;
/
#########################
Alter old auto stats setting for daily maintenance window
#########################
EXEC DBMS_STATS.alter_stats_history_retention(90);
EXEC DBMS_STATS.set_global_prefs('estimate_percent', '5');
BEGIN
DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'LOCAL_TIME_LIMIT', 1200);
DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'ACCEPT_SQL_PROFILES', 'FALSE');
DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'MAX_SQL_PROFILES_PER_EXEC', 20);
DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'MAX_AUTO_SQL_PROFILES', 10000);
END;
/
#########################
Old Auto Task Details View for daily maintenance window :
#########################
select log_id,owner,job_name,status,actual_start_date,instance_id from dba_scheduler_job_run_details
where job_name = 'GATHER_STATS_JOB' and ACTUAL_START_DATE>SYSDATE-3
or job_name like 'ORA$AT_OS_OPT_SY%' and ACTUAL_START_DATE>SYSDATE-3
order by log_date desc;
COL CLIENT_NAME FORMAT a31
select client_name, status, window_group from dba_autotask_client;
SELECT client_name, status FROM dba_autotask_operation;
SELECT task_name, status, TO_CHAR(execution_end,'DD-MON-YY HH24:MI')
FROM dba_advisor_executions WHERE task_name='SYS_AUTO_SQL_TUNING_TASK'
ORDER BY execution_end;
SELECT WINDOW_NAME, to_char(START_TIME,'DD-Mon-RR hh24:mi') START_TIME, DURATION
FROM DBA_AUTOTASK_SCHEDULE
ORDER BY WINDOW_NAME, START_TIME DESC;
select client_name,JOB_NAME,JOB_STATUS,JOB_START_TIME,JOB_DURATION from DBA_AUTOTASK_JOB_HISTORY
where JOB_START_TIME >systimestamp -7 and client_name='auto optimizer stats collection' order by 4 desc ;
select parameter_name, parameter_value
from dba_advisor_parameters
where task_name = 'SYS_AUTO_SQL_TUNING_TASK'
and parameter_name IN ('ACCEPT_SQL_PROFILES',
'MAX_SQL_PROFILES_PER_EXEC',
'MAX_AUTO_SQL_PROFILES');
select window_name, repeat_interval, duration from dba_scheduler_windows where window_name like 'WEEK%';
select window_name, next_start_date , enabled , resource_plan from dba_scheduler_windows ;
SELECT window_name,TO_CHAR(window_next_time,'DD-MON-YY HH24:MI:SS')
,sql_tune_advisor, optimizer_stats, segment_advisor
FROM dba_autotask_window_clients;
select * from dba_scheduler_wingroup_members order by 1,2;
col target for a20
col start_time for a20
col end_time for a20
col notes for a20
select target,start_time,end_time,notes
from DBA_OPTSTAT_OPERATION_TASKS
-- where target like '%DEMO%'
order by OPID desc;
set linesize 250
set pagesize 100
column preference_name format a30
column preference_value format a50
-- global preferences
with preflist (preference_name,global_only)
as (select 'APPROXIMATE_NDV_ALGORITHM',0 from dual union all
select 'AUTO_STAT_EXTENSIONS' ,0 from dual union all
select 'AUTO_TASK_STATUS' ,0 from dual union all
select 'AUTO_TASK_MAX_RUN_TIME' ,0 from dual union all
select 'AUTO_TASK_INTERVAL' ,0 from dual union all
select 'AUTOSTATS_TARGET' ,1 from dual union all
select 'CASCADE' ,0 from dual union all
select 'CONCURRENT' ,0 from dual union all
select 'DEGREE' ,0 from dual union all
select 'ESTIMATE_PERCENT' ,0 from dual union all
select 'GLOBAL_TEMP_TABLE_STATS' ,0 from dual union all
select 'GRANULARITY' ,0 from dual union all
select 'INCREMENTAL' ,0 from dual union all
select 'INCREMENTAL_STALENESS' ,0 from dual union all
select 'INCREMENTAL_LEVEL' ,0 from dual union all
select 'METHOD_OPT' ,0 from dual union all
select 'NO_INVALIDATE' ,0 from dual union all
select 'OPTIONS' ,0 from dual union all
select 'PREFERENCE_OVERRIDES_PARAMETER',0 from dual union all
select 'PUBLISH' ,0 from dual union all
select 'STALE_PERCENT' ,0 from dual union all
select 'STAT_CATEGORY' ,0 from dual union all
select 'TABLE_CACHED_BLOCKS' ,0 from dual union all
select 'WAIT_TIME_TO_UPDATE_STATS',0 from dual )
select preference_name,
sys.dbms_stats.get_prefs(preference_name) as preference_value,
global_only
from preflist;
#########################
Pdb Parameters related to AutoTask
#########################
AUTOTASK_MAX_ACTIVE_PDBS
ENABLE_AUTOMATIC_MAINTENANCE_PDB
#########################
Old Auto Task Window for daily maintenance window :
#########################
select window_name, repeat_interval, duration from dba_scheduler_windows order by window_name;
select * from dba_scheduler_window_groups;
select * from dba_scheduler_wingroup_members order by 1,2;
select client_name, status, con_id from cdb_autotask_client;
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKNIGHT_WINDOW','repeat_interval','freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=05;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKEND_WINDOW','repeat_interval',' freq=daily;byday=SAT;byhour=07;byminute=0;bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW', 'repeat_interval','freq=daily;byday=MON;byhour=05;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('TUESDAY_WINDOW', 'repeat_interval','freq=daily;byday=TUE;byhour=05;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW','repeat_interval','freq=daily;byday=WED;byhour=05;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('THURSDAY_WINDOW', 'repeat_interval','freq=daily;byday=THU;byhour=05;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('FRIDAY_WINDOW', 'repeat_interval','freq=daily;byday=FRI;byhour=05;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('SATURDAY_WINDOW', 'repeat_interval','freq=daily;byday=SAT;byhour=13;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('SUNDAY_WINDOW', 'repeat_interval','freq=daily;byday=SUN;byhour=13;byminute=0; bysecond=0');
BEGIN
dbms_scheduler.disable(
name => 'WINDOW_NAME');
dbms_scheduler.set_attribute(
name => 'WINDOW_NAME',
attribute => 'DURATION',
value => numtodsinterval(6, 'hour'));
dbms_scheduler.enable(
name => 'WINDOW_NAME');
END;
/
BEGIN
DBMS_SCHEDULER.disable(
name => 'SYS.MAINTENANCE_WINDOW_GROUP',
force => TRUE);
DBMS_SCHEDULER.enable(
name => 'SYS.MAINTENANCE_WINDOW_GROUP');
END;
/
BEGIN
dbms_scheduler.create_window(
window_name=>'MORNING_WINDOW',
resource_plan=>'DEFAULT_MAINTENANCE_PLAN',
repeat_interval=>'freq=daily;byhour=10;byminute=0;bysecond=0',
duration=>interval '2' hour,
comments=>'Maintenance window');
dbms_scheduler.add_window_group_member('MAINTENANCE_WINDOW_GROUP',
'MORNING_WINDOW');
END;
/
BEGIN
dbms_scheduler.remove_window_group_member('MAINTENANCE_WINDOW_GROUP',
'MONDAY_WINDOW');
END;
/
#########################
Data Dictionary Views:
#########################
DBA_AUTOTASK_CLIENT_JOB
DBA_AUTOTASK_CLIENT
DBA_AUTOTASK_JOB_HISTORY
DBA_AUTOTASK_WINDOW_CLIENTS
DBA_AUTOTASK_CLIENT_HISTORY
DBA_AUTOTASK_OPERATION
dba_autotask_schedule
#########################
Reference :
#########################
https://tinky2jed.wordpress.com/technical-stuff/oracle-stuff/changing-the-oracle-database-default-maintenance-window-time/
https://docs.oracle.com/database/121/ADMIN/tasks.htm
