Load failed : ORA-04031 : JBoss 3.2.3 CMP findAll

[jobor]

Hello,
A simple CMP bean on a Oracle table with 1200 records is causing an error : javax.ejb.EJBException: Load failed; CausedByException is:
ORA-04031: unable to allocate 232 bytes of shared memory ("shared pool","SELECT frm_code, frm_name, f...","sql area","qerixs : rixalo")

System : Oracle 8.1.7 : ojdbc14.jar : JBoss 3.2.3 (also 3.2.5) : jdk 1.4.2_04-b05 : local-tx-datasource

I'm using normal getters with default transaction attributes. I'm accessing the CMP bean through a session bean. This is the method in the session bean:
/**
* @ejb.interface-method
* @ejb.transaction
* type="Required"
*/
public Collection getProductionFirms() {
try {
Context context = new InitialContext();
ProductionFirmLocalHome home = (ProductionFirmLocalHome) context.lookup(ProductionFirmLocalHome.COMP_NAME);
Collection c = home.findAll();
Collection tos = new ArrayList(c.size());
for (Iterator i = c.iterator(); i.hasNext();) {
ProductionFirmLocal productionFirm = (ProductionFirmLocal) i.next();
ProductionFirmTO to = new ProductionFirmTO(productionFirm.getCode(), productionFirm.getName(),
productionFirm.getIndependent(), new DomainItemTO(productionFirm.getCountryCode(), productionFirm.getCountryName()));
tos.add(to);
}
return tos;
} catch (NamingException e) {
throw new EJBException(e);
} catch (FinderException e) {
throw new EJBException(e);
}
}


I turned on the trace on org.jboss.tm and org.jboss.ejb.plugins.

JBoss first finds the PK's (1200). Then JBoss writes a SQL statement like "Select ... from ... where (PK = ?) OR (PK=?) OR (PK=?) OR (PK=?) etc.
Then is JBoss is setting the parameters 1200 times. But at 1000 it gives the Oracle error.
This is the last snippet of the trace :
2004-09-22 23:20:07,119 TRACE [org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMP2xFieldBridge.ProductionFirm#code] Set parameter: index=999, jdbcType=VARCHAR, value=PDB
2004-09-22 23:20:07,119 TRACE [org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMP2xFieldBridge.ProductionFirm#code] Set parameter: index=1000, jdbcType=VARCHAR, value=PEARC
2004-09-22 23:20:14,181 TRACE [org.jboss.ejb.plugins.LRUEnterpriseContextCachePolicy] Resized cache for bean ProductionFirm: old capacity = 1000000, new capacity = 50
2004-09-22 23:20:17,010 TRACE [org.jboss.ejb.plugins.LRUEnterpriseContextCachePolicy] entryRemoved, entry=key: null, object: null, entry: 966905
2004-09-22 23:20:17,010 TRACE [org.jboss.ejb.plugins.EntityInstanceInterceptor] Ending invoke, exceptionThrown, ctx=org.jboss.ejb.EntityEnterpriseContext@857066
javax.ejb.EJBException: Load failed; CausedByException is:
ORA-04031: unable to allocate 232 bytes of shared memory ("shared pool","SELECT frm_code, frm_name, f...","sql area","qerixs : rixalo")

at org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.execute(JDBCLoadEntityCommand.java:232)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.execute(JDBCLoadEntityCommand.java:72)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.loadEntity(JDBCStoreManager.java:612)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.loadEntity(JDBCStoreManager.java:594)
at org.jboss.ejb.plugins.CMPPersistenceManager.loadEntity(CMPPersistenceManager.java:381)
at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.loadEntity(CachedConnectionInterceptor.java:352)
at org.jboss.ejb.plugins.EntitySynchronizationInterceptor.invoke(EntitySynchronizationInterceptor.java:239)
at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:185)
at org.jboss.ejb.plugins.EntityReentranceInterceptor.invoke(EntityReentranceInterceptor.java:114)
at org.jboss.ejb.plugins.EntityInstanceInterceptor.invoke(EntityInstanceInterceptor.java:163)
at org.jboss.ejb.plugins.EntityLockInterceptor.invoke(EntityLockInterceptor.java:89)
at org.jboss.ejb.plugins.EntityCreationInterceptor.invoke(EntityCreationInterceptor.java:54)
at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:84)
at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:267)
at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:128)
at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:118)
at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:191)
at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:122)
at org.jboss.ejb.EntityContainer.internalInvoke(EntityContainer.java:489)
at org.jboss.ejb.Container.invoke(Container.java:700)
at org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invoke(BaseLocalProxyFactory.java:375)
at org.jboss.ejb.plugins.local.EntityProxy.invoke(EntityProxy.java:38)
at $Proxy37.getCode(Unknown Source)
at nl.vda.won.tv.j2ee.ejb.DomainAgentBean.getProductionFirms(DomainAgentBean.java:94)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:683)
at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:185)
at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:72)
at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:84)
at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:267)
at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:128)
at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:118)
at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:191)
at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:122)
at org.jboss.ejb.StatelessSessionContainer.internalInvoke(StatelessSessionContainer.java:331)
at org.jboss.ejb.Container.invoke(Container.java:700)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
at org.jboss.invocation.jrmp.server.JRMPInvoker.invoke(JRMPInvoker.java:367)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:261)
at sun.rmi.transport.Transport$1.run(Transport.java:148)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.Transport.serviceCall(Transport.java:144)
at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:460)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:701)
at java.lang.Thread.run(Thread.java:534)


