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
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 |
|
Base URL |
|
Content-Type |
|
- 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;