Update a plan directive

You can call the UPDATE_PLAN_DIRECTIVE subprogram in the DBMS_RESOURCE_MANAGER package to update a plan directive based on business needs. You can update the resource management plan, bound resource group, comments on the plan directive, and limit on the CPU and IOPS resources available for the resource group.

Prerequisites

If you want to change the original resource management plan or resource group, make sure that you have created a new one. For more information, see Configure user-level resource isolation.

Procedure

1. Log in to a MySQL tenant of the cluster as the administrator of the tenant.

2. Call the UPDATE_PLAN_DIRECTIVE subprogram in the DBMS_RESOURCE_MANAGER package to update the plan directive.

   obclient [test]> CALL DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
   PLAN => 'daytime',
   GROUP_OR_SUBPLAN => 'interactive_group' ,
   NEW_COMMENT => 'new',
   NEW_MGMT_P1 => 40,
   NEW_UTILIZATION_LIMIT => 60);
   NEW_MIN_IOPS => 40,
   NEW_MAX_IOPS => 80,
   NEW_WEIGHT_IOPS => 70);
   

   where:

   • PLAN indicates the name of the new resource management plan.

   • GROUP_OR_SUBPLAN indicates the name of the new resource group.

   • NEW_COMMENT indicates the new comments on the plan directive.

   • NEW_MGMT_P1 indicates the new percentage of CPU resources available for the resource group when the system runs at full load.

   • NEW_UTILIZATION_LIMIT indicates the new upper limit on the CPU resources available for the resource group.

   • NEW_MIN_IOPS indicates the new IOPS resources reserved for the resource group in the case of I/O resource contention. The sum of NEW_MIN_IOPS values of all resource groups cannot exceed 100.

   • NEW_MAX_IOPS indicates the new maximum IOPS resources available for the resource group. The sum of NEW_MAX_IOPS values of all resource groups can exceed 100.

   • NEW_WEIGHT_IOPS indicates the new weight for IOPS resources. The sum of NEW_WEIGHT_IOPS values of all resource groups can exceed 100.

3. Query the oceanbase.DBA_RSRC_PLAN_DIRECTIVES or oceanbase.DBA_OB_RSRC_IO_DIRECTIVES view to verify whether the plan directive is updated.

   obclient [test]> SELECT PLAN, GROUP_OR_SUBPLAN, MGMT_P1, UTILIZATION_LIMIT FROM oceanbase.DBA_RSRC_PLAN_DIRECTIVES;
   +---------+-------------------+---------+-------------------+
   | PLAN    | GROUP_OR_SUBPLAN  | MGMT_P1 | UTILIZATION_LIMIT |
   +---------+-------------------+---------+-------------------+
   | DAY     | group1            |      80 |               100 |
   | daytime | batch_group       |     100 |                40 |
   | daytime | interactive_group |     100 |                60 |
   +---------+-------------------+---------+-------------------+
   3 rows in set
   
   obclient [test]> SELECT * FROM oceanbase.DBA_OB_RSRC_IO_DIRECTIVES;
   

   For more information about the oceanbase.DBA_RSRC_PLAN_DIRECTIVES view, see oceanbase.DBA_RSRC_PLAN_DIRECTIVES.

   For more information about the oceanbase.DBA_OB_RSRC_IO_DIRECTIVES view, see oceanbase.DBA_OB_RSRC_IO_DIRECTIVES.

References

UPDATE_PLAN_DIRECTIVE