8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

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

In the previous release there was no easy way to control the amount of disk I/O used by an individual PDB. As a result a "noisy neighbour" could use up lots of disk I/O and impact the performance of other PDBs in the same instance. Oracle Database 12c Release 2 (12.2) allows you to control the amount of disk I/O used by a PDB, making consolidation more reliable.

I/O Parameters (MAX_IOPS, MAX_MBPS)
Setting I/O Parameters (MAX_IOPS, MAX_MBPS)
Monitoring I/O Usage for PDBs

Multitenant : Disk I/O (IOPS, MBPS) Resource Management for PDBs
Multitenant : Resource Manager with Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 1 (12.1)
Multitenant : All Articles
Resource Manager Quick Links : 8i, 9i, 10g, 11gR1, 11gR2, 12cR1, 12cR2, All Articles

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.

MAX_IOPS : The maximum I/O operations per second for the PDB. Default "0". Values less that 100 IOPS are not recommended.
MAX_MBPS : 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.

Setting I/O Parameters

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

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;

The example below sets the MAX_IOPS and MAX_MBPS parameters at the PDB level

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;

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.

V$RSRCPDBMETRIC : A single row per PDB, holding the last of the 1 minute samples.
V$RSRCPDBMETRIC_HISTORY : 61 rows per PDB, holding the last 60 minutes worth of samples from the V$RSRCPDBMETRIC view.
V$RSRC_PDB : Cumulative statistics since the CDB resource plan ws set.
DBA_HIST_RSRC_PDB_METRIC : AWR snaphots, retained based on the AWR retention period.

The following queries are examples of their usage.

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;

For more information see:

Hope this helps. Regards Tim...

Back to the Top.