Database Reference
In-Depth Information
This plan includes the consumer groups SYS_GROUP, OTHER_GROUPS, and LOW_GROUP
(see the dynamic performance view
V$RSRC_CONSUMER_GROUP
). As the name suggests, LOW_GROUP
is the consumer group with the lowest priority. In a situation where CPU resources are scarce,
the SYS_GROUP and OTHER_GROUPS will be given slices of CPU time, whereas sessions in the
LOW_GROUP have to yield the CPU. Since the resource manager is disabled by default, it must
be enabled to use these features. This can be done at runtime using
ALTER
SYSTEM
.
SQL> ALTER SYSTEM SET resource_manager_plan=system_plan;
System altered.
The syntax for specifying a combination of instance service name, module, and action as
a single string in a call to
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
is undocumented.
There is also no statement on case sensitivity. Actual testing reveals that a dot (
.
) has to be used
to separate the service name from the module and the module from the action. Contrary to
DBMS_MONITOR
, capitalization is irrelevant. The code below instructs the resource manager to
place a session that uses the instance service “TEN.oradbpro.com” and has set module and
action to “mod” and “act” respectively into the consumer group LOW_GROUP (file
rsrc_mgr.sql
).
begin
dbms_resource_manager.create_pending_area();
dbms_resource_manager.set_consumer_group_mapping(
attribute=>dbms_resource_manager.service_module_action,
value=>'TEN.oradbpro.com.mod.act',
consumer_group=>'LOW_GROUP'
);
dbms_resource_manager.submit_pending_area();
dbms_resource_manager.clear_pending_area();
end;
/
SQL> SELECT * FROM dba_rsrc_group_mappings;
ATTRIBUTE VALUE CONSUMER_GROUP STATUS
--------------------- ------------------------ -------------- ------
SERVICE_MODULE_ACTION TEN.ORADBPRO.COM.MOD.ACT LOW_GROUP
ORACLE_USER SYS SYS_GROUP
ORACLE_USER SYSTEM SYS_GROUP
The setting takes effect immediately, i.e., it is applied to sessions that are already connected.
This completes the preparations. If we now run the Java program, we expect to see these three
things happening:
1.
A SQL trace file is created.
2.
Statistics are collected for service name, module, and action.
3.
The session is placed in the consumer group LOW_GROUP as soon as it sets module
and action to the values “mod” and “act” respectively.
Let's verify that the software responds as expected. The Java program is run by starting a
Java virtual machine and passing the class and parameters for the program as arguments.
