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

[ Post a follow-up to this message ]

schonlinner@yahoo.com wrote:
> 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?
>
[...]

Perhaps try Note:240877.1 on Metalink?

Are you sure there is a one-to-one correspondence between Unix processes
and the parallel slave sets of each individual user session?  Can you
provide ps output showing details of the four processes, as well as the
v$session information from the same time period?

It might be simpler to just focus on overall CPU allocation. The
resource manager in general only enforces CPU allocations when it needs
to (at least in 9i, I haven't used it under 10g yet).  Think of a car
pool (High Occupancy Vehicle) lane on the freeway -- when traffic is
light and the HOV's are not being delayed, then no restrictions on
anyone are enforced.  However when the traffic gets heavy enough (rush
hour) to start impacting the HOV's, then the limits are enforced for
non-HOV's.  This is true for CPU allocation, however I'm not sure if
it's true for parallel degree limits.

-Mark Bole

[ Post a follow-up to this message ]

schonlinner@yahoo.com wrote:
> 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.
>
...
>  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'our_plan',
> GROUP_OR_SUBPLAN => 'OURAPP', COMMENT => 'Emergency sessions',CPU_P2 =>
> 80,PARALLEL_DEGREE_LIMIT_P1 => 6);

I only see you set it to 6. Where is it set to 1?

Yong Huang

[ Post a follow-up to this message ]