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.
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.
- 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
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
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)
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
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).
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 abortedSo 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_GROUPUsing 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.
