DATABASE ALSO NEEDS REST AT NIGHT TO WORK EFFICIENTLY

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/subplanLevel 1Maximum Utilization Limit
ORA$AUTOTASK5%90
OTHER_GROUPS20%
SYS_GROUP75%

•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 NameDescription
MONDAY_WINDOW10 pm to 2 am
TUESDAY_WINDOW10 pm to 2 am
WEDNESDAY_WINDOW10 pm to 2 am
THURSDAY_WINDOW10 pm to 2 am
FRIDAY_WINDOW10 pm to 2 am
SATURDAY_WINDOW6 am to 2am(20 hours window)
SUNDAY_WINDOW6 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.

Leave a Reply

%d bloggers like this: