转 Oracle 12c: Managing Resources

http://www.oracle-class.com/?p=3058

 

1. Introduction:

Oracle database 12c comes with several Resource management enhancements to handle resources within CDBs and PDBs. In this article we will focus on the use of Resource Manager in a CDB environment.

Prior to Oracle database 12c, we used Resource Manager to manage database workloads “fighting” for system resources such as CPU. For environments, which have multiple Oracle databases running on the same server, the use of Oracle Resource Manager to manage system resources (CPU, I/O …) used by all those databases is not possible.
However with Oracle 12c, we can have multiple workloads within multiple PDBS “fighting” for system and CDB resources. Thus the possibility to use Oracle Resource Manager to manage system resources (CPU, I/O …) used by all those databases.
In a CDB, Oracle Resource Manager can manage resources on 2 different levels:
– CDB level: Oracle Resource Manager can manage resource usage by all the PDBs within a CDB.
– PDB level: Oracle Resource Manager can manage resource usage within the PDB itself.

2. What Solutions Does Resource Manager Provide for a CDB?

By default, the whole system may have the following problems:
– Inappropriate allocation of resources among PDBs; All PDBs have same level of priority.
– Inappropriate allocation of resources within a single PDB; All workloads within a PDB have same level of priority.
– Lack of resource usage data for PDBs.
Oracle Resource Manager helps to overcome these problems.
In the following post, we will see how Oracle Resource Manager manages resources:
– Between PDBs.
– With a single PDB.

3. Managing Resources Between PDBs:

In a CDB, PDBs might have different levels of priority / importance. You can create CDB resource plans to distribute resources to different PDBs based on these priorities. PDBs may contend for CPU, I/O resources.
A CDB resource plan allocates resources to its PDBs according to its set of resource plan directives (directives). Each directive references one PDB, and no two directives for the currently active plan can reference the same PDB.
The directives control allocation of the following resources to the PDBs:
• CPU
• I/O
• Parallel execution servers

Shares for resource allocation for PDBs:

A directive can control the allocation of resources to PDBs based on the share value granted to each PDB. You can grant different shares to different PDBs so that more system resources are allocated to the PDB with the highest priority.

For example:
The following image shows each PDB has one “share” With a total of 4 shares; each PDB is guaranteed 1/4 or 25% of the CPU.

To allocate more resources to PDB4, you can assign more share values to the PDB which results in more resource allocation for the PDB4.
The following image shows PDB4 get assigned 4 shares and the other PDBs get 3, 1 and 2 shares respectively. With a total of 10 shares, PDB1, PDB2 and PDB3 are guaranteed 3/10 (30%), 1/10 (10%), 2/10 (20%) of the system resources respectively.
Because PDB4 is the most important, it has been given 4 shares; it is guaranteed 4/10 or 40% of the system resources.

Limits to restrict resource utilization for specific PDB:
A utilization limit restrains the system resource usage of a specific PDB. You can specify utilization limits for CPU and parallel execution servers. The limits are default to 100%.
Limits restrict the resource utilization; you can create a Resource Manager Plan directive using the UTILIZATION_LIMIT parameter to limit CPU for each PDB. For example, if you create a plan directive with a UTILIZATION_LIMIT parameter equal to 20% for a specific PDB, the later will get 20% the maximum of CPU usage at CDB level.
For parallel execution servers, you can override the default defined by the UTILIZATION_LIMIT by creating a plan directive that uses the PARALLEL_SERVER_LIMIT parameter. A PDB cannot use more than the value of the PARALLEL_SERVERS_TARGET initialization parameter multiplied by the value of the PARALLEL_SERVER_LIMIT parameter in the CREATE_CDB_PLAN_DIRECTIVE procedure. For example, if the PARALLEL_SERVERS_TARGET initialization parameter is set to 100 and the PARALLEL_SERVER_LIMIT parameter for a PDB is set to 20%, then utilization limit for the PDB is 20 parallel execution servers (100 X 0.20).

Creating CDB Resource Plan for PDBs using SQL*Plus:
In the following example,
– PDBBI is allocated 1 share which represents 1/3 or 33% of the CPU and queued parallel. In addition, a UTILIZATION_LIMIT of 40% of resources is set as well as 60% limit of the available parallel servers.
– PDBHR is allocated 2 shares which represents 2/3 or 66% of the CPU and queued parallel. In addition, a UTILIZATION_LIMIT of 60% of resources is set as well as 100% limit of the available parallel servers.

