Windchill 中性能不佳的 WTGroup SQL 需要索引

• Oracle 诊断报告中发现性能不佳的 WTGroup 查询：

 SELECT 'wt.org.WTGroup',A0.blob$entrySetadHocAcl,A0.classnamekeycontainerReferen,A0.idA3containerReference,A0.description,A0.disabled,A0.classnamekeydomainRef,A0.idA3domainRef,A0.entrySetadHocAcl,A0.inheritedDomain,A0.internal,A0.name,A0.repairNeeded,A0.replicate,A0.securityLabels,TO_CHAR(A0.createStampA2,'dd mm yyyy hh24:mi:ss'),A0.markForDeleteA2,TO_CHAR(A0.modifyStampA2,'dd mm yyyy hh24:mi:ss'),A0.idA2A2,A0.updateCountA2,TO_CHAR(A0.updateStampA2,'dd mm yyyy hh24:mi:ss') FROM WTGroup A0 WHERE ((A0.name =:1 )) SELECT 'wt.org.WTGroup',A0.administrativeLockIsNull,A0.typeadministrativeLock,A0.blob$entrySetadHocAcl,A0.classnamekeycontainerReferen,A0.idA3containerReference,A0.description,A0.disabled,A0.classnamekeydomainRef,A0.idA3domainRef,A0.entrySetadHocAcl,A0.inheritedDomain,A0.internal,A0.name,A0.repairNeeded,A0.replicate,A0.securityLabels,TO_CHAR(A0.createStampA2,'dd mm yyyy hh24:mi:ss'),A0.markForDeleteA2,TO_CHAR(A0.modifyStampA2,'dd mm yyyy hh24:mi:ss'),A0.idA2A2,A0.updateCountA2,TO_CHAR(A0.updateStampA2,'dd mm yyyy hh24:mi:ss') FROM WTGroup A0 WHERE ((A0.name = :1 ) AND(A0.disabled = :2 ) AND (A0.internal = :3 )) AND (A0.markForDeleteA2 =0) SELECT 'wt.org.WTGroup',A0.administrativeLockIsNull,A0.typeadministrativeLock,A0.blob$entrySetadHocAcl,A0.classnamekeycontainerReferen,A0.idA3containerReference,A0.description,A0.disabled,A0.classnamekeydomainRef,A0.idA3domainRef,A0.entrySetadHocAcl,A0.inheritedDomain,A0.internal,A0.name,A0.repairNeeded,A0.replicate,A0.securityLabels,A0.status,TO_CHAR(A0.createStampA2,'dd mm yyyy hh24:mi:ss'),A0.markForDeleteA2,TO_CHAR(A0.modifyStampA2,'dd mm yyyy hh24:mi:ss'),A0.idA2A2,A0.updateCountA2,TO_CHAR(A0.updateStampA2,'dd mm yyyy hh24:mi:ss') FROM WTGroup A0 WHERE ((A0.name =:1 )) AND (A0.markForDeleteA2 = 0)

• 通过自定义组选择器搜索用户需要很长时间才能返回结果，数据库中相关查询脚本：

 SELECT 'wt.org.WTGroup',A0.administrativeLockIsNull,A0.typeadministrativeLock,A0.blob$entrySetadHocAcl,A0.classnamekeycontainerReferen,A0.idA3containerReference,A0.description,A0.disabled,A0.classnamekeydomainRef,A0.idA3domainRef,A0.entrySetadHocAcl,A0.inheritedDomain,A0.internal,A0.name,A0.remoteDirectoryServerId,A0.repairNeeded,A0.replicate,A0.securityLabels,A0.status,TO_CHAR(A0.createStampA2,'dd mm yyyy hh24:mi:ss'),A0.markForDeleteA2,TO_CHAR(A0.modifyStampA2,'dd mm yyyy hh24:mi:ss'),A0.idA2A2,A0.updateCountA2,TO_CHAR(A0.updateStampA2,'dd mm yyyy hh24:mi:ss') FROM WTGroup A0 WHERE (((A0.name IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?))) AND (A0.idA3containerReference IN (?,?,?,?,?)) AND (A0.internal = ?) AND (A0.status = ?) AND (A0.disabled = ?)) AND (A0.markForDeleteA2 = 0)

