How to improve the performance of queries against the WFTASK table? (Doc ID 3018420.1)

Last updated on APRIL 29, 2024

Applies to:

Symptoms

 In some situations queries against the WFTASK table are slow.  

Query0 - 7801 ms - SELECT subqry.* FROM (SELECT subsubqry.*, rownum r FROM ( SELECT DISTINCT wfn.TENANT_ID,wfn.acquiredBy,wfn.applicationContext,wfn.approvers,wfn.assignedDate,wfn.assigneeGroups,wfn.assigneeGroupsDisplayName,wfn.assigneeUsers,wfn.assigneeUsersDisplayName,wfn.assignees,wfn.category,wfn.compositeDN,wfn.createdDate,wfn.creator,wfn.decomposedTaskGrpInstanceId,wfn.digitalSignatureRequired,wfn.domainId,wfn.dueDate,wfn.endDate,wfn.excludedParticipants,wfn.expirationDate,wfn.formName,wfn.hasSubtask,wfn.instanceId,wfn.isDecomposedTask,wfn.isGroup,wfn.mdsLabel,wfn.numberOfTimesModified,wfn.organizationalUnitId,wfn.originalAssigneeUser,wfn.outcome,wfn.ownerGroup,wfn.ownerRole,wfn.ownerUser,wfn.participantName,wfn.passwordRequiredOnUpdate,wfn.percentageComplete,wfn.preActionUserSteps,wfn.priority,wfn.processId,wfn.processName,wfn.processType,wfn.processVersion,wfn.reviewers,wfn.rootTaskId,wfn.startDate,COALESCE(wfn.state,'COMPLETED') state,wfn.subState,wfn.subTaskGroupInstanceId,wfn.swimlaneRole,wfn.taskDefinitionId,wfn.taskDefinitionName,wfn.taskDisplayUrl,wfn.taskGroupId,wfn.taskGroupInstanceId,wfn.taskId,wfn.taskNumber,wfn.tenantId,wfn.title,wfn.updatedBy,wfn.updatedDate,wfn.version,wfn.workflowDescriptorURI,wfn.workflowPattern FROM WFTask wfn,WFAssignee wfa WHERE ((((( wfn.state IN (?,?))) AND ( wfn.subState IS NULL OR wfn.subState <> ?))) AND ( ((((((wfn.organizationalUnitId IS NULL ) AND ( ((((((((wfa.assigneeType = ? AND ( wfa.assignee IN (?))) OR ( wfa.assigneeType = ? AND ( wfa.assignee IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?))))) OR ( ( wfn.originalAssigneeUser IN (?))))) OR ( wfn.acquiredBy = ?)) AND wfn.taskId = wfa.taskId) AND ( wfn.identityContext = ?))))) AND ( wfn.isTemplateTask IS NULL OR wfn.isTemplateTask <> ?))) AND ( wfn.componentType IS NULL OR wfn.componentType = ?)))) AND wfn.aggregationTaskId IS NULL ORDER BY wfn.createdDate DESC NULLS LAST, wfn.taskNumber DESC NULLS LAST, wfn.title DESC NULLS LAST, wfn.priority DESC NULLS LAST) subsubqry WHERE rownum <= ? ) subqry WHERE subqry.r >= ?

Query0 - 1898 ms - SELECT COUNT(DISTINCT wfn.taskId) taskCount FROM WFTask wfn,WFAssignee wfa WHERE ((( wfn.state IN (?,?))) AND ( ((((((wfn.organizationalUnitId IS NULL ) AND ( ((((((((wfa.assigneeType = ? AND ( wfa.assignee IN (?))) OR ( wfa.assigneeType = ? AND ( wfa.assignee IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?))))) OR ( ( wfn.originalAssigneeUser IN (?))))) OR ( wfn.acquiredBy = ?)) AND wfn.taskId = wfa.taskId) AND ( wfn.identityContext = ?))))) AND ( wfn.isTemplateTask IS NULL OR wfn.isTemplateTask <> ?))) AND ( wfn.componentType IS NULL OR wfn.componentType = ?)))) AND wfn.aggregationTaskId IS NULL

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.