Clamping Down Discoverer

Performance is often a big issue with Discoverer especially when Discoverer users share resources with other system users. It is often said that Discoverer must always use a separate reporting database because the Discoverer users would slow down the production system. This is rubbish; there are some good reasons for putting Discoverer on a separate system, but performance is not one of them.

Much better is to put all your system resources into one database and then clamp down Discoverer so that it cannot interfere with other processes. You can reduce the priority or limit the CPU; you decide what database resource Discoverer should use and when it should use it.

This post describes how to clamp down the Discoverer so that it does not slow down the rest of the system.

Resource Consumer Groups

The Oracle Database Resource Manager provides essential functionality to help you do this. It allows you to manage the hardware resources that are allocated to a database session. The Resource Manager provides a flexible way to control the Discoverer sessions because it allows you:

1. limit the CPU or I/O used by a session
2. lower the priority of session
3. terminate sessions that use too much resource
4. terminate sessions where the optimizer estimates that the SQL run time will be over a threshold

Even better, the resource scheme used can be changed dynamically, so that you can for example, have one resource allocation for during the day and another for night time and weekends.

Setting up a resource plan can be quite complex but fortunately there is a default SYSTEM_PLAN provided which has a low priority group into which you can add the Discoverer sessions. All other sessions will use the default consumer group so that any Discoverer session in the low priority group will only use CPU not consumed by other database sessions.

A listing of the plans and consumer groups available in the database can be obtain from the DBA_RSRC_PLANS and DBA_RSRC_CONSUMER_GROUPS views.

By default the Resource Manager is not enabled. So first you need to enable the Resource Manager by setting the database initialization parameter to a resource plan:
RESOURCE_MANAGER_PLAN=SYSTEM_PLAN

Alternatively, you can activate the Resource Manager on the fly by entering:
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'SYSTEM PLAN'

Next you need to ensure that the Discoverer sessions are added to the low priority consumer group. There are basically two ways that you can do this; using the consumer group mapping functionality to automatically add the session to the consumer group when the session is created in the database; or run an initialization procedure at the start of the session to switch the consumer group from the default group to the low priority group.

Consumer Group Mappings – The database lets you set up a mapping between session and consumer groups. Many new mapping options were added in Oracle 10g and therefore on databases prior to 10g the mapping option you need may not be available. A mapping that automatically adds sessions to a consumer group can be created using DBMS_RESOURCE_MANAGE.

There are lots of mapping options available. You can add all the sessions create by a user into a consumer group. For example, to add all the sessions created by the DISCO_USER database user into the low priority consumer group the following call to the DBMS_RESOURCE_MANAGER package can be used:

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'DISCO_USER', 'LOW_GROUP');

You can add all the sessions created by a particular program. For example, the sessions created by the Discoverer Desktop program can be added using:

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
(DBMS_RESOURCE_MANAGER.CLIENT_PROGRAM, 'dis51usr.exe', 'LOW_GROUP');

You can also add sessions by service name, so here you would define a service name in the TNSNAMES.ora files used by Discoverer and map the service name to a resource group.

Initialization Procedure - This is the method we use. A procedure is run at the start of every Discoverer session which switches the consumer group. Because we are in an Oracle Applications 11i environment we use the ‘Initialization SQL Statement – Custom’ system profile to call the initialization procedure rather than a trigger. The procedure includes the following statement:

DBMS_SESSION.switch_current_consumer_group('LOW_GROUP',v_varchar2_240, FALSE);

to switch the session to the lower priority consumer group.

The initialization procedure gives you the best control over the consumer group setting. For example, you can set the priority of scheduled Discoverer sessions to be lower than the priority of regular sessions.

RAC Nodes

Real Application Clusters (RAC) provide an effective way of segregating Discoverer from other processes. Typically you would have a database node dedicated to processing Discoverer reports. Discoverer can then be directed to the dedicated node by setting the RAC node in the TNSNAMES.ora file on the Discoverer Application server (and on the desktop if Discoverer Desktop is used). You can use service names to specify preferred and failover nodes.

Using a dedicated RAC node is the cleanest way of separating Discoverer from other processing in a RAC environment however, if you use parallel processing in your Discoverer reports then some further configuration is required.

By default on a RAC node any SQL statement that uses parallel processing will parallelize the workload by running slave processes on all RAC nodes in the database. So if the parallel_max_servers database parameter is set to 8 then 8 parallel processes will be started on each RAC node in the database. That's great if you want your Discoverer report to use CPU resource across all the RAC nodes, but if you want Discoverer just to use the dedicated RAC node then in Oracle 10g you need to set up a RAC instance group. In 11g it’s a bit easier as the nodes are service aware and only run the named service.

