Oracle DB Performance – Slowdown with DEFAULT_MAINTENANCE_PLAN

maintenanceoracle

After midnight Oracle Database 12c turned to "slow mode". DML statements became about 10 times slower, though "selects" feel good. CPU load as usual, disk I/O is perfect (tested by copying 500m file), there are lots of free RAM and free disk space.

Restart of Oracle didn't help. All of the user sessions were killed if there were any.

In about one hour "slow mode" went away. Everything became as fast as before.

I've seen similar behavior on 11g as well.

Logs show:

    Thu Aug 07 00:38:13 2014
    Setting Resource Manager plan SCHEDULER[0x420E]:DEFAULT_MAINTENANCE_PLAN via scheduler window
    Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter

and when it's gone I see:

    Thu Aug 07 02:00:03 2014
    Closing scheduler window
    Closing Resource Manager plan via scheduler window
    Clearing Resource Manager plan via parameter

Nothing else that can be related to the above bug/feature I can see in the logs.

This trouble happens time after time.

How to avoid this?

Best Answer

Seems like there are some Database Resource Manager (DRM) constraints put on user sessions when the database switches to maintenance plan via Scheduler window each night (as evident by your log portion).

There are at least two predefined DRM plans when a database is created.

  1. DEFAULT_PLAN
  2. DEFAULT_MAINTENANCE_PLAN

(See the complete list.)

When a window activates, it can switch the current resource plan to one specified in the window. You can query ALL_SCHEDULER_WINDOWS view to see which plan each window is assigned.

You can also switch between plans in Enterprise Manager or manually changing RESOURCE_MANAGER_PLAN initialization parameter value.

You can use Enterprise Manager or DBA_RSRC_* views to see

  • which plans are active in database – DBA_RSRC_PLANS
  • which resource consumer groups are defined – DBA_RSRC_CONSUMER_GROUPS
  • which group mappings are defined – DBA_RSRC_GROUP_MAPPINGS
  • which resource plan directives are defined – DBA_RSRC_PLAN_DIRECTIVES
  • and so on

Resource plan directives is what defines resource usage by the database sessions. The sessions map to consumer groups via group mappings.

When your database switches to another plan (using window or manually), the different set of directives is enabled which controls the resource usage by sessions. Thus when your database switches from DEFAULT_PLAN to DEFAULT_MAINTENANCE_PLAN and vice versa, your user sessions notice the difference – their performance dynamically changes according to the resource directives defined in the enabled plan.

For more details on implementing and using resource plans in DRM, consult the Managing Resources with Oracle Database Resource Manager chapter in Database Administrator's Guide.

UPDATE

You can check which directives are defined for plans in your database.

select plan, group_or_subplan, mgmt_p1, mgmt_p2, mgmt_p3
from DBA_RSRC_PLAN_DIRECTIVES
where plan like ('DEFAULT_%')
order by plan, mgmt_p1 desc, mgmt_p2 desc, mgmt_p3 desc;

PLAN                        GROUP_OR_SUBPLAN    MGMT_P1 MGMT_P2 MGMT_P3
------------------------    ----------------    ------- ------- -------
DEFAULT_MAINTENANCE_PLAN    SYS_GROUP                75       0       0
DEFAULT_MAINTENANCE_PLAN    OTHER_GROUPS              0      70       0
DEFAULT_MAINTENANCE_PLAN    ORA$AUTOTASK_SUB_PLAN     0      25       0
DEFAULT_MAINTENANCE_PLAN    ORA$DIAGNOSTICS           0       5       0
DEFAULT_PLAN                SYS_GROUP                75       0       0
DEFAULT_PLAN                OTHER_GROUPS              0      90       0
DEFAULT_PLAN                ORA$AUTOTASK_SUB_PLAN     0       5       0
DEFAULT_PLAN                ORA$DIAGNOSTICS           0       5       0

Notice how resources are allocated between different consumer groups. Here OTHER_GROUPS and ORA$AUTOTASK_SUB_PLAN are allowed to use 70% and 25% of CPU respectively in DEFAULT_MAINTENANCE_PLAN plan, while in DEFAULT_PLAN they are allowed 90% and 5% respectively.

It means that these directives might be one of the reasons your users' sessions run slower when maintenance windows are open, because users are allocated less resources than what they are during work hours.

Related Question