The Oracle Resource Manager is a powerful tool for administrators to maintain system stability, optimize performance, and manage workload prioritization within Oracle Database environments. It provides granular control over resource allocation and ensures that database resources are utilized efficiently to meet business requirements and service level objectives.
Resource Manager is a feature that enables administrators to manage and prioritize system resources such as CPU and memory among different users, applications, sessions, and consumer groups within the database. The Resource Manager allows for the control of resource allocation based on specified policies, ensuring that critical workloads receive adequate resources while preventing resource contention and maintaining overall system performance.
This Oracle script is setting up a comprehensive resource management strategy using Oracle Resource Manager. It defines consumer groups to classify different types of workloads and establishes resource plans with directives to allocate resources and prioritize workload processing based on specified criteria like workload type, time of day, and user groups. This provided Oracle PL/SQL script demonstrates the configuration and utilization of Oracle Resource Manager to manage database resource allocation based on workload characteristics.
PROCEDURE create_consumer_group(
consumer_group IN VARCHAR2,
comment IN VARCHAR2,
cpu_mth IN VARCHAR2 DEFAULT 'ROUND-ROBIN')
The create_consumer_groups.sql script uses this procedure to create the OLTP and batch consumer groups.
CONN sys AS SYSDBA
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;
-- Create the consumer groups
DBMS_RESOURCE_MANAGER.create_consumer_group(
consumer_group => 'oltp_consumer_group',
comment => 'OLTP process consumer group.');
DBMS_RESOURCE_MANAGER.create_consumer_group(
consumer_group => 'batch_consumer_group',
comment => 'Batch process consumer group.');
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area();
DBMS_RESOURCE_MANAGER.create_pending_area();
-- Delete consumer groups.
DBMS_RESOURCE_MANAGER.delete_consumer_group (
consumer_group => 'oltp_consumer_group');
DBMS_RESOURCE_MANAGER.delete_consumer_group (
consumer_group => 'batch_consumer_group');
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/
PROCEDURE create_plan (
plan IN VARCHAR2,
comment IN VARCHAR2,
cpu_mth IN VARCHAR2 DEFAULT 'EMPHASIS',
active_sess_pool_mth IN VARCHAR2 DEFAULT 'ACTIVE_SESS_POOL_ABSOLUTE',
parallel_degree_limit_mth IN VARCHAR2 DEFAULT 'PARALLEL_DEGREE_LIMIT_ABSOLUTE',
queueing_mth IN VARCHAR2 DEFAULT 'FIFO_TIMEOUT')
PROCEDURE create_plan_directive (
plan IN VARCHAR2,
group_or_subplan IN VARCHAR2,
comment IN VARCHAR2,
cpu_p1 IN NUMBER DEFAULT NULL,
cpu_p2 IN NUMBER DEFAULT NULL,
cpu_p3 IN NUMBER DEFAULT NULL,
cpu_p4 IN NUMBER DEFAULT NULL,
cpu_p5 IN NUMBER DEFAULT NULL,
cpu_p6 IN NUMBER DEFAULT NULL,
cpu_p7 IN NUMBER DEFAULT NULL,
cpu_p8 IN NUMBER DEFAULT NULL,
active_sess_pool_p1 IN NUMBER DEFAULT NULL,
queueing_p1 IN NUMBER DEFAULT NULL,
parallel_degree_limit_p1 IN NUMBER DEFAULT NULL,
switch_group IN VARCHAR2 DEFAULT NULL,
switch_time IN NUMBER DEFAULT NULL,
switch_estimate IN BOOLEAN DEFAULT FALSE,
max_est_exec_time IN NUMBER DEFAULT NULL,
undo_pool IN NUMBER DEFAULT NULL,
max_idle_time IN NUMBER DEFAULT NULL,
max_idle_blocker_time IN NUMBER DEFAULT NULL,
switch_time_in_call IN NUMBER DEFAULT NULL)
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;
-- Create a new plan
DBMS_RESOURCE_MANAGER.create_plan(
plan => 'day_plan',
comment => 'Plan suitable for daytime processing.');
-- Assign consumer groups to plan and define priorities
DBMS_RESOURCE_MANAGER.create_plan_directive (
plan => 'day_plan',
group_or_subplan => 'oltp_consumer_group',
comment => 'Give OLTP processes higher priority - level 1',
cpu_p1 => 80,
switch_group => 'batch_consumer_group',
switch_time => 60);
DBMS_RESOURCE_MANAGER.create_plan_directive (
plan => 'day_plan',
group_or_subplan => 'batch_consumer_group',
comment => 'Give batch processes lower priority - level 2',
cpu_p2 => 100);
DBMS_RESOURCE_MANAGER.create_plan_directive(
plan => 'day_plan',
group_or_subplan => 'OTHER_GROUPS',
comment => 'all other users - level 3',
cpu_p3 => 100);
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;
-- Create a new plan
DBMS_RESOURCE_MANAGER.create_plan(
plan => 'night_plan',
comment => 'Plan suitable for daytime processing.');
-- Assign consumer groups to plan and define priorities
DBMS_RESOURCE_MANAGER.create_plan_directive (
plan => 'night_plan',
group_or_subplan => 'batch_consumer_group',
comment => 'Give batch processes lower priority - level 2',
cpu_p1 => 80);
DBMS_RESOURCE_MANAGER.create_plan_directive (
plan => 'night_plan',
group_or_subplan => 'oltp_consumer_group',
comment => 'Give OLTP processes higher priority - level 1',
cpu_p2 => 100);
DBMS_RESOURCE_MANAGER.create_plan_directive(
plan => 'night_plan',
group_or_subplan => 'OTHER_GROUPS',
comment => 'all other users - level 3',
cpu_p3 => 100);
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'bugdb_plan',
COMMENT => 'Resource plan/method for bug users sessions');
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'maildb_plan',
COMMENT => 'Resource plan/method for mail users sessions');
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'mydb_plan',
COMMENT => 'Resource plan/method for bug and mail users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Online_group',
COMMENT => 'Resource consumer group/method for online bug users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Batch_group',
COMMENT => 'Resource consumer group/method for batch job bug users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Bug_Maint_group',
COMMENT => 'Resource consumer group/method for users sessions for bug db maint');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Users_group',
COMMENT => 'Resource consumer group/method for mail users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Postman_group',
COMMENT => 'Resource consumer group/method for mail postman');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Mail_Maint_group',
COMMENT => 'Resource consumer group/method for users sessions for mail db maint');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan',
GROUP_OR_SUBPLAN => 'Online_group',
COMMENT => 'online bug users sessions at level 1', MGMT_P1 => 80, MGMT_P2=> 0);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan',
GROUP_OR_SUBPLAN => 'Batch_group',
COMMENT => 'batch bug users sessions at level 1', MGMT_P1 => 20, MGMT_P2 => 0,
PARALLEL_DEGREE_LIMIT_P1 => 8);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan',
GROUP_OR_SUBPLAN => 'Bug_Maint_group',
COMMENT => 'bug maintenance users sessions at level 2', MGMT_P1 => 0, MGMT_P2 => 100);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'all other users sessions at level 3', MGMT_P1 => 0, MGMT_P2 => 0,
MGMT_P3 => 100);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan',
GROUP_OR_SUBPLAN => 'Postman_group',
COMMENT => 'mail postman at level 1', MGMT_P1 => 40, MGMT_P2 => 0);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan',
GROUP_OR_SUBPLAN => 'Users_group',
COMMENT => 'mail users sessions at level 2', MGMT_P1 => 0, MGMT_P2 => 80);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan',
GROUP_OR_SUBPLAN => 'Mail_Maint_group',
COMMENT => 'mail maintenance users sessions at level 2', MGMT_P1 => 0, MGMT_P2 => 20);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'all other users sessions at level 3', MGMT_P1 => 0, MGMT_P2 => 0,
MGMT_P3 => 100);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'mydb_plan',
GROUP_OR_SUBPLAN => 'maildb_plan',
COMMENT=> 'all mail users sessions at level 1', MGMT_P1 => 30);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'mydb_plan',
GROUP_OR_SUBPLAN => 'bugdb_plan',
COMMENT => 'all bug users sessions at level 1', MGMT_P1 => 70);
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/
No comments:
Post a Comment