Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 439 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
PS_TXN table issue

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"
Answers
-
Timo Hahn Senior Principal Technical Consultant - Oracle ACE Director Member, Moderator Posts: 38,457 Red Diamond
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
and read
and
as they give you a good overview about am pooling in general.
Timo
-
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:
- 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"
- How many users use the productive system? - Approximately 25-30 users.
- How many concurrent requests are sent by the users? - Its not tracked by the system currently, however, it could be 25-30 users.
- 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?
-
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.
-
Timo Hahn Senior Principal Technical Consultant - Oracle ACE Director Member, Moderator Posts: 38,457 Red Diamond
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
-
The
PS_TXN
TableThe
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 theApplicationModule.passivateState()
method.The
PS_TXN_SEQ
SequenceThis 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 isSTARTS WITH
the integer that is one greater than theMAX(COLLID)
value from the rows inPS_TXN
.Drop the temporary tables / sequence and allow ADF to recreate them.