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.
(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 –
- which resource consumer groups are defined –
- which group mappings are defined –
- which resource plan directives are defined –
- 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_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.
You can check which directives are defined for plans in your database.
select plan, group_or_subplan, mgmt_p1, mgmt_p2, mgmt_p3
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
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.