Resource isolation for materialized views

OceanBase Database supports using DBMS_RESOURCE_MANAGER to provide resource isolation for incremental refresh and MLOG purge of materialized views. You can set the maximum amount of resources that can be used by materialized views.

Example

1. Create a resource plan named mview_task.

   CALL DBMS_RESOURCE_MANAGER.CREATE_PLAN('mview_task','Resource plan for materialized view processing');
   

   For more information about creating a resource plan, 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 a consumer group, see CREATE_CONSUMER_GROUP.

3. Create a resource plan directive to limit CPU 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 a resource plan directive, see CREATE_PLAN_DIRECTIVE.

4. Map the resource 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 resource groups, see SET_CONSUMER_GROUP_MAPPING.

5. Activate the resource plan mview_task.

   SET GLOBAL resource_manager_plan = 'mview_task';
   

   For more information about activating a resource plan, see resource_manager_plan.

6. Update the CPU usage 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 a resource plan directive, see UPDATE_PLAN_DIRECTIVE.

References

• Materialized view log
• Refresh materialized views
• DBMS_RESOURCE_MANAGER overview