Working with Oracle Database Resource Manager

Working with Oracle Database Resource Manager

Database Consolidation is an extremely common practice among companies these days. There are several reasons why people choose to consolidate their databases. The most important reason is cost. Consolidation allows you to save cost, it could be electricity bills, DB licences or operation costs.

There are 3 types of database consolidation:

  1. Multiple databases within same Operating System (OS)
  2. Multiple schemas/workload within same database
  3. Multiple databases within same database (12c multi-tenant feature!)

Instance Caging allows you to set the amount of CPU a database can access to. Oracle Database Resource Manager(DBRM) allows you to slice the CPU cycles to share them among the different workload, schemas or databases. For engineered systems, IO Resource Manager(IORM) allows you to manage the IO resources as well. However, I will not be sharing about them.

Below are the types of resources that be managed by Oracle Database Resource Manager. I will be using CPU with my examples later.

  • CPU
  • Degree of Parallelism Limit
  • Parallel Target Percentage
  • Parallel Queue Timeout
  • Active Session Pool with Queuing
  • Automatic Consumer Group Switching
  • Canceling SQL and Terminating Sessions
  • Execution Time Limit
  • Undo Pool
  • Idle Time Limit

Multiple Databases within the same OS – Instance Caging

If you are consolidating multiple databases within the same OS, instance caging will be the feature to use! It’s extremely easy to configure.

SQL> alter system set cpu_count = 4;
SQL> alter system set resource_manager_plan = 'default_plan' sid='*';

Note 1: CPU_COUNT is a dynamic parameter
Note 2: RESOURCE_MANAGER_PLAN is a dynamic parameter
Note 3: CPU_CORE refers to the CPU threads, not CPU cores.
Note 4: It’s a good idea to oversubscribe the CPU_COUNT to fully utilise the CPU cores

Multiple schemas/workload within same database – Resource Manager

The steps below will create a very basic resource plan to manage the workload within the database. Session using the service ‘ONLINE_OLTP’ will 40% of the CPU cycles if the CPU are utilised at 100%.

SQL> exec dbms_resource_manager.create_pending_area();
SQL> exec dbms_resource_manager.create_consumer_group('ONLINE_APP','Online OTLP Application');
SQL> exec dbms_resource_manager.set_consumer_group_mapping (attribute => dbms_resource_manager.service_name,value => 'ONLINE_OLTP',consumer_group => 'ONLINE_APP'); SQL> exec dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name => 'public',consumer_group => 'ONLINE_OLTP',grant_option => FALSE);
SQL> exec dbms_resource_manager.create_plan('DB_CONSOLIDATION','Plan for Consolidation');
SQL> exec dbms_resource_manager.create_plan_directive('DB_CONSOLIDATION','ONLINE_APP','Shares',mgmt_p1=> 40);
SQL> exec dbms_resource_manager.create_plan_directive('DB_CONSOLIDATION','OTHER_GROUPS',mgmt_p1=>10)
SQL> alter system set resource_manager_plan='DB_CONSOLIDATION';

The OTHER_GROUPS is to catch-all CONSUMER_GROUP for any workload not specified by any CONSUMER_GROUP. If the resource plan directive does not contain OTHER_GROUPS, you will hit the following error.

ORA-29377: consumer group OTHER_GROUPS is not part of top-plan DB_CONSOLIDATION
 ORA-06512: at "SYS.DBMS_RMIN_SYS", line 3640
 ORA-06512: at "SYS.DBMS_RMIN_SYS", line 3691
 ORA-06512: at "SYS.DBMS_RMIN_SYS", line 4364
 ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 756
 ORA-06512: at line 1

To check what are the consumer groups within the DB_CONSOLIDATION plan.

SQL> select group_or_subplan from dba_rsrc_plan_directives where plan='DB_CONSOLIDATION';

To check what is the currently active resource plan directives in the database.

SQL> select name, cpu_managed from v$rsrc_plan where is_top_plan = 'TRUE';
 -------------------------------- ---

Note 1: Switching of resource plan is a dynamic operations.
Note 2: Remember to create the necessary privilege for the session to switch into the resource plan. Else, the mapping rule will be ignored.
Note 3: If the session is being throttled, you will see “resmgr:cpu quantum” wait events in your AWR report.

Multiple databases within same database (12c multi-tenant feature!) – Resource Manager

Do the following within the root container:

SQL> exec dbms_resource_manager.create_pending_area();
SQL> exec dbms_resource_manager.create_cdb_plan(plan => 'CDB_PLAN','CDB Resource Plan)
SQL> exec dbms_resource_manager.create_cdb_plan_directive(plan=> 'CDB_PLAN','pluggable_database => 'pdb1',shares => 3)
SQL> exec dbms_resource_manager.validate_pending_area();
SQL> SQL> exec dbms_resource_manager.submit_pending_area();
SQL> alter system set resource_manager_plan='CDB_PLAN';

So what happens when we use all 3 methods to manage our resources?

Screen Shot 2015-02-04 at 16.27.05

Assuming the system has 24 Core threads, the question is, how many CPU threads will OLTP get?

Answer: 0.5*0.5*8 = 2 :))

Wei Shan


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s