ORA-AM: IO, Memory, CPU for resource isolation

The server host provides resources such as IO, memory, CPU, and storage space for the database.

The version 12.2 the following options have been introduced:

1) I/O rate limits for PDBs (Not supported on Exadata environment where I/O Resource Manager exists)

2) Memory Management

3) Performance Profiles

4) Resource Monitoring

Let's see how Oracle limits IO, memory, and CPU for different PDBs.

1) I/O Rate Limits for PDBs:-
****************************

In 12cR2, two parameters MAX_IOPS and MAX_MBPS are introduced to limit the physical IO of PDB. These two parameters only apply to PDB, and do not take effect for CDB and non-multi-tenant environments. In addition, these two parameters do not limit the redo log write (LGWR process) and buffercache dirty block write to disk (DBWR process).

IO is critical to database performance. If a PDB generates a lot of IO, it may cause other PDBs in the CDB to be affected as well. The MAX_IOPS parameter limits the number of IO operations per second, and the MAX_MBPS parameter limits the IO throughput per second. If these two parameters are set in pdb at the same time, they will take effect. If these two values are set in CDB$ROOT, then each PDB in the current CDB takes this as the default value.

By default, both parameters are 0. If these two parameters are 0 in PDB and 0 in CDB$ROOT, there is no IO limit for PDB.

In some cases, although the physical IO limit has been reached, some key IOs are still allowed, such as: access to control files or access to password files, but these key IOs are also counted in the IO count. It is not recommended to set the value of MAX_IOPS to less than 100 and the value of MAX_MBPS to less than 25

You can use DBA_HIST_RSRC_PDB_METRIC to calculate a reasonable PDB IO limit. When calculating the IO limit value, you can refer to the following: IOPS, IOMBPS, IOPS_THROTTLE_EXEMPT and IOMBPS_THROTTLE_EXEMPT.

APPLIES TO:
*************

Oracle Database – Enterprise Edition – Version 12.2.0.1 and later

Information in this document applies to any platform.

This feature limits the rate of physical I/O issued by a pluggable database (PDB). You can specify the limit as either I/O requests per second or as Mbps (megabytes of I/O per second). This limit can only be applied to a PDB, not to the multitenant container database (CDB) or a non-CDB.

Background I/O, such as redo log writes or dirty buffer cache writes, are not limited. The limits are specified with the new PDB parameters, MAX_IOPS and MAX_MBPS.

PURPOSE:-
***********

You can set the MAX_IOPS initialization parameter and the MAX_MBPS initialization parameter to limit the disk I/O generated by a PDB.

A large amount of disk I/O can cause poor performance. Several factors can result in excess disk I/O, such as poorly designed SQL or index and table scans in high-volume transactions. If one PDB is generating a large amount of disk I/O, then it can degrade the performance of other PDBs in the same CDB.

Use one or both of the following initialization parameters to limit the I/O generated by a particular PDB:

The MAX_IOPS initialization parameter limits the number of I/O operations for each second.

The MAX_MBPS initialization parameter limits the megabytes for I/O operations for each second.

Both limits are enforced if you set both initialization parameters for a single PDB.

DETAILS:-
**********

If these initialization parameters are set with the CDB root as the current container, then the values become the default values for all of the containers in the CDB. If they are set with an application root as the current container, then the values become the default values for all of the application PDBs in the application container. When they are set with a PDB or application PDB as the current container, then the settings take precedence over the default settings in the CDB root or the application root. These parameters cannot be set in a non-CDB.

The default for both of these initialization parameters is 0 (zero). If these initialization parameters are set to 0 (zero) in a PDB, and the CDB root is set to 0, then there is no I/O limit for the PDB. If these initialization parameters are set to 0 (zero) in an application PDB, and its application root is set to 0, then there is no I/O limit for the application PDB.

Critical I/O operations, such as ones for the control file and password file are exempted from the limit and continue to run even if the limit is reached. However, all I/O operations, including critical I/O operations, are counted when the number of I/O operations and the megabytes for I/O operations are calculated.

