OBIEE11g: Slow Performance on Database and Inactive Database Sessions

This is one of the important issues which I have faced at most of the implementations I have been part of. I have seen many places where lots of analytics database sessions are inactive or in cancelled stages and affecting overall performance of the OBIEE 11g.

This blog is an attempt to list down the steps needs to be done to deal with the inactive and cancelled sessions on OBIEE 11g. The instructions are based on the Oracle Support Doc ID 1340182.1.

Set Max Time Parameter  

The first step is to set "Max Time". The setting "Max Time" will terminate any user request that exceeds the set limit. That termination includes sending a cancel request to the data source and closing any database gateway threads opened for the cancelled request. The only caveat is that the communication protocol used in the connection pool and data source should support such request.

To specify the maximum time a query can run on a database, in the Max Time (Minutes) column, enter the maximum number of minutes you want queries to run on each database object. Then, in the Status Max Time field, select one of the following options for each database:

Enable: This limits the time to the value specified.

Disable: Disables any limits set in the Max Time field.

Warn: Does not enforce limits, but logs queries that exceed the set time limit in the Query log.

Ignore: Limits are inherited from the parent application role. If there is no time limit to inherit, no limit is enforced.

This is done via BI Administrator client tool and can be done at Group/Role or at user Level. A sample screen snapshot is shown below.

Set UnaccessedRunningTimeoutMinutes Parameter

The second step is to set parameter "UnaccessedRunningTimeoutMinutes" in instanceconfig.xml file. OBIEE 11g will close a database session that has passed the time set in

"UnaccessedRunningTimeoutMinutes" parameter.

The parameter should be used in the <ODBC> tags within <ServerInstance> tags. 

<ServerInstance>

<!-- others ....-->

<ODBC>

<UnaccessedRunningTimeoutMinutes>2</UnaccessedRunningTimeoutMinutes>

</ODBC>

</ServerInstance>

Important Note

There is a distinction between the query and session in OBIEE 11g. When a cancel is issued, the query is cancelled, but that does not mean the session will be closed. Database connections belong to connection pools and when the query that is being run is completed or cancelled, the connection will go into the pool and be used for other queries.

The session will be closed only if the time off setting is met, and more connections than the minimum value (DB_GATEWAY_THREAD_RANGE = 40-200) from the nqsconfig.ini file are open.  

I hope you find this information useful.