技术文章 - CS376328
批量索引队列处理速度非常慢。
已修改: 13-Mar-2024
适用于
- Windchill PDMLink 11.1 to 12.0
说明
- 批量索引队列处理速度非常慢。
- 一开始，大约四十七个条目（9400个对象）在几分钟内顺利处理，但到了第48个条目时，Bulk Indexing Queue处理没有更多进展。
- 它被卡在等待来自Microsoft SQL Server 的响应，这从后台方法服务器的线程转储中确认如下。
"BulkIndexQueue1.PollingThread" Id=382 prio=5 RUNNABLE (in native)
Blocked (cnt): 96; Waited (cnt): 5
CPU nanos: 4031250000; User nanos: 3875000000; Bytes allocated: 202952528
Method context: 3y854nxqw;l7gbqxym;13876;a8ohgc;204; DB session: 58
% of 1 CPU used by context: 0.3650231796729864 (4000000000/1095820819813); Bytes allocated by context: 198315152
at [email protected]/java.net.SocketInputStream.socketRead0(Native Method)
at [email protected]/java.net.SocketInputStream.socketRead(SocketInputStream.java:115)
at [email protected]/java.net.SocketInputStream.read(SocketInputStream.java:168)
at [email protected]/java.net.SocketInputStream.read(SocketInputStream.java:140)
at app//com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:2023)
at app//com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:6418)
- locked com.microsoft.sqlserver.jdbc.TDSReader@748acda0
at app//com.microsoft.sqlserver.jdbc.TDSReader.nextPacket(IOBuffer.java:6368)
at app//com.microsoft.sqlserver.jdbc.TDSReader.ensurePayload(IOBuffer.java:6346)
at app//com.microsoft.sqlserver.jdbc.TDSReader.readBytes(IOBuffer.java:6647)
at app//com.microsoft.sqlserver.jdbc.TDSReader.readWrappedBytes(IOBuffer.java:6668)
at app//com.microsoft.sqlserver.jdbc.TDSReader.readLong(IOBuffer.java:6641)
at app//com.microsoft.sqlserver.jdbc.ServerDTVImpl.getValue(dtv.java:3787)
at app//com.microsoft.sqlserver.jdbc.DTV.getValue(dtv.java:247)
at app//com.microsoft.sqlserver.jdbc.Column.getValue(Column.java:190)
at app//com.microsoft.sqlserver.jdbc.SQLServerResultSet.getValue(SQLServerResultSet.java:2041)
at app//com.microsoft.sqlserver.jdbc.SQLServerResultSet.getValue(SQLServerResultSet.java:2027)
at app//com.microsoft.sqlserver.jdbc.SQLServerResultSet.getObject(SQLServerResultSet.java:2347)
at app//wt.pds.AttributeItemBuilder.getResultSetObject(AttributeItemBuilder.java:772)
at app//wt.pds.sqlServer.SQLServerResultSetObjectBuilder.getResultSetObject(SQLServerResultSetObjectBuilder.java:57)
at app//wt.pds.AttributeItemBuilder.build(AttributeItemBuilder.java:527)
at app//wt.pds.AttributeACProcessor.processResults(AttributeACProcessor.java:212)
at app//wt.pds.AttributeACProcessor.processResults(AttributeACProcessor.java:83)
at app//wt.pds.SequentialResultBuilder.build(SequentialResultBuilder.java:453)
at app//wt.pds.BasicResultCursor.next(BasicResultCursor.java:93)
at app//wt.pds.SQLDatabasePds.query(SQLDatabasePds.java:880)
at app//wt.pom.PersistentObjectManager.query(PersistentObjectManager.java:1077)
at app//wt.fc.StandardPersistenceManager._query(StandardPersistenceManager.java:1977)
at app//wt.fc.StandardPersistenceManager.query(StandardPersistenceManager.java:1929)
at app//com.ptc.core.query.server.impl.BasicQueryService.execute(BasicQueryService.java:2076)
at app//com.ptc.core.query.server.impl.BasicQueryService.execute(BasicQueryService.java:2003)
at app//com.ptc.core.query.server.impl.BasicQueryService.query(BasicQueryService.java:947)
at app//com.ptc.core.command.server.delegate.entity.RetrievePersistentEntityCommandDelegate.doExecution(RetrievePersistentEntityCommandDelegate.java:211)
at app//com.ptc.core.command.common.delegate.AbstractCommandDelegate.execute(AbstractCommandDelegate.java:216)
at app//com.ptc.core.command.common.CommandDelegateForwarder._remoteExecute(CommandDelegateForwarder.java:284)
at app//com.ptc.core.command.common.CommandDelegateForwarder.remoteExecute(CommandDelegateForwarder.java:232)
at app//com.ptc.core.command.common.CommandDelegateForwarder.execute(CommandDelegateForwarder.java:194)
at app//com.ptc.core.command.common.bean.AbstractServerCommand.execute(AbstractServerCommand.java:202)
at app//com.ptc.core.command.server.delegate.entity.PrepareEntityCommandDelegate.doExecution(PrepareEntityCommandDelegate.java:108)
at app//com.ptc.core.command.common.delegate.AbstractCommandDelegate.execute(AbstractCommandDelegate.java:216)
at app//com.ptc.core.command.common.CommandDelegateForwarder._remoteExecute(CommandDelegateForwarder.java:284)
at app//com.ptc.core.command.common.CommandDelegateForwarder.remoteExecute(CommandDelegateForwarder.java:232)
at app//com.ptc.core.command.common.CommandDelegateForwarder.execute(CommandDelegateForwarder.java:194)
at app//com.ptc.core.command.common.bean.AbstractServerCommand.execute(AbstractServerCommand.java:202)
at app//wt.index.AbstractIndexServiceDelegate.getTypeInstances(AbstractIndexServiceDelegate.java:985)
at app//wt.index.AbstractIndexServiceDelegate.index(AbstractIndexServiceDelegate.java:406)
at app//wt.index.IndexingServiceImpl.processIndexRequest(IndexingServiceImpl.java:285)
at app//wt.index.StandardIndexPolicyManager.processIndexRequest(StandardIndexPolicyManager.java:387)
at [email protected]/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at [email protected]/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at [email protected]/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at [email protected]/java.lang.reflect.Method.invoke(Method.java:566)
at app//wt.services.ServiceFactory$ServerInvocationHandler.invoke(ServiceFactory.java:399)
at app//com.sun.proxy.$Proxy130.processIndexRequest(Unknown Source)
at app//wt.index.IndexingServiceImpl.updateIndexer(IndexingServiceImpl.java:201)
at app//wt.index.StandardIndexPolicyManager.updateIndexer(StandardIndexPolicyManager.java:137)
at [email protected]/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at [email protected]/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at [email protected]/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at [email protected]/java.lang.reflect.Method.invoke(Method.java:566)
at app//wt.queue.QueueEntry.execute(QueueEntry.java:231)
at app//wt.queue.ProcessingQueue.execEntry(ProcessingQueue.java:312)
at app//wt.queue.ProcessingQueue.execEntries(ProcessingQueue.java:898)
at app//wt.queue.PollingQueueThread.run(PollingQueueThread.java:102)下面的SQL消耗了非常长的时间。通过执行SQL Server 性能诊断脚本证实了这一点。
/* This query text was retrieved from showplan XML, and may be truncated. */ SELECT A0.blob$entrySetadHocAcl, A0.classnameA2A2, A0.branchIdA2typeDefinitionRefe, A0.idA3A2ownership, A0.idA2A2, A0.typeadministrativeLock, A0.classnamekeydomainRef, A0.securityLabels, A0.entrySetadHocAcl, A0.statestate, A0.idA3domainRef, A0.classnamekeyA2ownership, A0.idA2A2, A0.updateCountA2, A0.classnameA2A2, A0.branchIditerationInfo, A0.branchIdA2typeDefinitionRefe, A1.idA2A2, A1.updateCountA2, A1.defId, A1.vt, A1.nv, A1.np, A1.sv, A1.sv2, CONVERT(VARCHAR, A1.tv, 120), A1.defId FROM WTPart A0, ( SELECT A0.idA2A2 idA2A2, A0.updateCountA2 updateCountA2, A0.hierarchyIDA6 hid, A0.idA3A6 defId, A0.idA3A4 idA3A4, 3 vt, CONVERT(DECIMAL(28), NULL) nv, CONVERT(DECIMAL(28), NULL) np, A0.value sv, A0.value2 sv2, CONVERT(DATETIME, NULL, 120) tv FROM StringValue A0 WHERE ( ( A0.idA3A6 IN ( SELECT item FROM #wtarray_NUMBER_0_200 ) ) AND ( (A0.idA3A5 = @P0) OR (A0.idA3A5 IS NULL) ) ) ) A1 WHERE ( (A0.branchIdA2typeDefinitionRefe = @P1) AND (A1.idA3A4 = A0.idA2A2) ) AND (A0.markForDeleteA2 = 0) AND ( A0.idA2A2 IN ( @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10, @P11, @P12, @P13, @P14, @P15, @P16, @P17, @P18, @P19, @P20, @P21, @P22, @P23, @P24, @P25, @P26, @P27, @P28, @P29, @P30, @P31, @P32, @P33, @P34, @P35, @P36, @P37, @P38, @P39, @P40, @P41, @P42, @P43, @P44, @P45, @P46, @P47, @P48, @P49, @P50, @P51, @P52, @P53, @P54, @P55, @P56, @P57, @P58, @P59, @P60, @P61, @P62, @P63, @P64, @P65, @P66 ) )
| 最后执行时间 | 处决 | 总安全 | 平均秒数 | 平均CPU安全 | 平均逻辑读取数 | 平均物理读取数 | 平均行数 |
|---|---|---|---|---|---|---|---|
| 2022-09-02 22:49:07 | 1 | 39783 | 39783 | 1070 | 372366628 | 36156354 | 2939 |
这是文章 376328 的 PDF 版本，可能已过期。最新版本 CS376328