You can use the DBA_HIST_RSRC_PDB_METRIC view to calculate a reasonable I/O limit for a PDB. Consider the values in the following columns when calculating a limit: IOPS, IOMBPS, IOPS_THROTTLE_EXEMPT, and IOMBPS_THROTTLE_EXEMPT. The “resmgr:io rate limit” wait event indicates that a limit was reached.

I/O Parameters:
*****************

The following parameters can be set at the PDB level.

MAX_IOPS : The maximum I/O operations per second for the PDB.

MAX_MBPS : The maximum megabytes of I/O per second for the PDB.

View parameter on CDB level. (If set at the CDB level they become default values used on all PDBs)

Example 1 : Limiting the I/O Generated by a PDB
********************************************

With the PDB as the current container, run the following SQL statement to set the MAX_IOPS initialization parameter both in memory and in the spfile to a limit of 1,000 I/O operations for each second:

SQL> show parameter max_iops

PARAMETER_NAME      TYPE          VALUE
----------------- ------------ ---------
max_iops            integer        0

-->Set PDB as current Container
******************************************

SQL> alter session set container = PDB;

Set MAX_IOPS for the current PDB

SQL> alter system set max_iops=100 scope=both;

SQL> show parameter max_iops

PARAMETER_NAME         TYPE        VALUE
---------------       ---------- ------
max_iops               integer     100

Example 2 : Limiting the Megabytes of I/O Generated by a PDB
*******************************************************

With the PDB as the current container, run the following SQL statement to set the MAX_MBPS initialization parameter both in memory and in the spfile to a limit of 5 megabytes of I/O for each second:

SQL> ALTER SYSTEM SET MAX_MBPS = 5 SCOPE = BOTH;

=> To disable a limit, set the parameter to 0 (Default)

=> Event "resmgr: I/O rate limit“ (V$SYSTEM_EVENT, V$SESSION_EVENT) is raised when the limit is hit

=> Values set in CDB$ROOT are the default for PDBs

=> Not supported on Exadata

=> DBWR-I/O, Controlfile-I/O and Password file are exempted

=> Parameters are not supported on Non-CDBs ("ORA-56739: cannot modify max_iops or max_mbps parameter")

SQL> select con_id,event,time_waited from v$session_event where event='resmgr: I/O rate limit';

CON_ID     EVENT                       TIME_WAITED
-----     -----------------------       ------------
    0     resmgr: I/O rate limit         95
    1     resmgr: I/O rate limit        215

set linesize 400
col PDB_NAME for a10
col BEGIN_TIME for a30
col END_TIME for a30
SELECTR.SNAP_ID,
R.CON_ID,
P.PDB_NAME,
TO_CHAR(R.BEGIN_TIME, 'YYYY-MM-DHH24:MI') AS BEGIN_TIME,
TO_CHAR(END_TIME, 'YYYY-MM-D HH24:MI')AS 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
ORDER BY R.BEGIN_TIME;

ALTER SYSTEM SET MAX_IOPS = 1000 SCOPE = BOTH;

ALTER SYSTEM SET MAX_MBPS = 50 SCOPE = BOTH;

When the resmgr: I/O rate limit wait event occurs in the database, you can query the sql affected by the current IO overload through the following view. The p1 value indicates the current pdb_id. The occurrence of this wait event requires a reasonable adjustment of the max_iops and max_mbps values ​​to avoid impact To critical business.

SELECT H.EVENT,

  H.P1 AS PDB_ID,

  C.PDB_NAME,

  H.SQL_ID,

  TO_CHAR(H.SAMPLE_TIME, 'YYYY-MM-D HH24:MI') AS SAMPLE_TIME,

  H.INSTANCE_NUMBER

FROMDBA_HIST_ACTIVE_SESS_HISTORY H

JOINCDB_PDBS C

ONC.PDB_ID = H.P1

ANDH.EVENT = 'resmgr: I/O rate limit'

ORDER BY 4;

