notes/tiddlywiki/Oracle resource manager example.md

Original article

Create application users:

create user web_user identified by "iN_j8sC#d!kX6b:_";
create user batch_user identified by "r~65ktuFYyds+P_X";
grant connect,resource to web_user;
grant connect,resource to batch_user;

Create a pending area:

BEGIN
	DBMS_RESOURCE_MANAGER.clear_pending_area;
	DBMS_RESOURCE_MANAGER.create_pending_area;
END;
/

Create a plan:

BEGIN
	DBMS_RESOURCE_MANAGER.create_plan(
		plan    => 'hybrid_plan',
		comment => 'Plan for a combination of high and low priority tasks.');
END;
/

Create a web and a batch consumer group:

BEGIN
	DBMS_RESOURCE_MANAGER.create_consumer_group(
		consumer_group => 'WEB_CG',
		comment        => 'Web based OTLP processing - high priority');

	DBMS_RESOURCE_MANAGER.create_consumer_group(
		consumer_group => 'BATCH_CG',
		comment        => 'Batch processing - low priority');
END;
/

Assign the consumer groups to the plan and indicate their relative priority, remembering to add the OTHER_GROUPS plan directive:

BEGIN
	DBMS_RESOURCE_MANAGER.create_plan_directive (
		plan                     => 'hybrid_plan',
		group_or_subplan         => 'web_cg',
		comment                  => 'High Priority',
		cpu_p1                   => 80,
		cpu_p2                   => 0,
		parallel_degree_limit_p1 => 4);

	DBMS_RESOURCE_MANAGER.create_plan_directive (
		plan                     => 'hybrid_plan',
		group_or_subplan         => 'batch_cg',
		comment                  => 'Low Priority',
		cpu_p1                   => 0,
		cpu_p2                   => 80,
		parallel_degree_limit_p1 => 4);

	DBMS_RESOURCE_MANAGER.create_plan_directive(
		plan             => 'hybrid_plan',
		group_or_subplan => 'OTHER_GROUPS',
		comment          => 'all other users - level 3',
		cpu_p1           => 0,
		cpu_p2           => 0,
		cpu_p3           => 100);
END;
/

Validate and apply the resource plan:

BEGIN
	DBMS_RESOURCE_MANAGER.validate_pending_area;
	DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/

Assign our users to individual consumer groups:

BEGIN
	-- Assign users to consumer groups
	DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
		grantee_name   => 'web_user',
		consumer_group => 'web_cg',
		grant_option   => FALSE);

	DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
		grantee_name   => 'batch_user',
		consumer_group => 'batch_cg',
		grant_option   => FALSE);

	DBMS_RESOURCE_MANAGER.set_initial_consumer_group('web_user', 'web_cg');

	DBMS_RESOURCE_MANAGER.set_initial_consumer_group('batch_user', 'batch_cg');
END;
/

Connect users:

connect web_user/"iN_j8sC#d!kX6b:_"
connect batch_user/"r~65ktuFYyds+P_X"

Check resource_consumer_group column in v$session:

SELECT username, resource_consumer_group FROM v$session WHERE username IN ('WEB_USER','BATCH_USER');

Note that the value change for a connecte session if RESOURCE_MANAGER_PLAN change at instance level:

alter system set RESOURCE_MANAGER_PLAN = 'hybrid_plan' scope=both sid='*';
alter system set RESOURCE_MANAGER_PLAN = '' scope=both sid='*';