Mapping Sessions to Consumer Groups by Service, User, Module, and Workload Class

Learning outcomes

ServiceHub uses the same database user for HTTP requests, monthly reports and a reconciliation job. If Resource Manager maps only SERVICEHUB_APP to OLTP, the batch job inherits OLTP priority and can starve interactive traffic. Oracle can classify sessions by service, module, action, client identifier, login user/program/machine, or combinations. Good applications publish these attributes intentionally.

1. Service says which workload endpoint the client chose

A database service is a named workload endpoint. In a single instance it still identifies an application/SLO class. In RAC it also controls preferred/available instance placement; in Data Guard it can follow database role. A service is therefore a better classification input than an instance SID.

2. Module/action/client identifier describe work inside the service

DBMS_APPLICATION_INFO.SET_MODULE/SET_ACTION expose the application component and current operation. DBMS_SESSION.SET_IDENTIFIER sets CLIENT_IDENTIFIER, often to an end-user/request/tenant identity. Oracle exposes them in V$SESSION and service/module/action statistics, and Resource Manager can map on them.

BEGIN  DBMS_APPLICATION_INFO.SET_MODULE(    module_name => 'SERVICEHUB_API',    action_name => 'CREATE_ORDER'  );  DBMS_SESSION.SET_IDENTIFIER(    client_id => 'demo-request-0001'  );END;/SELECT  SYS_CONTEXT('USERENV','SERVICE_NAME') AS service_name,  SYS_CONTEXT('USERENV','SESSION_USER') AS session_user,  SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') AS client_idFROM dual;

3. Free lab: create two workload services

BEGIN  FOR s IN (    SELECT name    FROM v$services    WHERE name IN ('servicehub_ch21_oltp','servicehub_ch21_batch')  ) LOOP    BEGIN DBMS_SERVICE.STOP_SERVICE(s.name); EXCEPTION WHEN OTHERS THEN NULL; END;    BEGIN DBMS_SERVICE.DELETE_SERVICE(s.name); EXCEPTION WHEN OTHERS THEN NULL; END;  END LOOP;  DBMS_SERVICE.CREATE_SERVICE(    service_name => 'servicehub_ch21_oltp',    network_name => 'servicehub_ch21_oltp'  );  DBMS_SERVICE.CREATE_SERVICE(    service_name => 'servicehub_ch21_batch',    network_name => 'servicehub_ch21_batch'  );  DBMS_SERVICE.START_SERVICE('servicehub_ch21_oltp');  DBMS_SERVICE.START_SERVICE('servicehub_ch21_batch');END;/SELECT name,network_name,pdbFROM v$servicesWHERE name LIKE 'servicehub_ch21_%'ORDER BY name;

4. Observe two sessions with the same username but different workload identity

sql servicehub_app@//localhost:1521/servicehub_ch21_oltpBEGIN  DBMS_APPLICATION_INFO.SET_MODULE('SERVICEHUB_API','CREATE_ORDER');  DBMS_SESSION.SET_IDENTIFIER('request-oltp-01');END;/

sql servicehub_app@//localhost:1521/servicehub_ch21_batchBEGIN  DBMS_APPLICATION_INFO.SET_MODULE('SERVICEHUB_BATCH','MONTH_END');  DBMS_SESSION.SET_IDENTIFIER('job-month-end-01');END;/

SELECT  sid,  username,  service_name,  module,  action,  client_identifier,  resource_consumer_groupFROM v$sessionWHERE username='SERVICEHUB_APP'ORDER BY sid;

Both sessions authenticate as the same Oracle user but advertise different service/module/action identities. This is exactly why username-only mapping is too coarse.

5. Entitled mapping rules live in the pending area

