We should have DBA's input for deciding connection pool settings-
Maximum
Connections:
For each
connection pool, you must specify the maximum number of concurrent
connections allowed. After this limit is reached, the Oracle BI Server routes
all other connection requests to another connection pool or, if no other connection pools
exist, the connection request waits until a connection becomes available.
Lets say the
value is set to 1. And two reports are simultaneously executed in Answers. Then
we will see following in nqserver.log- (even if “Enable Connection Pooling” is
checked because there is no connection available. There is only one connection
which is busy in executing one report)
[2014-12-24T00:30:01.000+05:30]
[OracleBIServerComponent] [NOTIFICATION:1] [] [] [ecid:
47a9e22c3d5d6da6:-5024d0d:14a78575d17:-8000-00000000000002e0] [tid: 2c1c] [nQSError: 13013] Init block, 'DUAL Num
(=3)', has more variables than the query select list.
[2014-12-24T00:31:52.000+05:30]
[OracleBIServerComponent] [WARNING:1] [] [] [ecid:
47a9e22c3d5d6da6:-5024d0d:14a78575d17:-8000-0000000000000416] [tid: 2c18] Physical
connections limit is reached for connection pool "Star_HOME_Connection
Pool". Start delaying the queries.
[2014-12-24T00:32:20.000+05:30]
[OracleBIServerComponent] [WARNING:1] [] [] [ecid:
47a9e22c3d5d6da6:-5024d0d:14a78575d17:-8000-0000000000000409] [tid: 2508]
Physical queries backlog caused by connection shortage for connection pool
"Star_HOME_Connection Pool" is cleared
[2014-12-24T00:36:01.000+05:30]
[OracleBIServerComponent] [WARNING:1] [] [] [ecid:
47a9e22c3d5d6da6:-5024d0d:14a78575d17:-8000-000000000000050e] [tid: 3940] Physical connections limit is
reached for connection pool "Star_HOME_Connection Pool". Start delaying the queries.
[2014-12-24T00:36:24.000+05:30]
[OracleBIServerComponent] [WARNING:1] [] [] [ecid: 47a9e22c3d5d6da6:-5024d0d:14a78575d17:-8000-000000000000050a]
[tid: 3a40] Physical queries backlog caused by connection shortage for
connection pool "Star_HOME_Connection Pool" is cleared
Enable
Connection Pooling:
When selected,
allows a single database connection to remain open for the specified time for
use by future query requests. Connection pooling saves the overhead of opening
and closing a new connection for every query. If you do not select this option,
each query sent to the database opens a new connection.
Shared Logon:
If shared logon has been enabled for a particular connection pool, then the
cache (BI Server Cache, not Presentation Server Cache) can be shared across
users and does not need to be seeded for each user. If shared logon has not
been enabled and a user-specific database login is used, then each user
generates their own cache entries.
Clear cache in
the administration tool. Close cursor in web site.
Run a analysis (for a table that has cache enabled checked in the RPD. And of course the cache setting is on at global level i.e. in NQSConfig.ini. )
Now run the
analysis using a user let’s say varora,
create a report by pulling columns from the table (for which you have cache enabled)
Check the rpd-
(cache entry should be there)
Now open another
session and login with another user (let’s say ajain):
Check the rpd, there
is no entry for ajain (since cache is
shared as shared logon is selected for the connection pool)
There is Last Used value. But no new cache entry.
Check
presentation server cache:
(There is entry
for ajain): (So Shared Logon affects only BI Server cache)
