Oracle Database Resource Manager and OBIEE

When putting together an OBIEE system, one common requirement from clients is to provide an enhanced level of service for particular groups of users. For example, you might want to define a "standard" group for regular OBIEE users, and a "management" group that gets allocated more CPU time, more I/O resources and so on. You might want to limit standard users' queries to a maximum of one minute run time, whilst allow managerial users' queries to take as long as they need. You might want to define a set of rules that say, if a standard user's query is predicated to take longer than a minute, move the query to a lower-priority resource pool so that it doesn't take up all the available CPU. You might want to make all of these users higher priority than batch jobs, or you might want to restrict standard user queries from running in parallel. All of these are typical "resource management" issues that you might want to take control of.

OBIEE, at the BI Server level. lets you define query limits that either warn or stop users from exceeding certain elapsed query times or number of rows returned. Assuming you define a "standard" group for most OBIEE users, you might want to stop them from displaying reports (requests) that return more than 50,000 rows, whilst you might want to warn them if their query takes over five minutes to run.

(note that this is different to Discoverer, which used to maintain statistics and warn you if the query was predicted to take over a certain time, rather than warn you if it took over a certain time).

You can also restrict the times of day that users can run queries, which you might do to stop users running queries before 9am, assuming managers come in early and want to get their figures quickly.

Whilst these are quite handy, you might want to exercise more control over how resources are allocated to these groups, and if you're using an Oracle database as the back-end database, a feature you might therefore want to use is the Oracle Database Resource Manager. The Oracle Database Resource Manager (or DBRM for short) allows you to define consumer groups and resource plans which can then be used to:

Guarantee certain sessions a minimum amount of processing resource regardless of the total load on the system
Allocate percentages of CPU time to different users and applications
Restrict or limit the degree of parallelism for queries
Restrict the total number of active sessions for a particular group
Manage runaway sessions, by either stopping them running or switching them to a lower-priority group
Prevent the execution of queries predicted to take over a certain amount of time
Automatically allocate sessions to particular resource plans depending on attributes of the session
Limit the amount of time that a session can be idle, including if it is blocking another session from starting

In an OBIEE context, the way that you would set this up depends on whether users connect using their own Oracle login, or through a shared login. If they use their own Oracle database login, this will be passed through to the Oracle database holding the source data and the resource manager will apply the relevant resource plan, based on which consumer group they belong to.

More likely though is the situation where users access the database through a shared login which works via a connection pool. In this situation, it's the shared login that is sent through to the Oracle database which will again allocate it to a resource group and apply the resource plan accordingly. DBRM therefore, from a database perspective, has the following three major elements:

Resource Consumer Groups, which are groups of sessions that share the same characteristics and that have resource plan allocated to them
Resource Plans, containers for sets of Resource Plan Directives, and
Resource Plan Directives, which are instructions to Oracle on how to allocate database resources

DBRM resource plan directives are therefore allocated to resource consumer groups, not individual users, with all of this being managed by an Oracle PL/SQL package called DBMS_RESOURCE_MANAGER. So how does this work in practice?

Well, Tim Hall's website (http://www.oracle-base.com) is usually the place I go to for concise definitions of new Oracle features, and he's written a series of articles about DBRM since its inception back in Oracle 8i:

Resource Manager in Oracle Database 8i (a good initial introduction to the feature)
Resource Manager Enhancements in Oracle 9i (added automatic resource group switching, an extended cover to UNDO_POOL)
Resource Manager Enhancements in Oracle Database 10g (more fine-grained control over long-running sessions)
Resource Manager Enhancements in Oracle Database 11g (pre-defined resource groups plus minor enhancements)

There's also a more general white paper from Oracle entitled "Best Practices for a Data Warehouse on Oracle Database 11g" that mentions Resource Manager in the wider context of an Oracle Data Warehouse, and of course the Oracle Documentation. For the purposes of working through the feature though, it's probably worth thinking about a scenario where this might come in useful.

In this scenario, we have three main groups of users:

Standard DW users, who we will give a guaranteed 30% of CPU time to, who won't be able to run queries in parallel, and who won't be allowed to run queries that are predicted to take more than 5 minutes to run
Advanced DW users, who will be given a guaranteed 50% of CPU, who can use parallel query up to a DOP of 4, but will be bounced down to the Standard DW user group if their queries actually take more than 1 minute
Executives, who will be guaranteed 20% of CPU time and again, won't have restrictions on what they do

