Manage PDB Disk I/O for resources to avoid performance issue in Oracle

Manage I/O Parameters (MAX_IOPS, MAX_MBPS)

Max_IOPS parameter sets the maximum I/O operations per second for the PDB. Default is disabled with value 0.

MAX_MBPS parameter sets the maximum I/O MB per second for the PDB. Default is disabled with value 0.

We can set at our level to manage the max IO operation for a particular PDB to address the bottleneck for other PDBs or as a part of Resource Management.

Alter command:

SQL> alter session set container=pdb1;

SQL> show parameter max_mbps;
Name       TYPE     VALUE
---------  -------  ------
max_mbps   integer    0

SQL> show parameter max_iops;
Name       TYPE     VALUE
---------  -------  -------
max_iops   integer   0

SQL> alter system set max_iops=500 scope=both;
SQL> alter system set max_mbps=200 scope=both;

Monitor the IOPS and MBPS with following queries:

SET LINESIZE 180
COLUMN pdb_name FORMAT A10
COLUMN begin_time FORMAT A26
COLUMN end_time FORMAT A26
-- Check last hour data for all pdbs
SELECT r.con_id, p.pdb_name,convert(r.begin_time,'DD-MON-YYYY HH24:MI:SS'),
       convert(r.end_time,'DD-MON-YYYY HH24:MI:SS'),
r.iops, r.iombps, r.iops_throttle_exempt,r.iombps_throttle_exempt
FROM   v$rsrcpdbmetric r, cdb_pdbs p
WHERE  r.con_id = p.con_id ORDER BY p.pdb_name;

-- Check last hours data for PDB1 from history table
SELECT r.con_id, p.pdb_name, convert(r.begin_time,'DD-MON-YYYY HH24:MI:SS'), convert(r.end_time,
'DD-MON-YYYY HH24:MI:SS'), r.iops, r.iombps,
r.iops_throttle_exempt, r.iombps_throttle_exempt
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;