Importance of BC4J temp tables(PS_TXN & PS_TXN_SEQ) and Persistent Collections Facility in Oracle ADF

Recently i have seen a new exception in my production environment while 7 users working on deployed application

[2013-08-12T14:06:58.191+05:30] [AdminServer] [WARNING] [ADF_FACES-00009] [oracle.adf.view.rich.component.fragment.UIXRegion] [tid: [ACTIVE].ExecuteThread: '1' for queue: 'weblogic.kernel.Default (self-tuning)'] [userId: 11] [ecid: a8c0f3836ec62479:-26ea53cb:1407155e369:-8000-00000000000006e7,0] [APP: EBIZADF.ear] Error processing viewId: /ApplicationRoleTF/AppRole URI: /AppRole.jsff actual-URI: /AppRole.jsff.[[
oracle.jbo.PCollException: JBO-28030: Could not insert row into table PS_TXN, collection id 420,356, persistent id 1
 at oracle.jbo.PCollException.throwException(PCollException.java:36)
 at oracle.jbo.pcoll.OraclePersistManager.insert(OraclePersistManager.java:1905)
 at oracle.jbo.pcoll.PCollNode.passivateElem(PCollNode.java:564)
 at oracle.jbo.pcoll.PCollNode.passivate(PCollNode.java:688)
 at oracle.jbo.pcoll.PCollNode.passivateBranch(PCollNode.java:647)
 at oracle.jbo.pcoll.PCollection.passivate(PCollection.java:464)
 at oracle.jbo.server.DBSerializer.passivateRootAM(DBSerializer.java:294)
 at oracle.jbo.server.DBSerializer.passivateRootAM(DBSerializer.java:267)
 at oracle.jbo.server.ApplicationModuleImpl.passivateStateInternal(ApplicationModuleImpl.java:6046)
 at oracle.jbo.server.ApplicationModuleImpl.passivateState(ApplicationModuleImpl.java:5906)
 at oracle.jbo.common.ampool.DefaultConnectionStrategy.reconnect(DefaultConnectionStrategy.java:290)
 at oracle.jbo.server.ApplicationPoolMessageHandler.doPoolReconnect(ApplicationPoolMessageHandler.java:609)
 at oracle.jbo.server.ApplicationPoolMessageHandler.doPoolMessage(ApplicationPoolMessageHandler.java:399)
 at oracle.jbo.server.ApplicationModuleImpl.doPoolMessage(ApplicationModuleImpl.java:9053)
 at oracle.jbo.common.ampool.ApplicationPoolImpl.sendPoolMessage(ApplicationPoolImpl.java:4606)
 at oracle.jbo.common.ampool.ApplicationPoolImpl.prepareApplicationModule(ApplicationPoolImpl.java:2536)
 at oracle.jbo.common.ampool.ApplicationPoolImpl.doCheckout(ApplicationPoolImpl.java:2346)
 at oracle.jbo.common.ampool.ApplicationPoolImpl.useApplicationModule(ApplicationPoolImpl.java:3245)
 at oracle.jbo.common.ampool.SessionCookieImpl.useApplicationModule(SessionCookieImpl.java:571)
 at oracle.jbo.http.HttpSessionCookieImpl.useApplicationModule(HttpSessionCookieImpl.java:234)
 at oracle.jbo.common.ampool.SessionCookieImpl.useApplicationModule(SessionCookieImpl.java:504)
 at oracle.jbo.common.ampool.SessionCookieImpl.useApplicationModule(SessionCookieImpl.java:499)
 at oracle.adf.model.bc4j.DCJboDataControl.initializeApplicationModule(DCJboDataControl.java:517)
 at oracle.adf.model.bc4j.DCJboDataControl.getApplicationModule(DCJboDataControl.java:867)
 at oracle.adf.model.binding.DCBindingContainer.findDataControl(DCBindingContainer.java:1659)
 at oracle.adf.model.binding.DCIteratorBinding.initDataControl(DCIteratorBinding.java:2542)
 at oracle.adf.model.binding.DCIteratorBinding.getDataControl(DCIteratorBinding.java:2477)
 at oracle.adf.model.binding.DCIteratorBinding.getCheckedDataControl(DCIteratorBinding.java:2571)
 at oracle.adf.model.binding.DCIteratorBinding.executeQueryIfNeeded(DCIteratorBinding.java:2219)
 at oracle.adf.model.binding.DCBindingContainer.internalRefreshControl(DCBindingContainer.java:3279)
 at oracle.adf.model.binding.DCBindingContainer.refresh(DCBindingContainer.java:2906)
 at oracle.adf.controller.internal.binding.TaskFlowRegionController.doRegionRefresh(TaskFlowRegionController.java:284)
 at oracle.adf.controller.internal.binding.TaskFlowRegionController.refreshRegion(TaskFlowRegionController.java:134)
 at oracle.adf.model.binding.DCBindingContainer.internalRefreshControl(DCBindingContainer.java:3237)
 at oracle.adf.model.binding.DCBindingContainer.refresh(DCBindingContainer.java:2906)
 at oracle.adf.controller.v2.lifecycle.PageLifecycleImpl.prepareModel(PageLifecycleImpl.java:115)
 at oracle.adf.controller.faces.lifecycle.FacesPageLifecycle.prepareModel(FacesPageLifecycle.java:392)
 at oracle.adf.controller.v2.lifecycle.Lifecycle$2.execute(Lifecycle.java:149)
 at oracle.adfinternal.controller.lifecycle.LifecycleImpl.executePhase(LifecycleImpl.java:197)
 at oracle.adfinternal.controller.faces.lifecycle.ADFPhaseListener.access$400(ADFPhaseListener.java:23)
 at oracle.adfinternal.controller.faces.lifecycle.ADFPhaseListener$PhaseInvokerImpl.startPageLifecycle(ADFPhaseListener.java:238)
 at oracle.adfinternal.controller.faces.lifecycle.ADFPhaseListener$1.after(ADFPhaseListener.java:274)
 at oracle.adfinternal.controller.faces.lifecycle.ADFPhaseListener.afterPhase(ADFPhaseListener.java:75)
 at oracle.adfinternal.controller.faces.lifecycle.ADFLifecyclePhaseListener.afterPhase(ADFLifecyclePhaseListener.java:53)
 at oracle.adfinternal.view.faces.lifecycle.LifecycleImpl._executePhase(LifecycleImpl.java:447)
 at oracle.adfinternal.view.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:202)
 at javax.faces.webapp.FacesServlet.service(FacesServlet.java:308)
 at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)
 at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)
 at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:301)
 at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)
 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
 at oracle.adf.model.servlet.ADFBindingFilter.doFilter(ADFBindingFilter.java:173)
 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
 at oracle.adfinternal.view.faces.webapp.rich.RegistrationFilter.doFilter(RegistrationFilter.java:125)
 at org.apache.myfaces.trinidadinternal.webapp.TrinidadFilterImpl$FilterListChain.doFilter(TrinidadFilterImpl.java:468)
 at oracle.adfinternal.view.faces.activedata.AdsFilter.doFilter(AdsFilter.java:60)
 at org.apache.myfaces.trinidadinternal.webapp.TrinidadFilterImpl$FilterListChain.doFilter(TrinidadFilterImpl.java:468)
 at org.apache.myfaces.trinidadinternal.webapp.TrinidadFilterImpl._doFilterImpl(TrinidadFilterImpl.java:293)
 at org.apache.myfaces.trinidadinternal.webapp.TrinidadFilterImpl.doFilter(TrinidadFilterImpl.java:199)
 at org.apache.myfaces.trinidad.webapp.TrinidadFilter.doFilter(TrinidadFilter.java:92)
 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
 at oracle.adf.library.webapp.LibraryFilter.doFilter(LibraryFilter.java:180)
 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
 at oracle.security.jps.ee.http.JpsAbsFilter$1.run(JpsAbsFilter.java:119)
 at oracle.security.jps.util.JpsSubject.doAsPrivileged(JpsSubject.java:315)
 at oracle.security.jps.ee.util.JpsPlatformUtil.runJaasMode(JpsPlatformUtil.java:442)
 at oracle.security.jps.ee.http.JpsAbsFilter.runJaasMode(JpsAbsFilter.java:103)
 at oracle.security.jps.ee.http.JpsAbsFilter.doFilter(JpsAbsFilter.java:171)
 at oracle.security.jps.ee.http.JpsFilter.doFilter(JpsFilter.java:71)
 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
 at oracle.dms.servlet.DMSServletFilter.doFilter(DMSServletFilter.java:139)
 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
 at weblogic.servlet.internal.RequestEventsFilter.doFilter(RequestEventsFilter.java:27)
 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
 at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(WebAppServletContext.java:3730)
 at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3696)
 at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
 at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:120)
 at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2273)
 at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2179)
 at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1490)
 at weblogic.work.ExecuteThread.execute(ExecuteThread.java:256)
 at weblogic.work.ExecuteThread.run(ExecuteThread.java:221) 

