Oracle Resource Manager function with SAP BI7

[Former Member]

Hi,

Have anyone considered or using Oracle Database Resource Manager (Oracle 10.2) feature for SAP BI application in your site? I dont see a single OSS hit on this feature.

We have a SAP BI system that has few power users (who can run large STAR_TRANSFORMATION queries that analyzes large volume of data) and the main user community (our sales force > 3000+ users) who can run smaller queries of such nature or queries of different types.

We want to see if there is any way we can control the resource utilization on the database server.

Thanks in advance,

Raj

[Former Member]

That's right!!!

[Former Member]

In RAC environemnt we don't need to specify to different services for different type of users. We can connect the power user to SAP instance 1 which is connected to database instance 1. The other users can be connected to SAP instance 2 and thus to database instance 2. This means both types of users work on different database instances both share the same database. RAC is very suitable for SAP BW since you can configure both database instances differently according either to power user or users with small queries

barthez

[Former Member]

We can configure the resource manager to identify database sessions based on service names. It's not necessary to create resource plan based on Users only. In theory, the policy can still be setup to isolate the desired sessions for resource control.

With Oracle 10.2 and a single instance database, You can control CPU, degree of parallelism, total active sessions and so on.

As I said before, this will only help us to control CPU resources but not memory or I/O (I believe the I/O control is coming out in 11g). Our objective is to control the power users in not using all CPUs for the large queries (that could potentially affect loads or other batch activities in the system) at the cost of reduced performance if the business is prepared for that.

Raj

[Former Member]

I am assuming service name, one will configure at the SAP application server level, i.e., one application server will go to service1, anoher one to service 2 and then users will be instructed to logon to a specific application server.

[Former Member]

Why do you think your bottleneck is at ORACLE level?

If it's at oracle level I suggest the same solution as Markus, but I think ORACLE RAC might be better.

Did you check your SAP instances, sometimes when the query's are slow and the loading chains have design problems BW consultants claim about database. Specially if you do an upgrade from SAP BW 3.1 to SAP BI 7.0.

FF

[Former Member]

Hi,

assuming you configure a two-nodes RAC, you can configure the two database instances differently and assign each of them to the right user group. This means your "few power users" will work on different database instance than your "main commnity"

regards

Abdel

[Former Member]

1. I am curious where in SAP BW configuration, one can point to different service names for RAC.

2. Even if you go to different instances, database is still on same disks so disk i/o cannot be "resource" managed, only cpu/memory usage by being on different servers,

In any event, one is not utlizing Oracle's resource manager because SAP is usingg one database user for all end users.

[markus_doehr2]

I can think if of using then function of the SAN to guarantee I/O (depending on what SAN is used).

Markus

[markus_doehr2]

You can't use that feature.

The SAP system (BI) uses only one user to connect to the database (OPS$<sid>adm).

Markus

[Former Member]

Marcus,

I'm aware of that. Since SAP doesnt provide a method from the application to associate a front-end user to a database user, if we have SAP blessings, we can do some diligence to create different services based resource management at the database level to restrict and control resource utilization on the database server.

Of course, this would require an additional specifications such as SERVICE in the TNS and Listener files.

I'm thinking SAP didnt consider this feature because they have their own way of load balancing methods.

In the BI environment, I'm thinking you need this db feature to control database resources when loads and reports overlaps or even several small and large queries run concurrently despite however many app server a system has, all these come to the database.

The big question is whether SAP would support such a setup.

Thanks,

Raj

[markus_doehr2]

> Of course, this would require an additional specifications such as SERVICE in the TNS and Listener files.

Yes - but I'm not sure if that would give the necessary effect. Once the statement reaches the database kernel engine, it gets processed, no matter if you limit the TNS/listener usage because TNS is no more involved when the statement reaches the OLAP engine.

> In the BI environment, I'm thinking you need this db feature to control database resources when loads and reports overlaps or even several small and large queries run concurrently despite however many app server a system has, all these come to the database.

I think SAPs answer is: Buy a bigger box (or use RAC to distribute the load)

> The big question is whether SAP would support such a setup.

The feature is not listed explicitly in note 105047 - Support for Oracle functions in the SAP environment. I'd make a call at BC-DB-ORA and ask what they think about that.

I understand your problem very well but I doubt, that there's an easy way of accomplishing this with standard tools.

Markus