Knowledge is Power – share IT



Part One: Creating an Oracle 12c Container Database (CDB) Resource PlanIn a CDB since we have multiple pluggable databases sharing a set of common resources, we can prevent multiple workloads to compete with each other for both system as well as CDB resources by using Resource Manager.Let us look at an example of managing resources for Pluggable Databases (between PDB’s) at the multitenant Container database level.The same can be achieved using 12c Cloud Control, but displayed here are the steps to be performed at the command line using the DBMS_RESOURCE_MANAGER package.With Resource Manager at the Pluggable Database level, we can limit CPU usage of a particular PDB as well as the number of parallel execution servers which a particular PDB can use.To allocate resources among PDB’s we use a concept of shares where we assign shares to particular PDB’s and a higher share to a PDB results in higher allocation of guaranteed resources to that PDB.At a high level the steps involved include:Create a Pending Area Create a CDB resource plan Create directives for the PDB’s Optionally update the default directives which will specify resources which any newly created PDB’s will be allocated or which will be used when no directives have been explicitly defined for a particular PDBOptionally update the directives which apply by default to the Automatic Maintenance Tasks which are configured to run in the out of the box maintenance windowsValidate the Pending Area Submit the Pending Area Enable the plan at the CDB level by setting the RESOURCE_MANAGER_PLAN parameterLet us look at an example.We have 5 Pluggable databases contained in the Container database and we wish to enable resource management at the PDB level.We wish to guarantee CPU allocation in the ratio 4:3:1:1:1 so that the CPU is distributed among the PDB’s in this manner:PDBPROD1 : 40%PDBPROD2: 30%PDBPROD3: 10%PDBPROD4 : 10%PDBPROD5: 10%Further for PDB’s PDBPROD3, PDBPROD4 and PDBPROD5 we wish to ensure that CPU utilization for these 3 PDB’s never crosses the 70% limit.Also for these 3 PDB’s we would like to limit the maximum number of parallel execution servers available to the PDB.The value of 70% means that if the PARALLEL_SERVERS_TARGET initialization parameter is 200, then the PDB cannot use more than a maximum of 140 parallel execution servers. For PDBPROD1 and PDBPROD2 there is no limit, so they can use all 200 parallel execution servers if available.We also want to limit the resources used by the Automatic Maintenance Tasks jobs when they do execute in a particular job window and also want to specify a default resource allocation limit for newly created PDB’s or those PDB’s where a resource limit directive has not been explicitly defined.Create the Pending Area SQL SYS@PRODCDB> exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();PL/SQL procedure successfully completed.Create the CDB resource plan SQL SYS@PRODCDB> BEGIN DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN( plan => 'PRODCDB_PLAN', comment => 'CDB resource plan for PRODCDB');END;/ 2 3 4 5 6PL/SQL procedure successfully completed.Create directives which will specify how resources are allocated to each particular PDBSQL SYS@PRODCDB> BEGIN DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE( plan => 'PRODCDB_PLAN', pluggable_database => 'PDBPROD1', shares => 4, utilization_limit => 100, parallel_server_limit => 100);END;/ 2 3 4 5 6 7 8 9PL/SQL procedure successfully completed.SQL SYS@PRODCDB> BEGIN DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE( plan => 'PRODCDB_PLAN', pluggable_database => 'PDBPROD2', shares => 3, utilization_limit => 100, parallel_server_limit => 100);END;/ 2 3 4 5 6 7 8 9PL/SQL procedure successfully completed.SQL SYS@PRODCDB> BEGIN DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE( plan => 'PRODCDB_PLAN', pluggable_database => 'PDBPROD3', shares => 2, utilization_limit => 70, parallel_server_limit => 70);END;/ 2 3 4 5 6 7 8 9PL/SQL procedure successfully completed.SQL SYS@PRODCDB> BEGIN DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE( 2 3 plan => 'PRODCDB_PLAN', 4 pluggable_database => 'PDBPROD4', 5 shares => 1, 6 utilization_limit => 70, 7 parallel_server_limit => 70); 8 END; 9 /SQL SYS@PRODCDB> SQL SYS@PRODCDB> SQL SYS@PRODCDB> 2 3 plan => 'PRODCDB_PLAN', 4 pluggable_database => 'PDBPROD5', 5 shares => 1, 6 utilization_limit => 70, 7 parallel_server_limit => 70); 8 END; 9 /PL/SQL procedure successfully completed.Update (if required) default directive applies to PDBs for which specific directives have not been definedSQL SYS@PRODCDB> BEGIN DBMS_RESOURCE_MANAGER.UPDATE_CDB_DEFAULT_DIRECTIVE( plan => 'PRODCDB_PLAN', new_shares => 1, new_utilization_limit => 50, new_parallel_server_limit => 50);END;/ 2 3 4 5 6 7 8PL/SQL procedure successfully completed.Update the directive (if required) which will limit the resources available to run the Automatic Maintenance Tasks SQL SYS@PRODCDB> BEGIN 2 DBMS_RESOURCE_MANAGER.UPDATE_CDB_AUTOTASK_DIRECTIVE( 3 plan => 'PRODCDB_PLAN', 4 new_shares => 1, 5 new_utilization_limit => 75, 6 new_parallel_server_limit => 75); 7 END; 8 /PL/SQL procedure successfully completed.Validate the Pending Area SQL exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();PL/SQL procedure successfully completed.Submit the Pending Area SQL exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();PL/SQL procedure successfully completed.Enable the CDB resource plan SQL ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'PRODCDB_PLAN';System altered.If we launch Database Express (or OEM for that matter) we can now see the resource allocation to each of the PDB’s as well as the resource utilization limit which has been imposed on some of the PDB’s.We can also query the DBA_CDB_RSRC_PLAN_DIRECTIVES view to obtain the same information in some more detailSQL SYS@PRODCDB> SQL SYS@PRODCDB> SQL SYS@PRODCDB> SQL SYS@PRODCDB> SELECT PLAN, 2 PLUGGABLE_DATABASE, 3 SHARES, 4 UTILIZATION_LIMIT, 5 PARALLEL_SERVER_LIMIT 6 FROM DBA_CDB_RSRC_PLAN_DIRECTIVES 7 ORDER BY PLAN; Parallel Pluggable Utilization ServerPlan Database Shares Limit Limit-------------------------- ------------------------- ------ ----------- --------DEFAULT_CDB_PLAN ORA$AUTOTASK 90 100DEFAULT_CDB_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1 100 100DEFAULT_MAINTENANCE_PLAN ORA$AUTOTASK 90 100DEFAULT_MAINTENANCE_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1 100 100 Parallel Pluggable Utilization ServerPlan Database Shares Limit Limit-------------------------- ------------------------- ------ ----------- --------ORA$INTERNAL_CDB_PLAN ORA$DEFAULT_PDB_DIRECTIVEORA$INTERNAL_CDB_PLAN ORA$AUTOTASKORA$QOS_CDB_PLAN ORA$AUTOTASK 90 100ORA$QOS_CDB_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1 100 100PRODCDB_PLAN PDBPROD3 2 70 70PRODCDB_PLAN PDBPROD5 1 70 70PRODCDB_PLAN PDBPROD2 3 100 100PRODCDB_PLAN PDBPROD1 4 100 100PRODCDB_PLAN PDBPROD4 1 70 70 Parallel Pluggable Utilization ServerPlan Database Shares Limit Limit-------------------------- ------------------------- ------ ----------- --------PRODCDB_PLAN ORA$AUTOTASK 1 75 75PRODCDB_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1 50 5020 rows selected.exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();BEGIN DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN( plan => 'PRODCDB_PLAN', comment => 'CDB resource plan for PRODCDB');END;/BEGIN DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE( plan => 'PRODCDB_PLAN', pluggable_database => 'PDBPROD1', shares => 4, utilization_limit => 100, parallel_server_limit => 100);END;/BEGIN DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE( plan => 'PRODCDB_PLAN', pluggable_database => 'PDBPROD2', shares => 3, utilization_limit => 100, parallel_server_limit => 100);END;/BEGIN DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE( plan => 'PRODCDB_PLAN', pluggable_database => 'PDBPROD3', shares => 1, utilization_limit => 70, parallel_server_limit => 70);END;/BEGIN DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE( plan => 'PRODCDB_PLAN', pluggable_database => 'PDBPROD4', shares => 1, utilization_limit => 70, parallel_server_limit => 70);END;/BEGIN DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE( plan => 'PRODCDB_PLAN', pluggable_database => 'PDBPROD5', shares => 1, utilization_limit => 70, parallel_server_limit => 70);END;/BEGIN DBMS_RESOURCE_MANAGER.UPDATE_CDB_DEFAULT_DIRECTIVE( plan => 'PRODCDB_PLAN', new_shares => 1, new_utilization_limit => 50, new_parallel_server_limit => 50);END;/BEGIN DBMS_RESOURCE_MANAGER.UPDATE_CDB_AUTOTASK_DIRECTIVE( plan => 'PRODCDB_PLAN', new_shares => 1, new_utilization_limit => 75, new_parallel_server_limit => 75);END;/exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'PRODCDB_PLAN';Part Two: Creating an Oracle 12c Pluggable Database (PDB) Resource PlanWe have seen how a CDB resource plan allocates resources among the various PDB’s in the container database.The PDB resource plan will now allocate resources within the database based on Consumer Groups – the method is in fact the same as what we used for Non-CDB’s in earlier versions of the Oracle database.Our requirement is to ensure certain Batch users do not hog the system resources when batch jobs execute and we would like to enable a PDB resource plan which will impose limits on the amount of Undo the statement can use as well as the amount of time the statement is allowed to run and also the amount of physical I/O resources it can use.At a high level the steps involved are:Connect to the appropriate PDB where we need to create the PDB Resource PlanCreate the Pending Area Create the Consumer Groups Map sessions to the Consumer GroupsCreate the PDB Resource PlanCreate PDB resource plan directivesValidate the Pending AreaSubmit the Pending Area Enable the PDB Resource Plan Connect to the appropriate PDBSQL SYS@PRODCDB> alter session set container=pdbprod4;Session altered.Create the Pending AreaSQL SYS@PRODCDB> BEGINDBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();END;/ 2 3 4PL/SQL procedure successfully completed.Create the PDB Resource Plan SQL SYS@PRODCDB> BEGINDBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'pdbprod4_plan',COMMENT => 'Resource plan at the PDB level');END;/ 2 3 4 5PL/SQL procedure successfully completed.Create the Consumer GroupsSQL SYS@PRODCDB> BEGINDBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'batch_user_group',COMMENT => 'Resource consumer group for batch users');END;/ 2 3 4 5PL/SQL procedure successfully completed.Create the directives which will limit resources allocated to a particular Consumer Group Note – 20 MB of UNDO usage is a very small value – only used to illustrate the example!SQL SYS@PRODCDB> BEGIN 2 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'pdbprod4_plan', 3 GROUP_OR_SUBPLAN => 'batch_user_group', 4 COMMENT=>'Cancel SQL statement after exceeding either 20 MB of Undo, elapsed time of 3 minutes or limit i/o to 10 MB', 5 UNDO_POOL => 20000, 6 SWITCH_ELAPSED_TIME => 180, 7 SWITCH_IO_MEGABYTES => 10, 8 SWITCH_GROUP=>'CANCEL_SQL', 9 SWITCH_FOR_CALL=> TRUE); 10 END; 11 /PL/SQL procedure successfully completed.Here are some of the other common directives we can set (taken from the official documentation)MAX_IDLE_TIME – maxiumum session idle time in secondsMAX_EST_EXEC_TIME - maximum execution time (in CPU seconds) allowed for a call as determined by the CBOSWITCH_IO_REQS - number of physical I/O requests that a session can execute before an action is takenPARALLEL_SERVER_LIMIT - maximum percentage of the parallel execution server pool that a particular consumer group can useSWITCH_IO_LOGICAL - Number of logical I/O requests that will trigger the action specified by SWITCH_GROUPACTIVE_SESS_POOL_P1 - Specifies the maximum number of concurrently active sessions for a consumer group.MGMT_P1 - For a plan with the MGMT_MTH parameter set to EMPHASIS, specifies the CPU percentage to allocate at the first levelMGMT_P2 - For a plan with the MGMT_MTH parameter set to EMPHASIS, specifies the CPU percentage to allocate at the second levelCreate a directive for the mandatory Consumer Group OTHER_GROUPS. This will apply to all users not mapped to the Consumer Group BATCH_USER_GROUPSQL SYS@PRODCDB> BEGINDBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'pdbprod4_plan', 2 3 GROUP_OR_SUBPLAN => 'OTHER_GROUPS', 4 COMMENT => 'Mandatory Group covering all users not in batch group'); 5 END; 6 /Map the user SH to the Consumer Group BATCH_USER_GROUP and set the initial Consumer Group for the user SHSQL SYS@PRODCDB> BEGINDBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(ATTRIBUTE => DBMS_RESOURCE_MANAGER.ORACLE_USER,VALUE => 'SH',CONSUMER_GROUP => 'batch_user_group');END;/ 2 3 4 5 6 7PL/SQL procedure successfully completed.SQL SYS@PRODCDB> BEGINDBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ( 'SH','batch_user_group',FALSE);END;/ 2 3 4PL/SQL procedure successfully completed.SQL SYS@PRODCDB> BEGINDBMS_RESOURCE_MANAGER.set_initial_consumer_group('SH', 'batch_user_group');END;/ 2 3 4PL/SQL procedure successfully completed.Validate the Pending Area SQL SYS@PRODCDB> BEGIN DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();END;/PL/SQL procedure successfully completedSubmit the Pending Area BEGIN DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();END;/PL/SQL procedure successfully completed.Enable the PDB Resource Plan SQL SYS@PRODCDB> alter system set resource_manager_plan='pdbprod4_plan';System altered.SQL SYS@PRODCDB> show parameter resource_manager_planNAME TYPE VALUE------------------------------------ ----------- ------------------------------resource_manager_plan string pdbprod4_planLet us now test the PDB resource plan and see if the directive to limit the use of Undo to 20 MB is enforced.Initially we can see that the user SH which belongs to the BATCH_USER_GROUP Consumer Group is not using any Undo.SQL SYS@PRODCDB> alter system set resource_manager_plan='pdbprod4_plan';System altered.SQL SYS@PRODCDB> show parameter resource_manager_planNAME TYPE VALUE------------------------------------ ----------- ------------------------------resource_manager_plan string pdbprod4_planFrom another session let us connect as the user SH and execute an UPDATE statement which will cause the amount of Undo usage to exceed the 20 MB limit.SQL SH@pdbprod4> update sh.sales set amount_sold=1;Back to the original session we can now Undo usage is increasing – and then we see it has gone back to 0This is because the session was cancelled as the resource limit of 20 MB Undo usage was exceededSQL SYS@PRODCDB> / SESS_ID CONSUMER_GROUP CURRENT_UNDO_CONSUMPTION MAX_UNDO_CONSUMPTION---------- -------------------------------- ------------------------ -------------------- 43 BATCH_USER_GROUP 6342144 0 74 OTHER_GROUPS 0 0SQL SYS@PRODCDB> / SESS_ID CONSUMER_GROUP CURRENT_UNDO_CONSUMPTION MAX_UNDO_CONSUMPTION---------- -------------------------------- ------------------------ -------------------- 43 BATCH_USER_GROUP 11872540 0 74 OTHER_GROUPS 0 0SQL SYS@PRODCDB> / SESS_ID CONSUMER_GROUP CURRENT_UNDO_CONSUMPTION MAX_UNDO_CONSUMPTION---------- -------------------------------- ------------------------ -------------------- 43 BATCH_USER_GROUP 11872540 0 74 OTHER_GROUPS 0 0SQL SYS@PRODCDB> / SESS_ID CONSUMER_GROUP CURRENT_UNDO_CONSUMPTION MAX_UNDO_CONSUMPTION---------- -------------------------------- ------------------------ -------------------- 43 BATCH_USER_GROUP 11872540 0 74 OTHER_GROUPS 0 0SQL SYS@PRODCDB> / SESS_ID CONSUMER_GROUP CURRENT_UNDO_CONSUMPTION MAX_UNDO_CONSUMPTION---------- -------------------------------- ------------------------ -------------------- 43 BATCH_USER_GROUP 0 0 74 OTHER_GROUPS 0 0SQL SH@pdbprod4> update sh.sales set amount_sold=1;update sh.sales set amount_sold=1 *ERROR at line 1:ORA-56720: I/O data limit exceeded - call abortedIf we limit the usage of Undo now by including a WHERE clause in the UPDATE statement, we can see that the Undo usage remains below the 20 MB imposed resource limit and the statement executes successfully this time.SQL SH@pdbprod4> update sh.sales set amount_sold=1 where rownum < 50001;50000 rows updated.SQL SYS@PRODCDB> / SESS_ID CONSUMER_GROUP CURRENT_UNDO_CONSUMPTION MAX_UNDO_CONSUMPTION---------- -------------------------------- ------------------------ -------------------- 43 BATCH_USER_GROUP 9565964 0 74 OTHER_GROUPS 0 0SQL SYS@PRODCDB> / SESS_ID CONSUMER_GROUP CURRENT_UNDO_CONSUMPTION MAX_UNDO_CONSUMPTION---------- -------------------------------- ------------------------ -------------------- 43 BATCH_USER_GROUP 10830200 0 74 OTHER_GROUPS 0 0SQL SYS@PRODCDB> / SESS_ID CONSUMER_GROUP CURRENT_UNDO_CONSUMPTION MAX_UNDO_CONSUMPTION---------- -------------------------------- ------------------------ -------------------- 43 BATCH_USER_GROUP 12400376 0 74 OTHER_GROUPS 0 0SQL SYS@PRODCDB> / SESS_ID CONSUMER_GROUP CURRENT_UNDO_CONSUMPTION MAX_UNDO_CONSUMPTION---------- -------------------------------- ------------------------ -------------------- 43 BATCH_USER_GROUP 12400376 0 74 OTHER_GROUPS 0 0 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download