search cancel

Clarity: Post Timesheet slow as its unable to use index on Oracle

book

Article ID: 134428

calendar_today

Updated On:

Products

Clarity PPM SaaS

Issue/Introduction

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 

Cause

This is defect DE49770 and its fixed in Clarity PPM 15.7 

Environment

Release : 15.5, 15.5 SP1, 15.6, 15.6 SP1

Component : CA PPM SAAS TIME MANAGEMENT

Resolution

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