小工具知识网

当前位置: 首页 > 行业知识 > Oracle-PDB资源限制

行业知识

Oracle-PDB资源限制

2026-11-18 14:58:17 来源:互联网转载

前言:

​

在使用CDB容器数据库时,由于某些PDB出现高并发或者慢SQL,可能会出现PDB资源恶意争用的情况,最终导致一个PDB耗尽全部CDB资源。所以,为了避免PDB出现资源恶意争用,我们需要对PDB的资源,尤其是内存、CPU、IO、并发进行限制,以避免出现一个PDB耗尽全部CDB资源的情况。

资源限制维度:

​资源限制方法与参数
CPU

1 CPU_COUNT

2 DBMS_RESOURCE_MANAGER

内存

1 SGA(SGA_TARGET+SGA_MIN_SIZE+DB_CACHE_SIZE+SHARED_POOL_SIZE)

2 PGA(PGA_AGGREGATE_LIMIT+PGA_AGGREGATE_TARGET)

IO

1 MAX_IOP

2 SMAX_MBPS

连接数pdb目前无法限制单个pdb的连接数,processes无法在pdb里面单独设置
并行服务进程

1 PARALLEL_MAX_SERVERS+PARALLEL_SERVERS_TARGET

2 DBMS_RESOURCE_MANAGER

CPU资源限制(CPU_COUNT):

1 通过在pdb里面设置cpu_count,限制单个pdb的cpu使用个数,如果pdb里面没有设置,则默认pdb的cpu_count等于根容器CDB$ROOT的cpu_count

2 CDB$ROOT的默认cpu_count为服务器cpu逻辑数量

3 设置pdb1的cpu_count为1,限制最多使用1个逻辑cpu

[oracle@rac19a ~]$ sqlplus / as sysdba​SQL*Plus: Release 19.0.0.0.0 - Production on Mon Aug 22 13:30:00 2022Version 19.3.0.0.0​Copyright (c) 1982, 2019, Oracle.  All rights reserved.​Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0​SQL> show parameter cpu_countNAME             TYPE   VALUE------------------------------------ ----------- ------------------------------cpu_count           integer   2SQL> show pdbs;     ​    CON_ID CON_NAME        OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ----------   2 PDB$SEED        READ ONLY  NO   3 PDB3         READ WRITE NO   4 PDB1         READ WRITE NO   5 PDB2         READ WRITE NO   7 PDB4         READ WRITE NOSQL> alter session set container=pdb1;​Session altered.​SQL> alter system set cpu_count=1;​System altered.​SQL> show parameter cpu_count​NAME             TYPE   VALUE------------------------------------ ----------- ------------------------------cpu_count           integer   1SQL> ​SQL> set linesize 400set pagesize 400col name for a50col value for a50col dbname for a10select a.con_id,nvl(b.name,'CDB$ROOT') as dbname,a.name,a.value from v$system_parameter a,v$pdbs b where a.con_id=b.con_id(+) and  a.name ='cpu_count'order by a.con_id;SQL>​    CON_ID DBNAME     NAME             VALUE---------- ---------- -------------------------------------------------- --------------------------------------------------   0 CDB$ROOT   cpu_count            2   4 PDB1       cpu_count            1

CPU资源限制(DBMS_RESOURCE_MANAGER):

1 通过DBMS_RESOURCE_MANAGER进行限制,DBMS_RESOURCE_MANAGER可以创建不同的资源限制组(利用率、配额、并行不同),通过将不同PDB设置在不到的组里面,从而限制特定 PDB 的 CPU 使用率,并行执行服务器的数量,内存使用量(exadata)

2 DBMS_RESOURCE_MANAGER操作步骤

