Claim Task Processing performance
Tracking Tasks per Claim
The following query lists all tasks that are executed 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 used by the system 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 = '&YOUR_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, e.g.: and taty.name = 'Initialize'
-
Add a restriction for tracking a specific claim, e.g.: and 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 so that changes can be tracked over time.
Items to report in case of performance issues
-
Application logs
-
Server logs
-
AWRs for the time period of the issue
-
SQL Monitor reports for top 3 SQL queries in AWR (this is requested after we receive 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 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;