Hello Friend's,
In this post we will discuss how to deploy Oracle Resource manager with Services
In multi-tenant and multi-workload database environments, ensuring fair and efficient resource allocation is critical. Oracle Resource Manager (ORM) - combined with database services - enables DBAs to control CPU, memory, and parallel execution resources for particular users or applications.
This post offers a step-by-step approach to using Oracle Resource Manager with service-based resource allocation, allocating the resources critical users need while avoiding resource starvation to others.
1. Why Use Services with Resource Manager?
Database services and consumer group mapping can be used by DBAs to:
✅ Segregate workloads (e.g., OLTP vs. Reporting).
✅ Allocate critical applications (e.g., E-commerce vs. analytics).
✅ Dynamically allocate resources without application changes.
2. Key Components
3. Step-by-Step Implementation
Step 1: Set Up a Pending AreaSet up a pending area before defining plans to stage changes.
-- Set up a pending area
EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
Step 2: Set Up Consumer Groups
Set up groups for various workloads (e.g., APP_USERS, REPORTING_USERS).
-- Set up consumer groups
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
CONSUMER_GROUP => 'APP_USERS',
COMMENT => 'High-priority application users'
);
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
CONSUMER_GROUP => 'REPORTING_USERS',
COMMENT => 'Low-priority reporting users'
);
END;
/
Step 3: Create a Resource Plan
Define how CPU and parallelism are allocated.
-- Create a resource plan
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN(
PLAN => 'PRIORITY_PLAN',
COMMENT => 'Prioritizes APP_USERS over REPORTING_USERS'
);
-- 80% CPU to APP_USERS, 20% to REPORTING_USERS
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
PLAN => 'PRIORITY_PLAN',
GROUP_OR_SUBPLAN => 'APP_USERS',
CPU_P1 => 80,
PARALLEL_DEGREE_LIMIT_P1 => 4
);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
PLAN => 'PRIORITY_PLAN',
GROUP_OR_SUBPLAN => 'REPORTING_USERS',
CPU_P1 => 20,
PARALLEL_DEGREE_LIMIT_P1 => 2
);
END;
/
Step 4: Create a Database Service
Link the service to the consumer group.
-- Create a service for APP_USERS
BEGIN
DBMS_SERVICE.CREATE_SERVICE(
SERVICE_NAME => 'APP_SERVICE',
NETWORK_NAME => 'APP_SERVICE',
AQ_HA_NOTIFICATIONS => TRUE
);
-- Map the service to the consumer group
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
ATTRIBUTE => 'SERVICE_NAME',
VALUE => 'APP_SERVICE',
CONSUMER_GROUP => 'APP_USERS'
);
END;
/
Step 5: Validate & Activate the Plan
-- Validate changes
EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
-- Submit the pending area
EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
-- Activate the plan
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'PRIORITY_PLAN';
Step 6: Connect Users via the Service
Users connecting via APP_SERVICE will automatically use the APP_USERS consumer group.
# Example: SQL*Plus connection using the service
sqlplus user/password@db_host:1521/APP_SERVICE
4. Monitoring & Verification
Check resource allocations and active sessions:
-- Verify active services
SELECT name, service_id FROM V$SERVICES;
-- Check consumer group assignments
SELECT service_name, resource_consumer_group
FROM V$SERVICES;
-- Watch CPU usage by group
SELECT consumer_group_name, cpu_consumed_time
FROM V$RSRC_CONSUMER_GROUP;
5. Real-World Use Cases
✅ E-Commerce Priority – APP_SERVICE (checkout process) should have 80% CPU and REPORTING_SERVICE (analytics) should receive minimal resources.
✅ Batch Job Control – ETL_SERVICE should be limited to low-priority resources during working hours.
✅ Multi-Tenant Isolation – Assign isolated resources per tenant through services.
6. Best Practices
✔ Test in a non-production environment prior to implementing in production.
✔ Use Oracle Enterprise Manager (OEM) for GUI-based management.
✔ Combine with Oracle Scheduler for time-based plan switching.
ConversionConversion EmoticonEmoticon