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 = '&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;
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 you can track changes.
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 (we request this 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;
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;