By Franck Pachot

.
Yes, the topics for the OCM 12c upgrade are online and I’m already looking at the topics. I’m talking about the ‘Configure the Resource Manager’ one here. Configuring Resource Manager in commandline is not easy. At the OCM exam you can do things faster when you have a GUI. Of course, you need to know where to find the command line API in case no GUI is available. In 11g, even if no Cloud Control is set up, you can install the dbconsole which can be used to configure Resource manager. But in 12c, there’s no dbconsole and EM Express don’t that that feature. But in the Oracle Home, you have SQL Developer (version 3.2 in the 12.1.0.2 installation).

The feature is in the ‘DBA’ view of SQL Developer. Let’s show a quick example.

Consumer Groups

Consumer groups are users sessions that are grouped together based on resource processing requirements. Each Consumer Group definition specifies the users and roles that are allowed to switch into this Consumer Group.

You can create one with the ‘Create new’ action:

round robin scheduler distribute resources equally. ‘run-to-completion’ is to schedule long sessions ahead.

don’t hesitate to look at the ‘SQL’ tab to see the generated statement.

Of course, you can copy-paste the code to a script in case you need to re-run everything quickly later.

BEGIN
    DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
    DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
    DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
        CONSUMER_GROUP => 'SmartUsers',
        COMMENT => 'this is the group for users I find very smart',
        CPU_MTH => 'ROUND-ROBIN');
    DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;

And here is my ‘SMARTUSERS’ group created:

Resource plans directive

I’ll use the DEFAULT plan.

At level 1 it reserves 90% of resource for SYS_GROUPAt level 2 I’ll give and add a group directive to give 80% for my smart users

So I add a group directive and allow 80 to my smart users in level 2. Here is the result:

Consumer group mapping

Now I have to map sessions to groups. Here I add the BI user to my SMARTUSERS group.

I can add other users like this:

Active Plan

Now, I’m currently in the maintenance window. I have to make the DEFAULT plan active in order to test it.

My new plan is now active, leaving 80% of CPU for SYS and 90% of the remaining for my BI, HR and IX users. When the system is busy with all those users, only the remaining 20% of the remaining 10% will be available to other users.

Statistics

In order to show the result, and the graphs that are provided in SQL Developer, I’ll run a CPU bound session with the following users:

• from 22:58 to 23:08 : BI (belongs to SMARTUSERS)
• from 22:59 to 23:02 : SYS (belongs to SYS_GROUP)
• from 23:00 to 23:16 : DEMO (belongs to OTHER_GROUPS)

Let’s check the CPU consumption:

Here we see that SYS had most of the resources. When SYS job has finished then BI was able to use most of the resources. Other user were limited.

We can check the sessions who had to wait:

Waiting for SYS was always limited. SMARTUSERS had to wait while SYS was running, and then had most of the resources

Resource Manager

Resource Manager is clearly a feature that is under-used. If you are in Enterprise Edition, you should use it. No need to build complex plans. Simple plans can allow some group of users to connect to production without taking the risk to exhaust all resources. In my opinion, every OLTP database should have a plan that gives priority to the application. Then you can allow some developers to query on it with a lower priority.

It’s easy to set from Cloud Control or SQL Developer. Of course, better to use the latest SQLDev.

If you don’t have access to any GUI and have documentation without search box, the documentation is in Administrator’s Guide / Managing Resources with Oracle Database Resource Manager