These will become the main "Resource Consumer Groups" as far as DBRM is concerned. Note that for the above groups, the CPU restrictions will only take place when CPU usage reaches 100% (allowing us to ration a scarce resource only when necessary), and CPU resources from groups that aren't using them will be allocated to those that need them if we're running under 100% usage. So it's not too restrictive an approach and one that will only really kick-in when resources are scarce. Note also that the 11g release of DBRM also allows disk I/O to be taken into account, which would allow us to include the amount of disk activity in resource plans when disk I/O becomes the bottleneck (or we could just go and buy an Exadata machine, I guess...)

This will all be set up in a resource plan called "Daytime", that will only apply during normal working hours (out of hours, users can do what they want).

Using Oracle 11.1.0.7 on Linux x86, assuming we'll go with the shared logon approach, the first step is to create the shared logons, one for each of the groups.

CREATE USER standard_dw_user IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

GRANT CONNECT TO standard_dw_user;

CREATE USER advanced_dw_user IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

GRANT CONNECT TO advanced_dw_user;

CREATE USER executives IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

GRANT CONNECT TO executives;

Then I grant "select any table" to the three users (don't do this at home, kids) so that they can access the SH schema:

GRANT SELECT ANY TABLE TO standard_dw_user;

GRANT SELECT ANY TABLE TO advanced_dw_user;

GRANT SELECT ANY TABLE TO executive;

Now in 11g there's an option to create a simple resource plan that just allocates consumer groups a share of CPU resources. This is easier to set up than the full resource plan I'll create later on, and to do this you just issue the following PL/SQL call.

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(SIMPLE_PLAN => 'DAYTIME',
   CONSUMER_GROUP1 => 'STANDARD_DW_GROUP', GROUP1_PERCENT => 30,
   CONSUMER_GROUP2 => 'ADVANCED_DW_GROUP', GROUP2_PERCENT => 50,
   CONSUMER_GROUP3 => 'EXECUTIVE_DW_GROUP', GROUP3_PERCENT => 20);
END;
/

This has the effect of creating the three consumer groups, and setting up the three directives in the new DAYTIME resource plan.

Next we need to firstly, grant permission for each user to use its respective consumer group, and then set these groups as the users' default.

BEGIN
  DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
    GRANTEE_NAME   => 'standard_dw_user',
    CONSUMER_GROUP => 'standard_dw_group',
    GRANT_OPTION   => FALSE);

  DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
    GRANTEE_NAME   => 'advanced_dw_user',
    CONSUMER_GROUP => 'advanced_dw_group',
    GRANT_OPTION   => FALSE);

  DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
    GRANTEE_NAME   => 'executive',
    CONSUMER_GROUP => 'executive_dw_group',
    GRANT_OPTION   => FALSE);

  DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('standard_dw_user', 'standard_dw_group');

  DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('advanced_dw_user', 'advanced_dw_group');

  DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('executive', 'executive_dw_group');

END;
/

Finally we now need to turn on the resource plan.

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = daytime;

Now I can start sessions as these users, and then run the following SELECT statement as a DBA user.

SELECT username, resource_consumer_group
FROM   v$session
WHERE  username in ('EXECUTIVE','STANDARD_DW_USER');

USERNAME                       RESOURCE_CONSUMER_GROUP
------------------------------ --------------------------------
STANDARD_DW_USER               STANDARD_DW_GROUP
EXECUTIVE                      EXECUTIVE_DW_GROUP

which shows that the users have been allocated to the correct group. Now, when CPU usage reaches 100%, resources will be allocated according to this plan, splitting CPU use according to the percentages in the simple resource plan, and allocating spare resources to another plan when a particular group isn't using up all its allocation.

As I mentioned earlier, you can get cleverer and put in directives to restrict access to parallel query, shift down to a more constrained group if query times go past a certain level, restrict access to I/O and so on if you wish. This is done through what's called a "complex resource plan" and it's a bit more complicated to set up.

The first thing you need to do when working with a complex resource plan is to create a "pending area". According to the manuals, a pending area is "a staging area where you can create a new resource plan, update an existing plan, or delete a plan without affecting currently running applications. When you create a pending area, the database initializes it and then copies existing plans into the pending area so that they can be updated". We'll also take the opportunity to delete the previous resource plan, after we set up the pending area.

conn / as sysdba

SET ECHO ON

BEGIN
  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
END;
/

