-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Projects » Search, Validator, Shards

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 3 posts ] 
  Previous topic | Next topic 
Author Message
seemarich
 Post subject: Search fails with SQLException for high no of matches
PostPosted: Wed May 13, 2009 7:38 am 
Beginner
Beginner

Joined: Tue Feb 03, 2009 12:29 pm
Posts: 49
We use Hibernate Search 3.1.x with Hibernate Core 3.3, Hibernate Annotations 3.4, and Hibernate EntityManager 3.4. Our database is Oracle 9i. We are getting an SQLException from Hibernate when the search results are high (around 4000 matches). Is there a limit for the number of matches when using Hibernate search? Is it mandatory to use pagination in such cases?

Code:
May 13, 2009 4:49:38 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 4031, SQLState: 61000
May 13, 2009 4:49:38 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: ORA-04031: unable to allocate 96 bytes of shared memory ("shared pool","select this_.VEHICLE_SEARCH_...","sql area","optdef : apanlg")

05-13 16:49:38 [main] ERROR (com.appl.business.framework.logging.LoggingInterceptor:logException)     - THREW EXCEPTION : public abstract com.appl.common.vehicle.search.dto.ContainerDTO com.appl.business.vehicle.search.service.VehicleSearchService.getSearchedVehicles(com.appl.common.framework.criteria.AbstractSearchCriteria) throws com.appl.common.framework.exception.applBusinessException Exception : com.appl.data.vehicle.search.dao.jpa.VehicleSearchIndexDaoImpl.getSearchedVehicles(VehicleSearchIndexDaoImpl.java:115) com.appl.data.vehicle.search.manager.VehicleSearchIndexDataManager.doSearch(VehicleSearchIndexDataManager.java:70) com.appl.data.framework.service.SearchIndexDataService.doExecute(SearchIndexDataService.java:82) com.appl.data.framework.service.AbstractMappedDataService.execute(AbstractMappedDataService.java:97) com.appl.data.framework.controller.DataServiceControllerImpl.execute(DataServiceControllerImpl.java:109) com.appl.business.framework.manager.AbstractBusinessManager.callDataController(AbstractBusinessManager.java:111) com.appl.business.framework.manager.AbstractBusinessManager.indexedSearch(AbstractBusinessManager.java:235) com.appl.business.vehicle.search.manager.VehicleSearchManagerImpl.getSearchedVehicles(VehicleSearchManagerImpl.java:85) com.appl.business.vehicle.search.service.VehicleSearchServiceImpl.getSearchedVehicles(VehicleSearchServiceImpl.java:119) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:618) org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:304) org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149) org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:77) com.appl.business.framework.transaction.RestartTransactionInterceptor.profileLog(RestartTransactionInterceptor.java:30) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:618) org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:597) org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:583) org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:60) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:54) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:54) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) $Proxy78.getSearchedVehicles(Unknown Source) com.appl.business.vehicle.search.service.VehicleSearchServiceImplTest.testGetVehiclesWithValidMakeAndModelSearch(VehicleSearchServiceImplTest.java:134) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:618) junit.framework.TestCase.runTest(TestCase.java:164) junit.framework.TestCase.runBare(TestCase.java:130) org.springframework.test.ConditionalTestCase.runBare(ConditionalTestCase.java:69) org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests.access$001(AbstractAnnotationAwareTransactionalTests.java:47) org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests$1.run(AbstractAnnotationAwareTransactionalTests.java:115) org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests.runTest(AbstractAnnotationAwareTransactionalTests.java:180) org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests.runTestTimed(AbstractAnnotationAwareTransactionalTests.java:153) org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests.runBare(AbstractAnnotationAwareTransactionalTests.java:111) junit.framework.TestResult$1.protect(TestResult.java:110) junit.framework.TestResult.runProtected(TestResult.java:128) junit.framework.TestResult.run(TestResult.java:113) junit.framework.TestCase.run(TestCase.java:120) junit.framework.TestSuite.runTest(TestSuite.java:228) junit.framework.TestSuite.run(TestSuite.java:223) org.junit.internal.runners.OldTestClassRunner.run(OldTestClassRunner.java:35) org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38) org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460) org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673) org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386) org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
com.appl.data.framework.exception.applDataAccessException: org.hibernate.exception.LockAcquisitionException: could not execute query
      at com.appl.data.vehicle.search.dao.jpa.VehicleSearchIndexDaoImpl.getSearchedVehicles(VehicleSearchIndexDaoImpl.java:115)
      at com.appl.data.vehicle.search.manager.VehicleSearchIndexDataManager.doSearch(VehicleSearchIndexDataManager.java:70)
      at com.appl.data.framework.service.SearchIndexDataService.doExecute(SearchIndexDataService.java:82)
      at com.appl.data.framework.service.AbstractMappedDataService.execute(AbstractMappedDataService.java:97)
      at com.appl.data.framework.controller.DataServiceControllerImpl.execute(DataServiceControllerImpl.java:109)
      at com.appl.business.framework.manager.AbstractBusinessManager.callDataController(AbstractBusinessManager.java:111)
      at com.appl.business.framework.manager.AbstractBusinessManager.indexedSearch(AbstractBusinessManager.java:235)
      at com.appl.business.vehicle.search.manager.VehicleSearchManagerImpl.getSearchedVehicles(VehicleSearchManagerImpl.java:85)
      at com.appl.business.vehicle.search.service.VehicleSearchServiceImpl.getSearchedVehicles(VehicleSearchServiceImpl.java:119)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:618)
      at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:304)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
      at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
      at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:77)
      at com.appl.business.framework.transaction.RestartTransactionInterceptor.profileLog(RestartTransactionInterceptor.java:30)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:618)
      at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:597)
      at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:583)
      at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:60)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
      at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:54)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
      at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:54)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
      at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
      at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
      at $Proxy78.getSearchedVehicles(Unknown Source)
      at com.appl.business.vehicle.search.service.VehicleSearchServiceImplTest.testGetVehiclesWithValidMakeAndModelSearch(VehicleSearchServiceImplTest.java:134)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:618)
      at junit.framework.TestCase.runTest(TestCase.java:164)
      at junit.framework.TestCase.runBare(TestCase.java:130)
      at org.springframework.test.ConditionalTestCase.runBare(ConditionalTestCase.java:69)
      at org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests.access$001(AbstractAnnotationAwareTransactionalTests.java:47)
      at org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests$1.run(AbstractAnnotationAwareTransactionalTests.java:115)
      at org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests.runTest(AbstractAnnotationAwareTransactionalTests.java:180)
      at org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests.runTestTimed(AbstractAnnotationAwareTransactionalTests.java:153)
      at org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests.runBare(AbstractAnnotationAwareTransactionalTests.java:111)
      at junit.framework.TestResult$1.protect(TestResult.java:110)
      at junit.framework.TestResult.runProtected(TestResult.java:128)
      at junit.framework.TestResult.run(TestResult.java:113)
      at junit.framework.TestCase.run(TestCase.java:120)
      at junit.framework.TestSuite.runTest(TestSuite.java:228)
      at junit.framework.TestSuite.run(TestSuite.java:223)
      at org.junit.internal.runners.OldTestClassRunner.run(OldTestClassRunner.java:35)
      at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38)
      at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
      at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
      at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
      at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
      at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: org.hibernate.exception.LockAcquisitionException: could not execute query
      at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:110)
      at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
      at org.hibernate.loader.Loader.doList(Loader.java:2231)
      at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
      at org.hibernate.loader.Loader.list(Loader.java:2120)
      at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:118)
      at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1596)
      at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:306)
      at org.hibernate.search.engine.ObjectLoaderHelper.initializeObjects(ObjectLoaderHelper.java:66)
      at org.hibernate.search.engine.QueryLoader.load(QueryLoader.java:42)
      at org.hibernate.search.query.FullTextQueryImpl.list(FullTextQueryImpl.java:284)
      at org.hibernate.search.jpa.impl.FullTextQueryImpl.getResultList(FullTextQueryImpl.java:92)
      at com.appl.data.framework.dao.jpa.SearchIndexDaoImpl$1.doInJpa(SearchIndexDaoImpl.java:122)
      at org.springframework.orm.jpa.JpaTemplate.execute(JpaTemplate.java:185)
      at org.springframework.orm.jpa.JpaTemplate.execute(JpaTemplate.java:148)
      at com.appl.data.framework.dao.applJpaTemplate.execute(applJpaTemplate.java:287)
      at com.appl.data.framework.dao.jpa.SearchIndexDaoImpl.search(SearchIndexDaoImpl.java:114)
      at com.appl.data.vehicle.search.dao.jpa.VehicleSearchIndexDaoImpl.getSearchedVehicles(VehicleSearchIndexDaoImpl.java:112)
      ... 61 more
