Update resource management plan directive

After you create a resource management plan directive, you can call the UPDATE_PLAN_DIRECTIVE subprogram in the DBMS_RESOURCE_MANAGER package to update it as needed. You can update the resource management plan, the bound resource group, the remarks of the resource management plan directive, and the CPU and IOPS resources for the resource group.

Prerequisites

Before you update a resource management plan directive, if you want to change the resource management plan or resource group, make sure that you have created new resource management plans or resource groups. For more information about how to create a resource management plan or resource group, see Configure user-level resource isolation.

Procedure

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

2. Call the UPDATE_PLAN_DIRECTIVE subprogram in the DBMS_RESOURCE_MANAGER package to update the resource management 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,
   NEW_MAX_NET_BANDWIDTH => 30,
   NEW_NET_BANDWIDTH_WEIGHT => 30);
   

   The related parameters are described as follows:

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

   • GROUP_OR_SUBPLAN: the new resource group.

   • NEW_COMMENT: the new remarks for the resource management plan directive.

   • NEW_MGMT_P1: the new CPU ratio that is committed to be allocated to the resource group when the system is fully loaded.

   • NEW_UTILIZATION_LIMIT: the new limit on the CPU resources for the resource group.

   • NEW_MIN_IOPS: the new minimum IOPS resources reserved for the resource group in case of I/O contention. The sum of the IOPS resources cannot exceed 100.

   • NEW_MAX_IOPS: the new maximum IOPS resources available to the resource group. The sum can exceed 100.

   • NEW_WEIGHT_IOPS: the new IOPS weight. The sum can exceed 100.

   • NEW_MAX_NET_BANDWIDTH: redefines the maximum network bandwidth available to the resource group. The total can exceed 100.

   • NEW_NET_BANDWIDTH_WEIGHT: redefines the weight value for network bandwidth. The total can exceed 100.

3. After successful execution, you can check the DBA_OB_RSRC_DIRECTIVES view for confirmation.

   obclient [test]> SELECT * FROM oceanbase.DBA_OB_RSRC_DIRECTIVES;
   

   The query result is as follows:

   +---------+-------------------+----------+---------+-------------------+----------+----------+-------------+-------------------+----------------------+
   | PLAN    | GROUP_OR_SUBPLAN  | COMMENTS | MGMT_P1 | UTILIZATION_LIMIT | MIN_IOPS | MAX_IOPS | WEIGHT_IOPS | MAX_NET_BANDWIDTH | NET_BANDWIDTH_WEIGHT |
   +---------+-------------------+----------+---------+-------------------+----------+----------+-------------+-------------------+----------------------+
   | daytime | batch_group       |          |     100 |                40 |       40 |       80 |          70 |               100 |                    0 |
   | daytime | interactive_group | new      |      40 |                60 |       40 |       80 |          70 |                30 |                   30 |
   +---------+-------------------+----------+---------+-------------------+----------+----------+-------------+-------------------+----------------------+
   2 rows in set
   

   For more information about the DBA_OB_RSRC_DIRECTIVES view, see DBA_OB_RSRC_DIRECTIVES.

References

UPDATE_PLAN_DIRECTIVE