Bakcroundjobs - Runtime Minutes

Question

Wanted to create a custom field on the Background Jobs page to easier identify background jobs that we can move to other queues that are candidates for optimization etc, but was struggeling with the syntax…. We created a Quick report instead

Here is the query if someone should have the same requirement. Would appreciate if someone have defined a custom field for this already and would share it here.

select job_id,
schedule_id,
description,
Client_SYS.Get_Item_Value('INTFACE_NAME',arguments_string) migration_job,
state,
state_db,
queue_id,
created,
posted,
started,
executed,
ROUND((executed - started) * 24 * 60) runtime_minutes,
username,
lang_code,
procedure_name,
error_text,
process_id
from deferred_job
where (('&Queue' IS NULL) OR ('&Queue' IS NOT NULL and queue_id = '&Queue'))
and (('&Runtime' IS NULL) OR ('&Runtime' IS NOT NULL and ROUND((executed - started) * 24 * 60) >= '&Runtime'))
and (('&CreatedDate' IS NULL) OR ('&CreatedDate' IS NOT NULL AND to_char(created,'DDMM') = '&CreatedDate'))
and (('&MigrationJob' IS NULL) OR ('&MigrationJob' IS NOT NULL and Client_SYS.Get_Item_Value('INTFACE_NAME',arguments_string) like upper('&MigrationJob')))
and (('&ScheduleId' IS NULL) OR ('&ScheduleId' IS NOT NULL and schedule_id = '&ScheduleId'))
order by job_id desc

Add as many details as possible, by providing details you’ll make it easier for others to reply

hhanse · Answer

Agree this is an interesting field to understand and optimize. Back in the days i did plenty of that.
Here are my old SQL i used for it - you can easily modify them for your needs - enjoy!

-- hhanse

-- Latest job times from the last hour, unaggregated

SELECT id,created, procedure_name, round((executed-started)*24*60*60) Procdure_Time FROM ifsapp.transaction_sys_local_tab WHERE created > SYSDATE - 1/24 ORDER BY ID DESC

-------------------------------------------------------------------------------------

-- Ready jobs from the last hour, with procedure, queue, and total time in seconds

select a.posted,

a.username,

a.state,

a.queue_id,

a.procedure_name,

--a.arguments_string,

round((a.executed-a.started)*24*60*60) Procdure_Time,

round((a.started-a.posted)*24*60*60) Que_Time,

round((a.executed-a.posted)*24*60*60) Total_Time

from transaction_sys_local_tab a

where a.posted > sysdate - 1/24

AND state = 'Ready'

order by a.posted desc;

-------------------------------------------------------------------------------------

-- Grouped by procedure name, with average and total times

-- Also split night and day when comparing load

select a.queue_id,

a.procedure_name, MAX(a.description),

count(*) Executions,

round(avg((a.executed-a.started)*24*60*60)) Avg_Procdure_Time,

round(sum((a.executed-a.started)*24*60*60)) Sum_Tot_Procdure_Time,

round(avg((a.started-a.posted)*24*60*60)) Avg_Que_Time,

round(avg((a.executed-a.posted)*24*60*60)) Avg_Total_Time

from ifsapp.transaction_sys_local_tab a

where state in ('Ready','Warning')

AND a.posted between sysdate - 7 and sysdate -1 -- full days, from 7 days ago through yesterday

--order by a.posted desc;

group by a.queue_id,procedure_name

order by Sum_Tot_Procdure_Time desc;

-------------------------------------------------------------------------------------

-- Queue analysis by procedure and queue

select a.procedure_name, A.QUEUE_ID ,

count(*) Executions,

round(avg((a.executed-a.started)*24*60*60)) Avg_Procdure_Time,

round(sum((a.executed-a.started)*24*60*60)) Sum_Tot_Procdure_Time,

round(avg((a.started-a.posted)*24*60*60)) Avg_Que_Time,

round(avg((a.executed-a.posted)*24*60*60)) Avg_Total_Time

from ifsapp.transaction_sys_local_tab a

where a.posted between sysdate - 7 and sysdate -1

AND state in ('Ready','Warning')

--order by a.posted desc;

group by procedure_name,queue_id

order by queue_id,Sum_Tot_Procdure_Time desc;

-------------------------------------------------------------------------------------

-- Queue wait and execution time: minimum, average, and maximum

-- Cleanup retention differs for Ready, Warning, and Error, so wait times can look odd

select q.queue_id,q.description,count(*)jobs,

round(min(started-posted)*24*60*60,1) min_wait_sec,

round(avg(started-posted)*24*60*60,1) avg_wait_sec,

round(max(started-posted)*24*60*60,1) max_wait_sec,

round(min(executed-started)*24*60*60,1) min_exec_sec,

round(avg(executed-started)*24*60*60,1) avg_exec_sec,

round(max(executed-started)*24*60*60,1) max_exec_sec

from transaction_sys_local_tab t,batch_queue_tab q

where state in ('Ready','Warning','Error') and executed is not null

and t.queue_id=q.queue_id

and posted>sysdate-1

group by q.queue_id,q.description

order by q.queue_id;

-------------------------------------------------------------------------------------

-- Histogram of how many background jobs run per hour

SELECT

to_char(created,'YYYY-MON-DD') day,

to_char(sum(decode(to_char(created,'HH24'),'00',1,0)),'9999') "00",

to_char(sum(decode(to_char(created,'HH24'),'01',1,0)),'9999') "01",

to_char(sum(decode(to_char(created,'HH24'),'02',1,0)),'9999') "02",

to_char(sum(decode(to_char(created,'HH24'),'03',1,0)),'9999') "03",

to_char(sum(decode(to_char(created,'HH24'),'04',1,0)),'9999') "04",

to_char(sum(decode(to_char(created,'HH24'),'05',1,0)),'9999') "05",

to_char(sum(decode(to_char(created,'HH24'),'06',1,0)),'9999') "06",

to_char(sum(decode(to_char(created,'HH24'),'07',1,0)),'9999') "07",

to_char(sum(decode(to_char(created,'HH24'),'08',1,0)),'9999') "08",

to_char(sum(decode(to_char(created,'HH24'),'09',1,0)),'9999') "09",

to_char(sum(decode(to_char(created,'HH24'),'10',1,0)),'9999') "10",

to_char(sum(decode(to_char(created,'HH24'),'11',1,0)),'9999') "11",

to_char(sum(decode(to_char(created,'HH24'),'12',1,0)),'9999') "12",

to_char(sum(decode(to_char(created,'HH24'),'13',1,0)),'9999') "13",

to_char(sum(decode(to_char(created,'HH24'),'14',1,0)),'9999') "14",

to_char(sum(decode(to_char(created,'HH24'),'15',1,0)),'9999') "15",

to_char(sum(decode(to_char(created,'HH24'),'16',1,0)),'9999') "16",

to_char(sum(decode(to_char(created,'HH24'),'17',1,0)),'9999') "17",

to_char(sum(decode(to_char(created,'HH24'),'18',1,0)),'9999') "18",

to_char(sum(decode(to_char(created,'HH24'),'19',1,0)),'9999') "19",

to_char(sum(decode(to_char(created,'HH24'),'20',1,0)),'9999') "20",

to_char(sum(decode(to_char(created,'HH24'),'21',1,0)),'9999') "21",

to_char(sum(decode(to_char(created,'HH24'),'22',1,0)),'9999') "22",

to_char(sum(decode(to_char(created,'HH24'),'23',1,0)),'9999') "23"

from

transaction_sys_local_tab

WHERE state IN ('Ready' ,'Warning')

--AND procedure_name ='Cash_Receipt_Util_API.Validate_And_Inventory_'

-- AND procedure_name ='Batch_Sys.Fnd_Light_Cleanup_'

GROUP by

to_char(created,'YYYY-MON-DD')

ORDER BY 1 DESC;

-------------------------------------------------------------------------------------

-- Histogram of average queue time in seconds, by hour

SELECT

to_char(created,'YYYY-MON-DD') day,

