Resource Manager and Task Schedular-
In Oracle Database, the Resource
Manager is a feature that allows you to manage and allocate resources among
different users and applications. It enables you to control the number of
resources (CPU, memory, and parallel execution servers) consumed by database
sessions and helps in preventing runaway queries or resource-intensive
operations from adversely impacting the overall performance of the database.
Key features and components of Oracle
Resource Manager include:
Consumer Groups:
Resource Manager organizes database
sessions into "consumer groups." Each consumer group represents a
category of users or applications that share similar resource requirements.
Resource Plans:
Resource plans are a set of rules that
define how resources should be allocated among different consumer groups. They
specify the resource allocation policies, priorities, and limits for each
group.
Directive and Consumer
Group Mapping:
Directives are rules defined within a
resource plan that specify how resources should be allocated to consumer
groups. These directives may include rules for CPU usage, parallel execution
servers, and other resources.
Consumer Group Mapping associates
database sessions with specific consumer groups.
Parallel Degree Limits:
Resource Manager allows you to set
limits on the degree of parallelism for specific operations. This helps in
controlling the parallel execution of queries to prevent excessive parallelism.
Sub-Plan and Plan Directives:
Within a resource plan, you can define
sub-plans and plan directives to further customize resource allocation for
specific scenarios.
Automatic Consumer Group
Mapping:
Oracle Database can automatically map sessions to consumer groups based on user attributes, application attributes, or other criteria defined by the database administrator.
Monitoring and Statistics:
Resource Manager provides monitoring
views and statistics to track resource usage, plan effectiveness, and other
relevant metrics.
Here's a simple example of creating a basic resource plan with Oracle Resource
Manager:
-- Create a simple resource plan
BEGIN
DBMS_RESOURCE_MANAGER.create_pending_area();
DBMS_RESOURCE_MANAGER.create_consumer_group('low_priority');
DBMS_RESOURCE_MANAGER.create_consumer_group('high_priority');
DBMS_RESOURCE_MANAGER.create_plan('my_plan');
DBMS_RESOURCE_MANAGER.create_plan_directive(
plan
=> 'my_plan',
group_or_subplan =>
'low_priority',
switch_group
=> 'TRUE',
max_utilization
=> 40);
DBMS_RESOURCE_MANAGER.create_plan_directive(
plan
=> 'my_plan',
group_or_subplan =>
'high_priority',
switch_group
=> 'TRUE',
max_utilization
=> 60);
DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/
In this example, a resource plan named
'my_plan' is created with two consumer groups ('low_priority' and
'high_priority'). Directives are then defined to allocate resources based on
the maximum utilization for each consumer group.
Resource Manager is a powerful tool
for managing the performance of a multi-user Oracle Database by ensuring that
resources are allocated in a controlled and predictable manner. It is
particularly useful in environments where there is a need to prioritize and
allocate resources among different types of workloads or users.
Task Schedular-
Ask scheduling in Oracle typically
involves using the Oracle Scheduler, a comprehensive job scheduling solution
provided by Oracle Database. The Oracle Scheduler allows you to automate and
manage various database tasks, such as running PL/SQL procedures, calling
external programs, or executing SQL scripts at specified intervals.
Here are the basic steps to
create and manage scheduled tasks in Oracle:
Create a Job:
You can create a job using the
DBMS_SCHEDULER.CREATE_JOB procedure.
Specify the job name, job type (PL/SQL
block, stored procedure, etc.), and other relevant parameters.
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'MY_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN my_procedure; END;',
start_date => SYSTIMESTAMP,
repeat_interval
=> 'FREQ=DAILY; BYHOUR=0; BYMINUTE=0; BYSECOND=0',
enabled => TRUE
);
END;
/
Task scheduling in Oracle typically
involves using the Oracle Scheduler, a comprehensive job scheduling solution
provided by Oracle Database. The Oracle Scheduler allows you to automate and
manage various database tasks, such as running PL/SQL procedures, calling
external programs, or executing SQL scripts at specified intervals.
Here are the basic steps to create and
manage scheduled tasks in Oracle:
Create a Job:
You can create a job using the
DBMS_SCHEDULER.CREATE_JOB procedure.
Specify the job name, job type (PL/SQL
block, stored procedure, etc.), and other relevant parameters.
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'MY_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN my_procedure; END;',
…
-- Disable a job
BEGIN
DBMS_SCHEDULER.DISABLE('MY_JOB');
END;
/
-- Enable a job
BEGIN
DBMS_SCHEDULER.ENABLE('MY_JOB');
END;
/
Dropping a Job:
If you want to remove a job from the
scheduler, use the DBMS_SCHEDULER.DROP_JOB procedure.
sql
BEGIN
DBMS_SCHEDULER.DROP_JOB('MY_JOB');
END;
/
Remember to have the necessary
privileges to create and manage jobs (e.g., CREATE JOB, CREATE PROCEDURE,
CREATE ANY JOB, etc.).
Adjust the parameters and schedule
according to your specific requirements. The provided example is a basic
illustration, and you may need to customize it based on your needs.
--BY Tarif Ahmed
