Infrastructure at your Service

Often switching off resource plan feature of Oracle database is desired.
Unfortunately setting parameter RESOURCE_MANAGER_PLAN to empty string does not help, because it does not disable switching to another resource plan, e.g. with dbms_scheduler.

Following procedure prevents this switching.

1. Creation of a dummy resource plan, e.g. TEST_PLAN:


BEGIN
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PLAN(
plan =>'TEST_PLAN',
comment => 'plan for TEST',
mgmt_mth => 'EMPHASIS',
active_sess_pool_mth => 'ACTIVE_SESS_POOL_ABSOLUTE',
parallel_degree_limit_mth => 'PARALLEL_DEGREE_LIMIT_ABSOLUTE');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan =>'TEST_PLAN',
group_or_subplan => 'OTHER_GROUPS',
comment => 'dummy directive'
);
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

The only limitation is the number of available CPUs specified in parameter CPU_COUNT, resource manager enforces this limitation. The resource plan INTERNAL_PLAN is for internal use, so I would not use it.

2. Setting of parameter resource_manager_plan:


alter system set resource_manager_plan='FORCE:TEST_PLAN';

This setting prevents switching to another resource plan.

Caveat:

With this setting, deletion of specified resource plan (e.g. TEST_PLAN) causes database to crash:


alter system set resource_manager_plan='FORCE:TEST_PLAN';
BEGIN
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.DELETE_PLAN('TEST_PLAN');
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/
BEGIN
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 73473
Session ID: 24 Serial number: 17611

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Michael Hein
Michael Hein