Design Governance for OLTP, Batch, Analytics, Maintenance, and Administrative Workloads

Learning outcomes

The final governance problem is organizational, not syntactic. ServiceHub needs low-latency OLTP, predictable nightly batch completion, analyst access, safe backups/maintenance and emergency DBA access. Any one of those can consume enough shared resources to damage the others. A production policy must connect workload identity, SLO, capacity, resource controls, services, monitoring and rollback so governance itself does not become the outage.

1. Workload class starts with SLO and failure behavior

No class should be “unlimited because important.” Critical work still needs a measured capacity/resilience contract.

2. Free policy record

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE servicehub_workload_policy PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE servicehub_workload_policy (  workload_class      VARCHAR2(20) PRIMARY KEY,  service_name        VARCHAR2(64) NOT NULL,  module_pattern      VARCHAR2(128),  priority_rank       NUMBER NOT NULL,  cpu_shares          NUMBER,  max_active_calls    NUMBER,  max_dop             NUMBER,  queue_seconds       NUMBER,  runaway_action      VARCHAR2(30),  owner_team          VARCHAR2(60) NOT NULL,  rollback_rule       VARCHAR2(500) NOT NULL);INSERT INTO servicehub_workload_policy VALUES ('OLTP','servicehub_oltp','SERVICEHUB_API%',1,8,NULL,2,NULL,  'ALERT','Application',  'Disable new plan if p95/p99 latency or error rate breaches rollback gate');INSERT INTO servicehub_workload_policy VALUES ('BATCH','servicehub_batch','SERVICEHUB_BATCH%',3,3,2,4,120,  'QUEUE/RETRY','Data Ops',  'Pause scheduler and return to prior plan if backlog or OLTP impact breaches gate');INSERT INTO servicehub_workload_policy VALUES ('ANALYTICS','servicehub_analytics','SERVICEHUB_REPORT%',4,2,2,8,120,  'CANCEL_CALL','Analytics',  'Remove cancel/queue directive if valid reports breach agreed completion SLO');INSERT INTO servicehub_workload_policy VALUES ('MAINT','servicehub_maint','SERVICEHUB_MAINT%',2,1,1,2,300,  'WINDOW_ONLY','DBA',  'Stop maintenance and restore prior plan/service placement on validation failure');INSERT INTO servicehub_workload_policy VALUES ('ADMIN','servicehub_admin','DBA%',0,NULL,NULL,NULL,NULL,  'AUDITED_OVERRIDE','DBA/Security',  'Expire override immediately after incident and review audit trail');COMMIT;SELECT *FROM servicehub_workload_policyORDER BY priority_rank;

3. Capacity evidence must precede limits

SELECT  SYSTIMESTAMP AS captured_at,  MAX(CASE WHEN name='user commits' THEN value END) AS commits,  MAX(CASE WHEN name='redo size' THEN value END) AS redo_bytes,  MAX(CASE WHEN name='session logical reads' THEN value END) AS logical_reads,  MAX(CASE WHEN name='physical reads' THEN value END) AS physical_reads,  MAX(CASE WHEN name='parse count (hard)' THEN value END) AS hard_parsesFROM v$sysstatWHERE name IN (  'user commits',  'redo size',  'session logical reads',  'physical reads',  'parse count (hard)');SELECT  service_name,  module,  action,  COUNT(*) AS sessions,  SUM(CASE WHEN status='ACTIVE' THEN 1 ELSE 0 END) AS active_sessionsFROM v$sessionWHERE type='USER'GROUP BY service_name,module,actionORDER BY active_sessions DESC;

Compute interval deltas and correlate them with application latency/job completion. Limits selected without concurrency/resource measurements merely convert unknown capacity into arbitrary failures.

4. Entitled Resource Manager policy maps directly from this record

-- After consumer groups and mappings exist in a pending area:DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(  plan=>'SH21_GOV_PLAN',  group_or_subplan=>'SH21_OLTP',  shares=>8,  parallel_degree_limit_p1=>2);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(  plan=>'SH21_GOV_PLAN',  group_or_subplan=>'SH21_BATCH',  shares=>3,  active_sess_pool_p1=>2,  queueing_p1=>120,  parallel_degree_limit_p1=>4);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(  plan=>'SH21_GOV_PLAN',  group_or_subplan=>'SH21_ANALYTICS',  shares=>2,  active_sess_pool_p1=>2,  queueing_p1=>120,  parallel_degree_limit_p1=>8,  switch_group=>'CANCEL_SQL',  switch_elapsed_time=>120,  switch_for_call=>TRUE);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(  plan=>'SH21_GOV_PLAN',  group_or_subplan=>'SH21_MAINT',  shares=>1);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(  plan=>'SH21_GOV_PLAN',  group_or_subplan=>'OTHER_GROUPS',  shares=>1);

