Database Reference
In-Depth Information
dbms_resource_manager.create_plan_directive(
plan => 'CONTROL_DOP',
group_or_subplan => 'OTHER_GROUPS',
comment => 'Unrestricted degree of parallelism'
);
dbms_resource_manager.validate_pending_area();
dbms_resource_manager.submit_pending_area();
END;
2.
Provide a specific user with the privilege to switch to the
cap_dop
consumer group:
BEGIN
dbms_resource_manager_privs.grant_switch_consumer_group(
grantee_name => 'CHRIS',
consumer_group => 'CAP_DOP',
grant_option => FALSE
);
END;
3.
Map the sessions of a specific user to the
cap_dop
consumer group:
BEGIN
dbms_resource_manager.create_pending_area();
dbms_resource_manager.set_consumer_group_mapping(
attribute => 'ORACLE_USER',
value => 'CHRIS',
consumer_group => 'CAP_DOP'
);
dbms_resource_manager.submit_pending_area();
END;
4.
Enable the
control_dop
resource plan at the system level:
ALTER SYSTEM SET resource_manager_plan = control_dop
User Profile
Through user profiles, specifically the
sessions_per_user
parameter, it's possible to limit the number of concurrent
sessions a specific user can have. For example, the following SQL statements create a new user profile (
limit_dop
)
that limits the number of sessions to 16, associates it to a user, and enables it by setting the
resource_limit
initialization parameter to
TRUE
(the default value is
FALSE
):
CREATE PROFILE limit_dop LIMIT sessions_per_user 16
ALTER USER chris PROFILE limit_dop
Despite the fact that the limit imposed by a user profile was originally introduced to prevent an end-user from
concurrently logging into the same database instance more than a specific number of times, the limit is also useful
in managing the number of parallel sessions. Such sessions are created automatically by the database engine when a
