Before defining or updating any DRM objects, you must first establish a pending area. A pending area is a staging area where resource management objects can be defined and validated before they are activated. If you forget to create the pending area, you will receive the following error message if you try to create or update a DRM object:
ERROR at line 1: ORA-29371: pending area is not active ORA-06512: at "SYS.DBMS_RMIN", line 115 ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 108 ORA-06512: at line 1
The next sections explain how to manage pending areas. You will learn to create, validate, submit, and clear them.
To create a pending area, simply execute the DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA procedure. This procedure accepts no parameters, so it can be called as follows:
SQL> exec dbms_resource_manager.create_pending_area(); PL/SQL procedure successfully completed.
Once a pending area has been created, all changes will automatically be stored there until they are validated and submitted.
After changes have been made in the pending area, they need to be checked for validity before being activated. This can be accomplished through the DBMS_RESOURCE_GROUP.VALIDATE_PENDING_AREA procedure.
The validation process verifies that any changes in the pending area will not result in a violation of any of the following rules:
No plan schema can contain a loop.
All DRM objects identified in a plan directive must exist.
All plan directives refer to either plans or resource groups.
Allocation percentages for a given level cannot exceed 100.
Deletes are not allowed for top plans being used by an active instance.
Only plan directives that refer to consumer resource groups are allowed to set the following parameters:
ACTIVE_SESS_POOL_P1
MAX_EST_EXEC_TIME
MAX_IDLE_BLOCKER_TIME
MAX_IDLE_TIME
PARALLEL_DEGREE_LIMIT_P1
QUEUEING_P1
SWITCH_ESTIMATE
SWITCH_GROUP
SWITCH_TIME
SWITCH_TIME_IN_CALL
UNDO_POOL
An active plan schema can contain no more than 32 resource consumer groups.
Plan names cannot conflict with resource consumer group names.
There must be a plan directive for the OTHER_GROUPS group to allocate resources for sessions not identified in the active plan.
If any of the preceding rules are violated, the VALIDATE_PENDING_AREA procedure will return errors for each violation. Here's an example:
SQL> exec dbms_resource_manager.validate_pending_area; BEGIN dbms_resource_manager.validate_pending_area; END; * ERROR at line 1: ORA-29382: validation of pending area failed ORA-29377: consumer group OTHER_GROUPS is not part of top-plan OLTP_PLAN ORA-29383: all leaves of top-plan OLTP_PLAN must be consumer groups ORA-29374: resource plan OLTP_PLAN in top-plan OLTP_PLAN has no plan directives ORA-29377: consumer group OTHER_GROUPS is not part of top-plan OFF_HOURS_PLAN ORA-29383: all leaves of top-plan OFF_HOURS_PLAN must be consumer groups ORA-29374: resource plan OFF_HOURS_PLAN in top-plan OFF_HOURS_PLAN has no plan directives ORA-29377: consumer group OTHER_GROUPS is not part of top-plan DAY_PLAN ORA-29383: all leaves of top-plan DAY_PLAN must be consumer groups ORA-29374: resource plan DAY_PLAN in top-plan DAY_PLAN has no plan directives ORA-06512: at "SYS.DBMS_RMIN", line 402 ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 437 ORA-06512: at line 1
If validation is successful, no error messages will be returned.
When you are ready to make your changes active, you can use the DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA procedure, as shown here:
SQL> exec dbms_resource_manager.submit_pending_area; PL/SQL procedure successfully completed.
As you can see, no parameters are required when submitting the pending area. Submitting the contents of the pending area will activate those objects (move them to the data dictionary). Active objects are stored in the data dictionary and can be enabled by DRM.
|
Submitting the pending area actually performs three distinct actions: It validates, submits, and clears the pending area. Therefore, it is not required to perform a separate validation before submitting. However, from a debugging standpoint, it is beneficial to validate changes on an incremental basis rather than waiting until submit time.
To clear the pending area without submitting your changes, you can use the DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA procedure, as shown here:
SQL> exec dbms_resource_manager.clear_pending_area; PL/SQL procedure successfully completed.
Clearing the pending area drops everything in the pending area irretrievably, so use this procedure with caution. As mentioned earlier, submitting the pending area also clears the pending area, so it is not necessary to use this procedure after a successful submit is performed.
The name of this procedure is somewhat misleading. It seems to imply that the objects in the pending area will be cleared, but that the pending area will remain intact. This is not true. Once the pending area is cleared, a new pending area must be created before making any new changes.