1. For example in 10g, if PROD is the database instance and PROD4 is the dedicated RAC node then an INSTANCE_GROUP called DISCO for the Discoverer dedicated node can be defined by including in the database initialization file:
PROD4.INSTANCE_GROUP=DISCO

2. Then in the Discoverer initialization procedure include the following statement:
EXECUTE IMMEDIATE 'ALTER SESSION SET PARALLEL_INSTANCE_GROUP = ''DISCO''';

The Discoverer processes will then join the DISCO parallel instance group when the process is started and any subsequent parallel SQL run by this process will only use the RAC nodes in the DISCO instance group.

Temporary Tablespace

A Discoverer report that is very large or has been badly written can use a lot of temp space. The database uses temporary tablespace when a database sort is too large to fit in memory and so if Discoverer has to sort lots of data for example in a hash join then it will use temp space. The size of the temp space needs to be controlled to stop the disks filling up and hence a maximum size limit for the temporary tablespace should be set.

This is a problem if Discoverer is sharing the temp space with other processes. When a Discoverer report runs out of temp space then it will fail with ORA-01652: Unable to extend temp segment. The problem is that the temporary tablespace is a database user resource and so is shared with other processes that use the same temporary table space.

Hence when Discoverer processes are sharing a database with other processes it is important that the Discoverer processes use a separate tablespace. Otherwise one or more rogue Discoverer processes can gobble up all the temp space causing another process to fail when there is no temp space available.

Now if your Discoverer processes always connect to the database as separate database users then the solution is easy; you create one or more separate temporary tablespaces for these database users. Then the Discoverer processes may still fail with the ORA-01652: Unable to extend temp segment error but at least other processes will be unaffected.

However, where Discoverer is used with the e-Business Suite (and also in many other systems) Discoverer connects to the database using a common database user. In the e-Business Suite all processes connect as the APPS database user and therefore by default all processes share the temporary tablespace associated with the APPS database user. The OLTP processes probably do not use any temp space but interface and batch processing often use temp space and could error as a result of Discoverer processing.

The solution is not easy and different solutions may be required for different systems, but where Discoverer is implemented with the e-Business Suite a separate temporary tablespace can be implemented as follows.

1. Create a new user with a limited tablespace.

CREATE TEMPORARY TABLESPACE TEMP_DISCO TEMPFILE SIZE AUTOEXTEND OFF;

CREATE USER APPSDISCO IDENTIFIED BY DEFAULT TABLESPACE TEMPORARY TABLESPACE TEMP_DISCO ACCOUNT LOCK;

2. Create an initialization procedure for the Discoverer which changes the current schema to the APPSDISCO user for the Discoverer process. The current schema defines which database schema is used as the default schema where no schema is specified in front of a database object. Consequentally the temporary tablespace used is the temporary tablespace associated with the APPSDISCO user.

PROCEDURE initialize
IS
BEGIN
IF SYS_CONTEXT('USERENV','MODULE') LIKE 'Disco%' THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=APPSDISCO';
END IF;
END initialize;

3. Call the initalization procedure from the ‘Initialization SQL Statement – Custom’ system profile for all the responsibilities that use Discoverer. So if your initialization procedure is held within the DISCO_UTILITIES_PKG PL/SQL package then the profile would be set to:

begin disco_utilities_pkg.initialize; end;

However, there are some complications that may need to be addressed:

1. The SYS_CONTEXT('USERENV','MODULE') only works in Oracle 10g. In Oracle 9i you need to check in the V$SESSION table.


2. Although Discoverer puts the APPS schema in front of all the database objects I found that there were some database bugs in the version of the database I was using (Oracle 9i) and I had to create some synonyms for some of the APPS objects, for example, to get all the Discoverer reports to run I had to create synonyms in the APPSDISCO schema for all the packages used by functions mapped into the EUL.


3. Again, although the Discoverer processes still connect as the APPS user and therefore have all the APPS database privileges I found that database/Discoverer bugs caused privilege errors. To workaround this I had to grant privilege on some APPS database objects to the APPSDISCO user.

Conclusion

Where Discoverer is reporting on transactional data then Discoverer should use the same database as the transactional system. A single database allows Discoverer to report on real-time data and saves all the complications of replicating and managing separate databases.

A single database allows more efficient use of the hardware resources by allowing Discoverer to use spare CPU that would otherwise be wasted. Long running Discoverer processes can run at lower priority than other processes in the system increasing the overall throughput of the system. Provided Discoverer has been properly clamped down then the Discoverer processes will not affect other processing in the system.

You can go further and separate Discoverer scheduled processes from interactive Discoverer processes. Then you can run the scheduled larger Discoverer reports at a lower priority and separate temp space than the interactive reports.

When Discoverer Users and management know that they will not be able to affect the performance of the system as a whole by running Discoverer reports, they will then have confidence to build and run more complex reports making greater use of the Discoverer reporting system.