Oracle 12C r2-new features-pdb disk I/O (iops,mbps) resource management

Oracle 12C r2-new features-pdb disk I/O (iops,mbps) resource management
In previous versions, there was no easy way to control the amount of disk I/O used by a single PDB. As a result, a PDB may run out of disk I/O and affect the performance of other PDB in the same instance. The Oracle 12c R2 can control the amount of disk I/O used by the PDB and is more harmonious with multi-pdb.
1 Introduction to I/O parameters
Some of the following parameters can be set at the PDB or CDB level to limit the PDB I/O.
Maximum I/O operations per second for Max Iops:pdb. The default value is "0". It is not recommended to use values less than IOPS.
The maximum number of megabytes per second I/O for the max_mbps:pdb. The default value is "0". Values less than Mbps are not recommended.
Some areas to note:
• 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 PDB.
• When they are set at the PDB level, they override any default values.
• If the value is "0" at the CDB and PDB levels, there is no I/O limit.
• The critical I/O required for the normal function of the instance is unrestricted, but as long as the limit is involved, the total I/O is counted, so I/O may temporarily exceed the limit.
• These parameters are only available for multi-tenancy architectures.
• This feature is not available for Exadata.
• Throttling will result in a wait event named I/O rate limit.
2 setting I/O parameters
The following is the set Max_iops and max_mbps parameters at the CDB level, and the values are for all PDB.
--Set parameter values

SQL>ALTERSET max_iops= + SCOPE=BOTH; SQL>ALTERSET max_mbps= n SCOPE=BOTH;

--Remove parameter values

SQL>ALTERSET max_iops=0 SCOPE=BOTH; SQL>ALTERSET max_mbps=0 SCOPE=BOTH;

Here are the two parameters for the PDB level setting:

SQL>ALTERSET= pdb1;

--Set the value of the specified PDB parameter

SQL>ALTERSET max_iops= + SCOPE=BOTH; SQL>ALTERSET max_mbps= n SCOPE=BOTH;

--Remove the specified PDB parameter value

SQL>ALTERSET max_iops=0 SCOPE=BOTH; SQL>ALTERSET max_mbps=0 SCOPE=BOTH;

3 Monitoring of I/O usage of the PDB
Oracle now provides views to monitor the use of PDB resources (CPU, I/O, parallel execution, memory)
Such as:
· V $ rsrcpdbmetric: one row per pdb, save the last of the 1-minute sample.
· V $ rsrcpdbmetric_history: Each PDB has 61 rows, and the last 60 minute sample is saved from the V $ rsrcpdbmetric view.
· Dba_hist_rsrc_pdb_metric:awr snapshots are retained based on the awr retention period.
Here is the specific SQL:

Sql>SETLinesize theSQL>COLUMNpdb_name FORMAT A10sql>COLUMNbegin_time FORMAT A26sql>COLUMNend_time FORMAT A26sql>ALTERSESSIONSETNls_date_format='dd-mon-yyyy HH24:MI:SS'; SQL>ALTERSESSIONSETNls_timestamp_format='dd-mon-yyyy HH24:MI:SS.FF';

--final sample per PDB

SQL>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=  p.con_idORDER   by P.pdb_name;

--a sample of the last few hours of PDB1

SQL>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_history R,       Cdb_pdbs pWHERE=  p.con_id  and ='PDB1'ORDER by R.begin_time;

--Information on all awr snapshots of PDB1

SQL>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 pWHERE=  P.con _idand ='PDB1'ORDER by R.begin _time;

Oracle 12C r2-new features-pdb disk I/O (iops,mbps) resource management