Resource Manager and Task Schedular

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