BEGIN  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(    attribute      => DBMS_RESOURCE_MANAGER.SERVICE_MODULE_ACTION,    value          => 'servicehub_ch21_oltp.SERVICEHUB_API.CREATE_ORDER',    consumer_group => 'SH21_OLTP');  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(    attribute      => DBMS_RESOURCE_MANAGER.SERVICE_NAME,    value          => 'servicehub_ch21_batch',    consumer_group => 'SH21_BATCH');  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(    attribute      => DBMS_RESOURCE_MANAGER.ORACLE_USER,    value          => 'SERVICEHUB_APP',    consumer_group => 'OTHER_GROUPS');  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();END;/

SERVICE_NAME/ORACLE_USER are evaluated at login. Module/action/service-module combinations are runtime attributes; changing them can cause Resource Manager to reevaluate the session and switch its mapped group.

6. Mapping priority resolves conflicts

A session can simultaneously match service, username and module/action rules. SET_CONSUMER_GROUP_MAPPING_PRI assigns a unique priority where 1 is highest. Oracle requires the pseudo-attribute EXPLICIT to be priority 1.

BEGIN  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING_PRI(    explicit              => 1,    service_module_action => 2,    service_module        => 3,    module_name_action    => 4,    module_name           => 5,    service_name          => 6,    oracle_user           => 7,    client_program        => 8,    client_os_user        => 9,    client_machine        => 10,    client_id             => 11);  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();END;/SELECT attribute,priorityFROM dba_rsrc_mapping_priorityORDER BY priority;

7. Deliberately wrong: map SERVICEHUB_APP to OLTP and stop there

That rule classifies every use of the shared runtime—interactive API, scheduled reconciliation and reporting—as latency-critical. Under contention, batch work can receive the same CPU priority and bypass the intended governance. The repair is to expose service/module/action/client identity and give those mappings higher priority.

SELECT  s.sid,  s.username,  s.service_name,  s.module,  s.action,  r.current_consumer_group,  r.orig_consumer_group,  r.mapped_consumer_groupFROM v$session sJOIN v$rsrc_session_info r  ON r.sid=s.sidWHERE s.username='SERVICEHUB_APP'ORDER BY s.sid;

8. Instrumentation must be correct in pooled sessions

A pool reuses database sessions for different application requests. Module/action/client identifier must be set at request boundaries and cleared/reset afterward. Otherwise a later request can inherit stale identity, producing wrong Resource Manager mapping, audit attribution and observability.

BEGIN  DBMS_APPLICATION_INFO.SET_ACTION(NULL);  DBMS_APPLICATION_INFO.SET_MODULE(NULL,NULL);  DBMS_SESSION.CLEAR_IDENTIFIER;END;/

Oracle AI Database 26ai also has the service-level RESET_STATE capability for stateless applications, now independent of Application Continuity. Validate driver/pool/service support before relying on it.

9. Free classification report

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,sessions DESC;

This is real operational telemetry. It does not enforce shares/queues; an application or external orchestrator can still use it to control batch concurrency on Free.

10. Cleanup

BEGIN  DBMS_SERVICE.STOP_SERVICE('servicehub_ch21_oltp');  DBMS_SERVICE.STOP_SERVICE('servicehub_ch21_batch');  DBMS_SERVICE.DELETE_SERVICE('servicehub_ch21_oltp');  DBMS_SERVICE.DELETE_SERVICE('servicehub_ch21_batch');END;/

11. Production judgment

Make workload identity explicit in both connection endpoint and request instrumentation. Prefer service/module/action combinations for multipurpose application accounts; reserve username mapping for identities that truly represent one class. Audit mapping priority and stale/uninstrumented sessions regularly.

The instrumentation and DBMS_SERVICE lab is Free-compatible; DBMS_RESOURCE_MANAGER mappings are not. No restart or COMPATIBLE change is required. Lesson 3 applies limits after classification and shows why active-session queues are useful for batch/analytics but a poor substitute for OLTP connection pooling.

Authoritative references

• Assigning Sessions to Resource Consumer Groups — mapping attributes and runtime reevaluation
• DBMS_RESOURCE_MANAGER — mapping/priority APIs
• DBMS_APPLICATION_INFO — module/action instrumentation
• DBMS_SESSION — client identifier
• DBMS_SERVICE — service lifecycle and attributes