Workflow Background Process Program For POAPPRV Is Running For Long (Doc ID 2719156.1)

Last updated on JUNE 16, 2022

Applies to:

Symptoms

Workflow background process for POAPPRV is long running for more than 2 hrs.

Problematic statement is


SELECT SUPERVISOR_ID
FROM
 PER_ALL_ASSIGNMENTS_F WHERE PER_ALL_ASSIGNMENTS_F.PERSON_ID = :B1 AND
  PER_ALL_ASSIGNMENTS_F.PRIMARY_FLAG = 'Y' AND
  PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_TYPE IN ('E','C') AND
  PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID NOT IN (SELECT
  ASSIGNMENT_STATUS_TYPE_ID FROM PER_ASSIGNMENT_STATUS_TYPES WHERE
  PER_SYSTEM_STATUS = 'TERM_ASSIGN') AND TRUNC(SYSDATE) BETWEEN
  PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE AND
  PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 87823074 956.67 957.89 0 0 0 0
Fetch 87823073 7957.05 8066.20 0 702584580 0 87823073
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 175646148 8913.72 9024.09 0 702584580 0 87823073

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 173 (APPS) (recursive depth: 2)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
  1 1 1 HASH JOIN ANTI (cr=6 pr=0 pw=0 time=191 us cost=22 size=54 card=1)
  1 1 1 TABLE ACCESS BY INDEX ROWID BATCHED PER_ALL_ASSIGNMENTS_F (cr=4 pr=0 pw=0 time=32 us cost=20 size=544 card=17)
  1 1 1 INDEX RANGE SCAN PER_ASSIGNMENTS_F_N12 (cr=3 pr=0 pw=0 time=24 us cost=3 size=0 card=17)(object id 408891)
  5 5 5 TABLE ACCESS BY INDEX ROWID BATCHED PER_ASSIGNMENT_STATUS_TYPES (cr=2 pr=0 pw=0 time=38 us cost=2 size=66 card=3)
  36 36 36 INDEX SKIP SCAN PER_ASSIGNMENT_STATUS_TYPE_PK (cr=1 pr=0 pw=0 time=17 us cost=1 size=0 card=36)(object id 548193)


Rows Execution Plan
------- ---------------------------------------------------
  0 SELECT STATEMENT MODE: ALL_ROWS
  1 HASH JOIN (ANTI)
  1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID BATCHED) OF
  'PER_ALL_ASSIGNMENTS_F' (TABLE)
  1 INDEX MODE: ANALYZED (RANGE SCAN) OF
  'PER_ASSIGNMENTS_F_N12' (INDEX)
  5 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID BATCHED) OF
  'PER_ASSIGNMENT_STATUS_TYPES' (TABLE)
  36 INDEX MODE: ANALYZED (SKIP SCAN) OF
  'PER_ASSIGNMENT_STATUS_TYPE_PK' (INDEX (UNIQUE))

Changes

Cause

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