Caused by: java.sql.SQLException: ORA-04031: unable to allocate 96 bytes of shared memory ("shared pool","select this_.VEHICLE_SEARCH_...","sql area","optdef : apanlg")

      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
      at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
      at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
      at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:799)
      at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1038)
      at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:839)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1133)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
      at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
      at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
      at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
      at org.hibernate.loader.Loader.doQuery(Loader.java:697)
      at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
      at org.hibernate.loader.Loader.doList(Loader.java:2228)
      ... 76 more
05-13 16:49:38 [Thread-4] INFO  (org.quartz.core.QuartzScheduler:shutdown)     - Scheduler AceClusteredScheduler_$_C520USS1242212747886 shutting down.
05-13 16:49:38 [Thread-4] INFO  (org.quartz.core.QuartzScheduler:standby)     - Scheduler AceClusteredScheduler_$_C520USS1242212747886 paused.
05-13 16:49:38 [Thread-4] INFO  (org.quartz.core.QuartzScheduler:shutdown)     - Scheduler AceClusteredScheduler_$_C520USS1242212747886 shutdown complete.
Top
 Profile  
 
sanne.grinovero
 Post subject: Re: Search fails with SQLException for high no of matches
PostPosted: Wed May 13, 2009 4:58 pm 
Hibernate Team
Hibernate Team

Joined: Fri Oct 05, 2007 4:47 pm
Posts: 2536
Location: Third rock from the Sun
Hi,
it appears this is the cause:
Quote:
SEVERE: ORA-04031: unable to allocate 96 bytes of shared memory ("shared pool","select thi


you are loading too many objects for your buffers; pagination is not mandatory but is recommended.
What is your query meant to do? Are you showing more than 4000 elements to the user?
You could consider using a scrollable resultset.

_________________
Sanne
http://in.relation.to/

Top
 Profile  
 
seemarich
 Post subject: Re: Search fails with SQLException for high no of matches
PostPosted: Thu May 14, 2009 2:11 am 
Beginner
Beginner

Joined: Tue Feb 03, 2009 12:29 pm
Posts: 49
I understand, we would need to have a check in place. We were just running a junit test to see if our search functionality is working correctly for large datasets.

Thanks,
Seema
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 3 posts ] 

Board index » Projects » Search, Validator, Shards

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.