BEGIN
     DBMS_RESOURCE_MANAGER.DELETE_PLAN_CASCADE(PLAN=>'DAYTIME');
END;
/

We'll now set up a new plan, and the three different resource groups;

BEGIN
  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();

  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

  DBMS_RESOURCE_MANAGER.CREATE_PLAN(
   PLAN    => 'DAYTIME_PLAN',
   COMMENT => 'Complex Daytime Plan');

  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
   CONSUMER_GROUP => 'STANDARD_DW_GROUP',
   COMMENT        => 'Standard DW Users');

  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
   CONSUMER_GROUP => 'ADVANCED_DW_GROUP',
   COMMENT        => 'Advanced DW Users');

  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
   CONSUMER_GROUP => 'EXECUTIVE_DW_GROUP',
   COMMENT        => 'Executive DW Users');

END;
/

Now there are a fair few more parameters available for the CREATE_PLAN procedure, mostly concerned with the method by which we allocate resources ("emphasis" or "ratio"), but for now we'll go with the defaults.

Next we define the plan directives.

BEGIN

  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
   PLAN                     => 'DAYTIME_PLAN',
   GROUP_OR_SUBPLAN         => 'STANDARD_DW_GROUP',
   COMMENT                  => 'Standard DW group',
   MGMT_P1                  => 30,
   PARALLEL_DEGREE_LIMIT_P1 => 0,
   SWITCH_TIME              => 300,
   SWITCH_GROUP             => 'CANCEL_SQL',
   SWITCH_ESTIMATE          => TRUE
   );

  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
   PLAN                     => 'DAYTIME_PLAN',
   GROUP_OR_SUBPLAN         => 'ADVANCED_DW_GROUP',
   COMMENT                  => 'Advanced DW group',
   MGMT_P1                  => 45,
   PARALLEL_DEGREE_LIMIT_P1 => 4,
   SWITCH_TIME              => 60,
   SWITCH_GROUP             => 'STANDARD_DW_GROUP',
   SWITCH_ESTIMATE          => FALSE
   );

  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
   PLAN                     => 'DAYTIME_PLAN',
   GROUP_OR_SUBPLAN         => 'EXECUTIVE_DW_GROUP',
   COMMENT                  => 'Executive DW group',
   MGMT_P1                  => 20
   );

 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
   PLAN                     => 'DAYTIME_PLAN',
   GROUP_OR_SUBPLAN         => 'OTHER_GROUPS',
   COMMENT                  => 'other groups',
   MGMT_P1                  => 5
   );

END;
/

Note how I've had to allocate some resource to any users falling outside these groups, in this complex resource plan.

Finally we validate, and then submit, the pending area.

BEGIN
 DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
 DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

Then we have to grant permission to our users to use these plan directives, and set them as the user's default, as we did with the simple plan.

BEGIN
  DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
    GRANTEE_NAME   => 'standard_dw_user',
    CONSUMER_GROUP => 'standard_dw_group',
    GRANT_OPTION   => FALSE);

  DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
    GRANTEE_NAME   => 'advanced_dw_user',
    CONSUMER_GROUP => 'advanced_dw_group',
    GRANT_OPTION   => FALSE);

  DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
    GRANTEE_NAME   => 'executive',
    CONSUMER_GROUP => 'executive_dw_group',
    GRANT_OPTION   => FALSE);

  DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('standard_dw_user', 'standard_dw_group');

  DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('advanced_dw_user', 'advanced_dw_group');

  DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('executive', 'executive_dw_group');

END;
/

And finally, turn on the new resource plan.

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = daytime_plan;

Then we can log in, in separate sessions, as these three users, and check that they've been allocated to the correct consumer groups.

SELECT username, resource_consumer_group
FROM   v$session
WHERE  username in ('EXECUTIVE','STANDARD_DW_USER','ADVANCED_DW_USER');

USERNAME                       RESOURCE_CONSUMER_GROUP
------------------------------ --------------------------------
ADVANCED_DW_USER               ADVANCED_DW_GROUP
EXECUTIVE                      EXECUTIVE_DW_GROUP
STANDARD_DW_USER               STANDARD_DW_GROUP

So let's try and trigger some of the resource management. Now the hard CPU limits will be hard to test, as they only kick on when CPU usage on the machine gets to 100%, and even then when there is no spare capacity in a consumer group that's not using all it's allocation, but we should be able to get a "standard DW user" to have their query cancelled if it's going to take more than five minutes.

