Forum Stats

  • 3,836,787 Users
  • 2,262,189 Discussions
  • 7,900,109 Comments

Discussions

Thread is stuck while executing query in database

We are using Oracle 19C and Driver OJDBC 8 driver but we are seeing stuck thread while executing query in database. This is happening after our recent migration to 19C oracle database. This is stuck issue is not getting solved until we drop tables.


"task-scheduler-8" #2052 prio=5 os_prio=0 cpu=16735.92ms elapsed=91486.55s allocated=7923M defined_classes=10 tid=0x00007faae400a800 nid=0x2931 runnable [0x00007fa96d8d7000]

  java.lang.Thread.State: RUNNABLE

at sun.nio.ch.FileDispatcherImpl.read0([email protected]/Native Method)

at sun.nio.ch.SocketDispatcher.read([email protected]/Unknown Source)

at sun.nio.ch.IOUtil.readIntoNativeBuffer([email protected]/Unknown Source)

at sun.nio.ch.IOUtil.read([email protected]/Unknown Source)

at sun.nio.ch.IOUtil.read([email protected]/Unknown Source)

at sun.nio.ch.SocketChannelImpl.read([email protected]/Unknown Source)

at oracle.net.nt.TimeoutSocketChannel.read(TimeoutSocketChannel.java:174)

at oracle.net.ns.NSProtocolNIO.doSocketRead(NSProtocolNIO.java:555)

at oracle.net.ns.NIOPacket.readHeader(NIOPacket.java:258)

at oracle.net.ns.NIOPacket.readPacketFromSocketChannel(NIOPacket.java:190)

at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:132)

at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:105)

at oracle.net.ns.NIONSDataChannel.readDataFromSocketChannel(NIONSDataChannel.java:91)

at oracle.jdbc.driver.T4CMAREngineNIO.prepareForUnmarshall(T4CMAREngineNIO.java:764)

at oracle.jdbc.driver.T4CMAREngineNIO.unmarshalUB1(T4CMAREngineNIO.java:429)

at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:407)

at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268)

at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)

at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270)

at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91)

at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:807)

at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:983)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)

at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)

at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)

at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3713)

- locked <0x000000032e251a88> (a oracle.jdbc.driver.T4CConnection)

at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1167)

at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)

at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60)

at org.hibernate.loader.Loader.getResultSet(Loader.java:2265)

at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2028)

at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1990)

at org.hibernate.loader.Loader.doQuery(Loader.java:949)

at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:351)

at org.hibernate.loader.Loader.doList(Loader.java:2787)

at org.hibernate.loader.Loader.doList(Loader.java:2770)

at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2604)

at org.hibernate.loader.Loader.list(Loader.java:2599)

at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:505)

at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:395)

at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:220)

at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1526)

at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1538)

at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1506)

at org.hibernate.query.Query.getResultList(Query.java:132)

at com.querydsl.jpa.impl.AbstractJPAQuery.getResultList(AbstractJPAQuery.java:160)

at com.querydsl.jpa.impl.AbstractJPAQuery.fetch(AbstractJPAQuery.java:202)

at com.hybris.datahub.repository.jpa.impl.DefaultPublicationRetryJpaRepository.incrementRetryCount(DefaultPublicationRetryJpaRepository.java:207)

at jdk.internal.reflect.GeneratedMethodAccessor592.invoke(Unknown Source)

at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke([email protected]/Unknown Source)

at java.lang.reflect.Method.invoke([email protected]/Unknown Source)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)

at org.springframework.transaction.interceptor.TransactionInterceptor$$Lambda$1483/0x00000008013a0440.proceedWithInvocation(Unknown Source)

at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)

at com.sun.proxy.$Proxy119.incrementRetryCount(Unknown Source)

at com.hybris.datahub.service.publication.impl.DefaultPublicationRetryService.createRetriesForNotPublishedItems(DefaultPublicationRetryService.java:106)

at com.hybris.datahub.service.publication.impl.DefaultPublicationRetryService.manageRetriesForPublication(DefaultPublicationRetryService.java:72)

at com.hybris.datahub.service.impl.DefaultTargetSystemPublicationCompletionService.completeCompositeTargetSystemPublication(DefaultTargetSystemPublicationCompletionService.java:126)

at com.hybris.datahub.service.impl.DefaultTargetSystemPublicationCompletionService.completeCompositeTargetSystemPublication(DefaultTargetSystemPublicationCompletionService.java:450)

at com.hybris.datahub.service.impl.DefaultTargetSystemPublicationCompletionService.handleLastSubPublication(DefaultTargetSystemPublicationCompletionService.java:250)

at com.hybris.datahub.service.impl.DefaultTargetSystemPublicationCompletionService.completeTargetSystemPublication(DefaultTargetSystemPublicationCompletionService.java:118)

at jdk.internal.reflect.GeneratedMethodAccessor607.invoke(Unknown Source)

at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke([email protected]/Unknown Source)

at java.lang.reflect.Method.invoke([email protected]/Unknown Source)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)

at org.springframework.transaction.interceptor.TransactionInterceptor$$Lambda$1483/0x00000008013a0440.proceedWithInvocation(Unknown Source)

at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)

