Long running EPMBuildHistory Query During FBR Export in Windchill PDMLink

• FBR export runs for days
• Time is spent in DB query on EPMBuildHistory table
• The following stacktrace is responsible for generating the query:

at app//com.sun.proxy.$Proxy29.find(Unknown Source)
  at app//com.ptc.windchill.wp.delivery.export.EPMBuildHistoryParentLinksDependencyProcessor.addDependentObjects(EPMBuildHistoryParentLinksDependencyProcessor.java:60)
  at app//com.ptc.windchill.wp.delivery.export.AbstractDeliveryExportDelegate.secondaryObjectsVerification(AbstractDeliveryExportDelegate.java:1683)
  at app//com.ptc.windchill.wp.delivery.export.AbstractDeliveryExportDelegate.fetchDependents(AbstractDeliveryExportDelegate.java:1547)
  at app//com.ptc.windchill.wp.delivery.export.AbstractDeliveryExportDelegate.getPackageExportGraph(AbstractDeliveryExportDelegate.java:1125)
  at app//com.ptc.windchill.wp.delivery.export.AbstractDeliveryExportDelegate.buildExportFiles(AbstractDeliveryExportDelegate.java:531)
  at app//com.ptc.windchill.sync.wpsync.StandardWPSyncService.exportToFile(StandardWPSyncService.java:945)
  at app//com.ptc.windchill.sync.wpsync.StandardWPSyncService.createZip(StandardWPSyncService.java:888)
  at app//com.ptc.windchill.sync.wpsync.StandardWPSyncService.performExport(StandardWPSyncService.java:528)
  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.$Proxy142.performExport(Unknown Source)
  at app//com.ptc.windchill.sync.wpsync.ExportTask.runExportTask(ExportTask.java:328)
  at app//com.ptc.windchill.sync.wpsync.ExportTask._run(ExportTask.java:237)
  at app//com.ptc.core.task.AtomicTask.run(AtomicTask.java:58)
  at app//com.ptc.core.task.Task.execute(Task.java:262)
  at app//com.ptc.core.task.Task.executeInBackground(Task.java:474)
  at app//com.ptc.core.task.QueueableTaskServerHelper.executeTaskInQueue(QueueableTaskServerHelper.java:163)
  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)

• The query will look like the following:

(SELECT 'wt.epm.build.EPMBuildHistory' classnameA2A2,A0.idA2A2 
FROM EPMBuildHistory A0 
  WHERE ((A0.idA3A5 IN (
    SELECT DISTINCT A0.idA3A5 
    FROM EPMMemberLink A0 
      WHERE ((A0.idA3B5 IN (
        SELECT DISTINCT A0.idA3masterReference 
        FROM EPMDocument A0 
          WHERE ((A0.idA2A2 IN (SELECT /*+ CARDINALITY(wtot 1322000) */ * FROM TABLE(cast(:1  as <Schema Name>.TABLE_OF_NUMBER)) wtot WHERE (ROWNUM > 0) AND (column_value IS NOT NULL)))))) 
      AND (A0.idA3A5 NOT IN (SELECT /*+ CARDINALITY(wtot 1322000) */ * FROM TABLE(cast(:2  as <Schema Name>.TABLE_OF_NUMBER)) wtot WHERE (ROWNUM > 0) AND (column_value IS NOT NULL))))))) 
    AND (A0.markForDeleteA2 = 0)) 
