Oracle Pooling

[Rob]

We have created a windows service that takes many requests and have turned on pooling. the service keeps crashing and it appears to be at the point it tries to get a new session.

When I query the database pooling data using SELECT * FROM V$CPOOL_STATS it shows the # of open and busy sessions going up and down.

However when we query the Count property of TOracleSessionPool the value never goes down. When the service is started it starts at 1 and just goes up from there. As sessions are released in Oracle the count never decreases in the application.

The PoolType propery is set to ptOracle and the StatementCache is set to true.

What am I missing?

 

[Rob]

To clarify the crashing it does it about a dozen times a day but it also successfully gets a new session correctly at least hundreds of times a day.

 

[Marco Kalter]

It's difficult to say why it would crash. Can you change the PoolType to ptInternal and check if this affects the issue?

 

[Rob]

I believe the only choices for PoolType are ptDefault and ptOracle (we have ptOracle)
The pooling option of the session has spNone, spInternal and spMTS (we have spInternal)
I tried changing PoolType to ptDefault but it crashed almost right away.
Should I expect the TOracleSessionPool.Count to go up but never down, even when the sessions from V$CPOOL_STATS are going up and down?

 

[Marco Kalter]

The Count will increase or decrease depending on the Min, Max and TimeOut properties, and whether or not you call the CompressOld procedure.