at com.sun.proxy.$Proxy175.completeTargetSystemPublication(Unknown Source)

at com.hybris.datahub.service.impl.DefaultPublicationActionService.completeTargetSystemPublication(DefaultPublicationActionService.java:57)

at com.hybris.datahub.pubrecover.impl.DefaultPublicationMonitor.lambda$failPublication$5(DefaultPublicationMonitor.java:114)

at com.hybris.datahub.pubrecover.impl.DefaultPublicationMonitor$$Lambda$2204/0x00000008019abc40.accept(Unknown Source)

at java.util.stream.ForEachOps$ForEachOp$OfRef.accept([email protected]/Unknown Source)

at java.util.stream.ReferencePipeline$2$1.accept([email protected]/Unknown Source)

at java.util.stream.ReferencePipeline$2$1.accept([email protected]/Unknown Source)

at java.util.Iterator.forEachRemaining([email protected]/Unknown Source)

at java.util.Spliterators$IteratorSpliterator.forEachRemaining([email protected]/Unknown Source)

at java.util.stream.AbstractPipeline.copyInto([email protected]/Unknown Source)

at java.util.stream.AbstractPipeline.wrapAndCopyInto([email protected]/Unknown Source)

at java.util.stream.ForEachOps$ForEachOp.evaluateSequential([email protected]/Unknown Source)

at java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential([email protected]/Unknown Source)

at java.util.stream.AbstractPipeline.evaluate([email protected]/Unknown Source)

at java.util.stream.ReferencePipeline.forEach([email protected]/Unknown Source)

at com.hybris.datahub.pubrecover.impl.DefaultPublicationMonitor.failPublication(DefaultPublicationMonitor.java:114)

at com.hybris.datahub.pubrecover.impl.DefaultPublicationMonitor$$Lambda$2148/0x0000000801946440.accept(Unknown Source)

at java.util.stream.ForEachOps$ForEachOp$OfRef.accept([email protected]/Unknown Source)

at java.util.stream.ReferencePipeline$2$1.accept([email protected]/Unknown Source)

at java.util.stream.ReferencePipeline$3$1.accept([email protected]/Unknown Source)

at java.util.HashMap$EntrySpliterator.forEachRemaining([email protected]/Unknown Source)

at java.util.stream.AbstractPipeline.copyInto([email protected]/Unknown Source)

at java.util.stream.AbstractPipeline.wrapAndCopyInto([email protected]/Unknown Source)

at java.util.stream.ForEachOps$ForEachOp.evaluateSequential([email protected]/Unknown Source)

at java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential([email protected]/Unknown Source)

at java.util.stream.AbstractPipeline.evaluate([email protected]/Unknown Source)

at java.util.stream.ReferencePipeline.forEach([email protected]/Unknown Source)

at com.hybris.datahub.pubrecover.impl.DefaultPublicationMonitor.checkPublicationStateAndRecover(DefaultPublicationMonitor.java:72)

at jdk.internal.reflect.GeneratedMethodAccessor618.invoke(Unknown Source)

at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke([email protected]/Unknown Source)

at java.lang.reflect.Method.invoke([email protected]/Unknown Source)

at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:84)

at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)

at java.util.concurrent.Executors$RunnableAdapter.call([email protected]/Unknown Source)

at java.util.concurrent.FutureTask.runAndReset([email protected]/Unknown Source)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run([email protected]/Unknown Source)

at java.util.concurrent.ThreadPoolExecutor.runWorker([email protected]/Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.run([email protected]/Unknown Source)

at java.lang.Thread.run([email protected]/Unknown Source)


  Locked ownable synchronizers:

- <0x000000030a93cba0> (a java.util.concurrent.ThreadPoolExecutor$Worker)

- <0x000000032e257818> (a java.util.concurrent.locks.ReentrantLock$NonfairSync)

Comments

  • user9540031
    user9540031 Member Posts: 222 Gold Badge

    Hello,

    We are seeing stuck thread while executing query in database.

    We're not going to get far by just looking at the stack trace on the client side: it just shows that the client is waiting for the database to answer a database call. So what is the corresponding database session on the server doing (if anything) at the same time? We cannot guess.

    Please reach your database administrators, and have them do the performance troubleshooting accordingly.

    This is stuck issue is not getting solved until we drop tables.

    Dropping tables, really? Well that's a bit harsh! Surely a DBA could simply kill the hanging DB sessions.

    This is happening after our recent migration to 19C oracle database.

    From the above "resolution" method, I gather that this migration project is still in development, not in production, isn't it? Rough edges to polish, obviously...

    Driver OJDBC 8 driver

    The exact version of the Oracle JDBC driver can be found in the META-INF/MANIFEST.MF file in the JAR file of the driver. You should check that, and have your DBA verify that this specific version is certified against your version of the JDK and the version of the database. If it is not, obviously you'll have to update the driver version.

    Regards,

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,578 Red Diamond

    It is quite common that after upgrade query that was completing in seconds in old version runs "forever" in new. So first you need to see if execution plan didn't change in new version and if it did you need to investigate why and how to fix it if new plan is sub-optimal. That's why we usually have performance testing environment where we performance test database in new version before upgrading production.

    SY.