The policy intentionally leaves OLTP out of active-session queuing. Parallel attributes only matter on offerings licensed/configured for parallel execution.

5. Maintenance windows can switch plans—but plan changes need rollback

Oracle Scheduler windows can associate a Resource Manager plan so a maintenance policy activates during a defined window. This can be useful for batch/maintenance, but a bad plan can starve interactive work. Record the previous RESOURCE_MANAGER_PLAN value and have a tested manual override.

SELECT value AS prior_planFROM v$parameterWHERE name='resource_manager_plan';-- Activate:ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='SH21_GOV_PLAN';-- Emergency rollback:ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='';-- Or restore the exact previously recorded plan name.

Setting an empty plan disables the user top plan, but automated maintenance windows can still activate their referenced plans. Full disablement requires reviewing Scheduler window associations.

6. Emergency override is a controlled exception

Incident recovery may need RMAN, Data Pump, schema repair or privileged diagnostic work to outrank normal workloads. Do not grant application users SYSDBA or permanently place everything in SYS_GROUP. Use dedicated administrative accounts/roles/services and time-bounded change approval/auditing.

Resource Manager already has predefined function mappings for RMAN backup/copy and Data Pump data load to built-in groups. Review rather than blindly deleting those mappings.

7. Free alternatives for capacity governance

When Resource Manager is unavailable, the control plane moves outward:

• Separate services/connection pools for OLTP, batch and reporting.
• Bound batch workers in the scheduler/orchestrator.
• Use application semaphores/queues and backpressure for expensive work.
• Set driver statement/query timeouts where correctness permits cancellation.
• Use OS/container CPU/memory limits at the deployment boundary where supported.
• Schedule Data Pump/RMAN/maintenance outside peak windows.
• Tune the SQL/schema first; throttling an inefficient plan does not make it efficient.

These controls do not provide Resource Manager's in-database CPU scheduling, but they can still implement a defensible Free/self-managed workload contract.

8. Deliberately wrong: give OLTP 100% and starve OTHER_GROUPS

A plan that effectively leaves maintenance, monitoring or required background/admin work without progress can create operational deadlock: the database remains “up” while recovery/maintenance cannot finish. Shares should express preference under contention, not denial of necessary progress. Keep OTHER_GROUPS covered and test all required operational jobs.

9. Monitoring signals and acceptance gates

10. 26ai CPU scope note

RESOURCE_MANAGER_CPU_SCOPE=SERVER_WIDE is a new 26ai option for CPU management across multiple database instances on one supported Linux Exadata server, using Linux cgroups. Default is INSTANCE_ONLY. The parameter is static, non-PDB-modifiable and unsupported on ordinary non-Exadata Linux/Windows. It is an infrastructure design choice, not a portable Resource Manager default.

11. Cleanup

DROP TABLE servicehub_workload_policy PURGE;

12. Production judgment and chapter close

Governance succeeds when critical work meets its SLO while background work still makes predictable progress. Make workload identity observable first, then encode priorities/limits, test under realistic concurrency and failure/maintenance scenarios, surface queue/cancel errors to applications, and preserve a fast rollback path. Review the policy whenever workload mix, hardware, topology or business deadlines change.

Current baseline: 26ai RU 23.26.3, SQL Developer 26.2 and SQLcl 26.2.1. Database Resource Manager is not available in Free; the Free design path uses services, instrumentation and application/external admission controls. Normal plan activation is dynamic/PDB-scoped; the new server-wide CPU scope is static and Exadata/Linux-specific. No hidden parameters or universal limits are recommended.

Authoritative references

• Managing Resources with Oracle AI Database Resource Manager — end-to-end governance and maintenance windows
• Resource Manager Data Dictionary/Dynamic Views — V$RSRC/DBA_RSRC monitoring
• RESOURCE_MANAGER_PLAN — activation and PDB scope
• RESOURCE_MANAGER_CPU_SCOPE — 26ai server-wide mode
• Licensing Information — Resource Manager/parallel/HA option boundaries