oracle12c之三 控制PDB中CPU 资源使用
CPU资源隔离
数据库中,不同的PDB对主机CPU资源使用要求不同,那么我们就可以使用CDB resourceplans来管理不同pdb对CPU资源的使用。
CDB Resource Plans
PDB数量很多的情况下,使用这种CDB级别的资源计划对每个PDB进行资源限制会很麻烦。在12cR2版本中,引入了PDB Performance profile特性,来使一组PDB使用相同的resource plan,而不是一个个PDB进行配置。一些资源需求相同的PDB,可以创建一个performance profile,这些PDB只要设置一下DB_PERFORMANCE_PROFILE初始化参数,就可以使用相同的性能配置。
例:
创建PDB性能配置的CDB资源计划
woqucdb_plan
。里面包括PDB performance profile,gold、silver。1)创建pending area
在CDB$ROOT
中
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN(
plan => 'woqucdb_plan',
comment => 'CDB resource plan for woqucdb');
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN(
plan => 'woqucdb_plan',
comment => 'CDB resource plan for woqucdb');
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(
plan => 'woqucdb_plan,
profile => 'gold',
shares => 3,
utilization_limit => 100,
parallel_server_limit => 100);
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(
plan => 'woqucdb_plan,
profile => 'gold',
shares => 3,
utilization_limit => 100,
parallel_server_limit => 100);
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(
plan => 'newcdb_plan',
profile => 'silver',
shares => 2,
utilization_limit => 40,
parallel_server_limit => 40);
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(
plan => 'newcdb_plan',
profile => 'silver',
shares => 2,
utilization_limit => 40,
parallel_server_limit => 40);
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(
plan => 'newcdb_plan',
profile => 'bronze',
shares => 1,
utilization_limit => 20,
parallel_server_limit => 20);
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(
plan => 'newcdb_plan',
profile => 'bronze',
shares => 1,
utilization_limit => 20,
parallel_server_limit => 20);
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.UPDATE_CDB_PROFILE_DIRECTIVE(
plan=> 'woqucdb_plan',
profile=> 'gold',
new_shares=> 1,
new_utilization_limit=> 10,
new_parallel_server_limit=> 20);
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.UPDATE_CDB_PROFILE_DIRECTIVE(
plan=> 'woqucdb_plan',
profile=> 'gold',
new_shares=> 1,
new_utilization_limit=> 10,
new_parallel_server_limit=> 20);
END;
/
execDBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
execDBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
execDBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
execDBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
ALTERSYSTEM SET RESOURCE_MANAGER_PLAN = 'woqucdb_plan' scope=both;
ALTERSYSTEM SET RESOURCE_MANAGER_PLAN = 'woqucdb_plan' scope=both;
altersession set container=PDB1;
altersystem set db_performance_profile='gold' scope=spfile;
altersession set container=PDB2;
altersystem set db_performance_profile='silver' scope=spfile;
altersession set container=PDB3;
altersystem set db_performance_profile='bronze' scope=spfile;
altersession set container=PDB1;
altersystem set db_performance_profile='gold' scope=spfile;
altersession set container=PDB2;
altersystem set db_performance_profile='silver' scope=spfile;
altersession set container=PDB3;
altersystem set db_performance_profile='bronze' scope=spfile;
conn/as sysdba
alterpluggable database all close immediate;
alterpluggable database all open;
conn/as sysdba
alterpluggable database all close immediate;
alterpluggable database all open;
altersession set container=PDB4;
altersystem set db_performance_profile=gold scope=spfile;
alterpluggable database close immediate;
alterpluggable database open;
altersession set container=PDB4;
altersystem set db_performance_profile=gold scope=spfile;
alterpluggable database close immediate;
alterpluggable database open;
altersession set container=CDB$ROOT;
selectinst_id, name, con_id, value, ispdb_modifiable from gv$system_parameter2 wherename = 'db_performance_profile' order by 1,2,3,4;
altersession set container=CDB$ROOT;
selectinst_id, name, con_id, value, ispdb_modifiable from gv$system_parameter2 wherename = 'db_performance_profile' order by 1,2,3,4;
指令中的share参数意义
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE
中shares来明确给PDB的共享值。多个PDB可以共用这一个profile。
CPU : utilization_limit标识一个pdb可以使用系统百分比,取值0-100. 你可以在pdb中设置cpu_count参数限制使用。
例:cpu_count=4 那么当前pdb任何情况下都不会超过4,如果utilization_limit和 cpu_count同时设置,默认取最小值
Paralle:如果初始化PARALLEL_SERVERS_TARGET 设置为200 并具,parallel_server_limit 在当前PDB设置为10%,对于PDB利用极限是20并行度(200乖以0.1)
show parameter db_performance_profile
show parameter db_performance_profile
31)更改默认performance profile
我们可以用下面的过程来更改默认的指令。
BEGIN
DBMS_RESOURCE_MANAGER. UPDATE_CDB_DEFAULT_DIRECTIVE (
PLAN =>'newcdb_plan',
NEW_COMMENT => 'CDB resourceplan for newcdb1',
NEW_SHARES => 3,
NEW_UTILIZATION_LIMIT => 100,
NEW_PARALLEL_SERVER_LIMIT => 100);
END;
/
BEGIN
DBMS_RESOURCE_MANAGER. UPDATE_CDB_DEFAULT_DIRECTIVE (
PLAN =>'newcdb_plan',
NEW_COMMENT => 'CDB resourceplan for newcdb1',
NEW_SHARES => 3,
NEW_UTILIZATION_LIMIT => 100,
NEW_PARALLEL_SERVER_LIMIT => 100);
END;
/
当一个PDB从CDB中拔出时,相应的指令默认保留,当这个PDB迁移回来,它就会继续使用这个指令。除非手工删除。
一个指令可以给多个PDB使用,但是一个PDB不能用多个指令。
<wiz_tmp_tag id="wiz-table-range-border" contenteditable="false" style="display: none;">