Claim Task Processing Performance

Tracking Tasks Per Claim

The following query lists all tasks that the application executes for processing Claims for the current day.

The process_start_interval_ms is the time interval in milliseconds between creation of the Claim and picking up the task.
The process_elapsed_time_ms is the time in milliseconds that the system uses to execute the task.

select task.id   taskid
,      task.status
,      taty.name tasktype
,      clai.code claimcode
,      to_char(clai.creation_date, 'DD-MON HH:MI:SSxFF') claim_creation_time
,      to_char(task.process_start_datetime , 'DD-MON HH:MI:SSxFF') process_start_time
,      extract(second from (task.process_start_datetime - clai.creation_date))*1000 process_start_interval_ms
,      to_char(task.process_stop_datetime , 'DD-MON HH:MI:SSxFF') process_stop_time
,      extract(second from (task.process_stop_datetime - task.process_start_datetime))*1000 process_elapsed_time_ms
from   ohi_tasks   task
,      ohi_task_types_tl taty
,      cla_claims  clai
where  task.taty_id = taty.base_table_id
and    task.subject_id = clai.id
and    clai.code = '&THE_CLAIM_CODE'
--and    taty.name = 'Initialize'
and    task.tabl_id = 2690
and    taty.language = 'en__OHI'
and    task.creation_date > trunc(sysdate)
order by clai.code asc, task.id asc;

Possible Variations

  • Add a restriction for tracking specific task types. For example,

taty.name = 'Initialize'

  • Add a restriction for tracking a specific claim. For example,

clai.code is 'A_CLAIM_CODE'

Average Task Timings for Claim Lines That Were Updated in the Last 10 Minutes

select substr(taty.name, 1, 30) task_name
,      count(*) task_count
,      count(distinct clli.clai_id) number_of_claims
,      count(*) number_of_claimlines
,      decode(count(*), 0, '0', to_char(round(count(*) / count(distinct clli.clai_id), 2), '9990D00')) claimlines_per_claim
,      to_char(avg(cast(task.process_stop_datetime as date) - cast(task.process_start_datetime as date)) * 3600 * 24, '9999990D9990') avg_task_time
,      to_char(min(cast(task.process_stop_datetime as date) - cast(task.process_start_datetime as date)) * 3600 * 24, '9999990D9990') min_task_time
,      to_char(max(cast(task.process_stop_datetime as date) - cast(task.process_start_datetime as date)) * 3600 * 24, '9999990D9990') max_task_time
,      to_char(sysdate - (10 * (1 / (24*60))), 'yyyy-mm-dd hh24:mi:ss') window_start
,      to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') window_stop
from   ohi_tasks task
,      ( select base_table_id
,      name
from   ohi_task_types_tl
where  language = 'en__OHI'
) taty
,      cla_claim_lines clli
where  task.taty_id = taty.base_table_id
and    to_number(to_char(clli.clai_id)) = task.subject_id
and    task.tabl_id = 2690
and    clli.last_updated_date between sysdate - (10 / 1440) and sysdate -- 10 minutes
group  by taty.name

In case of suspected performance problems, run this query every 15 minutes, capturing the output in a file to track changes.

Items to Report in Case of Performance Issues

  1. Application Logs

  2. Server Logs

  3. AWRs for the period of the issue

  4. SQL Monitor reports for top three SQL queries in AWR (request this after receiving the AWR and determine the top SQL queries)

  5. Status History of the Claim, please find the query to find the status history documented below.

  6. Task Flow History of the Claim, please find the query to find the task flow history documented below.

Status History Query

select cshi.*
from   ohi_claims_owner.cla_claims clai
join   ohi_claims_owner.cla_claim_status_history cshi on cshi.clai_id = clai.id
where  clai.code = '&CLAIM_CODE'
order by cshi.date_time;

Task Flow History Query

with    input as (
select '&CLAIM_CODE' clai_code
from    dual
)
select  /*+ NOPARALLEL */
clai.code clai_code
,       clai.status clai_status
,       taty.name taty_name
,       task.status
,       task.id task_id
,       task.task_id parent_task_id
,       task.creation_date
,       task.last_updated_date
,       task.process_start_datetime
,       task.process_stop_datetime
,       task.thread_code
,       task.extra_info
from    ohi_claims_owner.cla_claims clai
,       ohi_claims_owner.ohi_tasks task
,       ohi_claims_owner.ohi_task_types_tl taty
,       ohi_claims_owner.ohi_languages lang
,       input
where   clai.code = input.clai_code
and     lang.ind_default = 'Y'
and     taty.language = lang.code
and     task.taty_id = taty.base_table_id
and     task.tabl_id = 2690
and     task.subject_id = clai.id
union all
select  clai.code
,       clai.status
,       taty.name
,       task.status
,       task.id
,       task.task_id
,       task.creation_date
,       task.last_updated_date
,       task.process_start_datetime
,       task.process_stop_datetime
,       task.thread_code
,       task.extra_info
from    ohi_claims_owner.cla_claims clai
,       ohi_claims_owner.ohi_tasks task
,       ohi_claims_owner.ohi_task_types_tl taty
,       ohi_claims_owner.ohi_languages lang
,       input
where   clai.code = input.clai_code
and     lang.ind_default = 'Y'
and     taty.language = lang.code
and     task.taty_id = taty.base_table_id
and     task.subject_id = -1
and     task.tabl_id = 2250
and     task.id in (select child_task.task_id
from   ohi_claims_owner.ohi_tasks child_task
where  child_task.subject_id = clai.id
and    child_task.tabl_id = 2690
)
order by clai_code, process_start_datetime;

Oracle AQ Related Problems

If Oracle AQ process hangs (for example, when messages don’t change to READY status even after the configured delay expires), then restart QMON background process.

Use the following commands to do so (these commands do not require the database to be restarted).

alter system set "_aq_stop_backgrounds"=TRUE;
alter system set "_aq_stop_backgrounds"=FALSE;