STEPS TO REPRODUCE
1. Login to Clarity and schedule the Post Timesheet Job to run
2. Login to Oracle Enterprise manager and run the tuning advisor on the SQL id generated by the Post Timesheet Job
3. Below is the query and even though it runs in millisecond this executes 10k times and probably != is something which oracle is not linking it.
SELECT MAX(a.prfinish) prfinish FROM prassignment a WHERE a.prtaskid = :"SYS_B_0" AND a.prid != :"SYS_B_1"
4. Result of SQL tuning advisor is
Predicate "A"."PRID"<>:B1 used at line ID 2 of the execution plan is an inequality condition on indexed column "PRID". This inequality condition prevents the optimizer from efficiently using indices on table "CLARITY"."PRASSIGNMENT". Rewrite the predicate into an equivalent form to take advantage of indices.
The optimizer is unable to use an index if the predicate is an inequality condition or if there is an expression or an implicit data type conversion on the indexed column.
Expected Results: The job should consistently take advantage of the index
Actual Results: The job is not able to consistently take advantage of the index and resulting in slowness
Release : 15.5, 15.5 SP1, 15.6, 15.6 SP1
Component : CA PPM SAAS TIME MANAGEMENT
This is defect DE49770 and its fixed in Clarity PPM 15.7
1. Short term resolution is to ask the DBA to check if there are multiple plans for this query is there in database and if it exist to flush out the multiple plan and base line the best plan as it with that plan it will process faster
2. Once you upgrade to Clarity PPM 15.7, the job has been optimized