2) Memory Management for PDBs:-
*********************************

Optionally starting from Oracle 12.2 it is possible to limit
the PDB memory utilization using the following parameters:
 
SGA_Target             – 
Max SGA size for PDB.
SGA_Min_Size           – 
Amount of guaranteed SGA size for PDB.
DB_Cache_Size          – 
Amount of guaranteed Buffer Cache size for PDB.
DB_Shared_Pool_Size    – 
Amount of guaranteed Shared Pool size for PDB.
PGA_Aggregate_Limit    – 
Max PGA size for PDB.
PGA_Aggregate_Target   – 
Target PGA size for PDB.
 
Requirements:
=> Parameter NONCDB_COMPATIBLE=FALSE in CDB$ROOT
=> MEMORY_TARGET not set in CDB$ROOT
 
Restrictions
for SGA related parameters (if SGA_TARGET=0) 
=> Sum of all values for SGA (DB_CACHE_SIZE,
SGA_MIN_SIZE, SHARED_POOL_SIZE) for all PDBS must no be higher than 50% of the
corresponding value for CDB$ROOT
 
Restrictions
(PGA)
=> PGA_AGGREGATE_LIMIT: not more than PGA_AGGREGATE_LIMIT
in CDB$ROOT
=> PGA_AGGREGATE_TARGET: max 50% of PGA_AGGREGATE_LIMIT
 
 
PDB Memory Management:
SGA can be divided between pluggable databases
Parameters that now set minimum sizes within a PDB:
1) DB_CACHE_SIZE
2) SHARED_POOL_SIZE
3) SGA_MIN_SIZE (Sum of SGA_MIN_SIZE for all PDBs should be
<50% of SGA)
 
SGA/PGA size for a PDB can also be capped
1) SGA_TARGET
2) PGA_AGGREGATE_LIMIT
 
*settings not compatible with Automatic Memory Management
(MEMORY_TARGET)
 
PDB
Memory Management:
SGA can be divided between pluggable databases
SQL>
@pdb_parms_memory.sql          
PDB         Parameter                 VALUE
----------
-------------------    -------------
PDB1      db_cache_size           209715200
PDB1      sga_target              1073741824
PDB1      shared_pool_size        0
PDB2      db_cache_size           1073741824
PDB2      sga_target              4294967296
PDB2      shared_pool_size        0
PDB$SEED  db_cache_size           0
PDB$SEED  sga_target              0
PDB$SEED  shared_pool_size        0

3) CPU resource isolation:-
**************************

In the database, different PDBs have different requirements on the use of host CPU resources, so we can use CDB resource plans to manage the use of CPU resources by different pdb.

CDB Resource Plans

In the case of a large number of PDBs, it may be cumbersome to use this CDB-level resource plan to limit resources for each PDB. In the 12cR2 version, the PDB Performance profile feature was introduced to enable a group of PDBs to use the same resource plan instead of configuring each PDB. Some PDBs with the same resource requirements can create a performance profile. These PDBs can use the same performance configuration by setting the DB_PERFORMANCE_PROFILE initialization parameter.

Create CDB resource plan for PDB performance configuration

Create a CDB resource plan: woqucdb_plan. It includes PDB performance profile, gold, silver, bronze.

1) Create pending area

In CDB$ROOT

exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

This proceduremakes changes to resource manager objects.

All changes tothe plan schema must be done within a pending area. The pending area can bethought of as a "scratch" area for plan schema changes. The administratorcreates this pending area, makes changes as necessary, possibly validates thesechanges, and only when the submit is completed do these changes become active.

2) Create a CDB resource plan

The name of the plan is woqucdb_plan

BEGIN

DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN(

plan => 'woqucdb_plan',

comment => 'CDB resource plan for woqucdb');

END;

3) Create PDB Performance profile

Create PDBprofile, gold

BEGIN

DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(

plan => 'woqucdb_plan,

profile => 'gold',

shares => 3,

utilization_limit => 100,

parallel_server_limit => 100);

