当前位置： 首页 > news >正文

DBMS_RESOURCE_MANAGER

news 2025/8/18 1:35:49

参考文档：

Database Administrator’s Guide

27 Managing Resources with Oracle Database Resource Manager

27.5.5 Creating a Resource Plan

BEGINDBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN    => 'bugdb_plan',COMMENT => 'Resource plan/method for bug users sessions');DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN    => 'maildb_plan',COMMENT => 'Resource plan/method for mail users sessions');DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN    => 'mydb_plan',COMMENT => 'Resource plan/method for bug and mail users sessions');DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Online_group',COMMENT        => 'Resource consumer group/method for online bug users sessions');DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Batch_group',COMMENT        => 'Resource consumer group/method for batch job bug users sessions');DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Bug_Maint_group',COMMENT        => 'Resource consumer group/method for users sessions for bug db maint');DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Users_group',COMMENT        => 'Resource consumer group/method for mail users sessions');DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Postman_group',COMMENT        => 'Resource consumer group/method for mail postman');DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Mail_Maint_group',COMMENT        => 'Resource consumer group/method for users sessions for mail db maint');DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN             => 'bugdb_plan',GROUP_OR_SUBPLAN => 'Online_group',COMMENT          => 'online bug users sessions at level 1',MGMT_P1          => 80,MGMT_P2          => 0);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN                     => 'bugdb_plan',GROUP_OR_SUBPLAN         => 'Batch_group',COMMENT                  => 'batch bug users sessions at level 1',MGMT_P1                  => 20,MGMT_P2                  => 0,PARALLEL_DEGREE_LIMIT_P1 => 8);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN             => 'bugdb_plan',GROUP_OR_SUBPLAN => 'Bug_Maint_group',COMMENT          => 'bug maintenance users sessions at level 2',MGMT_P1          => 0,MGMT_P2          => 100);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN             => 'bugdb_plan',GROUP_OR_SUBPLAN => 'OTHER_GROUPS',COMMENT          => 'all other users sessions at level 3',MGMT_P1          => 0,MGMT_P2          => 0,MGMT_P3          => 100);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN             => 'maildb_plan',GROUP_OR_SUBPLAN => 'Postman_group',COMMENT          => 'mail postman at level 1',MGMT_P1          => 40,MGMT_P2          => 0);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN             => 'maildb_plan',GROUP_OR_SUBPLAN => 'Users_group',COMMENT          => 'mail users sessions at level 2',MGMT_P1          => 0,MGMT_P2          => 80);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN             => 'maildb_plan',GROUP_OR_SUBPLAN => 'Mail_Maint_group',COMMENT          => 'mail maintenance users sessions at level 2',MGMT_P1          => 0,MGMT_P2          => 20);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN             => 'maildb_plan',GROUP_OR_SUBPLAN => 'OTHER_GROUPS',COMMENT          => 'all other users sessions at level 3',MGMT_P1          => 0,MGMT_P2          => 0,MGMT_P3          => 100);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN             => 'mydb_plan',GROUP_OR_SUBPLAN => 'maildb_plan',COMMENT          => 'all mail users sessions at level 1',MGMT_P1          => 30);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN             => 'mydb_plan',GROUP_OR_SUBPLAN => 'bugdb_plan',COMMENT          => 'all bug users sessions at level 1',MGMT_P1          => 70);DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

BEGINDBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN    => 'MAXCAP_PLAN',COMMENT => 'Limit overall database CPU');DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN              => 'MAXCAP_PLAN',GROUP_OR_SUBPLAN  => 'OTHER_GROUPS',COMMENT           => 'This group is mandatory',UTILIZATION_LIMIT => 90);DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;

BEGINDBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'START_GROUP',COMMENT        => 'Sessions start here');DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'QUARANTINE_GROUP',COMMENT        => 'Sessions switched here to quarantine them');DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN    => 'Quarantine_plan',COMMENT => 'Quarantine runaway queries');DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN             => 'Quarantine_plan',GROUP_OR_SUBPLAN => 'START_GROUP',COMMENT          => 'Max CPU 10 minutes before switch',MGMT_P1          => 75,switch_group     => 'QUARANTINE_GROUP',switch_time      => 600);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN             => 'Quarantine_plan',GROUP_OR_SUBPLAN => 'OTHER_GROUPS',COMMENT          => 'Mandatory',MGMT_P1          => 25);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN              => 'Quarantine_plan',GROUP_OR_SUBPLAN  => 'QUARANTINE_GROUP',COMMENT           => 'Limited CPU',MGMT_P2           => 100,UTILIZATION_LIMIT => 20);DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

BEGINDBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'APP1_GROUP',COMMENT        => 'Apps group 1');DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'APP2_GROUP',COMMENT        => 'Apps group 2');DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'APP3_GROUP',COMMENT        => 'Apps group 3');DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'APP4_GROUP',COMMENT        => 'Apps group 4');DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN    => 'apps_plan',COMMENT => 'Application consolidation');DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN              => 'apps_plan',GROUP_OR_SUBPLAN  => 'APP1_GROUP',COMMENT           => 'Apps group 1',UTILIZATION_LIMIT => 30);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN              => 'apps_plan',GROUP_OR_SUBPLAN  => 'APP2_GROUP',COMMENT           => 'Apps group 2',UTILIZATION_LIMIT => 30);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN              => 'apps_plan',GROUP_OR_SUBPLAN  => 'APP3_GROUP',COMMENT           => 'Apps group 3',UTILIZATION_LIMIT => 30);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN              => 'apps_plan',GROUP_OR_SUBPLAN  => 'APP4_GROUP',COMMENT           => 'Apps group 4',UTILIZATION_LIMIT => 30);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN              => 'apps_plan',GROUP_OR_SUBPLAN  => 'OTHER_GROUPS',COMMENT           => 'Mandatory',UTILIZATION_LIMIT => 20);DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

BEGINDBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'APP1_GROUP',COMMENT        => 'Group for application #1');DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'APP2_OLTP_GROUP',COMMENT        => 'Group for OLTP activity in application #2');DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'APP2_ADHOC_GROUP',COMMENT        => 'Group for ad-hoc queries in application #2');DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'APP2_REPORT_GROUP',COMMENT        => 'Group for reports in application #2');DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN    => 'APPS_PLAN',COMMENT => 'Plan for managing 3 applications');DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN     => 'APP2_SUBPLAN',COMMENT  => 'Subplan for managing application #2',SUB_PLAN => TRUE);DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN     => 'APP2_REPORTS_SUBPLAN',COMMENT  => 'Subplan for managing reports in application #2',SUB_PLAN => TRUE);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN              => 'APPS_PLAN',GROUP_OR_SUBPLAN  => 'APP1_GROUP',COMMENT           => 'Limit CPU for application #1 to 40%',UTILIZATION_LIMIT => 40);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN              => 'APPS_PLAN',GROUP_OR_SUBPLAN  => 'APP2_SUBPLAN',COMMENT           => 'Limit CPU for application #2 to 40%',UTILIZATION_LIMIT => 40);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN              => 'APP2_SUBPLAN',GROUP_OR_SUBPLAN  => 'APP2_OLTP_GROUP',COMMENT           => 'Limit CPU for OLTP to 90% of application #2',UTILIZATION_LIMIT => 90);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN             => 'APP2_SUBPLAN',GROUP_OR_SUBPLAN => 'APP2_REPORTS_SUBPLAN',COMMENT          => 'Subplan for ad-hoc and normal reports for application #2');DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN              => 'APP2_REPORTS_SUBPLAN',GROUP_OR_SUBPLAN  => 'APP2_ADHOC_GROUP',COMMENT           => 'Limit CPU for ad-hoc queries to 50% of application #2 reports',UTILIZATION_LIMIT => 50);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN              => 'APP2_REPORTS_SUBPLAN',GROUP_OR_SUBPLAN  => 'APP2_REPORT_GROUP',COMMENT           => 'Limit CPU for reports to 50% of application #2 reports',UTILIZATION_LIMIT => 50);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN             => 'APPS_PLAN',GROUP_OR_SUBPLAN => 'OTHER_GROUPS',COMMENT          => 'No directives for default users');DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

BEGINDBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN    => 'erp_plan',COMMENT => 'Resource plan/method for ERP Database');DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'oltp',COMMENT        => 'Resource consumer group/method for OLTP jobs');DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'batch',COMMENT        => 'Resource consumer group/method for BATCH jobs');DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN              => 'erp_plan',GROUP_OR_SUBPLAN  => 'oltp',COMMENT           => 'OLTP sessions',MGMT_P1           => 60,SWITCH_GROUP      => 'batch',SWITCH_TIME       => 3,UNDO_POOL         => 200,SWITCH_FOR_CALL   => TRUE,SESSION_PGA_LIMIT => 20);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN                   => 'erp_plan',GROUP_OR_SUBPLAN       => 'batch',COMMENT                => 'BATCH sessions',MGMT_P1                => 30,PARALLEL_SERVER_LIMIT  => 8,PARALLEL_QUEUE_TIMEOUT => 600,MAX_EST_EXEC_TIME      => 3600);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN             => 'erp_plan',GROUP_OR_SUBPLAN => 'OTHER_GROUPS',COMMENT          => 'mandatory',MGMT_P1          => 10);DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

END

http://www.lryc.cn/news/159310.html

相关文章：

  • 通俗讲解傅里叶变换
  • 数据结构——带头双向循环链表
  • MySQL大数据量高速迁移，500GB只需1个小时
  • kafka复习：（25）kafka stream
  • 接口自动化测试总结
  • 【Redis】Lua脚本在Redis中的基本使用及其原子性保证原理
  • 汇编--int指令
  • 生成式AI的JavScript技术栈
  • 从零开始学习软件测试-第39天笔记
  • 【多思路附源码】2023高教社杯 国赛数学建模C题思路 - 蔬菜类商品的自动定价与补货决策
  • Vue2+Vue3基础入门到实战项目（六）——课程学习笔记
  • QT—基于http协议的网络文件下载
  • SpringBoot-配置优先级
  • 科普初步了解大模型
  • Nginx 和 网关的关系是什么
  • 解决springboot项目中的groupId、package或路径的混淆问题
  • Vmware 网络恢复断网和连接
  • 学生来看！如何白嫖内网穿透？点进来！
  • C++中的stack和queue
  • Ubuntu-22.04通过RDP协议连接远程桌面
  • 20230908java面经整理
  • uniapp 开发App 网络异常如何处理
  • docker安装常用软件
  • CocosCreator3.8研究笔记（五）CocosCreator 脚本说明及使用（下）
  • Adobe Acrobat Reader界面改版 - 解决方案
  • 实用调试技巧（2）
  • 海外ASO优化之如何优化游戏应用
  • SpringMVC: Java Web应用开发的框架之选
  • 【华为设备升级】AR路由器升级设备软件示例
  • Dataset 的一些 Java api 操作