PS_TXN table issue

User_R4C5K

Hello Team,

Currently in production environment, we are getting below error message on most frequently basis. Just above below error message, there is an message for PS_TXN table appears as mentioned below. Can you help us know the suggested solution for this issue? Do we need to increase connection pool size of data source? Also, do we need to clear PS_TXN table on periodic basis? Any help appreciated.

Environment details :

Weblogic 12c

Oracle ADF 12.2.1

"ApplicationDS" pool size : Max capacity : 15, Initial Capacity : 1 Minimum capacity : 1, statement cache size : 10.

Error Message :

"No resources available in the pool "ApplicationDS" to allocate to applications, please increase the size of the pool"

PS_TXN message:

"could not retrieve row from table PS_TXN collection id 2,485,128 persistence id 1"

Timo Hahn

Yes, you should increase the pool size for this connection.

One other question to ask is why do you get the error?

How many users use the productive system?

How many concurrent requests are sent by the users?

Are the application module pool and the DB connection pool configured for this many requests?

As you get PS_TXN entries, your application tries to save state in this table because there are more requests to the app than available application modules. In this case, the framework stores the state of one unused am into the PS_TXN table and loads another state stored by another user into the AM. This is called activation and passivation.

In general, I try to avoid exactly this behavior. There are discussions (here and in other groups) about this. For almost all production systems it turns out that it's better to avoid this storing of a state as it puts load on a system that is under load already.

You can disable the activation/passivation by setting the pool parameters to some specific values. Before going into detail you should view

https://www.youtube.com/watch?v=ebwSO--ZK94

and read

https://www.oracle.com/application-development/technologies/jdeveloper/application-module-pooling.html

and

https://andrejusb.blogspot.com/2010/02/optimizing-oracle-adf-application-pool.html

as they give you a good overview about am pooling in general.

Timo

User_R4C5K

Hi Timo,

Thank you for your help. However, we have few more questions to ask and regarding your questions, following is the answers. Can you let us know the appropriate actions for the same.

Your questions:

1. One other question to ask is why do you get the error? - We dont know, why we get the error, its usually observed by the user that whenever they are on the screen, it suddenly pops out error message as "No resources available in the pool "ApplicationDS" to allocate to applications, please increase the size of the pool"
2. How many users use the productive system? - Approximately 25-30 users.
3. How many concurrent requests are sent by the users? - Its not tracked by the system currently, however, it could be 25-30 users.
4. Are the application module pool and the DB connection pool configured for this many requests? - Following is the details of DB connection pool size, 1. Maximum capacity = 15 2. Initial capacity =1 3. Minimum capacity = 1 4. Statement cache type = LRU 5. statement cache size = 10. What are the recommended values? However, application module size is as follow

However, application module size is as shown in image below. What is the recommended pool size for AM and database connection? 

5. I think we already have disabled, check mark of activation / passivation by unchecking mark against element named "Failover Transaction State Upon Managed Release" to false. Can you let us know what is the parameter you are referring in case to disable / enable activation / passivation?

User_R4C5K

6. Do we need to periodically purge PS_TXN table by running below command through regular db schedule?

PROCEDURE Session_State( olderThan_minutes INTEGER )

Let us know in case any more details required.

AM.JPG

Timo Hahn

You have all the info to find out the right settings for your application.

You know that you need to increase the number of connections to the DB. Look at the documents linked in my other post and tweak the current settings to your need. There is no silver bullet setting. Each application needs to be investigated on the server in real life. The AM pooling parameters look OK, the connection parameter needs to be increased.

Activation/passivation can not be shut down by just one checkmark. So, no, you have not deactivated activation/passivation of application modules. To turn it off, read this thread https://groups.google.com/g/adf-methodology/c/P3pHU2BBPM0

You can purge the PS_TXN table (using scripts provided by Oracle or your own), but this has nothing to do with the error you see.

Timo

dvohra21

The  PS_TXN Table

The  PS_TXN table stores snapshots of pending changes made to BC4J application module instances. The table manages the B-Tree storage of rows. The snapshot information is stored as an XML document that encodes the unposted changes in an application module instance. Only pending data changes are stored in the snapshot, along with information about the current state of active iterators (i.e. "current row" pointers information). The value of the COLLID column corresponds to the value returned by the  ApplicationModule.passivateState() method.

The  PS_TXN_SEQ Sequence

This sequence is used to assign the next persistent snapshot Id for Application Module pending state management. If the  PS_TXN table contains any rows at the time this sequence is created, the sequence is created so that is  STARTS WITH the integer that is one greater than the  MAX(COLLID) value from the rows in  PS_TXN.

Drop the temporary tables / sequence and allow ADF to recreate them.