view plaincopy to clipboardprint?
  1. SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;  
  2.   
  3.     CON_ID       DBID NAME                           OPEN_MODE  
  4. ---------- ---------- ------------------------------ ----------  
  5.          2 4063385794 PDB$SEED                       READ ONLY  
  6.          4 1911833382 PDBBI                          READ WRITE  
  7.          5  889037338 PDBHR                          READ WRITE  
  8.   
  9. SQL>  

Connect to the root container database and do the following:
1. Create a pending area; pending area is staging areas where resource objects get created, defined, before validated and activated.
2. Create a CDB resource plan; called “everyday_plan”.
3. Create directives for the PDBs.
4. (Optional) Update the default PDB directive using the UPDATE_CDB_DEFAULT_DIRECTIVE procedure.
5. (Optional) Update the default auto task directive using the UPDATE_CDB_AUTOTASK_DIRECTIVE procedure.
6. Validate the pending area.
7. Submit the pending area.

Use CREATE_CDB_PLAN procedure to create the CDB plan. Create a CDB resource plan directives for the PDBs using the CREATE_CDB_PLAN_DIRECTIVE procedure:

view plaincopy to clipboardprint?
  1. C:\Users\sesa258020>sqlplus / as sysdba  
  2.   
  3. SQL*Plus: Release 12.1.0.1.0 Production on Sat Jul 20 16:10:30 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle.  All rights reserved.  
  6.   
  7.   
  8. Connected to:  
  9. Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production  
  10. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options  
  11.   
  12. SQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();  
  13.   
  14. PL/SQL procedure successfully completed.  
  15.   
  16. SQL>  
  17. SQL> BEGIN  
  18.   2  DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN(  
  19.   3     plan => 'everyday_plan',  
  20.   4     comment => 'CDB Resource Plan for PDBBI $ PDBHR'  
  21.   5     );  
  22.   6  END;  
  23.   7  /  
  24.   
  25. PL/SQL procedure successfully completed.  
  26.   
  27. SQL>  
  28.   
  29. SQL> BEGIN  
  30.   2  DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(  
  31.   3  plan => 'everyday_plan',  
  32.   4  pluggable_database => 'PDBBI',  
  33.   5  shares => 1,  
  34.   6  utilization_limit => 40,  
  35.   7  parallel_server_limit => 60  
  36.   8  );  
  37.   9  END;  
  38.  10  /  
  39.   
  40. PL/SQL procedure successfully completed.  
  41.   
  42. SQL>  
  43.   
  44. SQL> BEGIN  
  45.   2  DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(  
  46.   3  plan => 'everyday_plan',  
  47.   4  pluggable_database => 'PDBHR',  
  48.   5  shares => 2,  
  49.   6  utilization_limit => 60,  
  50.   7  parallel_server_limit => 100  
  51.   8  );  
  52.   9  END;  
  53.  10  /  
  54.   
  55. PL/SQL procedure successfully completed.  
  56.   
  57. SQL>  
  58.   
  59. SQL> EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();  
  60.   
  61. PL/SQL procedure successfully completed.  
  62.   
  63. SQL> EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();  
  64.   
  65. PL/SQL procedure successfully completed.  
  66.   
  67. SQL>  

Update CDB Resource Plan for PDBs using SQL*Plus:
Use the procedure UPDATE_CDB_AUTOTASK_DIRECTIVE to update CDB auto task (automated maintenance tasks).
For example:

view plaincopy to clipboardprint?
  1. SQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();  
  2.   
  3. PL/SQL procedure successfully completed.  
  4.   
  5. SQL> BEGIN  
  6.   2    DBMS_RESOURCE_MANAGER.UPDATE_CDB_AUTOTASK_DIRECTIVE(  
  7.   3      plan                  => 'everyday_plan',  
  8.   4      new_shares            => 2,  
  9.   5      new_utilization_limit => 20,  
  10.   6      new_parallel_server_limit => 75);  
  11.   7  END;  
  12.   8  /  
  13.   
  14. PL/SQL procedure successfully completed.  
  15.   
  16. SQL> EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();  
  17.   
  18. PL/SQL procedure successfully completed.  
  19.   
  20. SQL> EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();  
  21.   
  22. PL/SQL procedure successfully completed.  
  23.   
  24. SQL>  

You can create new directives for the new PDB. You can also change the default directive attribute values for PDBs by using the UPDATE_CDB_DEFAULT_DIRECTIVE procedure in the DBMS_RESOURCE_MANAGER package. New PDB will have 1 share of CPU. In addition, a UTILIZATION_LIMIT of 40% of resources is set as well as 30% limit of the available parallel servers.

