Tuning Oracle 10g AS Reports Server

Lets take a swift walk-through over how to tuneup oracle 10g AS Reports server

We shall be focusing on the following aspects of reports engine/server

•Accepting the report request from various clients.

•Scheduling the jobs to run.

•Managing Oracle Reports engines

•Managing the cache

•Managing various destinations

•Security check

•Managing the jobstore (persistent job data)

Ok folks, here we go

1. Finding out the values for the following elements initEngine, maxEngine, and minEngine in the server configuration file:


maxIdle="30" callbackTimeOut="90000">

Calculation and calculations and calculations :(
lets assume:

a) average time in seconds to run a report = 10
b) minimum and maximum numbers of requests to run reports per minute = 5 to 50

now lets convert average run time in minutes = 10/60 = 0.16666

•initEngine = ( average time to run report) * (minimum report requests input rate) = 0.16666 * 5 = 1

•maxEngine = (average time to run report) * (maximum report requests input rate) = .016666 * 50 = 8

•minEngine = Depending on the kind of load, anything between 0 to initEngine

minEngine=1 and maxEngine=8 ensures that whenever a job arrives, it gets an idle engine immediately.

Note:
If you are not using the URL engine, comment the engine element with ID="rwURLEng" in the server configuration file.


2.Lets talk about cacheSize, maxQueueSize, and the EXPIRATION.

EXPIRATION: Cache to hold jobs for certain number of minutes. If EXPIRATION=480, jobs will keep in cache for 4 hours (480 minutes). Therefore, maxQueueSize should be set to accommodate all the jobs for 4 hours. Thus, at a rate of 10 jobs per minute:

maxQueueSize = (report requests input rate) * (expiration period) = 480 * 10 =4800.

The value of cacheSize also should be set sufficiently high to accommodate 4800 jobs. Suppose the average size of each report is 100K:

cacheSize = (maxQueueSize) * (average size of report) = 4800*100/1000 = 480MB



3.Setting up the engineResponseTimeOut


maxEngine="8" minEngine="1" engLife="50" maxIdle="30"
callbackTimeOut="90000" engineResponseTimeOut="10">

The value of engineResponseTimeOut should be set to the maximum time a report takes in the set of reports you have. For example, if you have set of reports that takes 10 seconds to 10 minutes to run, you can set engineResponseTimeOut="10" (10 minutes).


Note:

It is always better to run batch reports on a separate server with different engineResponseTimeOut values. Do not submit interactive and batch reports to same server.


4.Set the maxConnect attribute of the connection element in the server configuration file;



Simple, make the value a little higher than simultaneous clients, say expected simultaneous clients are about 150, you can set maxConnect to any value above 150. You can use a safety factor of 10% to 20%, as follows:

maxConnect = 150 + 150 * 0.2 = 180

5.Set the HTTP timeout value (applicable to AS only).

The HTTP timeout value should be set based on the time required to run the longest report in the system. If longest-running report takes 20 minutes to run, HTTP timeout should be more than 20 minutes. Otherwise, an HTTP timeout error will display when the report is still running in the server. This parameter can be set in the ORACLE_HOME/Apache/Apache/conf/httpd.conf file.

Thanks to OTN