Materialized view resource isolation

OceanBase Database supports DBMS_RESOURCE_MANAGER to provide resource isolation for incremental refresh and MLOG purge of materialized views, allowing you to limit the upper limit of resources that materialized views can use.

Examples

1. Create a resource plan named mview_task.

   CALL DBMS_RESOURCE_MANAGER.CREATE_PLAN('mview_task','This resource plan is used for materialized view processing');
   

   For more information about creating resource plans, see CREATE_PLAN.

2. Create a consumer group named mview_group.

   CALL DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP('mview_group','Materialized view processing group');
   

   For more information about creating consumer groups, see CREATE_CONSUMER_GROUP.

3. Create a resource plan directive to limit the CPU resource usage to 5%.

   CALL DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
       PLAN => 'mview_task',
       GROUP_OR_SUBPLAN => 'mview_group',
       UTILIZATION_LIMIT => 5
       );
   

   For more information about creating resource plan directives, see CREATE_PLAN_DIRECTIVE.

4. Bind the consumer group mview_group to FUNCTION.

   CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
       ATTRIBUTE => 'FUNCTION',
       VALUE => 'MVIEW',
       CONSUMER_GROUP => 'mview_group'
       );
   

   For more information about mapping sessions to consumer groups, see SET_CONSUMER_GROUP_MAPPING.

5. Activate the resource management plan mview_task.

   SET GLOBAL resource_manager_plan = 'mview_task';
   

   For more information about activating resource management plans, see resource_manager_plan.

6. Modify the CPU resource usage upper limit to 30%.

   CALL DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
       PLAN => 'mview_task',
       GROUP_OR_SUBPLAN => 'mview_group',
       NEW_UTILIZATION_LIMIT => 30
       );
   

   For more information about updating resource plans, see UPDATE_PLAN_DIRECTIVE.

References

• Materialized view log
• Refresh a materialized view
• DBMS_RESOURCE_MANAGER overview