And i was not able understand why this exception occurs, i googled about it and found some very interesting facts about BC4J.

BC4J creates temporary database object (PS_TXN and PS_TXN_SEQ), that are used by ADF to maintain state of user session as per DB, it has facility to store temporary data in BLOB column to avoid out of memory problem.
visit this link to read about these objects- (How to manage PS_TXN and PS_TXN_SEQ)
http://www.oracle.com/technetwork/developer-tools/jdev/overview/bc4j-temp-tables-087270.html#ID34

now i have seen cascading exception after this  oracle.jbo.PcollException, PColl refers persistent collection facility.
when ADF was unable to insert data in PS_TXN table, java.sql.SQLException occurs, connection forcefully closed

Caused by: oracle.jbo.JboException: JBO-29000: Unexpected exception caught: java.sql.SQLException, msg=Connection has already been closed.
 at oracle.jbo.server.DBTransactionImpl.getDatabaseProductName(DBTransactionImpl.java:1248)
 at oracle.jbo.server.DBTransactionImpl.getInternalConnection(DBTransactionImpl.java:1355)
 at oracle.jbo.server.DBTransactionImpl.getPersistManagerConnection(DBTransactionImpl.java:1282)
 at oracle.jbo.pcoll.PCollManager.ensureConnection(PCollManager.java:486)
 at oracle.jbo.pcoll.OraclePersistManager.getConnection(OraclePersistManager.java:153)
 at oracle.jbo.pcoll.OraclePersistManager.insert(OraclePersistManager.java:1885)
 at oracle.jbo.pcoll.PCollNode.passivateElem(PCollNode.java:564)
 at oracle.jbo.pcoll.PCollNode.passivate(PCollNode.java:688)
 at oracle.jbo.pcoll.PCollNode.passivateBranch(PCollNode.java:647)
 at oracle.jbo.pcoll.PCollection.passivate(PCollection.java:464)
 at oracle.jbo.server.DBSerializer.passivateRootAM(DBSerializer.java:294)
 at oracle.jbo.server.DBSerializer.passivateRootAM(DBSerializer.java:267)
 at oracle.jbo.server.ApplicationModuleImpl.passivateStateInternal(ApplicationModuleImpl.java:6046)
 at oracle.jbo.server.ApplicationModuleImpl.passivateState(ApplicationModuleImpl.java:5906)

