LAB – Multitenant Resource Management | DataUnique

Introduction

Oracle 12c Release 2 New Features:

Controlling Memory allocation and Disk I/O on individual PDB.

In the previous release (12.1) oracle introduced Multitenant Database, allowing better management of resources and cost reduction.

On oracle 12c release 1 there was no easy way to control the amount of disk I/O and memory usage on individual PDB. As a result a “Noise Neighbor” could use lots of Memory and I/O impacting the performance of other PDB in the same instance.

In oracle 12c release 2 we can restrict the amount of memory and disk I/O on PDB Level, as well as creating resource plans based on performance profiles, which defines the resource management for groups of PDBs.

Requirements

Oracle 12c release 2 onward.

Container Database (Multitenant).

PDB Memory Parameters

The following parameters can be set at the PDB level.

DB_CACHE_SIZE: The minimum buffer cache size for the PDB.
SHARED_POOL_SIZE: The minimum shared pool size for the PDB.
PGA_AGGREGATE_LIMIT: The maximum PGA size for the PDB.
PGA_AGGREGATE_TARGET: The target PGA size for the PDB.
SGA_MIN_SIZE: The minimum SGA size for the PDB.
SGA_TARGET: The maximum SGA size for the PDB.

These initialization parameters control the memory usage of PDBs only if the following conditions are met:

The NONCDB_COMPATIBLE initialization parameter is set to false in the CDB root.
The MEMORY_TARGET initialization parameter is not set or is set to 0 (zero) in the CDB root.

SQL> show parameter target
 
PARAMETER_NAME                                    TYPE         VALUE
------------------------------------------------------------ ----------- ------------------------------------------
memory_max_target                                 big integer 0
memory_target                                     big integer 0
pga_aggregate_target                              big integer 200M
sga_target                                        big integer 800M
SQL> show parameter NONCDB_COMPATIBLE
 
PARAMETER_NAME                                    TYPE          VALUE
------------------------------------------------------------ ----------- ------------------------------------------
noncdb_compatible                                 boolean       FALSE

Set PDB as current Container

SQL> alter session set container = WINTPDB;

Set SGA_TARGET for the current PDB

SQL> alter system set sga_target=300M scope=both;
 
Sistema alterado.
 
SQL> show parameter sga_target
 
PARAMETER_NAME                                   TYPE           VALUE
------------------------------------------------------------ ----------- -----------------------------
sga_target                                       big integer 300M

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)

SQL> show parameter max_iops
 
PARAMETER_NAME                                  TYPE           VALUE
------------------------------------------------------------ ----------- -------------------------------
max_iops                                        integer 0

Set PDB as current Container

SQL> alter session set container = WINTPDB;

Set MAX_IOPS for the current PDB

SQL> alter system set max_iops=100 scope=both;
 
Sistema alterado.
 
SQL> show parameter max_iops
 
PARAMETER_NAME                                  TYPE            VALUE
------------------------------------------------------------ ----------- -----------------------------
max_iops                                        integer 100

Monitoring Resources

The following views can be used to monitor PDB Resources (CPU, I/O, Parallel Execution, Memory).

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.

Monitor I/O and Memory Usage for PDBs (Last 1 minute samples)

COLUMN pdb_name FORMAT A10
COLUMN begin_time FORMAT A21
COLUMN end_time FORMAT A21
SELECT r.con_id,
       p.pdb_name,
       r.begin_time,
       r.end_time,
       round(r.iops ,3) iops,
       round(r.iombps ,3) iombps,
       round(r.iops_throttle_exempt ,3) iops_throttle_exempt,
       round(r.iombps_throttle_exempt ,3) iombps_throttle_exempt,
       r.avg_io_throttle,
       r.sga_bytes,
       r.pga_bytes,
       r.buffer_cache_bytes,
       r.shared_pool_bytes
FROM  v$rsrcpdbmetric r,
      cdb_pdbs p
WHERE r.con_id = p.con_id
ORDER BY p.pdb_name;

DB Performance Profiles

In Oracle 12c Release 2 is possible to create a resource plan based on performance profiles which defines the resource management for groups of PDBs.

Each profile directive is made up of the following elements:

profile : The profile the directive relates to.
shares : The proportion of the CDB resources available to the PDB.
utilization_limit : The percentage of the CDBs available CPU that is available to the PDB.
parallel_server_limit : The percentage of the CDBs available parallel servers (PARALLEL_SERVERS_TARGET initialization parameter) that are available to the PDB.

Creating CDB Resource Plan:

DECLARE
l_plan VARCHAR2(30) := 'CDB_PLAN_TESTE';
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;
 
DBMS_RESOURCE_MANAGER.create_cdb_plan(
plan => l_plan,
comment => 'Profile Plan para Testes');
 
DBMS_RESOURCE_MANAGER.create_cdb_profile_directive(
plan     => l_plan,
profile => 'gold',
shares => 3,
memory_min => 50);
 
DBMS_RESOURCE_MANAGER.create_cdb_profile_directive(
plan                    => l_plan,
profile                 => 'silver',
shares                  => 2,
utilization_limit       => 40,
memory_limit            => 25);
 
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/

Information about the available CDB resource plans can be queried using the DBA_CDB_RSRC_PLANS view.

col comments format a30
col plan format a30
col profile format a30
SELECT PLAN, PROFILE, SHARES, UTILIZATION_LIMIT, PARALLEL_SERVER_LIMIT, MEMORY_MIN, MEMORY_LIMIT, COMMENTS
FROM DBA_CDB_RSRC_PLAN_DIRECTIVES
WHERE PLAN = 'CDB_PLAN_TESTE';

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

SHOW PARAMETER RESOURCE_MANAGER_PLAN
 
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'CDB_PLAN_TESTE';

Setting performance profile on PDB

ALTER SESSION SET CONTAINER=WINTPDB;
 
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'CDB_PLAN_TESTE';
ALTER SYSTEM SET DB_PERFORMANCE_PROFILE=gold SCOPE=SPFILE;
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE OPEN;
 
SHOW PARAMETER DB_PERFORMANCE_PROFILE;

References:

https://oracle-base.com/articles/12c/multitenant-disk-iops-mdps-resource-management-for-pdbs-12cr2
https://oracle-base.com/articles/12c/multitenant-memory-resource-management-for-pdbs-12cr2
https://oracle-base.com/articles/12c/multitenant-resource-manager-pdb-performance-profiles-12cr2
https://www.linkedin.com/pulse/oracle-12c-release-2-new-feature-controlling-memory-gavin-soorma/