When interactive requests slow down while batch processing runs, first establish which control applies and what the sessions are waiting for. Account profile limits, consumer groups, and the resource plans at the CDB and PDB levels are different controls. Connect each defined policy to evidence of the plan that is active.
Oracle DBA Lesson 104 Part 1 — Identify which resource control governs a workload
Account limits and workload allocation
An account profile contains limits assigned to a database user. SESSIONS_PER_USER limits that user's concurrent sessions. Resource-related profile limits require RESOURCE_LIMIT to be enabled. Password-related limits are enforced independently of that parameter. CPU_PER_SESSION and CPU_PER_CALL limit accumulated CPU consumption in hundredths of seconds. These account limits are separate from allocating processor time among competing workloads.
Oracle Database Resource Manager classifies sessions into resource consumer groups and applies the directives in the active resource plan. One group can hold interactive sessions, and another can hold batch sessions. Mapping can depend on session attributes such as service, user, or module, and a session's group can change. Read the actual mapping and the current group.
The account profiles discussed here are different from SQL profiles used by the optimizer. A CDB PDB performance profile is a third concept: it can supply CDB resource directives to a set of PDBs. Keep the three meanings separate when reading configuration.
Full Database Resource Manager is an Enterprise Edition feature. Standard Edition 2 instance caging does not establish eligibility for the complete consumer-group and PDB resource-management workflow. Dictionary visibility alone does not establish that entitlement. See CREATE PROFILE, RESOURCE_LIMIT, Oracle Database Resource Manager, and Licensing Information.
Follow both allocation layers
In a multitenant database, allocation occurs in two steps:
- The CDB resource plan, configured in the root, allocates resources among PDBs.
- A PDB resource plan allocates the resources received by that PDB among its local consumer groups and their sessions.
PDB A and PDB B can share the CPU capacity available to the CDB. Interactive and batch consumer groups inside PDB A then share PDB A's allocation. Increasing an interactive group's local allocation still operates inside that outer allocation. The root's activity is managed automatically. These two PDBs are not an exhaustive list of database activity.
CPU management at both layers depends on the applicable CDB configuration. Oracle documents automatic activation of DEFAULT_CDB_PLAN when a PDB plan that requires CPU or parallel-statement-queuing management is enabled without an explicit CDB plan. A blank parameter alone is insufficient evidence that no resource management is occurring. The 19c Multitenant guide also describes CPU_MIN_COUNT and CPU_COUNT behavior when CDB shares and utilization directives are unset. Check the release update, the parameters, the runtime views, and the documented configuration. A simple shares example does not describe every installation.
See Using Oracle Resource Manager for PDBs, RESOURCE_MANAGER_PLAN, and V$RSRC_PLAN.
Shares, utilization limits and switching rules
An illustrative CDB plan gives PDB A 2 shares and PDB B 1 share. Shares provide relative allocation weights under applicable contention. PDB A has the larger relative entitlement in that comparison. Shares do not promise an absolute amount of physical host CPU, and they do not make a query complete twice as quickly.
Actual allocation depends on the participating workloads, demand, available CDB capacity, and the other directives and limits. Oracle permits use above a share allocation when capacity is available, subject to the applicable limits.
A utilization limit is a ceiling. A PDB CPU utilization limit of 40 caps that PDB at 40 percent of the CPU capacity available to the CDB, including when competing PDBs leave capacity idle. It does not assign 40 percent to each session. Host-wide activity outside the CDB, instance caging, and other applicable controls still matter when you interpret that capacity.
A switching rule names a condition and an action. A configured consumption threshold can move a session to another consumer group. Resource Manager also supports other threshold actions, so read the actual directive. A threshold does not always mean a group switch. Consumer-group switching can also result from reevaluated session mapping rules.
See Using Oracle Resource Manager for PDBs and Oracle Database Resource Manager.
Identify the container, plan selection, and runtime state
Run the same checks in the root, then repeat them in the affected PDB. Confirm the container after each connection.
SHOW CON_NAME
SHOW PARAMETER resource_manager_plan
SELECT name, is_top_plan, cpu_managed
FROM v$rsrc_plan
ORDER BY name;SHOW is a SQL*Plus command. The parameter describes resource-plan selection in the current scope: the CDB plan in the root, and the PDB plan inside a PDB. V$RSRC_PLAN describes currently active plans. IS_TOP_PLAN = 'TRUE' identifies the current top plan, and FALSE identifies a subplan. CPU_MANAGED = 'ON' means the plan has a CPU scheduling policy. OFF means it is not managing CPU usage.
Maintenance and Scheduler windows can change the active plan. When a window closes, its predecessor can be restored. Review the time period of the slowdown, not only a later snapshot. No rows, unexpected flags, or an unexpected plan require a check of container, configuration, and timing before you draw a conclusion. Root and common-user visibility, including container-data restrictions, can change which rows appear. Reading these views is separate from the privileges required to administer resource plans.
See RESOURCE_MANAGER_PLAN, V$RSRC_PLAN, and Oracle Database Resource Manager.
Read the relevant root directives
After confirming the root:
SELECT plan, pluggable_database, shares, utilization_limit
FROM dba_cdb_rsrc_plan_directives
ORDER BY plan, pluggable_database;This view contains defined directives across CDB plans. Match PLAN to the active CDB plan before interpreting a PDB's shares or limit. A row in this dictionary view is not proof that its plan is active.
Include ORA$DEFAULT_PDB_DIRECTIVE, the default PDB directive, when a PDB lacks a specific directive. Where PDB performance profiles are in use, read the profile membership and the profile directive. PLUGGABLE_DATABASE can be null for a performance-profile directive. A missing row for one PDB name does not establish the absence of a policy. Pending-area edits are distinct from the installed policy. Read the selected directive's status together with the plan that is current.
See DBA_CDB_RSRC_PLAN_DIRECTIVES and Using Oracle Resource Manager for PDBs.
Read local definitions and session membership
In the affected PDB:
SHOW CON_NAME
SELECT plan, status
FROM dba_rsrc_plans
ORDER BY plan;
SELECT consumer_group
FROM dba_rsrc_consumer_groups
ORDER BY consumer_group;DBA_RSRC_PLANS lists plan definitions. STATUS = 'PENDING' means a plan is in the editing pending area. A null status means it is outside that pending area. Neither status means the plan is currently active. Some imported legacy plans may be recorded with LEGACY. Use the current transformed definitions. Consumer-group definitions likewise do not prove the affected session's current membership.
Repeat the active-plan query in this PDB. Then read the active local plan's directives:
SELECT plan, group_or_subplan, type, mgmt_p1,
utilization_limit, switch_group, switch_time
FROM dba_rsrc_plan_directives
WHERE plan = :active_local_plan
ORDER BY group_or_subplan;In SQL*Plus, define a VARCHAR2(128) client bind named active_local_plan and assign the name returned by the current PDB's runtime view before running the query. These fields identify the target group, the allocation directive, the utilization cap, and the selected switching settings. SWITCH_TIME is CPU-time related. Other threshold fields and SWITCH_FOR_CALL can change the applicable condition or the measurement scope. Read the complete directive. PDB plans have restrictions, including no subplans and no multiple-level CPU scheduling policy. A non-CDB multilevel example does not transfer unchanged.
See DBA_RSRC_PLANS, DBA_RSRC_CONSUMER_GROUPS, and DBA_RSRC_PLAN_DIRECTIVES.
To read a particular session's current group:
SELECT sid, serial#, resource_consumer_group
FROM v$session
WHERE sid = :target_sid;Define a SQL*Plus NUMBER bind named target_sid and set it to the session identifier. Confirm SERIAL#, the container, and the time so that a reused session identifier is not mistaken for the original session. These statements read state. They do not switch groups or change plans.
See V$SESSION.
Connect the policy to the slowdown
Write the evidence chain:
Session and time → current consumer group → active local plan and directive → PDB → active root plan and applicable directive.
Relate that chain to workload demand, available host and CDB capacity, and the current wait evidence. CPU allocation matters when the workload requires processor time and the applicable policy constrains it. A session waiting for a lock needs investigation of the blocker. Interpret a wait event as current only when the session state indicates that the session is waiting now. Otherwise the event may describe a previous wait. A plan name, a larger share count, or one slow query is insufficient to establish causation.
This part establishes the control path before any proposed change.
Recap
Account profiles, consumer groups, and resource plans govern different things. An account profile limits one user. With resource limits enabled, SESSIONS_PER_USER limits concurrent sessions, and CPU_PER_SESSION and CPU_PER_CALL limit accumulated CPU in hundredths of seconds. Resource Manager classifies sessions into consumer groups and applies the active plan. SQL profiles and PDB performance profiles are separate meanings. In a multitenant database the CDB resource plan allocates resources among PDBs, and the PDB resource plan allocates what that PDB received among its consumer groups. Shares are relative weights under contention. A utilization limit is a ceiling that still applies when other PDBs are idle. A switching rule is a separate condition and action.
Read V$RSRC_PLAN in the root and again in the PDB, with the container name and the time of the slowdown. Scheduler windows can change the active plan. A blank RESOURCE_MANAGER_PLAN is insufficient evidence that resource management is idle, because Oracle can activate DEFAULT_CDB_PLAN when a PDB plan needs CPU or parallel-statement-queuing management and no explicit CDB plan is set. Match the active CDB plan to DBA_CDB_RSRC_PLAN_DIRECTIVES, including ORA$DEFAULT_PDB_DIRECTIVE or a performance-profile directive when a PDB has no row of its own. In the PDB, a null STATUS means the definition is outside the pending area. It does not mean the plan is active. Follow the session's current consumer group through the active local directive and the applicable root directive, then compare that policy with demand, capacity, and the current wait.
No comments:
Post a Comment