Database maintenance tasks

oracle maintenance

The default maintenance window is not a good fit for every database, by default the maintenance window start at 10 PM and run for 4 hours during the week and start at 6 AM and last for 20 hours during the week-end. A different window exists for each day of the week and all the windows are grouped inside the MAINTENANCE_WINDOW_GROUP. It is possible to alter each window independently or to create a single window for all days of the week.

The following maintenance tasks are performed during the maintenance window:

  • Optimizer statistics (OPTIMIZER_STATS)
  • Segment advisor (SEGMENT_ADVISOR)
  • SQL Tuning (SQL_TUNE_ADVISOR)
  • Health Monitor (HEALTH_MONITOR)

And the default window group named MAINTENANCE_WINDOW_GROUP is associated with a DEFAULT_MAINTENANCE_PLAN which has the following configuration:

Consumer Group/subplanLevel 1Maximum Utilization Limit
ORA$AUTOTASK5%90
OTHER_GROUPS20%
SYS_GROUP75%

In this plan 75% of the resources are affected to sessions created by SYS or SYSTEM user. If these resources are not used by the SYS_GROUP then they are transfered to the other groups. As you can see there is also a restriction for the automatic maintenance tasks, they cannot use more that 90% of the resources.

here is what you is configured by default, independently of the management pack configured and the edition you use. This configuration is from a standard edition database with control_management_pack_access set to NONE.

Continue reading Database maintenance tasks