OCP 12C – Resource Manager and Performance Enhancements

Cyrille Modiano
Latest posts by Cyrille Modiano (see all)

Use Resource Manager for a CDB and a PDB

Managing Resources between PDBs

  • The Resource Manager uses Shares ans Utilization limit to manage resources allocated to PDBs.
  • The more “Shares” you allocate to a PDB, the more resource it will have.
  • Shares are allocated through DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE.
  • One directive can only concern one PDB and you can’t have multiple directive for the same PDB in the same plan.

Resource Manager

  • You can limit resource utilization of a specific PDB by using the UTILIZATION_LIMIT of the CREATE_CDB_PLAN_DIRECTIVE procedure. UTILIZATION_LIMIT is expressed in percentage of total system resources, if you set it to 50 it, the PDB will be able to use 50% of total system resources (CPU, I/O, parallel server).
  • The PARALLEL_SERVER_LIMIT parameter let you limit the parallel server utilization for a PDB.
  • If you don’t define a plan for a PDB, the default one applies, by default a PDB is being allocated :
    • 1 Share
    • No Utilization Limit
    • No Parallel Server Utilization limit

Create a CDB Resource Plan

Here is the step by step to create a CDB resource plan:

  1. Create a pending area:
    exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
    
  2. Create the directive for the PDB
    BEGIN
      DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
        plan                  => 'newcdb_plan', 
        pluggable_database    => 'operpdb', 
        shares                => 1, 
        utilization_limit     => 20,
        parallel_server_limit => 30);
    END;
    /
    
  3. Validate the pending area:
    exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
    
  4. Submit the pending area:
    exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
  •  This plan will limit total  system resource utilization for operpdb to 20% and this same PDB can use 30% of total PARALLEL_SERVERS_TARGET . The shares is set to default value (1).
  • To activate a plan:
SQL> alter system set resource_manager_plan='my_plan';

 Create a PDB Resource Plan

  • It is also possible to create a Resource Manager plan at the PDB level.
  • A PDB Resource Plan let you define how the portion of system resource sallocated by the CDB plan to your PDB will be used within this PDB.

Resource Manager

  • There are rules to create a Resource Manager plan in a PDB:
    • You can’t create a multi-level plan.
    • You are limited yo 8 consumer groups (the limit is 32 for non-cdb).
    • You can’t create subplans in a PDB plan.
  • The method to create and manage a PDB resource plan is the same than a CDB, you just have to connect to the PDB before creating the plan.
  • You can see all Resource Manager plan created for CDB and PDB by querying the V$RSRC_PLAN view

 Multiprocess Multithreaded Architecture

  • Oracle 12c intoduces a new background processes architecture, some of the usual background processes will now run as threads within an OS process.
  • This architecture change will (according to oracle) reduce memory and cpu usage and increase performance for parallel executions.
  • To activate the new Multiprocess Multithreaded architecture you need to enable it explicitly, it is disabled by default :
SQL> alter system set threaded_execution=true scope=spfile;
  •  When the “thread mode” is enable, you can’t use OS authentication, you need to use password authentication.
  • To see which process run as thread and which one is a real os process you can query the v$process view. It contains a new column STID. If the STID is equal to the SPID, the process is a real OS process, if the STID is different from the SPID then it is a thread an the SPID indicates to which OS process the thread depends.
SELECT SPID, STID, PROGRAM FROM V$PROCESS ORDER BY SPID;

SPID   STID   PROGRAM
-----  -----  ---------------------------
7190   7190   oracle@samplehost (PMON) 
7192   7192   oracle@samplehost (PSP0) 
7194   7194   oracle@samplehost (VKTM) 
7198   7198   oracle@samplehost (SCMN) 
7198   7200   oracle@samplehost (GEN0) 
7202   7202   oracle@samplehost (SCMN) 
7202   7204   oracle@samplehost (DIAG) 
7198   7205   oracle@samplehost (DBRM) 
7202   7206   oracle@samplehost (DIA0)

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.