END;

Create PDBProfile, silver

BEGIN

DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(

plan => 'newcdb_plan',

profile => 'silver',

shares => 2,

utilization_limit => 40,

parallel_server_limit => 40);

END;

Create PDBProfile, bronze

BEGIN

DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(

plan => 'newcdb_plan',

profile => 'bronze',

shares => 1,

utilization_limit => 20,

parallel_server_limit => 20);

END;

4) Update the default PDB directive:-

BEGIN

DBMS_RESOURCE_MANAGER.UPDATE_CDB_PROFILE_DIRECTIVE(

plan=> 'woqucdb_plan',

profile=> 'gold',

new_shares=> 1,

new_utilization_limit=> 10,

new_parallel_server_limit=> 20);

END;

5) Update automatic PDB directive:-

Use UPDATE_CDB_AUTOTASK_DIRECTIVE

6) Verify the pending area:-

execDBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();

7) Submit pending area:-

execDBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

8) Enable CDB resource management:-

In CDB$ROOT

ALTERSYSTEM SET RESOURCE_MANAGER_PLAN = 'woqucdb_plan' scope=both;

9) Set performanceprofile in PDB:-

SQL> alter session set container=PDB1;

SQL> alter system set db_performance_profile='gold' scope=spfile;

SQL> alter session set container=PDB2;

SQL> alter system set db_performance_profile='silver' scope=spfile;

SQL> alter session set container=PDB3;

SQL> alter system set db_performance_profile='bronze' scope=spfile;

Restart PDB

conn/as sysdba

SQL> alter pluggable database all close immediate;

SQL> alter pluggable database all open;

You can also set and restart individually in the PDB

SQL> alter session set container=PDB4;

SQL> alter system set db_performance_profile=gold scope=spfile;

SQL> alter pluggable database close immediate;

SQL> alter pluggable database open;

10) Check the parameters of the PDB

SQL> alter session set container=CDB$ROOT;

SQL> selectinst_id, name, con_id, value, ispdb_modifiable from gv$system_parameter2 wherename = 'db_performance_profile' order by 1,2,3,4;

11) Check the PDB resource management settings

SQL> alter session set container=CDB$ROOT;

SQL> selectp.name,shares,utilization_limit,parallel_server_limit,profile from v$rsrc_planr,v$pdbs p where r.con_id = p.con_id;

We only need to set the db_performance_profile initialization parameter in the corresponding pdb to let which profile the current PDB uses.

The meaning of the share parameter in the instruction

DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE shares to specify the shared value of PDB. Multiple PDBs can share this profile.

the total number of shares is seven (3 plus 3 plus 1).
The salespdb andthe servicespdb PDBare each guaranteed 3/7th of the resources, while the hrpdb PDB isguaranteed 1/7th of the resources. However, any PDB can use more than theguaranteed amount of a resource if there is no resource contention.

PDB resource limit, we can set CDB resource plan can also set pdb initialization parameter control, both can be. When there are many PDB data, it is more recommended to use CDB resource plan in combination with pdb performance profile.

Default performance profile

We can use the show parameter command to view the current performance profile of pdb. If it is null, it is the default performance profile. In the default performance profile, the shares value is 1 by default, and the utilization_limit and parallel_server_limit are both 100, which means that there is no restriction on the CPU resources of the current pdb.

Change the default performance profile

We can use the following procedure to change the default command.


BEGIN
DBMS_RESOURCE_MANAGER. UPDATE_CDB_DEFAULT_DIRECTIVE (
PLAN =>'newcdb_plan',
NEW_COMMENT => 'CDB resourceplan for newcdb1',
NEW_SHARES => 3,
NEW_UTILIZATION_LIMIT => 100,
NEW_PARALLEL_SERVER_LIMIT => 100);
END;
/

When a PDB is unplugged from the CDB, the corresponding command is retained by default. When the PDB migrates back, it will continue to use this command. Unless manually deleted.

