Introduction

The Denodo Resource Manager allows you to optimize the resources’ utilization for your application environment.

The Resource Manager introduces two main concepts:

• Plans
• Rules

A “plan” is a set of restrictions assigned to a user session. For example: limit the number of concurrent queries the same user can run, limit the maximum number of rows of the queries’ results, etc.

A “rule” assigns a “plan” to the session of a user if a certain condition is met. This condition can use any of the attributes of the session. I.e. user name, access interface used by the application (JDBC, ODBC, etc.), IP address, etc.

In this document, we will show how to configure the resource manager for executing two plans which depend on the user_agent and the access_interface used. Based on these values these execution plans will change the resource consumer group from an Oracle data source.

In our Oracle data source we have created resource consumer groups (MYGROUP1, MYGROUP2) in an Oracle resource execution plan which limits the CPU usage to 80% for MYGROUP1 and 20% for MYGROUP2.

The Oracle data source in Denodo will be configured with an initial SQL sentence and with authentication pass-through session credentials to use the same user credentials in Denodo Virtual DataPort (VDP) and in Oracle.

Oracle Data Source configuration

First, we will configure our Oracle data source with the objective of using the same user credentials in VDP and in the Oracle database.

To do so, in the data source go to the Configuration tab and then to Connection. There we need to indicate:

1. Authentication: “Use pass-through session credentials”. When we select this option, when a client queries a view that uses this data source, the VDP server will use the credentials of the VDPuser to connect to this database.

2. Use other type of authentication to create base views: Select if you want to use a different authentication method to connect to the database during the introspection process.

Initial SQL Sentences

After setting the authentication of the data source, we need to establish an initial SQL sentence with interpolation variables. One is @USER_NAME which is the username (in Virtual DataPort) for the user that executes the query and the other is @GROUP which will be the interpolation variable where we will indicate the Oracle consumer group.

To set this sentence we need to go to our Oracle data source and in the tab Option > Advanced, go to Initial SQL Sentences and enter the Oracle statement:

The oracle procedure SWITCH_CONSUMER_GROUP_FOR_USER  changes the resource consumer group for all sessions with a given user ID or name. It also changes the consumer group of any parallel execution servers that are related to the top user session.

Denodo Resource Manager Plans

The GROUP interpolation variable that we are using in our SQL sentence will be set by the Denodo Resource Manager Plans. In this section we describe the plans and rules that will be defined.

Plan 1

For the first plan we will assign the value MYGROUP2 to the interpolation variable GROUP. For creating the execution plan, go to the Server Explorer and then to Administration > Resource Manager.

1. Go to Administration > Resource manager > Plans.
2. Click on New to create a plan.
3. In the “Create plan” dialog, we enter the name of the plan, for instance “oracle_plan”.
4. Then, click on “New” to create a restriction.

5. In the dialog different actions will be displayed, in our example we want to establish a query context variable (GROUP) that will be interpolated to the initial SQL statement with the group: MYGROUP2. So, go to the “Add variables values to query context” section and introduce the variable name and its value.

6. Finally, click on “OK”.

Rule 1

After creating the plan, we have to create a rule. In this case,  if the user_agent is the Denodo-Design-Studio this plan will be used. Go to the Server Explorer and then to Administration > Resource Manager.

1. Click the Rules tab.
2. New to create a rule.

3. In the “Create rule” dialog, we enter the name of the rule: “change_consumer_group”.
4. We have to select the plan that we want to apply the rule. “oracle_plan”.
5. Enable “Specify condition”.
6. Condition: user_agent =  ‘Denodo-Design-Studio’. User agent of the client that executed the query.
7. Finally, click on “Ok”.

Plan 2

Now, we have to set another plan to assign a restriction that will set the GROUP variable with the value MYGROUP1. To create the execution plan, go to the Server Explorer and then to Administration > Resource Manager.

1. Go to the Plans tab.
2. Click New to create a plan.
3. In the “Create plan” dialog, we enter the name of the plan: “oracle_plan2”.
4. Click on “New” to create a restriction.

5. In the dialog we want to establish a query context variable (GROUP) that will be set to MYGROUP1. So, go to the “Add variables values to query context” section and introduce the variable and its value.

6. Finally, click on “OK”.

Rule 2

After  creating the second plan, we have to create a rule where if the access_interface is ODBC this plan will be executed. So, go to the Server Explorer and then to Administration > Resource Manager.

1. Go to the Rules tab.
2. Click on New to create a rule.

3. In the “Create rule” dialog, we enter the name of the rule: “change_consumer_group”.
4. We have to select the plan that we want to apply the rule. “oracle_plan2”.
5. Enable “Specify condition”.
6. Condition: access_interface=’ODBC’.
7. Finally, click on “Ok”.

Result

In the screenshot below, we show what happens in our Oracle data source,  if we execute a query from the Denodo Web Design Studio with the Resource Manager enabled. We can see that the group of the user DENODO has been changed to the consumer group MYGROUP2 for the execution of the query.

In the screenshot below, we show what happens in the data source,  when we execute a query from a BI tool that uses ODBC to access Denodo. We can see that the group of the user DENODO has been changed to the consumer group MYGROUP1 for the execution of the query.

Conclusion

This document presents a specific example where we change the Oracle resource consumer group depending on the access method for the queries executed in Denodo (Denodo Design Studio or an ODBC access interface).

To change this resource consumer group we have created in Denodo two plans that are executed when a specific condition is met. For this, we use an initial SQL sentence in the data source configuration using a stored procedure that is specific to Oracle.

Similar steps can be followed to achieve similar outcomes in different databases, using other rules and conditions and the specific mechanisms that those databases will have to change their resources manager plans or other session attributes.

References

Oracle SWITCH_CONSUMER_GROUP_FOR_USER

Resource Manager

Defining a Plan

Defining a Rule

Initial SQL Statements

V$SESSION

V$SQL

JDBC Sources