-
DBMS_RESOURCE_MANAGER
参考文档：
Database Administrator’s Guide
27 Managing Resources with Oracle Database Resource Manager
27.5.5 Creating a Resource Plan
- BEGIN
- DBMS_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;
- /
- BEGIN
- DBMS_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;
- BEGIN
- DBMS_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;
- /
- BEGIN
- DBMS_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;
- /
- BEGIN
- DBMS_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;
- /
- BEGIN
- DBMS_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
-
相关阅读:
智慧电力运维系统助力实现配电室无人值守
【笔记】从ES到ClickHouse：B站海量日志分析场景迁移的实践与思考
Python Day3 爬虫-数据接口和selenium基础
java 并发AQS 理解
小米面试——计算机视觉算法实习生
【移动应用开发期末复习】第五/六章
【CSP】2021-09-2 非零段划分 索引+递推/差分+前缀和
MMSeg搭建自己的网络
由浅入深理解latent diffusion/stable diffusion(3)：一步一步搭建自己的stable diffusion models
视频教程下载：用ChatGPT的 API 开发AI应用指南
- 原文地址：https://blog.csdn.net/xxzhaobb/article/details/132687595