UNION (
SELECT 'wt.epm.build.EPMBuildHistory' classnameA2A2,A0.idA2A2 
FROM EPMBuildHistory A0 
  WHERE ((A0.idA3B5 IN (
    SELECT DISTINCT A0.idA3A5 
    FROM WTPartUsageLink A0 
      WHERE ((A0.idA3B5 IN (
        SELECT DISTINCT A0.idA3masterReference 
        FROM WTPart A0 
          WHERE ((A0.idA2A2 IN (SELECT /*+ CARDINALITY(wtot 3000) */ * FROM TABLE(cast(:3  as <Schema Name>.TABLE_OF_NUMBER)) wtot WHERE (ROWNUM > 0) AND (column_value IS NOT NULL)))) 
        UNION 
        SELECT DISTINCT A0.idA3masterReference 
        FROM MPMProcessMaterial A0 
          WHERE ((A0.idA2A2 IN (SELECT /*+ CARDINALITY(wtot 3000) */ * FROM TABLE(cast(:4  as <Schema Name>.TABLE_OF_NUMBER)) wtot WHERE (ROWNUM > 0) AND (column_value IS NOT NULL)))) 
        UNION 
        SELECT DISTINCT A0.idA3masterReference 
        FROM MPMWorkCenter A0 
          WHERE ((A0.idA2A2 IN (SELECT /*+ CARDINALITY(wtot 3000) */ * FROM TABLE(cast(:5  as <Schema Name>.TABLE_OF_NUMBER)) wtot WHERE (ROWNUM > 0) AND (column_value IS NOT NULL)))) 
        UNION 
        SELECT DISTINCT A0.idA3masterReference 
        FROM ManufacturerPart A0 
          WHERE ((A0.idA2A2 IN (SELECT /*+ CARDINALITY(wtot 3000) */ * FROM TABLE(cast(:6  as <Schema Name>.TABLE_OF_NUMBER)) wtot WHERE (ROWNUM > 0) AND (column_value IS NOT NULL)))))) 
      AND (A0.idA3A5 NOT IN (SELECT /*+ CARDINALITY(wtot 3000) */ * FROM TABLE(cast(:7  as <Schema Name>.TABLE_OF_NUMBER)) wtot WHERE (ROWNUM > 0) AND (column_value IS NOT NULL))))))) 
  AND (A0.markForDeleteA2 = 0)) 
UNION (
SELECT 'wt.epm.build.EPMBuildHistory' classnameA2A2,A0.idA2A2 
FROM EPMBuildHistory A0 
  WHERE ((A0.idA3A5 IN (
    SELECT DISTINCT A0.idA3A5 
    FROM ModelItemLink A0 
      WHERE ((A0.idA3B5 IN (
        SELECT DISTINCT A0.idA3B5 
        FROM ModelItem A0 
          WHERE ((A0.idA2A2 IN (:8 ,:9 ,:10 ,:11 ,:12 ,:13 ,:14 ,:15 ,:16 ,:17 ,:18 ,:19 ,:20 ,:21 ,:22 ,:23 ,:24 ,:25 ,:26 ,:27,:28 ,:29 ,:30 ,:31 ,:32 ,:33 ,:34 ,:35 ,:36 ,:37 ,:38 ,:39 ,:40 ,:41 ,:42 ,:43 ,:44 ,:45 ,:46 ,:47 ,:48 ,:49 ,:50 ,:51 ,:52 ,:53 ,:54 ,:55 ,:56 ,:57 ,:58 ,:59 ,:60 ,:61 ,:62 ,:63 ,:64 ,:65 ,:66 ,:67 ,:68 ,:69 ,:70 ,:71 ,:72 ,:73 ,:74 ,:75 ,:76 ,:77 ,:78 ,:79 ,:80 ,:81 ,:82 ))))) 
      AND (A0.idA3A5 NOT IN (:83 ,:84 ,:85 ,:86 ,:87 ,:88 ,:89 ,:90 ,:91 ,:92 ,:93 ,:94 ,:95 ,:96 ,:97 ,:98 ,:99 ,:100 ,:101 ,:102 ,:103 ,:104 ,:105 ,:106 ,:107 ,:108 ,:109,:110 ,:111 ,:112 ,:113 ,:114 ,:115 ,:116 ,:117 ,:118 ,:119 ,:120 ,:121 ,:122 ,:123 ,:124 ,:125 ,:126 ,:127 ,:128 ,:129 ,:130 ,:131 ,:132 ,:133 ,:134 ,:135 ,:136 ,:137 ,:138 ,:139 ,:140 ,:141,:142 ,:143 ,:144 ,:145 ,:146 ,:147 ,:148 ,:149 ,:150 ,:151 ,:152 ,:153 ,:154 ,:155 ,:156 ,:157 )))))) 
  AND (A0.markForDeleteA2 = 0))