The purpose of object statistics is to inform the cost-based optimizer about the size and shape of your data, enabling it to make informed decisions about how to execute SQL statements. You need to tell it the truth about your data. If you don’t, you can’t be surprised if it makes a poor decision. Thus, statistics need to be kept up to date as data changes, including not just changes to the volume of data, but also the ranges and distribution of data values in columns.
In fact, most of the time, we refresh statistics so that the execution plan remains the same!
Table Monitoring
Oracle tracks the number of rows inserted, updated or deleted for each table since statistics were last refreshed. The tracking is done in memory by the database and is periodically flushed out to the dictionary. This feature was known as table monitoring when it was first introduced in Oracle 8i, but since 11g, it has been just a part of the database and is enabled for all tables.
Stale Statistics
If the sum of the number of rows inserted, updated and deleted as a percentage of the number of rows in the table (as recorded in the statistics) is greater than or equal to the stale threshold (by default 10%), then the statistics on that table are considered to be stale. The STALE_PERCENT threshold can be set and changed as a statistics preference either globally or for specific tables or groups of tables.
- See DBMS_STATS: SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.
If you need statistics to be collected more frequently on a particular table, then the stale threshold can be reduced as required (it can be set to any non-negative value; it does not have to be an integer).
Maintenance Windows
Oracle delivers several maintenance jobs that run in delivered maintenance windows on the database scheduler. There are maintenance windows for each day of the week, MONDAY_WINDOW, etc. That Automatic Optimizer Statistics Collection job gathers statistics on tables, indexes and their partitions whose statistics are either missing or stale.
- Index statistics are gathered if the CASCADE preference is set.
- Collection of partition statistics is controlled by the GRANULARITY and other preferences.
- Tables or partitions whose statistics are locked (set LOCK_TABLE_STATS and LOCK_PARTITION_STATS) are omitted.
The delivered weekday windows open at 10pm for 4 hours, and the weekend windows are open for 24 hours. 10pm on weekday evenings may or may not be a good time to collect statistics on a particular system. If it isn’t, the window should be changed to a more convenient time.
I work on several systems where the default maintenance window coincides with the overnight batch workload.
- On one system, the daytime workload is much less than the overnight batch, so the weekday maintenance windows have simply been moved to 10am, during the working day.
- On another system, it has been decided to run statistics twice per weekday as the system transitions between overnight batch processing and daytime online processing. Thus, each workload begins with fresh statistics. The weekday windows now open at 5am and 8pm, each for 3 hours. The weekend windows still open for 24 hours, but also at 5am.
- Some customers shut down non-production cloud environments overnight to save costs. In this case, the maintenance window should be moved to a time when the environment is usually running; otherwise, statistics may never be refreshed.
In the second case, the delivered windows were changed by the following script. It uses DBMS_SCHEDULER.SET_ATTRIBUTE to set the REPEAT_INTERVAL attribute of the delivered window.
Scheduler Time Zone
"The way the scheduler handles time zones can be a little confusing at first. If the scheduler default_timezone is not specified, it attempts to determine it from the operating system. If that isn't possible, it is set to NULL.
…
When you create [or alter] a job, the time zone for the job is determined using the time zone associated with the start_date parameter. If that is not supplied, the default_timezone is used. In the case of the start_date value, using SYSTIMESTAMP will fix an offset, rather than honouring the daylight savings rules. A more consistent result is produced … where the time zone is specified explicitly"
You may need to explicitly specify the time zone when you create or alter scheduler jobs.
In this case, the system is running in the Mexico City time zone (UTC-6), but the scheduler time zone is UTC.
----------------------------------------------------------------------------------------------------
--current time zone settings
----------------------------------------------------------------------------------------------------
--ALTER SESSION SET TIME_ZONE = '-6:00';
SELECT systimestamp, DBTIMEZONE, DBMS_SCHEDULER.STIME, SESSIONTIMEZONE FROM DUAL;
--select * from v$TIMEZONE_names where tzname like '%Mex%';
--select systimestamp at time zone 'America/Mexico_City' from dual;
----------------------------------------------------------------------------------------------------
--get current scheduler timezone
----------------------------------------------------------------------------------------------------
declare
l_attrib VARCHAR2(100);
begin
dbms_Scheduler.get_scheduler_attribute('default_timezone',l_attrib);
dbms_output.put_line(l_attrib);
end;
/SYSTIMESTAMP DBTIME STIME
---------------------------------- ------ -----------------------------------------
SESSIONTIMEZONE
---------------------------------------------------------------------------
09/01/26 11.20.21.946810000 -06:00 +00:00 09/01/26 17.20.21.947216000 EUROPE/LONDON
Europe/London
…
Etc/UTC
…
Altering the Maintenance Windows
The following script alters the DEFAULT_MAINTENANCE_PLAN windows for each day of the week.
This method of multiple openings for a single window is effective when each opening lasts for the same duration. If I want the weekday morning and evening openings to be for different durations (for example, 2 and 3 hours, respectively), or at different minutes past the hour (for example, 5.15am and 7.30pm), then I would need to define additional windows and add them to the MAINTENANCE_WINDOW_GROUP window group.- A time zone is specified in a constant at the top of the PL/SQL block. It is used to specify the start time attribute for each window as the current system time. That also determines the time zone of the repeat interval.
- I have specified the time zone by name, rather than a UTC offset, because I want the jobs to follow the daylight saving adjustment for that region.
- The duration and repeat interval of each window are also set.
- The script is available on GitHub.
REM gfc_default_maintenance_plan_window.sql
REM alter weekday schedule for maintenance window to run for 3 hours twice per day at 5am and 8pm, and for 24 hours on weekends at 5am
…
DECLARE
--k_time_zone CONSTANT VARCHAR2(30) := ''; --do not specify timezone
--k_time_zone CONSTANT VARCHAR2(30) := 'GMT'; --specify timezone
--k_time_zone CONSTANT VARCHAR2(30) := 'Europe/London'; --specify timezone
k_time_zone CONSTANT VARCHAR2(30) := 'America/Mexico_City'; --specify timezone
l_duration INTEGER; l_by VARCHAR2(30);
BEGIN
FOR i IN ( --update all windows in DEFAULT_MAINTENANCE_PLAN
select *
from dba_Scheduler_Windows
where resource_plan = 'DEFAULT_MAINTENANCE_PLAN'
and regexp_like(window_name,'(MON|TUES|WEDNES|THURS|FRI|SATUR|SUN)DAY_WINDOW')
) LOOP
IF regexp_like(i.window_name,'(MON|TUES|WEDNES|THURS|FRI)DAY_WINDOW') THEN
l_duration := 3; --duration of weekday opening in hours
l_by := ';byhour=5,20'; --two openings of weekday windows
ELSE
l_duration := 24; --duration of weekend opening in hours
l_by := ';byhour=5'; --one opening of weekend windows
END IF;
dbms_output.put_line('Window:'||i.owner||'.'||i.window_name||': schedule:'||l_by||' :'||l_duration||' hours');
dbms_scheduler.disable --disable window, otherwise change does not apply until it next opens
(name => i.owner||'.'||i.window_name
);
IF k_time_zone IS NOT NULL THEN --set start date with time zone, it applies to repeat interval
dbms_scheduler.set_attribute
(name => i.owner||'.'||i.window_name
,attribute => 'START_DATE'
,value => systimestamp at time zone k_time_zone
);
END IF;
dbms_scheduler.set_attribute --set duration for each window opening
(name => i.owner||'.'||i.window_name
,attribute => 'DURATION'
,value => numtodsinterval(l_duration, 'hour')
);
dbms_scheduler.set_attribute --set times that window opens
(name => i.owner||'.'||i.window_name
,attribute => 'REPEAT_INTERVAL'
,value => 'freq=daily;byday='||SUBSTR(i.window_name,1,3)||l_by||';byminute=0;bysecond=0'
);
dbms_scheduler.enable
(name => i.owner||'.'||i.window_name
);
for j in (
select * from dba_scheduler_window_groups
where window_group_name IN('MAINTENANCE_WINDOW_GROUP','ORA$AT_WGRP_OS','ORA$AT_WGRP_SA','ORA$AT_WGRP_SQ')
) LOOP
DBMS_SCHEDULER.add_window_group_member --add window to window group
(group_name => j.window_group_name
,window_list => i.owner||'.'||i.window_name);
END LOOP;
END LOOP;
END;
/The script reports the windows that it updates.
Window:SYS.MONDAY_WINDOW: schedule:;byhour=5,20 :3 hours
Window:SYS.TUESDAY_WINDOW: schedule:;byhour=5,20 :3 hours
Window:SYS.WEDNESDAY_WINDOW: schedule:;byhour=5,20 :3 hours
Window:SYS.THURSDAY_WINDOW: schedule:;byhour=5,20 :3 hours
Window:SYS.FRIDAY_WINDOW: schedule:;byhour=5,20 :3 hours
Window:SYS.SATURDAY_WINDOW: schedule:;byhour=5 :24 hours
Window:SYS.SUNDAY_WINDOW: schedule:;byhour=5 :24 hours
PL/SQL procedure successfully completed.After running the configuration script, we can check that the settings have been configured.
select * from dba_scheduler_window_groups
where window_group_name = 'MAINTENANCE_WINDOW_GROUP'
/
select * from dba_Scheduler_wingroup_members
where window_group_name = 'MAINTENANCE_WINDOW_GROUP'
/
select * from dba_Scheduler_Windows
where resource_plan = 'DEFAULT_MAINTENANCE_PLAN'
order by next_Start_date
/There are 7 delivered windows in the MAINTENANCE_WINDOW_GROUP. We can see their last run date, repeat interval, and next scheduled run date.
Number
WINDOW_GROUP_NAME ENABL Windows NEXT_START_DATE COMMENTS
------------------------ ----- ------- ------------------------------------------------ --------------------------------------------------
MAINTENANCE_WINDOW_GROUP TRUE 7 09-JAN-26 08.00.00.000000 PM AMERICA/MEXICO_CITY Window group for Automated Maintenance
…
WINDOW_GROUP_NAME WINDOW_NAME
------------------------ ----------------
MAINTENANCE_WINDOW_GROUP MONDAY_WINDOW
MAINTENANCE_WINDOW_GROUP TUESDAY_WINDOW
MAINTENANCE_WINDOW_GROUP WEDNESDAY_WINDOW
MAINTENANCE_WINDOW_GROUP THURSDAY_WINDOW
MAINTENANCE_WINDOW_GROUP FRIDAY_WINDOW
MAINTENANCE_WINDOW_GROUP SATURDAY_WINDOW
MAINTENANCE_WINDOW_GROUP SUNDAY_WINDOW
…
Schedule Schedule
OWNER WINDOW_NAME RESOURCE_PLAN Owner SCHEDULE_NAME Type START_DATE REPEAT_INTERVAL
----- ---------------- ------------------------ ---------- ---------------- ---------- --------------------------------------------- ------------------------------------------------------------
Window
END_DATE DURATION Priority NEXT_START_DATE LAST_START_DATE ENABL ACTIV
------------------------------- ------------------- -------- ------------------------------------------------ ------------------------------------------------ ----- -----
MANUAL_OPEN_TIME MANUAL_DURATION COMMENTS
------------------------------- ------------------- --------------------------------------------------
SYS WEDNESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN CALENDAR Wed 14/01/26 05:28:16.000 AMERICA/MEXICO_CITY freq=daily;byday=WED;byhour=5,20;byminute=0;bysecond=0
+00 03:00:00.000000 LOW Wed 21/01/26 05:00:00.000 AMERICA/MEXICO_CITY Wed 21/01/26 05:00:00.119 AMERICA/MEXICO_CITY TRUE TRUE
Wednesday window for maintenance tasks
SYS THURSDAY_WINDOW DEFAULT_MAINTENANCE_PLAN CALENDAR Wed 14/01/26 05:28:16.000 AMERICA/MEXICO_CITY freq=daily;byday=THU;byhour=5,20;byminute=0;bysecond=0
+00 03:00:00.000000 LOW Thu 22/01/26 05:00:00.000 AMERICA/MEXICO_CITY Thu 15/01/26 20:00:00.023 AMERICA/MEXICO_CITY TRUE FALSE
Thursday window for maintenance tasks
SYS FRIDAY_WINDOW DEFAULT_MAINTENANCE_PLAN CALENDAR Wed 14/01/26 05:28:16.000 AMERICA/MEXICO_CITY freq=daily;byday=FRI;byhour=5,20;byminute=0;bysecond=0
+00 03:00:00.000000 LOW Fri 23/01/26 05:00:00.000 AMERICA/MEXICO_CITY Fri 16/01/26 20:00:00.100 AMERICA/MEXICO_CITY TRUE FALSE
Friday window for maintenance tasks
SYS SATURDAY_WINDOW DEFAULT_MAINTENANCE_PLAN CALENDAR Wed 14/01/26 05:28:16.000 AMERICA/MEXICO_CITY freq=daily;byday=SAT;byhour=5;byminute=0;bysecond=0
+00 23:00:00.000000 LOW Sat 24/01/26 05:00:00.000 AMERICA/MEXICO_CITY Sat 17/01/26 05:00:00.036 AMERICA/MEXICO_CITY TRUE FALSE
Saturday window for maintenance tasks
SYS SUNDAY_WINDOW DEFAULT_MAINTENANCE_PLAN CALENDAR Wed 14/01/26 05:28:16.000 AMERICA/MEXICO_CITY freq=daily;byday=SUN;byhour=5;byminute=0;bysecond=0
+00 23:00:00.000000 LOW Sun 25/01/26 05:00:00.000 AMERICA/MEXICO_CITY Sun 18/01/26 05:00:00.042 AMERICA/MEXICO_CITY TRUE FALSE
Sunday window for maintenance tasks
SYS MONDAY_WINDOW DEFAULT_MAINTENANCE_PLAN CALENDAR Wed 14/01/26 05:28:16.000 AMERICA/MEXICO_CITY freq=daily;byday=MON;byhour=5,20;byminute=0;bysecond=0
+00 03:00:00.000000 LOW Mon 26/01/26 05:00:00.000 AMERICA/MEXICO_CITY Mon 19/01/26 20:00:00.067 AMERICA/MEXICO_CITY TRUE FALSE
Monday window for maintenance tasks
SYS TUESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN CALENDAR Wed 14/01/26 05:28:16.000 AMERICA/MEXICO_CITY freq=daily;byday=TUE;byhour=5,20;byminute=0;bysecond=0
+00 03:00:00.000000 LOW Tue 27/01/26 05:00:00.000 AMERICA/MEXICO_CITY Tue 20/01/26 20:00:00.157 AMERICA/MEXICO_CITY TRUE FALSE
Tuesday window for maintenance tasks
…After the schedule has been allowed to run for a few days, we use the scheduler job logs to verify that the jobs are running as desired.
select log_id, log_date, owner, job_name, status, error#, actual_start_Date, run_duration, instance_id, session_id, cpu_used
from dba_scheduler_job_run_details
where owner = 'SYS'
and job_name like 'ORA$AT_OS_OPT_SY%'
order by /*run_duration desc,*/ log_date desc
fetch first 50 rows only
/Note that: "In the case of a very long maintenance window, all automated maintenance tasks except Automatic SQL Tuning Advisor are restarted every four hours. This feature ensures that maintenance tasks are run regularly, regardless of window size." - see Database Administrator's Guide, 26 Managing Automated Database Maintenance Tasks, 26.2 About Maintenance Windows
Inst
LOG_ID LOG_DATE OWNER JOB_NAME STATUS ERROR# ACTUAL_START_DATE RUN_DURATION ID SESSION_ID CPU_USED
-------- -------------------------------- ----- ----------------------- --------- ------ --------------------------------- ------------------- ---- ---------- -------------------
455724 Wed 21/01/26 05:00:43.047 -06:00 SYS ORA$AT_OS_OPT_SY_26423 SUCCEEDED 0 Wed 21/01/26 11:00:02.472 ETC/UTC +00 00:00:41.000000 1 1467,25845 +00 00:00:34.900000
455582 Tue 20/01/26 20:02:00.474 -06:00 SYS ORA$AT_OS_OPT_SY_26444 SUCCEEDED 0 Wed 21/01/26 02:00:04.307 ETC/UTC +00 00:01:56.000000 2 1751,14822 +00 00:01:24.520000
455384 Tue 20/01/26 05:02:35.196 -06:00 SYS ORA$AT_OS_OPT_SY_26430 SUCCEEDED 0 Tue 20/01/26 11:00:02.728 ETC/UTC +00 00:02:32.000000 1 884,22319 +00 00:02:09.700000
455262 Mon 19/01/26 20:01:58.846 -06:00 SYS ORA$AT_OS_OPT_SY_26412 SUCCEEDED 0 Tue 20/01/26 02:00:03.780 ETC/UTC +00 00:01:55.000000 1 884,4883 +00 00:01:39.430000
455036 Mon 19/01/26 05:00:13.187 -06:00 SYS ORA$AT_OS_OPT_SY_26402 SUCCEEDED 0 Mon 19/01/26 11:00:02.276 ETC/UTC +00 00:00:11.000000 2 333,21412 +00 00:00:09.000000
454946 Mon 19/01/26 01:04:03.499 -06:00 SYS ORA$AT_OS_OPT_SY_26399 SUCCEEDED 0 Mon 19/01/26 07:03:09.820 ETC/UTC +00 00:00:54.000000 2 327,11548 +00 00:00:48.290000
454872 Sun 18/01/26 21:02:44.802 -06:00 SYS ORA$AT_OS_OPT_SY_26397 SUCCEEDED 0 Mon 19/01/26 03:02:31.836 ETC/UTC +00 00:00:13.000000 2 1459,27367 +00 00:00:10.710000
454788 Sun 18/01/26 17:02:54.589 -06:00 SYS ORA$AT_OS_OPT_SY_26394 SUCCEEDED 0 Sun 18/01/26 23:01:56.036 ETC/UTC +00 00:00:59.000000 2 1455,13997 +00 00:00:52.520000
454750 Sun 18/01/26 13:01:33.537 -06:00 SYS ORA$AT_OS_OPT_SY_26392 SUCCEEDED 0 Sun 18/01/26 19:01:15.866 ETC/UTC +00 00:00:18.000000 2 1468,50519 +00 00:00:14.760000
454666 Sun 18/01/26 09:01:01.283 -06:00 SYS ORA$AT_OS_OPT_SY_26390 SUCCEEDED 0 Sun 18/01/26 15:00:39.891 ETC/UTC +00 00:00:21.000000 2 1468,38883 +00 00:00:19.730000
454572 Sun 18/01/26 05:00:25.942 -06:00 SYS ORA$AT_OS_OPT_SY_26387 SUCCEEDED 0 Sun 18/01/26 11:00:07.639 ETC/UTC +00 00:00:18.000000 2 2017,20454 +00 00:00:14.860000
454482 Sun 18/01/26 01:07:16.123 -06:00 SYS ORA$AT_OS_OPT_SY_26383 SUCCEEDED 0 Sun 18/01/26 07:06:18.742 ETC/UTC +00 00:00:57.000000 2 1157,32828 +00 00:00:46.240000
454412 Sat 17/01/26 21:06:25.203 -06:00 SYS ORA$AT_OS_OPT_SY_26380 SUCCEEDED 0 Sun 18/01/26 03:05:47.683 ETC/UTC +00 00:00:38.000000 2 1175,48150 +00 00:00:31.870000
454334 Sat 17/01/26 17:05:37.560 -06:00 SYS ORA$AT_OS_OPT_SY_26378 SUCCEEDED 0 Sat 17/01/26 23:05:23.029 ETC/UTC +00 00:00:15.000000 2 336,36837 +00 00:00:11.480000
454302 Sat 17/01/26 13:05:20.405 -06:00 SYS ORA$AT_OS_OPT_SY_26376 SUCCEEDED 0 Sat 17/01/26 19:04:54.828 ETC/UTC +00 00:00:26.000000 2 871,11733 +00 00:00:22.170000
454166 Sat 17/01/26 09:04:43.136 -06:00 SYS ORA$AT_OS_OPT_SY_26374 SUCCEEDED 0 Sat 17/01/26 15:04:23.641 ETC/UTC +00 00:00:19.000000 2 871,14655 +00 00:00:15.770000
454184 Sat 17/01/26 05:01:40.118 -06:00 SYS ORA$AT_OS_OPT_SY_26371 SUCCEEDED 0 Sat 17/01/26 11:00:03.076 ETC/UTC +00 00:01:37.000000 1 1442,22115 +00 00:01:18.340000
453992 Fri 16/01/26 20:00:36.757 -06:00 SYS ORA$AT_OS_OPT_SY_26365 SUCCEEDED 0 Sat 17/01/26 02:00:05.360 ETC/UTC +00 00:00:31.000000 2 2025,17953 +00 00:00:25.270000
453434 Fri 16/01/26 05:02:26.340 -06:00 SYS ORA$AT_OS_OPT_SY_26352 SUCCEEDED 0 Fri 16/01/26 11:00:01.652 ETC/UTC +00 00:02:25.000000 1 338,49711 +00 00:02:04.670000
453352 Thu 15/01/26 20:05:14.702 -06:00 SYS ORA$AT_OS_OPT_SY_26336 SUCCEEDED 0 Fri 16/01/26 02:00:06.180 ETC/UTC +00 00:05:09.000000 1 906,60694 +00 00:04:12.300000
453274 Thu 15/01/26 05:02:56.172 -06:00 SYS ORA$AT_OS_OPT_SY_26331 SUCCEEDED 0 Thu 15/01/26 11:00:02.280 ETC/UTC +00 00:02:54.000000 1 2026,42084 +00 00:02:16.260000
453212 Wed 14/01/26 20:04:07.215 -06:00 SYS ORA$AT_OS_OPT_SY_26344 SUCCEEDED 0 Thu 15/01/26 02:00:02.944 ETC/UTC +00 00:04:04.000000 1 26,35644 +00 00:03:09.810000
…Conclusion
There is nothing special about running the statistics maintenance job at 10pm on weekdays. It is perfectly satisfactory for many systems. However, if it doesn't fit with your system workload, then change it to a time or times that do.
@go-faster.co.uk on BlueSky
Posts
No comments :
Post a Comment