DBA Hub

📋Steps in this guide1/3

Multitenant : Disk I/O (IOPS, MBPS) Resource Management for PDBs in Oracle Database 12c Release 2 (12.2)

Control the amount of disk I/O used by a PDB, making consolidation more reliable in Oracle Database 12c Release 2 (12.2).

oracle 12cconfigurationintermediate
by OracleDba
12 views
1

I/O Parameters (MAX_IOPS, MAX_MBPS)

The following parameters can be set at the CDB or PDB level to throttle I/O at the PDB level. - : The maximum I/O operations per second for the PDB. Default "0". Values less that 100 IOPS are not recommended. - : The maximum megabytes of I/O per second for the PDB. Default "0". Values less that 25 MBPS are not recommended. Some things to consider about their usage are listed below. - The parameters are independent. You can use none, one or both. - When the parameters are set at the CDB level they become the default values used by all PDBs. - When they are set at the PDB level they override any default values. - If the values are "0" at both the CDB and PDB level there is no I/O throttling. - Critical I/Os necessary for normal function of the instance are not limited, but do count towards the total I/O as far as the limit is concerned, so it is possible for the I/O to temporarily exceed the limit. - The parameters are only available for the multitenant architecture. - This feature is not available for Exadata. - Throttling will result in a resource manager wait event called resmgr: I/O rate limit .
2

Setting I/O Parameters

The example below sets the and parameters at the CDB level, the default values for all PDBs. The example below sets the and parameters at the PDB level

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CONN / AS SYSDBA

-- Set defaults.
ALTER SYSTEM SET max_iops=100 SCOPE=BOTH;
ALTER SYSTEM SET max_mbps=400 SCOPE=BOTH;

-- Remove defaults.
ALTER SYSTEM SET max_iops=0 SCOPE=BOTH;
ALTER SYSTEM SET max_mbps=0 SCOPE=BOTH;

CONN / AS SYSDBA

ALTER SESSION SET CONTAINER = pdb1;

-- Set PDB-specific values.
ALTER SYSTEM SET max_iops=100 SCOPE=BOTH;
ALTER SYSTEM SET max_mbps=400 SCOPE=BOTH;

-- Remove PDB-specific values.
ALTER SYSTEM SET max_iops=0 SCOPE=BOTH;
ALTER SYSTEM SET max_mbps=0 SCOPE=BOTH;
3

Monitoring I/O Usage for PDBs

Oracle now provides views to monitor the resource (CPU, I/O, parallel execution, memory) usage of PDBs. Each view contains similar information, but for different retention periods. - : A single row per PDB, holding the last of the 1 minute samples. - : 61 rows per PDB, holding the last 60 minutes worth of samples from the view. - : Cumulative statistics since the CDB resource plan ws set. - : AWR snaphots, retained based on the AWR retention period. The following queries are examples of their usage. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
CONN / AS SYSDBA

SET LINESIZE 180
COLUMN pdb_name FORMAT A10
COLUMN begin_time FORMAT A26
COLUMN end_time FORMAT A26
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; 
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YYYY HH24:MI:SS.FF'; 

-- Last sample per PDB.
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;

-- Last hours samples for PDB1
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_history r,
       cdb_pdbs p
WHERE  r.con_id = p.con_id
AND    p.pdb_name = 'PDB1'
ORDER BY r.begin_time;

-- All AWR snapshot information for PDB1.
SELECT r.snap_id,
       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   dba_hist_rsrc_pdb_metric r,
       cdb_pdbs p
WHERE  r.con_id = p.con_id
AND    p.pdb_name = 'PDB1'
ORDER BY r.begin_time;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!