Hello Friend's,
IntroductionIn modern database environments that are complex and where several applications and users vie for limited system resources, resource allocation that is fair and efficient is more essential than ever before. Oracle Resource Manager (ORM) is a strong solution to this problem, and when coupled with database services, it becomes an even sharper instrument for managing workloads. This in-depth guide will lead you through how to implement Oracle Resource Manager using service-based resource allocation, including examples and best practices.
Why Resource Manager with Services Matters
Current databases normally support:
Mission-critical OLTP applications
Resource-hungry reporting systems
Batch processing jobs
Ad-hoc analytical queries
If not managed with adequate resource control, these conflicting workloads can result in:
✔ Deteriorated performance for critical applications
✔ Unpredictable query response times
✔ Starvation of resources for priority workloads
Oracle Resource Manager with services addresses these issues by allowing:
✅ Workload isolation through dedicated services
✅ Predictable performance through assured resource allocation
✅ Dynamic adjustments without application modifications
Core Implementation Concepts
1. Database Services: Your Workload Gatekeepers
Database services are logical access points that:
Group related database components
Allow service-specific resource allocation
Offer failover in RAC environments
2. Resource Manager Components
Step-by-Step Implementation Guide
Phase 1: Setting Up the Foundation
-- Create pending area for changes
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
END;
/
-- Verify pending area status
SELECT * FROM DBA_RSRC_PLAN_DIRECTIVES WHERE STATUS = 'PENDING';
Phase 2: Defining Consumer Groups
BEGIN
-- Priority application users
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
CONSUMER_GROUP => 'GOLD_TIER',
COMMENT => 'Mission-critical application users'
);
-- Standard reporting users
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
CONSUMER_GROUP => 'SILVER_TIER',
COMMENT => 'Standard reporting users'
);
-- Background processes
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
CONSUMER_GROUP => 'BRONZE_TIER',
COMMENT => 'Low-priority batch jobs'
);
END;
/
Phase 3: Creating a Multi-Level Resource Plan
BEGIN
-- Create the resource plan
DBMS_RESOURCE_MANAGER.CREATE_PLAN(
PLAN => 'TIERED_PLAN',
COMMENT => 'Three-tier resource allocation strategy'
);
-- Gold tier gets 60% CPU with parallel degree 8
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
PLAN => 'TIERED_PLAN',
GROUP_OR_SUBPLAN => 'GOLD_TIER',
CPU_P1 => 60,
PARALLEL_DEGREE_LIMIT_P1 => 8,
ACTIVE_SESS_POOL_P1 => 50
);
-- Silver tier gets 30% CPU with parallel degree 4
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
PLAN => 'TIERED_PLAN',
GROUP_OR_SUBPLAN => 'SILVER_TIER',
CPU_P1 => 30,
PARALLEL_DEGREE_LIMIT_P1 => 4,
ACTIVE_SESS_POOL_P1 => 30
);
-- Bronze tier gets 10% CPU with parallel degree 2
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
PLAN => 'TIERED_PLAN',
GROUP_OR_SUBPLAN => 'BRONZE_TIER',
CPU_P1 => 10,
PARALLEL_DEGREE_LIMIT_P1 => 2,
ACTIVE_SESS_POOL_P1 => 20
);
END;
/
Phase 4: Creating and Mapping Services
BEGIN
-- Create service for gold tier applications
DBMS_SERVICE.CREATE_SERVICE(
SERVICE_NAME => 'GOLD_APP_SVC',
NETWORK_NAME => 'GOLD_APP_SVC',
AQ_HA_NOTIFICATIONS => TRUE
);
-- Map service to consumer group
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
ATTRIBUTE => 'SERVICE_NAME',
VALUE => 'GOLD_APP_SVC',
CONSUMER_GROUP => 'GOLD_TIER'
);
-- Repeat for other tiers
DBMS_SERVICE.CREATE_SERVICE(
SERVICE_NAME => 'SILVER_REPORT_SVC',
NETWORK_NAME => 'SILVER_REPORT_SVC'
);
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
ATTRIBUTE => 'SERVICE_NAME',
VALUE => 'SILVER_REPORT_SVC',
CONSUMER_GROUP => 'SILVER_TIER'
);
END;
/
Phase 5: Finalizing and Activating
-- Validate all changes
EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
-- Commit the configuration
EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
-- Activate the plan
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'TIERED_PLAN';
Advanced Configuration Options
1. Time-Based Plan Switching
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'BUSINESS_HOURS',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; BYHOUR=9,10,11,12,13,14,15,16,17; BYMINUTE=0',
end_date => NULL,
comments => 'Standard business hours'
);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => 'BUSINESS_HOURS_PLAN',
group_or_subplan => 'GOLD_TIER',
cpu_p1 => 70
);
-- Create window group and schedule
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN_SCHEDULE(
schedule_name => 'PLAN_SCHEDULE',
start_time => '09:00:00',
duration => INTERVAL '8' HOUR,
repeat_interval => 'FREQ=DAILY',
end_date => NULL,
plan => 'TIERED_PLAN'
);
END;
/
2. Implementing Session Pools
BEGIN
DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
PLAN => 'TIERED_PLAN',
GROUP_OR_SUBPLAN => 'BRONZE_TIER',
NEW_ACTIVE_SESS_POOL_P1 => 10,
NEW_QUEUEING_P1 => 300
);
END;
/
Monitoring and Troubleshooting
Key Views for Monitoring
-- Active resource plan
SELECT name, is_top_plan FROM v$rsrc_plan;
-- Consumer group resource usage
SELECT consumer_group_name, cpu_consumed_time, cpu_wait_time
FROM v$rsrc_consumer_group;
-- Service to consumer group mapping
SELECT service_name, resource_consumer_group
FROM v$services;
-- Current session assignments
SELECT sid, serial#, service_name, resource_consumer_group
FROM v$session WHERE service_name IS NOT NULL;
Common Issues and Solutions
Service Not Honoring Resource Allocation
Verify service mapping: SELECT * FROM DBA_RSRC_GROUP_MAPPINGS;
Check for conflicting mappings
Resource Starvation
Review plan directives: SELECT * FROM DBA_RSRC_PLAN_DIRECTIVES;
Consider adding MAX_UTILIZATION_LIMIT
Plan Not Activating
Check ALTER SYSTEM permissions
Verify pending area was submitted
Real-World Implementation Patterns
E-Commerce Platform Example
-- Prime time allocation (9AM-5PM)
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
PLAN => 'ECOMMERCE_PEAK',
GROUP_OR_SUBPLAN => 'CHECKOUT_SERVICE',
CPU_P1 => 70,
PARALLEL_DEGREE_LIMIT_P1 => 8,
ACTIVE_SESS_POOL_P1 => 100
);
END;
/
-- Off-hours allocation
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
PLAN => 'ECOMMERCE_OFFPEAK',
GROUP_OR_SUBPLAN => 'ETL_SERVICE',
CPU_P1 => 60,
PARALLEL_DEGREE_LIMIT_P1 => 16
);
END;
/
Financial Reporting System
-- Month-end close configuration
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
PLAN => 'FINANCIAL_CLOSE',
GROUP_OR_SUBPLAN => 'GL_PROCESSING',
CPU_P1 => 80,
MAX_EST_EXEC_TIME => 3600
);
END;
/
Best Practices for Production Deployments
Gradual Rollout Strategy
Start with monitoring-only mode
Implement in test first
Use percentage-based increments
Comprehensive Documentation
Maintain a runbook of all resource plans
Document service-to-group mappings
Record historical changes
Automated Validation
CREATE OR REPLACE PROCEDURE validate_resource_config AS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM DBA_RSRC_PLANS;
IF v_count = 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'No resource plans defined');
END IF;
-- Additional validation checks
END;
/
Performance Baselining
Capture AWR snapshots before/after changes
Monitor key metrics:
SELECT metric_name, value
FROM v$sysmetric
WHERE metric_name IN ('CPU Usage Per Sec', 'Database CPU Time Ratio');
Conclusion: Transforming Resource Management
Implementing Oracle Resource Manager with service-based allocation provides:
🔹 Predictable performance for critical applications
🔹 Efficient resource utilization across workloads
🔹 Dynamic adaptability to changing business needs
ConversionConversion EmoticonEmoticon