1. 创建初始化区域​In the CDB:exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();​2. 创建CDB资源计划​BEGINDBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN(plan => 'cdb_plan',comment => 'CDB resource plan for cdb');END;/​3. 使用CREATE_CDB_PROFILE_DIRECTIVE创建pdb资源限制策略,这里创建3个等级a,b,c,对不同的pdb进行分组#plan:所属CDB资源计划,填上一步创建的CDB资源计划即可#profile pdb资源策略名称#shares:资源配额,a,b,c的shares分别对应(3,2,1),所以a的资源配额占比为50%[3/(3+2+1)]#cpu限制:最高使用cdb的cpu百分比#并行服务限制:最高使用cdb的并行服务进程百分比---创建A策略BEGINDBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(plan => 'cdb_plan',profile => 'A',shares => 3,utilization_limit => 100,parallel_server_limit => 100);END;/---创建B策略BEGINDBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(plan => 'cdb_plan',profile => 'B',shares => 2,utilization_limit => 40,parallel_server_limit => 40);END;/---创建C策略BEGINDBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(plan => 'cdb_plan',profile => 'C',shares => 1,utilization_limit => 20,parallel_server_limit => 20);END;/​4 验证初始化区域exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();5 提交初始化区域exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();6 CDB设置资源计划(In the CDB)ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'cdb_plan' scope=both;7 在pdb层面设置不同的资源限制组alter session set container=PDB1;alter system set db_performance_profile='A' scope=spfile;alter session set container=PDB2;alter system set db_performance_profile='B' scope=spfile;alter session set container=PDB3;alter system set db_performance_profile='C' scope=spfile;8 重启pdb生效alter pluggable database all close immediate;alter pluggable database all open;9 检查pdb资源限制配置col name for a30col value for a30 set linesize 400alter session set container=CDB$ROOT;select inst_id, name, con_id, value, ispdb_modifiable from gv$system_parameter2 where name = 'db_performance_profile' order by 1,2,3,4;​   INST_ID NAME             CON_ID VALUE          ISPDB---------- ------------------------------ ---------- ------------------------------ -----   1 db_performance_profile       0            TRUE   1 db_performance_profile       2            TRUE   1 db_performance_profile       3 C            TRUE   1 db_performance_profile       4 A            TRUE   1 db_performance_profile       5 B            TRUE   1 db_performance_profile       7            TRUE​alter session set container=CDB$ROOT;select p.name, shares, utilization_limit, profile,INSTANCE_CAGING,CPU_MANAGED,PARALLEL_SERVER_LIMIT from v$rsrc_plan r, v$pdbs p where r.con_id = p.con_id;​NAME        SHARES     UTILIZATION_LIMIT PROFILE    INS CPU PARALLEL_SERVER_LIMIT--------------- ---------- ----------------- ------------   --- --- ---------------------PDB$SEED     1                      OFF   ONPDB3       1            20       C      OFF  ON         20PDB1       3                  A      OFF  ONPDB2       2           40       B      OFF  ON         40PDB4       1                      OFF  ON​#PDB1 因为UTILIZATION_LIMIT和PARALLEL_SERVER_LIMIT 都是100%,所以没有限制,查询结果为空

内存资源限制SGA:

1 PDB的SGA限制主要通过参数SGA_MIN_SIZE限制SGA内存下限以及通过参数SGA_TARGET限制SGA内存上限

2 通过sga_target参数限制单个PDB的最大SGA大小,当前root容器的sga设置为1104M

SQL> show parameter sga​NAME             TYPE   VALUE------------------------------------ ----------- ------------------------------allow_group_access_to_sga       boolean   FALSElock_sga           boolean   FALSEpre_page_sga           boolean   TRUEsga_max_size           big integer 1104Msga_min_size           big integer 0sga_target           big integer 1104Munified_audit_sga_queue_size       integer   1048576

3 将pdb1 的sga最大大小限制为800M

---pdb 默认sga_target=0SQL> alter session set container=pdb1;​Session altered.​SQL> show parameter sga​NAME             TYPE   VALUE------------------------------------ ----------- ------------------------------sga_max_size           big integer 1104Msga_min_size           big integer 0sga_target           big integer 0---将sga_target设置为800MSQL> alter system set sga_target=800M;​System altered.​SQL> show parameter sga​NAME             TYPE   VALUE------------------------------------ ----------- ------------------------------allow_group_access_to_sga       boolean   FALSElock_sga           boolean   FALSEpre_page_sga           boolean   TRUEsga_max_size           big integer 1104Msga_min_size           big integer 0sga_target           big integer 500Munified_audit_sga_queue_size       integer   1048576

4 通过sga_min_size参数限制单个PDB的最小SGA大小

5 将pdb1 的sga最小值限制为400M,注意:sga_min_size alter session set container=pdb1;​Session altered.​SQL> alter system set sga_min_size=400M;​System altered.​SQL> show parameter sga​NAME TYPE VALUE------------------------------------ ----------- ------------------------------allow_group_access_to_sga boolean FALSElock_sga boolean FALSEpre_page_sga boolean TRUEsga_max_size big integer 1104Msga_min_size big integer 400Msga_target big integer 800Munified_audit_sga_queue_size integer 1048576

6 此外,还可以进一步对pdb的db_cache_size以及shared_pool_size最小值进行限制,注意:db_cache_size or shared_pool_size alter system set db_cache_size=400M;​System altered.​SQL> alter system set shared_pool_size=200M;​System altered.​SQL> SQL> show parameter db_cache​NAME TYPE VALUE------------------------------------ ----------- ------------------------------db_cache_advice string ONdb_cache_size big integer 400MSQL> show parameter shared​NAME TYPE VALUE------------------------------------ ----------- ------------------------------hi_shared_memory_address integer 0max_shared_servers integershared_memory_address integer 0shared_pool_reserved_size big integer 18454937shared_pool_size big integer 200Mshared_server_sessions integershared_servers integer 1

内存资源限制PGA:

1 PDB的PGA限制主要通过参数pga_aggregate_target以及pga_aggregate_limit限PGA内存的上限

