Resource Manager and PARALLEL_DEGREE_LIMIT

From: <schonlinner_at_yahoo.com>
Date: 23 Aug 2005 05:49:39 -0700
Message-ID: <1124801379.513058.30150@g47g2000cwa.googlegroups.com>

Hi,

we have an Oracle 10g database here and want to use the resource manager. We created a plan and set PARALLEL_DEGREE_LIMIT_P1 of a consumer group to 1.

What we wanted to achieve is, that each single user of that specific consumer group can only have a single process, thus only a single CPU (of our 2 CPU server), so users in other consumer groups can perform real parallel queries using both processors.

I looked at v$session and there the users indeed have the proper consumer group assigned, thus the plan is in effect and the correct consumer group is assigned.

But when a user performs a query, I can see 4 processes on Unix which all perform parts of the query. Do I misunderstand something?

Here's the script which creates our plan:

BEGIN
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA(); DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA(); DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'our_plan',COMMENT => 'Resource plan for OURAPP');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'EMERGENCY', COMMENT => 'Consumer group for emergency cases'); DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'OURAPP', COMMENT => 'Consumer group for Tomcat connections, i.e. OURAPP queries');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'our_plan', GROUP_OR_SUBPLAN => 'EMERGENCY', COMMENT => 'Emergency sessions',CPU_P1 => 80);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'our_plan', GROUP_OR_SUBPLAN => 'OURAPP', COMMENT => 'Emergency sessions',CPU_P2 => 80,PARALLEL_DEGREE_LIMIT_P1 => 6);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'our_plan', GROUP_OR_SUBPLAN => 'OTHER_GROUPS', COMMENT => 'mandatory',CPU_P2 => 20);
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(ATTRIBUTE => 'MODULE_NAME',VALUE => 'OURAPP', CONSUMER_GROUP => 'OURAPP');

 DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
 DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
 DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (grantee_name

=> 'PUBLIC', consumer_group => 'EMERGENCY', grant_option => False);
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (grantee_name
=> 'PUBLIC', consumer_group => 'OURAPP', grant_option => False);
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (grantee_name
=> 'PUBLIC', consumer_group => 'OTHER_GROUPS', grant_option => False);
dbms_resource_manager.set_initial_consumer_group(user => 'SQ_P2', consumer_group => 'DEFAULT_CONSUMER_GROUP'); END; Best regards,
Alex Received on Tue Aug 23 2005 - 07:49:39 CDT

Resource Manager and PARALLEL_DEGREE_LIMIT_P1