• Now i have counted rows in PS_TXN , there was more 3000 rows, have cleared all rows




• after this again 7 users worked for 3hrs and there was no exception, no bizarre behaviour
• Now once connection is closed, ADF errors starts coming out as NullPointerException



javax.faces.el.EvaluationException: java.lang.NullPointerException
 at org.apache.myfaces.trinidad.component.MethodExpressionMethodBinding.invoke(MethodExpressionMethodBinding.java:51)
 at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:98)
 at org.apache.myfaces.trinidad.component.UIXCommand.broadcast(UIXCommand.java:190)
 at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:783)
 at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1248)



• Finally what i come to know that in order to avoid this kind of exceptions , we have to schedule a job to clear data in PS_TXN

What PS_TXN stores-

• PS_TXN structure- 
  
   

• Suppose you have a search query with more that 10000 records and that is on page as af:table, when user scrolls through table to view records then to avoid out of memory problem bc4j stores extra result set in a temporary storage (PS_TXN) in BLOB column
• When AM (Application Module) passivates and in AM pooling dofailover is set to true,pending state and changes are stored in this table



<AM-Pooling jbo.dofailover="true"/> 





• In case of Multiple users , session information stored in PS_TXN, each user session must be serialised with database, otherwise database object can't behave as per separate user session
• to test a scenario , i have deleted all data from PS_TXN in default HR Schema and created a ADF application  that makes uses of HR's tables

• Run that application , till few operations on application there was no data in PS_TXN table , when i opened application in 3 browser's window and then performed some operations after this there was 2 rows in PS_TXN, it means ADF automatically insert data in PS_TXN for state,session,AM Changes. developer need not to worry about it

At-last summary is - Always keep in mind these db objects if you are facing unexpected behaviour of ADF application in production (multi user) environment, if your application working perfectly on development environment but not in production always check it and schedule a job to clear table PS_TXN