The error is reproducable and occurs under the Required transaction attribute of the session method. It does not appear when I change the transaction attribute to NotSupported (but that should be a bad idee)

Anybody any idee?

T.I.A.
Johan Borchers

[aloubyansky]

Try using on-find read-ahead, probably, with some specific load-group. Also check our optimized loading in our on-line docs.

[jobor]

Thanks for the answer.

But still in basic I do think that I'm doing nothing wrong. It may be not efficient but it should work I think.

I will read the docs about load groups.

Johan.

[antioh]

please increase shared_pool_size parameter
of Oracle instance and monitor SGA.

[jobor]

The shared pool size is 50 Meg.

I did try all versions of Oracle JDBC drivers from 8.1.7 to 10g.

I do have the same problem with deleting 1200 records.
The following code is from a session bean method with a Required transaction.
Collection c = home.findAllForDelete();
for (Iterator i = c.iterator(); i.hasNext();) {
ProductionFirmLocal productionFirm = (ProductionFirmLocal) i.next();
productionFirm.remove();
}
There is a sort of default page size of 1000. But when JBoss tries to begin at the deletion of the part after 1000 the error comes.
You can see that in the log files.

I also switched to my local MySql on my laptop. No problem at all doing the same things with CMP.

CMP seems to do something strange to our Oracle database so it can't allocate memory for executing SQL statements.

Johan

[antioh]

i am sorry for poor explanation.
i mean increase shared_pool_reserved_size
parameter.
Default value is 5% of shared_pool_size
U can increase shared_pool_size or
set shared_pool_reserved_size to 5mb
for example.

[darwinl]

Just from a J2EE design perspective, using CMP to retrieve 1200 records are simply not a good idea.

The container will cache all these CMPs, which will then takes up all the memory.

Do you need these beans to be cached? If not, you will be better off to use a DAO pattern ( ie a jdbc sql statement that returns the 1200 records ) The performance will be significantly increased, and you don't need to tune any memory setting.

[jobor]

Hello,

Thanks for the answers. Yes retrieving 1200 records and then deleting them is not very efficient. I my opinion 1200 is not so much but creating 1200 EJB's for nothing is a waste of time and resources. Removing 1200 records with 1 SQL statement is much faster. Also iterating over an array with PK's and deleting them one by one with a prepared statement is faster than the overhead of CMP.
100% CMP is not always the solution. I always wrote EJB's (Session) talking directly to databases and now I'm experimenting with CMP.
I'm a little dissapointed but still I think CMP has good things.

Doing things inefficient should work for the Oracle databases. The errors appeared on 2 databases at the office. On metalink there is an article for avoiding ORA-04031 errors. The checks on the databases proved that the shared_pool_size was a candidate for such errors. So thanks for the hints to tune the shared_pool_size. I will ask our dba if he is willing to change settings.

Still I'm curious about the fact why this happens with JBoss CMP. Maybe studying the log file what happens with CMP gives me more knowledge.
At least I know now more about the read ahead stuff.

Johan