Managing Resources; Real World Example

1- Introduction :

For most companies, database resource requirements depend on the time of day.
In the company I am working for, during business hours, online transaction processing (OLTP) ; Back office editing may be mission critical, along with small reporting processing.
Off hours, bulk data loads, and online analytical processing reports (OLAP) reports take priority.
However, database resources must be available depending on these elements.
I am going to design the elements using Oracle resource manager.

2- Applies to :

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0.
Information in this document applies to any platform.

3- Design the Elements :
I am going to make a graph of the high-level design, split by ; day plan and off hours plan,

4- Steps overview:
1- Create a pending area.
2- Create consumer resource groups.
3- Create resource plans.
4- create plan directives.
5- Validate and submit the pending area.
6- Enable the resource plans.
7- Execute the resource plans.
8- Disable the resource plans.

5- Step by Step :
We are going to create a user named rsrc_man_user and give this user the resource manager admin privilege:

We create a pending area which will hold all the elements before validation and submit.
We create also the consumer groups.

Make sure that consumer groups are created ;

Now, we create the plans ;

Make sure that the plans are created ;

We are going to create the plans directives ;

We validate and submit the pending area;

Enable the resource plans;

Example : Let ‘s map the user WISSEM with the DAY_REPORTS_GROUP and grant this user the EXPLICIT switch of groups with admin option;

After connect to the database with the user WISSEM, we can note that the user is in OTHER_GROUPS. This is not was we set in the previous step. This user should be in the DAY_REPORTS_GROUP. Let ‘s investigate the problem;
First, we check the DBA_RSRC_GROUP_MAPPINGS table. The check confirm that the user WISSEM is in DAY_REPORTS_GROUP.
Second, we check the DBA_RSRC_MAPPING_PRIORITY table, so we are not sure that this order could be the origin of the problem, so we set the new order using DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING_PRI procedure, we set the ORACLE_USER to be in the 2nd priority list ;

We check again V$SESSION dictionary view and the user WISSEM still in OTHER_GROUPS.
The problem is fixed by setting the initialization parameter RESOURCE_MANAGER_PLAN to OLTP_PLAN

But, note that setting the initialization parameter RESOURCE_MANAGER_PLAN must be set to DAY_PLAN so SYS user will not considered in the OTHER_GROUPS.
Setting the parameter RESOURCE_MANAGER_PLAN to DAY_PLAN should enable SYS users to SYS_GROUP and WISSEM user to DAY_REPORTS_GROUP

Let ‘s check now the V$SESSION ;

We can also, force the sessions of the User WISSEM to switch the group, using the DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS or DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER or DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP procedures.

Now, let ‘s test the elements;

To disable the elements and the plans ;

Nice Reading,
Wissem EL KHLIFI