Cognos Planning Contributor

Information for Oracle DBA's

Overview

The information in this article is based upon Cognos Planning Contributor version 8.1.

If you are using Cognos Planning Contributor running against an Oracle 9i or 10g database platform there are a number of things you may want to know. Amongst these are recommended memory sizing, required user rights for the Cognos user and sizing guidelines for your disks.

Oracle SGA sizing

There is no easy calculation for displaying the correct memory settings for use with Oracle and Cognos Contributor, however for most customers a base of around 350MB SGA should be a good start. This will obviously need to increase based on a number of factors.

• The number of Job Servers / CPU's - The more CPU's and Servers you have pointing at your Contributor Datastores the more memory will be required due to the increased number of processes spawned.
• The number of Web Users - Again the more users you have, the greater the number of reads and writes your database will have.
• The number of Contributor Applications - The greater the number of Contributor Applications and Publish Containers, the greater the number of processes reading and writing the database you will have.
• The size of your Contributor Applications - Not only will larger models require more memory simply to load but when running intensive jobs such as Publish and Import, the greater the number of rows of data being read / written at a time.

Don't forget the processes!

A user process within Oracle is directly related to a user connection to the system. A typical Cognos Planning Job server with 4 CPU's will likely have 4 copies of the jhost running at any one time, this process connects to Oracle to monitor applications in your "Monitored Applications" section of the Administration Console.

If you had 10 Contributor Applications this would mean that 40 connections to the Oracle instance are being used just by the one job server monitoring for new jobs. Consider also that the default monitoring interval is 15 seconds and you will find you can easily have 40 x 15 connections to Oracle in this configuration as the old connections may not be closed off quickly. If you add a second job server you can double this amount of connections again. 80 x 15.

There is little or no overhead associated with setting a high number of processes in your initialization parameters.

User Privilleges requried for the Cognos User

The script commonly used to create the Cognos system user within Oracle is shown below.

create user COGNOSEP identified by COGNOSEP
default tablespace users
quota unlimited on users
temporary tablespace temp
quota unlimited on temp;
GRANT
CREATE SESSION,
CREATE USER,
CREATE ANY INDEX,
CREATE ANY SEQUENCE,
CREATE ANY SYNONYM,
CREATE ANY TABLE,
CREATE ANY TRIGGER,
CREATE ANY VIEW,
DELETE ANY TABLE,
INSERT ANY TABLE,
UPDATE ANY TABLE,
SELECT ANY TABLE,
DROP ANY VIEW,
DROP ANY TABLE /* required to truncate tables */,
EXP_FULL_DATABASE /* Optional if backup via Administration Console is
desired */,
GRANT DROP USER /* Optional for ability to drop applications, or DBA can
drop user*/
TO COGNOSEP;

Redo Logs

It is a good idea to increase the number and size of the redo logs significantly from the default settings, as a base setting perhaps consider 5x 512mb redo logs which should provide adequate capacity whilst reducing the risk of locking the system whilst waiting for the logs to be backed up and recycled.

Cognos Planning can rapidly fill the redo logs during job activity, especially when there are a large number of job server processors.