Allocating Resources to Oracle Data Miner User Sessions

You can use Database Resource Manager to create resource plans that allocate system resources for groups of sessions based on session attributes.

Oracle Database Resource Manager allocates CPU time, configures parallel query processing, limits the number of sessions, and controls other aspects of system behavior that would otherwise be controlled by the operating system. In a database where multiple applications run concurrently and compete for system resources, you can use Oracle Database Resource Manager to distribute the workloads and optimize overall performance. For example, you could balance the demands of ETL, OLAP, data mining, and reporting workloads running simultaneously in the database.

Oracle Data Miner workflows can potentially make extensive demands on system resources, especially when transformations and large data sets are involved. In a database that must accommodate the demands of multiple applications, you can create a resource plan to limit the impact of Oracle Data Miner sessions on other applications and prevent other applications from compromising the performance of Oracle Data Miner.

Example 6-1 illustrates the creation of a simple resource plan for Oracle Data Miner. The resource plan, called SIMPLE_RESOURCE_PLAN, creates two consumer groups: DATA_MINER_GROUP and OTHER_GROUPS. The plan allocates 50% of CPU resource to DATA_MINER_GROUP and the rest to OTHER_GROUPS. The DATA_MINER_GROUP is mapped to the DMUSER account; other users are mapped to the OTHER_GROUPS group.

See Also:

"Managing Resources With Oracle Resource Manager" in Oracle Database Administrator’s Guide

Example 6-1 Simple Resource Plan for Oracle Data Miner Sessions

CONNECT sys as sysdba;
Enter password: password
-- creating a pending area is the first step in defining 
-- consumer groups and resource plans
EXEC dbms_resource_manager.create_pending_area();
-- delete old plan (optional)
EXEC dbms_resource_manager.delete_plan_cascade(
               plan => 'SIMPLE_RESOURCE_PLAN');
-- create a custom consumer group for data miner workload
EXEC dbms_resource_manager.create_consumer_group(
               consumer_group => 'DATA_MINER_GROUP', 
               comment => 'Sessions for data miner operations');
-- map DMUSER account to the consumer group
EXEC dbms_resource_manager.set_consumer_group_mapping(
               attribute => dbms_resource_manager.oracle_user, 
               value => 'DMUSER', 
               consumer_group => 'DATA_MINER_GROUP');
-- create a custom resource plan
EXEC dbms_resource_manager.create_plan(
               plan => 'SIMPLE_RESOURCE_PLAN', 
               comment => 'Resource plan for database operations');
-- specifies how much CPU and parallelism 
-- should be allocated to the consumer group
EXEC dbms_resource_manager.create_plan_directive(
               plan => 'SIMPLE_RESOURCE_PLAN',
               group_or_subplan => 'DATA_MINER_GROUP',
               comment => 'Percentage of CPU for DATA_MINER_GROUP', 
               mgmt_p1 => 50, 
               utilization_limit => 55, 
               parallel_degree_limit_p1 => 8, 
               parallel_server_limit => 4);
-- specifies how much CPU should be allocated to the required OTHER_GROUPS
EXEC dbms_resource_manager.create_plan_directive(
                plan => 'SIMPLE_RESOURCE_PLAN',
                group_or_subplan => 'OTHER_GROUPS',
                comment => 'Percentage of CPU for OTHER_GROUPS', 
                mgmt_p1 => 50);
-- persist plan to the database
EXEC dbms_resource_manager.submit_pending_area();
-- Now that the resource plan is defined, enable it by setting
-- the resource_manager_plan parameter with the resource plan name
ALTER SYSTEM SET resource_manager_plan = 'SIMPLE_RESOURCE_PLAN'; 
-- DBA can also enable a resource plan for the period of time corresponding 
-- to a job scheduler Window (via the dbms_scheduler.create_window procedure)