Post Timesheets Job has been taking longer to complete

Products

• Clarity PPM SaaS

Issue/Introduction

Only the Post Timesheets job has been increasing in runtime.

Run the following query to review the runtimes:

select n.created_date, n.created_by, n.receiver_id, n.sender_id, n.last_updated_date, n.localized_message, csu.user_name, csu.first_name, csu.last_name

FROM

CLB_NOTIFICATIONS N,

CMN_SEC_USERS CSU

where CSU.id = N.RECEIVER_ID
and N.CREATED_DATE > sysdate-30

--and n.receiver_id = <user_id>

and n.localized_message like '%Post Timesheets%'

order by N.CREATED_DATE desc

Cause

Oracle is selecting a poor SQL execution plan.

Environment

Clarity

Oracle

Resolution

Have the DBA team review and apply SQL Tuning Advisor.