• 堆栈跟踪示例：

 "SessionUserMonitor" Id=31 RUNNABLE (in native)
      Blocked (cnt): 2; Waited (cnt): 98
  at sun.nio.ch.FileDispatcherImpl.read0(Native Method)
  at sun.nio.ch.SocketDispatcher.read(SocketDispatcher.java:39)
  at sun.nio.ch.IOUtil.readIntoNativeBuffer(IOUtil.java:223)
  at sun.nio.ch.IOUtil.read(IOUtil.java:197)
  at sun.nio.ch.SocketChannelImpl.read(SocketChannelImpl.java:378)
  at oracle.net.nt.TimeoutSocketChannel.read(TimeoutSocketChannel.java:178)
  at oracle.net.ns.NSProtocolNIO.doSocketRead(NSProtocolNIO.java:544)
  at oracle.net.ns.NIOPacket.readHeader(NIOPacket.java:234)
  at oracle.net.ns.NIOPacket.readPacketFromSocketChannel(NIOPacket.java:174)
  at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:122)
  at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:100)
  at oracle.net.ns.NIONSDataChannel.readDataFromSocketChannel(NIONSDataChannel.java:86)
  at oracle.jdbc.driver.T4CMAREngineNIO.prepareForUnmarshall(T4CMAREngineNIO.java:762)
  at oracle.jdbc.driver.T4CMAREngineNIO.unmarshalUB1(T4CMAREngineNIO.java:427)
  at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:394)
  at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:255)
  at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:610)
  at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:253)
  at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:86)
  at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:928)
  at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:950)
  at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1099)
  at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3640)
  at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1384)
  at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3687)
  at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1165)
  at wt.pds.AbstractResultCursor.executeQuery(AbstractResultCursor.java:576)
  at wt.pds.AbstractResultCursor.executeQuery(AbstractResultCursor.java:543)
  at wt.pds.BasicResultCursor.getNextResultSet(BasicResultCursor.java:282)
  at wt.pds.BasicResultCursor.advance(BasicResultCursor.java:234)
  at wt.pds.BasicResultCursor.next(BasicResultCursor.java:82)
  at wt.pds.SQLDatabasePds.query(SQLDatabasePds.java:879)
  at wt.pom.PersistentObjectManager.query(PersistentObjectManager.java:1076)
  at wt.fc.StandardPersistenceManager._query(StandardPersistenceManager.java:1977)
  at wt.fc.StandardPersistenceManager._find(StandardPersistenceManager.java:2008)
  at wt.fc.StandardPersistenceManager.find(StandardPersistenceManager.java:667)
  at sun.reflect.GeneratedMethodAccessor234.invoke(Unknown Source)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  at java.lang.reflect.Method.invoke(Method.java:498)
  at wt.services.ServiceFactory$ServerInvocationHandler.invoke(ServiceFactory.java:399)
  at com.sun.proxy.$Proxy13.find(Unknown Source)
  at wt.org.LicenseGroupHelper.findGroup(LicenseGroupHelper.java:1009)
  at wt.org.LicenseGroupHelper.findGroup(LicenseGroupHelper.java:969)
  at wt.org.LicenseGroupHelper.getExternalLicenseGroup(LicenseGroupHelper.java:1386)
  at wt.org.LicenseGroupHelper.setupLicenseGroupRelationshipCache(LicenseGroupHelper.java:1408)
  at wt.org.LicenseGroupHelper.setupLicenseGroupCache(LicenseGroupHelper.java:151)
  at wt.org.LicenseGroupHelper.<clinit>(LicenseGroupHelper.java:107)
  at wt.licenseusage.StandardLicenseUsageService.getLicenseGroupsForUser(StandardLicenseUsageService.java:1502)
  at wt.licenseusage.StandardLicenseUsageService.recordLicenseUsageOnLogin(StandardLicenseUsageService.java:1322)
  at wt.licenseusage.StandardLicenseUsageService.recordLicenseUsageOnLogin(StandardLicenseUsageService.java:1218)
  at wt.licenseusage.StandardLicenseUsageService$2.notifyVetoableEvent(StandardLicenseUsageService.java:352)
  at wt.events.StandardKeyedEventBranch.intDispatchEvent(StandardKeyedEventBranch.java:320)
  at wt.events.StandardKeyedEventBranch.dispatchVetoableEvent(StandardKeyedEventBranch.java:255)
  at wt.events.StandardKeyedEventDispatcher.intDispatchEvent(StandardKeyedEventDispatcher.java:304)
  at wt.events.StandardKeyedEventDispatcher.dispatchVetoableEvent(StandardKeyedEventDispatcher.java:228)
  at wt.services.StandardManagerService.intDispatchVetoableEvent(StandardManagerService.java:410)
  at wt.services.StandardManagerService.dispatchVetoableEvent(StandardManagerService.java:303)
  at wt.session.SessionUsers.processUser(SessionUsers.java:1064)
  at wt.session.SessionUsers.access$700(SessionUsers.java:74)
  at wt.session.SessionUsers$SessionUsersTimer.run(SessionUsers.java:1120)
  at java.lang.Thread.run(Thread.java:748)