The above figure shows that the default pdb command share value is 1, utilization_limit is 50%, and parallel_server_limit is 50%. When pdb does not specify a command, such as marketingpdb and testingpdb, the default performance profile will be used, so marketingpdb and testingpdb each get 1 and 50% of CPU usage.

One instruction can be used by multiple PDBs, but one PDB cannot use multiple instructions.

4) PDB Lockdown Profiles:-

*********************************

In addition to IO, memory, CPU, there are some restrictions, such as: limit the operation command in pdb, we can create a lockdown profile to limit the operation of the current PDB, enhance the security of certain operations.

About PDB Lockdown Profiles

The following is a simple test to see the basic function of this feature. First create a profile under the CDB root. This profile will be globally available and requires:

Create PDB Lockdown Profile


SQL> connect / as sysdba
Connected.
 
SQL> CREATE LOCKDOWN PROFILE woqutech;
Lockdown Profile created.
 
SQL> ALTER LOCKDOWN PROFILE woqutechDISABLE STATEMENT  = ('ALTER SYSTEM');
Lockdown Profile altered.

Connect to PDB WXH and enable lockdown profile at the PDB level:

Make PDB Lockdown Profile effective


SQL> alter session set container = wxh;
Connected.
 
SQL> ALTER SYSTEM SET PDB_LOCKDOWN =woqutech;
System altered.

Test it, you can see that all ALTER SYSTEM operations are disabled:

LOCKDOWN PROFILE can be restricted to very fine-grained permissions. For example, the following restrictions only restrict users from performing ARCHIVE LOG and CHECKPOINT operations.


SQL> connect / as sysdba
Connected.
SQL> alter lockdown profile woqutechenable statement = ('ALTER SYSTEM') clause all except = ('ARCHIVE LOG','CHECKPOINT');
Lockdown Profile altered.

Now test it, you can see that on the PDB, the restriction takes effect precisely, and the CHECKPOINT operation is not allowed to be executed:

SQL>alter session set container = wxh;

Prohibit all alter system statements of the current PDB

ALTER LOCKDOWN PROFILE woqutech DISABLESTATEMENT  = ('ALTER SYSTEM');

Prohibit all altersystem statements except altersystem flush shared_pool.

ALTER LOCKDOWN PROFILE woqutech ENABLESTATEMENT = ('ALTER SYSTEM') clause = ('flush shared_pool');

Prohibit the use of XDB protocols (FTP, HTTP, HTTPS) in PDB

ALTER LOCKDOWN PROFILE woqutech DISABLEFEATURE = ('XDB_PROTOCOLS')

In addition to specific permissions, you can also restrict certain database features, such as calling and executing UTL_HTTP and UTL_TCP packets may be high-risk, then the following PROFILE settings can disable these features:


SQL> alter lockdown profile woqutechdisable feature = ('UTL_HTTP', 'UTL_TCP');
Lockdown profile altered.
SQL> conn system/oracle@woqu
Connected.
SQL> declare
2    l_request   utl_http.req;
3    l_response  utl_http.resp;
4begin
5    l_request :=utl_http.begin_request('http://www.woqutech.com');
6    l_response :=utl_http.get_response(l_request);
7end;
8/
declare
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-01031: insufficient privileges
ORA-06512: at "SYS.UTL_HTTP",line 380
ORA-06512: at "SYS.UTL_HTTP",line 1127
ORA-06512: at line 5

Delete PDB Lockdown Profile

DROP LOCKDOWN_PROFILE woqutech;

ORA-AM

Disclaimer

Sunday 25 October 2020

IO, Memory, CPU for resource isolation - Oracle 12c

The meaning of the share parameter in the instruction

DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE shares to specify the shared value of PDB. Multiple PDBs can share this profile.

Default performance profile

Change the default performance profile

About PDB Lockdown Profiles

Create PDB Lockdown Profile

Make PDB Lockdown Profile effective

Delete PDB Lockdown Profile

No comments:

Post a Comment

100 Oracle DBA Interview Questions and Answers

Report Abuse

Labels