DATABASE ALSO NEEDS REST AT NIGHT TO WORK EFFICIENTLY
Similar to our subconscious mind which repair our body during sleep at night, oracle also needs a nightly window to subconsciously replenish itself to perform better.
Three predefined automated maintenance jobs are available for Oracle Database:
Automatic Optimizer Statistics Collection—Collects optimizer statistics for any database objects with a schema but no or outdated statistics. The SQL query optimizer uses the statistics acquired by this process to speed up the execution of SQL queries.
Automatic Segment Advisor: Detects segments with space that can be recovered and offers suggestions for defragmenting certain segments.
Automatic SQL Tuning Advisor: This tool analyses the performance of high-load SQL statements and offers tuning suggestions. This adviser can be set up to automatically apply SQL profile recommendations.
•Any sessions belonging to the consumer group SYS GROUP are given priority in this arrangement.
•Sessions generated by the user accounts SYS and SYSTEM are included in this group.
• Sessions from the other consumer groups and subplans in the plan then share any unused resource allocation that was made available to sessions in SYS GROUP.
•20 percent of that budget is allotted to user sessions, while 5 percent is used for maintenance chores. The ORA$AUTOTASK utilization ceiling is set at 90.
•As a result, even when the CPU is not in use, this group or plan cannot receive more than 90% of the CPU’s resources.
| Consumer Group/subplan | Level 1 | Maximum Utilization Limit |
| ORA$AUTOTASK | 5% | 90 |
| OTHER_GROUPS | 20% | – |
| SYS_GROUP | 75% | – |
•There are seven predefined maintenance windows by default, each of which corresponds to a different day of the week.
•The SATURDAY WINDOW and SUNDAY WINDOW maintenance windows last longer than the weekday maintenance windows.
•These seven windows make up the window group MAINTENANCE WINDOW GROUP.
| Window Name | Description |
| MONDAY_WINDOW | 10 pm to 2 am |
| TUESDAY_WINDOW | 10 pm to 2 am |
| WEDNESDAY_WINDOW | 10 pm to 2 am |
| THURSDAY_WINDOW | 10 pm to 2 am |
| FRIDAY_WINDOW | 10 pm to 2 am |
| SATURDAY_WINDOW | 6 am to 2am(20 hours window) |
| SUNDAY_WINDOW | 6 am to 2am(20 hours window) |
Resource manager traces are immediately dumped after manual trigger of automatic jobs
*** 2023-02-03T06:17:14.523754+05:30 (CDB$ROOT(1))
kgsksysstop: blocking mode (2) timestamp: 174353116
kgsksysstop: successful; Total stall time = 0 ms.
kgsksysresume: successful
kgskreset: disabling CPURM: scheduled 0 vts
START RESOURCE MANAGER PLAN/CONSUMER GROUP DUMP
CDB ORA$INTERNAL_CDB_PLAN cpu_count=1 cpu_min=1.00
PDB #1 ORA$AUTOTASK_PLAN dirtype=AUTO shrs=0
PDB #1 ORA$ROOT_PLAN shrs=0
END RESOURCE MANAGER PLAN/CONSUMER GROUP DUMP
*** 2023-02-03T06:17:17.048790+05:30 (CDB$ROOT(1))
kgsksysstop: blocking mode (2) timestamp: 176878153
kgsksysstop: successful; Total stall time = 0 ms.
kgsksysresume: successful
kgskreset: disabling CPURM: scheduled 0 vts
START RESOURCE MANAGER PLAN/CONSUMER GROUP DUMP
CDB PE: PDB #2 PDB$SEED dirtype=DEF shrs=0
PDB #2 PDB$SEED/INTERNAL_PLAN cpu_min=1.00
END RESOURCE MANAGER PLAN/CONSUMER GROUP DUMP
*** 2023-02-03T06:19:18.634520+05:30 (CDB$ROOT(1))
s: cpu% cputm cpuwt avgrun avgwt actps
s: 6 247 0 0.00 0.00 0
r: < 5 < 10 < 50 < 100 < 200 < 1000 > 1K
r: 0 0 0 0 0 0 0
*** 2023-02-03T06:24:16.426569+05:30 (CDB$ROOT(1))
s: 6 251 0 0.00 0.00 0
r: 0 0 0 0 0 0 0
By default, all of the three jobs are enabled
kIsH@xyl>>SELECT CLIENT_NAME,OPERATION_NAME,TASK_NAME,STATUS from dba_autotask_task;
CLIENT_NAME OPERATION_NAME TASK_NAME STATUS
-------------------- -------------------- -------------------- --------
sql tuning advisor automatic sql tuning AUTO_SQL_TUNING_PROG ENABLED
task
auto optimizer stats auto optimizer stats gather_stats_prog ENABLED
collection job
auto space advisor auto space advisor j auto_space_advisor_p ENABLED
ob rog
To check the logs of the autotask jobs, the below query can be used. All autotask jobs has a prefix “ORA$AT_” which easily denote autotask (AT for autotask)
kIsH@xyl>>col LOG_DATE for a20
kIsH@xyl>>col OWNER for a20
kIsH@xyl>>col JOB_NAME for a20
kIsH@xyl>>SELECT log_id, to_char(log_date,'dd-mm-yy hh24:mi') log_date, owner, job_name FROM ALL_SCHEDULER_JOB_LOG WHERE job_name like '%ORA$AT_%';
LOG_ID LOG_DATE OWNER JOB_NAME
---------- -------------------- -------------------- --------------------
248 20-03-23 22:13 SYS ORA$AT_OS_OPT_SY_1
282 20-03-23 22:13 SYS ORA$AT_OS_OPT_SY_1
284 20-03-23 22:13 SYS ORA$AT_OS_OPT_SY_1
232 20-03-23 22:00 SYS ORA$AT_OS_OPT_SY_1
234 20-03-23 22:00 SYS ORA$AT_OS_OPT_SY_1
236 20-03-23 22:00 SYS ORA$AT_OS_OPT_SY_1
238 20-03-23 22:00 SYS ORA$AT_SA_SPC_SY_2
240 20-03-23 22:00 SYS ORA$AT_SQ_SQL_SW_3
242 20-03-23 22:00 SYS ORA$AT_OS_OPT_SY_1
244 20-03-23 22:00 SYS ORA$AT_SA_SPC_SY_2
246 20-03-23 22:00 SYS ORA$AT_SQ_SQL_SW_3
252 20-03-23 22:00 SYS ORA$AT_SQ_SQL_SW_3
256 20-03-23 22:00 SYS ORA$AT_SQ_SQL_SW_3
258 20-03-23 22:00 SYS ORA$AT_SQ_SQL_SW_3
250 20-03-23 22:00 SYS ORA$AT_SA_SPC_SY_2
260 20-03-23 22:00 SYS ORA$AT_SA_SPC_SY_2
262 20-03-23 22:00 SYS ORA$AT_SA_SPC_SY_2
17 rows selected.
If more details are required for the autotask jobs, the below query can help. There are three job prefix names. They are
- ORA$AT_OS_OPT_SY_* – Optimizer stats advisor
- ORA$AT_SQ_SQL_SW_* – SQL tuning advisor
- ORA$AT_SA_SPC_SY_* – Space advisor
- ORA$AUTOTASK_CLEAN – Clean up the obsolete autotask objects using ora$age_autotask_data
If you notice the scheduled maintenance jobs, they start at 22:00 at night.
kIsH@xyl>>
set lines 200 pages 1000
col LOG_DATE for a20
col OWNER for a10
col JOB_NAME for a20
col additional_info for a20
col operation for a15
SELECT log_id, to_char(log_date,'dd-mm-yy hh24:mi') log_date, owner, job_name,
CASE WHEN job_name LIKE '%ORA$AT_OS_OPT_SY%'
THEN 'Stat advisor'
WHEN job_name LIKE '%ORA$AT_SQ_SQL_SW%'
THEN 'SQL tuning advisor'
WHEN job_name LIKE '%ORA$AT_SA_SPC_SY%'
THEN 'Space advisor'
END Job_desc,
operation,additional_info
FROM ALL_SCHEDULER_JOB_LOG WHERE job_name like '%ORA$AT_%';kIsH@xyl>>kIsH@xyl>>kIsH@xyl>>kIsH@xyl>>kIsH@xyl>>kIsH@xyl>>kIsH@xyl>> 2 3 4 5 6 7 8 9 10
LOG_ID LOG_DATE OWNER JOB_NAME JOB_DESC OPERATION ADDITIONAL_INFO
---------- -------------------- ---------- -------------------- ------------------ --------------- --------------------
248 20-03-23 22:13 SYS ORA$AT_OS_OPT_SY_1 Stat advisor RUN
282 20-03-23 22:13 SYS ORA$AT_OS_OPT_SY_1 Stat advisor COMPLETED REASON="Max runs rea
ched"
284 20-03-23 22:13 SYS ORA$AT_OS_OPT_SY_1 Stat advisor DROP REASON="Auto drop jo
b dropped", PROGRAM_
NAME=""SYS"."GATHER_
STATS_PROG"", JOB_TY
232 20-03-23 22:00 SYS ORA$AT_OS_OPT_SY_1 Stat advisor UPDATE STOP_ON_WINDOW_CLOSE
="TRUE", PREVIOUS_VA
LUE="TRUE"
234 20-03-23 22:00 SYS ORA$AT_OS_OPT_SY_1 Stat advisor UPDATE USER_OPERATIONS_CALL
BACK="DBMS_STATS.CLE
ANUP_STATS_JOB_PROC"
, PREVIOUS_VALUE="NU
236 20-03-23 22:00 SYS ORA$AT_OS_OPT_SY_1 Stat advisor UPDATE USER_CALLBACK_CONTEX
T="1", PREVIOUS_VALU
E="NULL"
238 20-03-23 22:00 SYS ORA$AT_SA_SPC_SY_2 Space advisor UPDATE STOP_ON_WINDOW_CLOSE
="TRUE", PREVIOUS_VA
LUE="TRUE"
240 20-03-23 22:00 SYS ORA$AT_SQ_SQL_SW_3 SQL tuning advisor UPDATE STOP_ON_WINDOW_CLOSE
="TRUE", PREVIOUS_VA
LUE="TRUE"
To manually trigger the autotask, use the below procedure.
kIsH@xyl>>EXEC DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;
PL/SQL procedure successfully completed.Usually, these scheduled maintenance window can be extended as per the size of the database and resource availability in the system.
If day to day DML transactions are quite high and more resources are available, then give power to oracle to complete the mandatory jobs with in less time or increase the maintenance time. In order for the jobs to be successful without failure, then follow the below guidelines,
- Create a baseline for job performance
- Increase the duration of maintenance hours
- Improve performance of jobs using parallel and concurrent resources
- Configure resource groups to distribute the resource to prevent over or under allocation to specific job
- Identify the cause of slowness for any bugs, recent data load or internal query using suboptimal plans
kIsH@xyl>>show parameter job_queue
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 20
kIsH@xyl>>show parameter statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_statistics_level string TYPICAL
statistics_level string TYPICAL
To trace the auto task jobs, use event 27402
SQL> alter session set tracefile_identifier='schedtrc';
Session altered.
SQL> alter system set events '27402 trace name context forever, level 1023';
System altered.