pga_aggregate_target在PDB的设置需要遵循以下规则:

1 小于或等于CDB级别设置的PGA_AGGREGATE_TARGET

2 小于或等于CDB级别设置的50% PGA_AGGREGATE_LIMIT

3 小于或等于PDB级别设置的50%  PGA_AGGREGATE_LIMIT

PGA_AGGREGATE_LIMIT在PDB的设置需要遵循以下规则:

1 小于或等于CDB级别设置的PGA_AGGREGATE_LIMIT

2 大于或等于两倍PDB级别设置的PGA_AGGREGATE_TARGET

2 限制单个PDB的pga大小,当前CDB根容器pga_aggregate_limit为2G,pga_aggregate_target为1G

SQL> alter session set container=pdb1;​Session altered.​SQL> show parameter pga​NAME             TYPE   VALUE------------------------------------ ----------- ------------------------------pga_aggregate_limit         big integer 2Gpga_aggregate_target         big integer 1G​SQL> ALTER SYSTEM SET PGA_AGGREGATE_LIMIT = 600M SCOPE = BOTH;​System altered.​SQL> ALTER SYSTEM SET PGA_AGGREGATE_TARGET =300M SCOPE = BOTH;​System altered.​SQL> show parameter pga​NAME             TYPE   VALUE------------------------------------ ----------- ------------------------------pga_aggregate_limit         big integer 600Mpga_aggregate_target         big integer 300M

3 可以通过检查监控PDB的内存使用情况

COLUMN PDB_NAME FORMAT A10SELECT r.CON_ID, p.PDB_NAME, r.SGA_BYTES/1024/1024, r.PGA_BYTES/1024/1024, r.BUFFER_CACHE_BYTES/1024/1024, r.SHARED_POOL_BYTES/1024/1024 FROM V$RSRCPDBMETRIC r, CDB_PDBS p WHERE r.CON_ID = p.CON_ID;

IO限制:

1 IO限制是Oracle12.2之后推出的新功能,通过参数MAX_IOPS 以及max_mbps限制数据库每秒的IO数量以及吞吐量

2 MAX_IOPS:每秒的IOPS数量,默认为0

3 MAX_MBPS:每秒的IO吞吐量(MB),默认为0

4 通过在PDB里面设置,可以限制PDB的IOPS以及MBPS

SQL> alter session set container=pdb1;​Session altered.​SQL> ALTER SYSTEM SET MAX_IOPS = 1000 SCOPE = BOTH;​System altered.​SQL> ALTER SYSTEM SET MAX_MBPS = 50 SCOPE = BOTH;​System altered.​SQL> show parameter MAX_​NAME             TYPE   VALUE------------------------------------ ----------- ------------------------------max_iops           integer   1000max_mbps           integer   50

​5 PDB监控IO使用情况

SELECT r.con_id,p.pdb_name,r.begin_time,r.end_time,r.iops,r.iombps,r.iops_throttle_exempt,r.iombps_throttle_exempt,r.avg_io_throttleFROM v$rsrcpdbmetric r,cdb_pdbs pWHERE r.con_id = p.con_idORDER BY p.pdb_name;

并行服务进程限制(PARALLEL参数):

1 并行服务进程通过PARALLEL_MAX_SERVERS与PARALLEL_SERVERS_TARGET进行PDB的限制

2 PARALLEL_MAX_SERVERS:用于控制数据库最大的并行进程处理数量,默认值为PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 5

3 PARALLEL_SERVERS_TARGET:用于控制单个执行语句的并行最大数量,默认值为

For a CDB:

Equal to the PARALLEL_MAX_SERVERS value for the CDB.

For a PDB or non-CDB:

PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 2

4 在PDB里面并行服务进程数量,当前根容器parallel_max_servers为40,parallel_servers_target为40

SQL> alter session set container=pdb1;​Session altered.​SQL> alter system set parallel_max_servers=16;System altered.​SQL> alter system set parallel_servers_target=8;​System altered.​SQL> SQL> SQL> show parameter parallel_​NAME             TYPE   VALUE------------------------------------ ----------- ------------------------------parallel_max_servers         integer   16parallel_min_degree         string   1parallel_min_percent         integer   0parallel_min_servers         integer   4parallel_min_time_threshold       string     AUTOparallel_servers_target        integer   8parallel_threads_per_cpu       integer   1recovery_parallelism         integer   0SQL> ​

并行服务进程限制(DBMS_RESOURCE_MANAGER):

并行服务进程通过DBMS_RESOURCE_MANAGER进行限制的方法参考CPU资源限制(DBMS_RESOURCE_MANAGER)章节的内容。       

CREATE_CDB_PROFILE_DIRECTIVE存储过程对parallel_server_limit的设置就是对并行服务的限制;

总结:

 

上一篇:揭秘小莎的秘密世界,探寻背后的故事传奇!

下一篇:爱奇艺电视下载教程,轻松获取大屏观影体验