to_char(AVG(decode(to_char(created,'HH24'),'00',(started-posted)*24*60*60,NULL)),'9999') "00",

to_char(AVG(decode(to_char(created,'HH24'),'01',(started-posted)*24*60*60,NULL)),'9999') "01",

to_char(AVG(decode(to_char(created,'HH24'),'02',(started-posted)*24*60*60,NULL)),'9999') "02",

to_char(AVG(decode(to_char(created,'HH24'),'03',(started-posted)*24*60*60,NULL)),'9999') "03",

to_char(AVG(decode(to_char(created,'HH24'),'04',(started-posted)*24*60*60,NULL)),'9999') "04",

to_char(AVG(decode(to_char(created,'HH24'),'05',(started-posted)*24*60*60,NULL)),'9999') "05",

to_char(AVG(decode(to_char(created,'HH24'),'06',(started-posted)*24*60*60,NULL)),'9999') "06",

to_char(AVG(decode(to_char(created,'HH24'),'07',(started-posted)*24*60*60,NULL)),'9999') "07",

to_char(AVG(decode(to_char(created,'HH24'),'08',(started-posted)*24*60*60,NULL)),'9999') "08",

to_char(AVG(decode(to_char(created,'HH24'),'09',(started-posted)*24*60*60,NULL)),'9999') "09",

to_char(AVG(decode(to_char(created,'HH24'),'10',(started-posted)*24*60*60,NULL)),'9999') "10",

to_char(AVG(decode(to_char(created,'HH24'),'11',(started-posted)*24*60*60,NULL)),'9999') "11",

to_char(AVG(decode(to_char(created,'HH24'),'12',(started-posted)*24*60*60,NULL)),'9999') "12",

to_char(AVG(decode(to_char(created,'HH24'),'13',(started-posted)*24*60*60,NULL)),'9999') "13",

to_char(AVG(decode(to_char(created,'HH24'),'14',(started-posted)*24*60*60,NULL)),'9999') "14",

to_char(AVG(decode(to_char(created,'HH24'),'15',(started-posted)*24*60*60,NULL)),'9999') "15",

to_char(AVG(decode(to_char(created,'HH24'),'16',(started-posted)*24*60*60,NULL)),'9999') "16",

to_char(AVG(decode(to_char(created,'HH24'),'17',(started-posted)*24*60*60,NULL)),'9999') "17",

to_char(AVG(decode(to_char(created,'HH24'),'18',(started-posted)*24*60*60,NULL)),'9999') "18",

to_char(AVG(decode(to_char(created,'HH24'),'19',(started-posted)*24*60*60,NULL)),'9999') "19",

to_char(AVG(decode(to_char(created,'HH24'),'20',(started-posted)*24*60*60,NULL)),'9999') "20",

to_char(AVG(decode(to_char(created,'HH24'),'21',(started-posted)*24*60*60,NULL)),'9999') "21",

to_char(AVG(decode(to_char(created,'HH24'),'22',(started-posted)*24*60*60,NULL)),'9999') "22",

to_char(AVG(decode(to_char(created,'HH24'),'23',(started-posted)*24*60*60,NULL)),'9999') "23"

from

ifsapp.transaction_sys_local_tab

WHERE state IN ('Ready' ,'Warning')

--AND procedure_name ='Cash_Receipt_Util_API.Validate_And_Inventory_'

--AND queue_id=0

GROUP by

to_char(created,'YYYY-MON-DD')

ORDER BY 1 DESC;

-------------------------------------------------------------------------------------

-- Histogram of average execution time in seconds, by hour

SELECT

to_char(created,'YYYY-MON-DD') day,

to_char(AVG(decode(to_char(created,'HH24'),'00',(executed-started)*24*60*60,NULL)),'99999') "00",

to_char(AVG(decode(to_char(created,'HH24'),'01',(executed-started)*24*60*60,NULL)),'99999') "01",

to_char(AVG(decode(to_char(created,'HH24'),'02',(executed-started)*24*60*60,NULL)),'99999') "02",

to_char(AVG(decode(to_char(created,'HH24'),'03',(executed-started)*24*60*60,NULL)),'99999') "03",