I create a copy of the SH schema, insert the contents of the SALES table into itself a couple of times, then write a SELECT statement that aggregates the table using a cartesian join - something that will take ten minutes or so to run.

conn standard_dw_user/password

SELECT sum(amount_sold)
FROM   sh_copy.sales
,      sh_copy.products
,      sh_copy.customers
,      sh_copy.times
,      sh_copy.promotions
,      sh_copy.channels
/

ERROR at line 2:
ORA-00040: active time limit exceeded - call aborted

So the Database Resource Manager has aborted the query, even before it executes, because the predicted time for the query was greater than five minutes.

If we now log in as the advanced DW user, we can see consumer group switching in action.

conn advanced_dw_user/password

select sum(amount_sold)
from   sh_copy.sales
,      sh_copy.products
,      sh_copy.customers
/

Leaving the session running switching back to the SYS user, I can see their consumer group initially being the advanced user group:

SELECT username, resource_consumer_group
FROM   v$session
WHERE  username in ('EXECUTIVE','STANDARD_DW_USER','ADVANCED_DW_USER');

USERNAME                       RESOURCE_CONSUMER_GROUP
------------------------------ --------------------------------
ADVANCED_DW_USER               ADVANCED_DW_GROUP

After a minute though, I check again and see that they have indeed switched down to the lower priority group.

SQL> /

USERNAME                       RESOURCE_CONSUMER_GROUP
------------------------------ --------------------------------
ADVANCED_DW_USER               STANDARD_DW_GROUP

Using this technique you can "manage" power users who write runaway queries, allowing the query to continue but switching them away from taking up all of the system's resources.

So, wrapping this all up, how to we make use of it with OBIEE. The trick to it is two things; firstly, we need to amend the resource directive to make it work properly with connection pools, otherwise one particular OBIEE user exceeding time limits will push all the other users of that connection pool into this lower-priority consumer group, and secondly we need to define multiple connection pools that make use of each resource group.

Firstly I need to go back and amend the directives to use SWITCH_TIME_IN_CALL rather than SWITCH_TIME (which bounces any new sessions by a user back to the original resource group rather than leaving them in the ones the previous session was switched in to), like this:

BEGIN

 DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
 DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

 DBMS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE (
   PLAN                     => 'DAYTIME_PLAN',
   GROUP_OR_SUBPLAN         => 'STANDARD_DW_GROUP'
  );

 DBMS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE (
   PLAN                     => 'DAYTIME_PLAN',
   GROUP_OR_SUBPLAN         => 'ADVANCED_DW_GROUP'
  );

 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
   PLAN                     => 'DAYTIME_PLAN',
   GROUP_OR_SUBPLAN         => 'STANDARD_DW_GROUP',
   COMMENT                  => 'Standard DW group',
   MGMT_P1                  => 30,
   PARALLEL_DEGREE_LIMIT_P1 => 0,
   SWITCH_TIME_IN_CALL      => 300,
   SWITCH_GROUP             => 'CANCEL_SQL',
   SWITCH_ESTIMATE          => TRUE
   );

  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
   PLAN                     => 'DAYTIME_PLAN',
   GROUP_OR_SUBPLAN         => 'ADVANCED_DW_GROUP',
   COMMENT                  => 'Advanced DW group',
   MGMT_P1                  => 45,
   PARALLEL_DEGREE_LIMIT_P1 => 4,
   SWITCH_TIME_IN_CALL      => 60,
   SWITCH_GROUP             => 'STANDARD_DW_GROUP',
   SWITCH_ESTIMATE          => FALSE
   );

  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

END;
/

Now we switch over to the BI Administration tool, and define some standard, advanced and executive users that belong to standard, advanced and executive security groups.

Now we take copies of the standard connection pool, and name it after each of the consumer groups, deleting the original one.

Then we edit each connection pool, changing the shared login to correspond to the users we created earlier (remember to check the "use qualified names" checkbox so that it still reads from the correct database schema once connected).

Now set permissions on each of the connection pools, so that only the relevant BI Server security group can connect through them.

Then when users log in, they'll be assigned to the correct connection pool (as it's the only one, for that data source, they'll have permissions on). When the user then comes to run the query, they'll run as the correct database user and get assigned to the correct consumer group, and then if they hit a resource directive issue, they will either get bumped down a group or, as in the case below, have their query aborted as it exceeds the allowed time estimate.

Full details of Oracle Database Resource Manager can be found in the online docs here.