Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Oracle Database 12.2 New Feature – Pluggable Database Performance Profiles

  • Posted by Gavin Soorma
  • On April 11, 2017
  • 1 Comments
  • 12.2, 12c new feature, 12c Release 2, multitenant, performance profile, pluggable database

In the earlier 12.1.0.2 Oracle database version, we could limit the amount of CPU utilization as well as Parallel Server allocation at the PDB level via Resource Plans.

Now in 12c Release 2, we can not only regulate CPU and Parallelism at the Pluggable database level, but in addition we can also restrict the amount of memory that each PDB hosted by a Container Database (CDB) uses.

Further, we can also limit the amount of I/O operations that each PDB performs so that now we have a far improved Resource Manager at work ensuring that no PDB hogs all the CPU or the IO because of maybe some runaway query and thereby impacts the other PDBs hosted in the same PDB.

We can now limit the amount of SGA or PGA that an individual PDB can utilize as well as ensure that certain PDBs always are ensured a minimum level of both available SGA and PGA memory.

For example we can now issue SQL statements like these while connected to the individual PDB.

 

SQL> ALTER SYSTEM SET SGA_TARGET = 500M SCOPE = BOTH;

SQL> ALTER SYSTEM SET SGA_MIN_SIZE = 300M SCOPE = BOTH;

SQL> ALTER SYSTEM SET PGA_AGGREGATE_LIMIT = 200M SCOPE = BOTH;

SQL> ALTER SYSTEM SET MAX_IOPS = 10000 SCOPE = BOTH;

 
Another 12c Release 2 New Feature related to Multitenancy is Performance Profiles.

With Performance Profiles we can manage resources for large numbers of PDBs by specifying Resource Manager directives for profiles instead for each individual PDB.

These profiles are then allocated to the PDB via the initialization parameter DB_PERFORMANCE_PROFILE

Let us look at a worked example of Performance Profiles.

In this example we have three PDBs (PDB1, PDB2 and PDB3) hosted in the container database CDB1. PDB1 pluggable database hosts some mission critical applications and we need to ensure that PDB1 gets a higher share of memory,I/O as well as CPU resources as compared to PDB2 and PDB3.

So we will be enforcing this resource allocation via two sets of Performance Profiles – we call those TIER1 and TIER2.

Here are the steps:

 

Create a Pending Area

 

SQL> exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA ();

PL/SQL procedure successfully completed.

 

 

Create a CDB Resource Plan
 

SQL> BEGIN

 DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN(

   plan   => 'profile_plan',

   comment => 'Performance Profile Plan allocating highest share of resources to PDB1');

END;

/ 

PL/SQL procedure successfully completed.

 
Create the CDB resource plan directives for the PDBs

Tier 1 performance profile ensures at least 60% (3 shares) of available CPU and parallel server resources and no upper limit on CPU utilization or parallel server execution. In addition it ensures a minimum allocation of at least 50% of available memory.

 

SQL> BEGIN

DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(

plan                 => 'profile_plan',

profile              => 'Tier1',

shares               => 3,

memory_min           => 50);

END;

/

PL/SQL procedure successfully completed.

 

Tier 2 performance profile is more restrictive in the sense that it has fewer shares as compared to Tier 1 and limits the amount of CPU/Parallel server usage to 40% as well as limits the amount of memory usage at the PDB level to a maximum of 25% of available memory.

 

SQL> BEGIN

 DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(

   plan                 => 'profile_plan',

   profile              => 'Tier2',

   shares               => 2,

   utilization_limit    => 40,

   memory_limit          => 25);

END;

/   

PL/SQL procedure successfully completed.

 

Validate and Submit the Pending Area

 

SQL> exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();

PL/SQL procedure successfully completed.

SQL> exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

PL/SQL procedure successfully completed.


 
Allocate Performance Profiles to PDBs

 

TIER1 Performance Profile is allocated to PDB1 and TIER2 Performance Profile is allocated to PDB2 and PDB3.

 

SQL> alter session set container=pdb1;

Session altered.

SQL> alter system set DB_PERFORMANCE_PROFILE='TIER1' scope=spfile;

System altered.

SQL> alter session set container=pdb2;

Session altered.

SQL> alter system set DB_PERFORMANCE_PROFILE='TIER2' scope=spfile;

System altered.

SQL> alter session set container=pdb3;

Session altered.

SQL> alter system set DB_PERFORMANCE_PROFILE='TIER2' scope=spfile;

System altered.

 

Set the Resource Plan at the CDB level

 

SQL> conn / as sysdba

Connected.

SQL> alter system set resource_manager_plan='PROFILE_PLAN' scope=both;

System altered.

 

Set the Performance Profiles at the PDB level

 

SQL> alter pluggable database all close immediate;

Pluggable database altered.

SQL> alter pluggable database all open;

Pluggable database altered.


 
Monitor memory utilization at PDB level

 

The V$RSRCPDBMETRIC view enables us to track the amount memory used by PDBs.

We can see that the PDB1 belonging to the profile TIER1 has almost double the memory allocated to the other two PDBs in profile TIER2.

Oracle 12.2 has a lot of new exciting features. Learn all about these at a forthcoming online training session. Contact prosolutions@gavinsoorma.com to register interest!

 

1 Comments

Foued
  • Apr 18 2017
Thanks for this post. I would be nice if the blog contained the option to follow new posts by email. Foued

Leave Reply

Your email address will not be published. Required fields are marked *