view plaincopy to clipboardprint?
  1. SQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();  
  2.   
  3. PL/SQL procedure successfully completed.  
  4.   
  5. SQL> BEGIN  
  6.   2  DBMS_RESOURCE_MANAGER.UPDATE_CDB_DEFAULT_DIRECTIVE(  
  7.   3  plan => 'everyday_plan',  
  8.   4  new_shares => 1,  
  9.   5  new_utilization_limit => 40,  
  10.   6  new_parallel_server_limit => 30  
  11.   7  );  
  12.   8  END;  
  13.   9  /  
  14.   
  15. PL/SQL procedure successfully completed.  
  16.   
  17. SQL> EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();  
  18.   
  19. PL/SQL procedure successfully completed.  
  20.   
  21. SQL> EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();  
  22.   
  23. PL/SQL procedure successfully completed.  
  24.   
  25. SQL>  

You can delete CDB resource plan directives for the PDBs using the DELETE_CDB_PLAN_DIRECTIVE procedure
Example:

view plaincopy to clipboardprint?
  1. BEGIN  
  2.   DBMS_RESOURCE_MANAGER.DELETE_CDB_PLAN_DIRECTIVE(  
  3.     plan               => 'everyday_plan',   
  4.     pluggable_database => 'PDBBI');  
  5. END;  

View CDB Resource Plan for PDBs using SQL*Plus:

Query the table DBA_CDB_RSRC_PLAN_DIRECTIVES to see all of the directives defined in all the CDB resource plans in the root container.

Note: The ORA$DEFAULT_PDB_DIRECTIVE is the default directive for PDBs (You can see shares set to 1, the UTILIZATION_LIMIT set to 40% and PARALLEL_SERVER_LIMIT set to 30% like we already modified using the UPDATE_CDB_DEFAULT_DIRECTIVE procedure.)
Query the table DBA_CDB_RSRC_PLANS to see all of the resource plans defined in the root container.

Enable CDB Resource Plan for PDBs using SQL*Plus:
You can enable the Resource Manager for a CDB by setting the RESOURCE_MANAGER_PLAN parameter in the root. This parameter specifies a CDB resource plan to be active;

view plaincopy to clipboardprint?
  1. SQL> ALTER SYSTEM SET resource_manager_plan='everyday_plan';  
  2.   
  3. System altered.  
  4.   
  5. OR  
  6.   
  7. ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:everyday_plan';  

You can disable CDB resource management;

view plaincopy to clipboardprint?
  1. SQL> ALTER SYSTEM SET resource_manager_plan=’’;  
  2.   
  3. System altered.  

4. Creating a PDB resource plan:

We have seen in the previous section, a CDB resource plan determines the amount of resources allocated to each PDB within the multitenant environment. A PDB resource plan determines how the resources allocated to a specific PDB are allocated to consumer groups within that PDB. A PDB resource plan is similar to a resource plan for a non-CDB, thus similar to previous versions of Oracle database.
The following is a summary of the steps required to create a PDB resource plan:
1. In SQL*Plus, ensure that the current container is a PDB.
2. Create a pending area using the CREATE_PENDING_AREA procedure.
3. Create, modify, or delete consumer groups using the CREATE_CONSUMER_GROUP procedure.
4. Map sessions to consumer groups using the SET_CONSUMER_GROUP_MAPPING procedure.
5. Create the PDB resource plan using the CREATE_PLAN procedure.
6. Create PDB resource plan directives using the CREATE_PLAN_DIRECTIVE procedure.
7. Validate the pending area using the VALIDATE_PENDING_AREA procedure.
8. Submit the pending area using the SUBMIT_PENDING_AREA procedure.

Example: For PDBHR, according to the image above, we will use CPU ratio allocation method; the result of these directives will allocate CPU resources using 10:2:1 ratio. The OLTP group will get only 2 CPU cycles for every 10 that REPORTING group receives. We will create the following plan and plan directives; OLTP_PLAN and REPORTING_PLAN. But, before that we will create resource consumer groups. Consumer groups allow classifying end users into logical groupings based on resource consumption requirements; we create OLTP and REPORTING consumer groups. After creation of consumer groups, we can assign user sessions to it using the SET_CONSUMER_GROUP_MAPPING procedure (Obviously we need to create resource groups, plan and plan directives before issue the following commands). For example we assign the Oracle user “WISSEM” to the consumer group OLTP:

view plaincopy to clipboardprint?
  1. SQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();  
  2.   
  3. PL/SQL procedure successfully completed.  
  4.   
  5. SQL> BEGIN  
  6.   2  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING  
  7.   3  (  
  8.   4  ATTRIBUTE=> 'ORACLE_USER',  
  9.   5  VALUE=> 'WISSEM',  
  10.   6  CONSUMER_GROUP => 'OLTP'  
  11.   7  );  
  12.   8  END;  
  13.   9  /  
  14.   
  15. PL/SQL procedure successfully completed.  
  16.   
  17. SQL> EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();  
  18.   
  19. PL/SQL procedure successfully completed.  
  20.   
  21. SQL> EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();  
  22.   
  23. PL/SQL procedure successfully completed.  
  24.   
  25. SQL>  

We create resource groups, plan and plan directives. Before doing that, we need to switch the session from the root container to the PDBHR using “alter session set container=PDBHR” command.

view plaincopy to clipboardprint?
  1. SQL> alter session set container=PDBHR;  
  2.   
  3. Session altered.  
  4.   
  5. SQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();  
  6.   
  7. PL/SQL procedure successfully completed.  
  8.   
  9. SQL> BEGIN  
  10.   2  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP('OLTP');  
  11.   3  END;  
  12.   4  /  
  13.   
  14. PL/SQL procedure successfully completed.  
  15.   
  16. SQL> BEGIN  
  17.   2  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP('REPORTING');  
  18.   3  END;  
  19.   4  /  
  20.   
  21. PL/SQL procedure successfully completed.  
  22.   
  23. SQL> BEGIN  
  24.   2  DBMS_RESOURCE_MANAGER.CREATE_PLAN('OLTP_PLAN');  
  25.   3  END;  
  26.   4  /  
  27.   
  28. PL/SQL procedure successfully completed.  
  29.   
  30. SQL> BEGIN  
  31.   2  DBMS_RESOURCE_MANAGER.CREATE_PLAN('REPORTING_PLAN');  
  32.   3  END;  
  33.   4  /  
  34.   
  35. PL/SQL procedure successfully completed.  
  36.   
  37. SQL> BEGIN  
  38.   2    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE  
  39.   3     (PLAN             => 'OLTP_PLAN',  
  40.   4      GROUP_OR_SUBPLAN => 'SYS_GROUP',  
  41.   5      CPU_P1          => 2);  
  42.   6    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE  
  43.   7     (PLAN             => 'REPORTING_PLAN',  
  44.   8      GROUP_OR_SUBPLAN => 'SYS_GROUP',  
  45.   9        CPU_P1          => 10);  
  46.  10  END;  
  47.  11  /  
  48.   
  49. PL/SQL procedure successfully completed.  
  50.   
  51. SQL>  BEGIN  
  52.   2   DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE  
  53.   3      (PLAN            => 'OLTP_PLAN',  
  54.   4      GROUP_OR_SUBPLAN => 'OTHER_GROUPS',  
  55.   5      CPU_P1          => 1);  
  56.   6  END;  
  57.   7  /  
  58.   
  59. PL/SQL procedure successfully completed.  
  60.   
  61. SQL>  BEGIN  
  62.   2   DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE  
  63.   3      (PLAN            => 'REPORTING_PLAN',  
  64.   4      GROUP_OR_SUBPLAN => 'OTHER_GROUPS',  
  65.   5      CPU_P1          => 1);  
  66.   6  END;  
  67.   7  /  
  68.   
  69. PL/SQL procedure successfully completed.  
  70.   
  71. SQL> EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();  
  72.   
  73. PL/SQL procedure successfully completed.  
  74.   
  75. SQL> EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();  
  76.   
  77. PL/SQL procedure successfully completed.  
  78.   
  79. SQL>  

For more information about plans and directive plans, refer to the Oracle documentation. Find below the link:
Doc 12cR1

5. Summary:
In this article, we learned how Oracle 12c Resource Manager manages resources:
– Between PDBs.
– With a single PDB.
Now, with the new Oracle 12c database, we can use Oracle Resource Manager to manage resource consumption, sharing between all the databases installed on your server, under the condition that those databases are plugged into one CDB (PDBs).

Download the article in PDF here: Oracle_12c_Resource_Management
Cheers,
Wissem

posted @ 2017-03-29 17:13  feiyun8616  阅读(868)  评论(0)  编辑  收藏  举报