Why are there many database connections opened by Oracle BI?

Okay, let's start blogging!

Currently I'm in a project that requires me to be able to use Business Intelligence tool: Oracle Business Intelligence Suite Enterprise Edition. Actually it was Siebel's but then -as some big companies love to do- Oracle 'take' it. Oracle, yeah it's expensive! So from that time me and my friend have been trying to learn it and here I want to share.

Let's go to the first issue I want to share.

Why are there many database connections opened by Oracle BI?

If you notice in your database activities that there so many connections opened by Oracle BI but they do nothing (idle). This becomes a problem when DBA(s) limit the connection of user for BIEE application, and they shout at you that BIEE users open connection without doing anything (you should be grateful if your DBA is patience).

Connection idle (opened by Oracle BI)

Actually Oracle BI enables us to use one connection for more than one request. The way it does is by remaining open after one request completed, so if you run another request it won’t open another connection instead it uses the remaining one.

You can set the time for Oracle BI to remain open, by setting Timeout property in database properties options. To change the properties you can follow these steps:

1. Open rpd you want to set in Administration tool
2. On the Physical Layer, right click the connection pool

Connection Pool

3. You can find Timeout property

Connection Pool Properties Dialog Box

4. Set the connection timeout for the connection

Note from help

Setting the timeout depends on your need. Ask your DBA also to have discussion how long it should be specified.