OceanBase AI Database supports the DBMS_RESOURCE_MANAGER capability to provide resource isolation for incremental refreshes and mlog purge of materialized views, allowing you to set an upper limit on the resources a materialized view can use.
Example
Create the resource plan
mview_task.CALL DBMS_RESOURCE_MANAGER.CREATE_PLAN('mview_task','This resource plan is used for materialized view processing');For more information about creating a resource plan, see CREATE_PLAN.
Create the consumer group
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.
Create a resource plan directive to limit the CPU resource usage ratio to a maximum of 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.
Bind the consumer group
mview_groupto theFUNCTION.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.
Activate the resource management plan
mview_task.SET GLOBAL resource_manager_plan = 'mview_task';For more information about activating a resource management plan, see resource_manager_plan.
Modify the CPU resource 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, see UPDATE_PLAN_DIRECTIVE.