Oracle DB occasionally slows down when "Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN"

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?

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.

• DEFAULT_PLAN

• 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.

StackExchange Database Administrators Q#73600, answer score: 3