to_char(AVG(decode(to_char(created,'HH24'),'04',(executed-started)*24*60*60,NULL)),'99999') "04",

to_char(AVG(decode(to_char(created,'HH24'),'05',(executed-started)*24*60*60,NULL)),'99999') "05",

to_char(AVG(decode(to_char(created,'HH24'),'06',(executed-started)*24*60*60,NULL)),'99999') "06",

to_char(AVG(decode(to_char(created,'HH24'),'07',(executed-started)*24*60*60,NULL)),'99999') "07",

to_char(AVG(decode(to_char(created,'HH24'),'08',(executed-started)*24*60*60,NULL)),'99999') "08",

to_char(AVG(decode(to_char(created,'HH24'),'09',(executed-started)*24*60*60,NULL)),'99999') "09",

to_char(AVG(decode(to_char(created,'HH24'),'10',(executed-started)*24*60*60,NULL)),'99999') "10",

to_char(AVG(decode(to_char(created,'HH24'),'11',(executed-started)*24*60*60,NULL)),'99999') "11",

to_char(AVG(decode(to_char(created,'HH24'),'12',(executed-started)*24*60*60,NULL)),'99999') "12",

to_char(AVG(decode(to_char(created,'HH24'),'13',(executed-started)*24*60*60,NULL)),'99999') "13",

to_char(AVG(decode(to_char(created,'HH24'),'14',(executed-started)*24*60*60,NULL)),'99999') "14",

to_char(AVG(decode(to_char(created,'HH24'),'15',(executed-started)*24*60*60,NULL)),'99999') "15",

to_char(AVG(decode(to_char(created,'HH24'),'16',(executed-started)*24*60*60,NULL)),'99999') "16",

to_char(AVG(decode(to_char(created,'HH24'),'17',(executed-started)*24*60*60,NULL)),'99999') "17",

to_char(AVG(decode(to_char(created,'HH24'),'18',(executed-started)*24*60*60,NULL)),'99999') "18",

to_char(AVG(decode(to_char(created,'HH24'),'19',(executed-started)*24*60*60,NULL)),'99999') "19",

to_char(AVG(decode(to_char(created,'HH24'),'20',(executed-started)*24*60*60,NULL)),'99999') "20",

to_char(AVG(decode(to_char(created,'HH24'),'21',(executed-started)*24*60*60,NULL)),'99999') "21",

to_char(AVG(decode(to_char(created,'HH24'),'22',(executed-started)*24*60*60,NULL)),'99999') "22",

to_char(AVG(decode(to_char(created,'HH24'),'23',(executed-started)*24*60*60,NULL)),'99999') "23"

from

transaction_sys_local_tab

WHERE state IN ('Ready' ,'Warning')

--AND procedure_name ='Cash_Receipt_Util_API.Validate_And_Inventory_'

--AND queue_id=0

GROUP by

to_char(created,'YYYY-MON-DD')

ORDER BY 1 DESC;

-------------------------------------------------------------------------------------

-- After finding an hour where queue time jumped, list the batch jobs waiting in that hour

select to_char(a.created,'YYYY-MON-DD HH24:MM') TIME,

a.procedure_name,

(a.executed-a.started)*24*60*60 Procdure_Time,

(a.started-a.posted)*24*60*60 Que_Time,

(a.executed-a.posted)*24*60*60 Total_Time

from

transaction_sys_local_tab a

WHERE state IN ('Ready' ,'Warning')

AND to_char(created,'YYYY-MON-DD HH24') = '2010-AUG-24 14' -- set the drill-down hour here

AND queue_id=0

ORDER BY 3 DESC;

-------------------------------------------------------------------------------------

-- Submit a background job with PL/SQL

declare

job_key_ NUMBER;

begin

Batch_SYS.New_Job_App_Owner__(job_key_, 'Transaction_SYS.Process_All_Pending__('||'0'||','''||'Default Queue'||''','''||'%'||''',job_id_)', 'SYSDATE+30/86400');

end;

Did you find what you're looking for? If not:

Sign up

Login to the community