Issue Summary

Using Jira Portfolio JQL function, namely issuesInPlan, issuesInProgram, childIssuesOf, can cause the thread to stall while Jira Portfolio calculates custom field for plan data.

This is because the JPOS function, DefaultPlanCustomFieldService#getRelevantCustomFieldIds obtains the data inefficiently by running N+1 SELECT query against the database. First query retrieve a list of plan IDs (from AO_D9132D_PLAN) and then for each plan ID another query runs against AO_D9132D_PLAN_CUSTOM_FIELD to retrieve a list of the custom fields linked to that plan.

Steps to Reproduce

1. Create 1000 Portfolio plans, some with assigned custom fields
2. Add a filter with JQL function above
3. Try to load the filter, use it in a board or Filter Issues gadget.

Expected Results

The function of Jira loads relitively quickly

Actual Results

• The thread takes a long time to complete
• When view AWR report, high number of SELECT query to AO_D9132D_PLAN_CUSTOM_FIELD
• Long running thread on database on com.atlassian.rm.jpo.core.plancustomfield.DefaultPlanCustomFieldService.getCustomFieldIdsForPlan - for example:

  "http-nio-8080-exec-119 url:/rest/gadget/1.0/favfilters username:mars" #11506 daemon prio=5 os_prio=0 tid=0x00007f59b00ff000 nid=0x254a runnable [0x00
  007f4567eef000]
     java.lang.Thread.State: RUNNABLE
          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:380)
          - locked <merged>(a java.lang.Object)
          at oracle.net.nt.TimeoutSocketChannel.read(TimeoutSocketChannel.java:144)
          at oracle.net.ns.NIOHeader.readHeaderBuffer(NIOHeader.java:82)
          at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:139)
          at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:101)
          at oracle.net.ns.NIONSDataChannel.readDataFromSocketChannel(NIONSDataChannel.java:80)
          at oracle.jdbc.driver.T4CMAREngineNIO.prepareForReading(T4CMAREngineNIO.java:98)
          at oracle.jdbc.driver.T4CMAREngineNIO.unmarshalUB1(T4CMAREngineNIO.java:534)
          at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:485)
          at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
          at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)
          at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:226)
          at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:59)
          at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:747)
          at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:904)
          at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1082)
          at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3780)
          at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1343)
          at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3822)
          - locked <merged>(a oracle.jdbc.driver.T4CConnection)
          at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1165)
          at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
          at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
          at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:446)
          at com.atlassian.rm.jpo.core.plancustomfield.sql.QueryDslPlanCustomFieldPersistence.getCustomFieldIdsForPlan(QueryDslPlanCustomFieldPersistence.java:54)
          at com.atlassian.rm.jpo.core.plancustomfield.DefaultPlanCustomFieldService$1$1.perform(DefaultPlanCustomFieldService.java:79)
          at com.atlassian.rm.jpo.core.plancustomfield.DefaultPlanCustomFieldService$1$1.perform(DefaultPlanCustomFieldService.java:75)
          at com.atlassian.rm.common.persistence.transaction.Registry.run(Registry.java:36)
          at com.atlassian.rm.jpo.core.plancustomfield.DefaultPlanCustomFieldService$1.apply(DefaultPlanCustomFieldService.java:75)
          at com.atlassian.rm.jpo.core.plancustomfield.DefaultPlanCustomFieldService$1.apply(DefaultPlanCustomFieldService.java:70)
          at com.atlassian.rm.common.persistence.transaction.TransactionHandler.inTransaction(TransactionHandler.java:139)
          at com.atlassian.rm.common.persistence.transaction.TransactionHandler.reuseExistingDatabaseTransaction(TransactionHandler.java:42)
          at com.atlassian.rm.jpo.core.plancustomfield.DefaultPlanCustomFieldService.getCustomFieldIdsForPlan(DefaultPlanCustomFieldService.java:70)
          at com.atlassian.rm.jpo.core.plancustomfield.DefaultPlanCustomFieldService$4.apply(DefaultPlanCustomFieldService.java:186)
          at com.atlassian.rm.jpo.core.plancustomfield.DefaultPlanCustomFieldService$4.apply(DefaultPlanCustomFieldService.java:172)
          at com.atlassian.rm.common.persistence.transaction.TransactionHandler.inTransaction(TransactionHandler.java:139)
          at com.atlassian.rm.common.persistence.transaction.TransactionHandler.reuseExistingDatabaseTransaction(TransactionHandler.java:42)
          at com.atlassian.rm.jpo.core.plancustomfield.DefaultPlanCustomFieldService.getRelevantCustomFieldIds(DefaultPlanCustomFieldService.java:172)
          at com.atlassian.rm.jpo.core.customfields.CombinedRelevantCustomFieldsProvider$1.perform(CombinedRelevantCustomFieldsProvider.java:45)
          at com.atlassian.rm.jpo.core.customfields.CombinedRelevantCustomFieldsProvider$1.perform(CombinedRelevantCustomFieldsProvider.java:39)
          at com.atlassian.rm.common.persistence.transaction.Registry.run(Registry.java:36)
          at com.atlassian.rm.common.persistence.transaction.TransactionHandler$3.apply(TransactionHandler.java:60)
          at com.atlassian.rm.common.persistence.transaction.TransactionHandler.inTransaction(TransactionHandler.java:139)
          at com.atlassian.rm.common.persistence.transaction.TransactionHandler.inNewTransaction(TransactionHandler.java:127)
          at com.atlassian.rm.common.persistence.transaction.TransactionHandler.access$000(TransactionHandler.java:14)
          at com.atlassian.rm.common.persistence.transaction.TransactionHandler$1.apply(TransactionHandler.java:36)
          at com.atlassian.rm.common.persistence.DefaultDatabaseProvider$1.perform(DefaultDatabaseProvider.java:30)
          at com.atlassian.rm.common.persistence.env.JiraDatabaseProvider$1.perform(JiraDatabaseProvider.java:41)
          at com.atlassian.rm.common.bridges.jira.persistence.JiraDatabaseProviderBridge63.execute(JiraDatabaseProviderBridge63.java:66)
          at com.atlassian.rm.common.persistence.env.JiraDatabaseProvider.execute(JiraDatabaseProvider.java:34)
          at com.atlassian.rm.common.persistence.DefaultDatabaseProvider.run(DefaultDatabaseProvider.java:26)
          at com.atlassian.rm.common.persistence.transaction.TransactionHandler.reuseExistingDatabaseTransaction(TransactionHandler.java:32)
          at com.atlassian.rm.common.persistence.transaction.TransactionHandler.reuseExistingDatabaseTransaction(TransactionHandler.java:64)
          at com.atlassian.rm.jpo.core.customfields.CombinedRelevantCustomFieldsProvider.getRelevantCustomFieldIds(CombinedRelevantCustomFieldsProvider.java:39)
          at com.atlassian.rm.jpo.issueloading.lucene.issue.naming.DefaultIssueFieldNamingFactory.create(DefaultIssueFieldNamingFactory.java:43)
          at com.atlassian.rm.jpo.issueloading.lucene.issue.naming.DefaultIssueFieldNamingFactory.create(DefaultIssueFieldNamingFactory.java:19)
          at com.atlassian.rm.common.core.scope.OperationScopeCachedBeanProxyFactory$OperationScopeCachedBeanProxyFactoryInvocationHandler.invoke(OperationScopeCachedBeanProxyFactory.java:63)
          at com.sun.proxy.$Proxy3052.create(Unknown Source)
          at com.atlassian.rm.jpo.issueloading.lucene.issue.naming.CachedIssueFieldNaming.get(CachedIssueFieldNaming.java:28)
          at com.atlassian.rm.jpo.customfields.lucene.parent.searcher.EpicLinkQueryGenerator.generateEpicLinkIdQuery(EpicLinkQueryGenerator.java:55)
          at com.atlassian.rm.jpo.customfields.lucene.parent.searcher.EpicLinkQueryGenerator.generateQuery(EpicLinkQueryGenerator.java:51)
          at com.atlassian.rm.jpo.customfields.lucene.parent.searcher.ParentCustomFieldClauseQueryFactory.generateEpicLinkQuery(ParentCustomFieldClauseQueryFactory.java:134)
          at com.atlassian.rm.jpo.customfields.lucene.parent.searcher.ParentCustomFieldClauseQueryFactory.getQuery(ParentCustomFieldClauseQueryFactory.java:98)
          at com.atlassian.jira.jql.query.ContextAwareQueryVisitor.visit(ContextAwareQueryVisitor.java:97)
          at com.atlassian.jira.jql.query.ContextAwareQueryVisitor.visit(ContextAwareQueryVisitor.java:25)
          at com.atlassian.query.clause.TerminalClauseImpl.accept(TerminalClauseImpl.java:143)
          at com.atlassian.jira.jql.query.QueryVisitor.createQuery(QueryVisitor.java:51)
          at com.atlassian.jira.jql.query.DefaultLuceneQueryBuilder.createLuceneQuery(DefaultLuceneQueryBuilder.java:29)
          at com.atlassian.jira.issue.search.providers.LuceneSearchProvider.createLuceneQuery(LuceneSearchProvider.java:306) 

Workaround

• Reduce the amount of plans
• Recuce the amount of plans using custom fields
• Increase the performance of AO_D9132D_PLAN_CUSTOM_FIELD - if not already added, adding an index to AO_D9132D_PLAN_CUSTOM_FIELD.PLAN_ID will help
• Disable the JQL function noted above via UPM (Jira Admin -> Manage Apps -> Jira Portfolio / Advanced Roadmaps -> Modules)
  • Note when doing so, those filter will no longer load. So removing the problematic function from the JQL itself will be nessesary