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_claims_owner.ohi_tasks_bv      task
join   ohi_claims_owner.ohi_task_types_bv taty on task.taty_id = taty.id
join   ohi_claims_owner.cla_claims_bv     clai on task.subject_id = clai.id
join   ohi_claims_owner.ohi_tables_bv     tabl on task.tabl_id = tabl.id and tabl.name = 'CLA_CLAIMS'
where  clai.code = '&THE_CLAIM_CODE'
--and    taty.name = 'Initialize'
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, '999999990D9990') avg_task_time
,      to_char(min(cast(task.process_stop_datetime as date) - cast(task.process_start_datetime as date)) * 3600 * 24, '999999990D9990') min_task_time
,      to_char(max(cast(task.process_stop_datetime as date) - cast(task.process_start_datetime as date)) * 3600 * 24, '999999990D9990') 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_claims_owner.ohi_tasks_bv        task
join   ohi_claims_owner.ohi_task_types_bv   taty on task.taty_id = taty.id
join   ohi_claims_owner.cla_claim_lines_bv  clli on clli.clai_id = task.subject_id
join   ohi_claims_owner.ohi_tables_bv       tabl on task.tabl_id = tabl.id and tabl.name = 'CLA_CLAIMS'
where  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.

More Performance and Throughput Information

See Dealing With Performance and Throughput Problems for more options on gathering performance information.

The section administration:troubleshooting:dealing-with-performance-and-throughput-problems.adoc#_task_processing_performance offers an additional query for task performance analysis.

Items to Report in Case of Performance Issues

Application Logs
Server Logs
AWRs for the period of the issue
SQL Monitor reports for top three SQL queries in AWR (request this after receiving the AWR and determine the top SQL queries)
Status History of the claim, please find the query to find the status history documented below.
Task Flow History of the claim, please find the query to find the task flow history documented below.

Status History Query

select clai.code
,      cshi.status
,      cshi.date_time
from   ohi_claims_owner.cla_claims_bv clai
join   ohi_claims_owner.cla_claim_status_history_bv cshi on cshi.clai_id = clai.id
where  clai.code = '&CLAIM_CODE'
order by cshi.date_time;

The above request is also performed using the following REST API:

Request

HTTP Method

POST

Base URL

http://[hostName]:[portNumber]/[api-context-root]/generic/claimstatushistory/search

Content-Type

application/json

Request Body

{
"resource": {
  "q":"claim.code.eq('CALC1')",
  "orderBy": "creationDate",
  "offset": 0,
  "limit": 200,
  "totalResults": *true*
  },
  "resourceRepresentation": {
    "fields":"all"
  }
}

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
from    ohi_claims_owner.cla_claims_bv     clai
join    ohi_claims_owner.ohi_tasks_bv      task on task.subject_id = clai.id
join    ohi_claims_owner.ohi_task_types_bv taty on task.taty_id = taty.id
join    ohi_claims_owner.ohi_tables_bv     tabl on task.tabl_id = tabl.id and tabl.name = 'CLA_CLAIMS'
,       input
where   clai.code = input.clai_code
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
from    ohi_claims_owner.cla_claims_bv     clai
join    ohi_claims_owner.ohi_tasks_bv      task on task.subject_id = clai.id
join    ohi_claims_owner.ohi_task_types_bv taty on task.taty_id = taty.id
join    ohi_claims_owner.ohi_tables_bv     tabl on task.tabl_id = tabl.id and tabl.name = 'OHI_TABLES'
,       input
where   clai.code = input.clai_code
and     task.subject_id = -1
and     task.id in (select child_task.task_id
                    from   ohi_claims_owner.ohi_tasks_bv   child_task
                    join   ohi_claims_owner.ohi_tables_bv  tabl on child_task.tabl_id = tabl.id and tabl.name = 'CLA_CLAIMS'
                    where  child_task.subject_id = clai.id
                   )
order by clai_code, process_start_datetime;