Oracle-PDB资源限制

前言:

​

在使用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 2022
Version 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 - Production
Version 19.3.0.0.0
​
SQL> show parameter cpu_count
NAME             TYPE   VALUE
------------------------------------ ----------- ------------------------------
cpu_count           integer   2
SQL> 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 NO
SQL> 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   1
SQL> 
​
SQL> set linesize 400
set pagesize 400
col name for a50
col value for a50
col dbname for a10
select 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资源计划
​
BEGIN
DBMS_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策略
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(
plan => 'cdb_plan',
profile => 'A',
shares => 3,
utilization_limit => 100,
parallel_server_limit => 100);
END;
/
---创建B策略
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(
plan => 'cdb_plan',
profile => 'B',
shares => 2,
utilization_limit => 40,
parallel_server_limit => 40);
END;
/
---创建C策略
BEGIN
DBMS_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 a30
col value for a30 
set linesize 400
alter 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   ON
PDB3       1            20       C      OFF  ON         20
PDB1       3                  A      OFF  ON
PDB2       2           40       B      OFF  ON         40
PDB4       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   FALSE
lock_sga           boolean   FALSE
pre_page_sga           boolean   TRUE
sga_max_size           big integer 1104M
sga_min_size           big integer 0
sga_target           big integer 1104M
unified_audit_sga_queue_size       integer   1048576

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

---pdb 默认sga_target=0
SQL> alter session set container=pdb1;
​
Session altered.
​
SQL> show parameter sga
​
NAME             TYPE   VALUE
------------------------------------ ----------- ------------------------------
sga_max_size           big integer 1104M
sga_min_size           big integer 0
sga_target           big integer 0
---将sga_target设置为800M
SQL> alter system set sga_target=800M;
​
System altered.
​
SQL> show parameter sga
​
NAME             TYPE   VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga       boolean   FALSE
lock_sga           boolean   FALSE
pre_page_sga           boolean   TRUE
sga_max_size           big integer 1104M
sga_min_size           big integer 0
sga_target           big integer 500M
unified_audit_sga_queue_size       integer   1048576

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

5 将pdb1 的sga最小值限制为400M，注意:sga_min_size<50%(sga_target)

SQL> 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   FALSE
lock_sga           boolean   FALSE
pre_page_sga           boolean   TRUE
sga_max_size           big integer 1104M
sga_min_size           big integer 400M
sga_target           big integer 800M
unified_audit_sga_queue_size       integer   1048576

6 此外，还可以进一步对pdb的db_cache_size以及shared_pool_size最小值进行限制,注意:db_cache_size or shared_pool_size<50%(sga_target)

SQL> 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   ON
db_cache_size           big integer 400M
SQL> show parameter shared
​
NAME             TYPE   VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address       integer   0
max_shared_servers         integer
shared_memory_address         integer   0
shared_pool_reserved_size       big integer 18454937
shared_pool_size         big integer 200M
shared_server_sessions         integer
shared_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 2G
pga_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 600M
pga_aggregate_target         big integer 300M

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

COLUMN PDB_NAME FORMAT A10
SELECT 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   1000
max_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_throttle
FROM v$rsrcpdbmetric r,
cdb_pdbs p
WHERE r.con_id = p.con_id
ORDER 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   16
parallel_min_degree         string   1
parallel_min_percent         integer   0
parallel_min_servers         integer   4
parallel_min_time_threshold       string     AUTO
parallel_servers_target        integer   8
parallel_threads_per_cpu       integer   1
recovery_parallelism         integer   0
SQL> 
​

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

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

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

总结:

以上为CDB容器数据库PDB的资源限制方式以及方法，在实际PDB的资源划分以及限制中，我们还需要根据数据库业务系统的等级以及压测结果进行评估与调整，从而确保CDB与PDB的数据库的运行稳定以及资源的合理分配。