algorithm - multithreading - Query to show total time elapsed working on a set of processes -

algorithm - multithreading - Query to show total time elapsed working on a set of processes -

i'm trying determine total elapsed time finish set of processes multithreaded application keeps track of start , end times in table. easiest way describe problem example.

here's dumbed-down version of table (we'll phone call processes) i'm working with:

| id | start_date | end_date | --------------------------------------------------- | 1 | 07/15/2011 12:00:00 | 07/15/2011 12:01:00 | | 2 | 07/15/2011 12:00:00 | 07/15/2011 12:02:00 | | 3 | 07/15/2011 12:00:00 | 07/15/2011 12:03:00 | | 4 | 07/15/2011 12:01:00 | 07/15/2011 12:05:00 | | 5 | 07/15/2011 12:01:00 | 07/15/2011 12:03:00 | | 6 | 07/15/2011 12:03:00 | 07/15/2011 12:04:00 | | 7 | 07/15/2011 12:03:00 | 07/15/2011 12:07:00 | | 8 | 07/15/2011 12:03:00 | 07/15/2011 12:06:00 | | 9 | 07/15/2011 12:04:00 | 07/15/2011 12:05:00 | | 10 | 07/15/2011 12:05:00 | 07/15/2011 12:07:00 | | 11 | 07/15/2011 12:08:00 | 07/15/2011 12:09:00 |

with such little sample of info it's easy plenty visualize (i'm assuming thread can finish process , instantaneously pick next 1 no overhead purposes of question)

12:xx: | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | thread1: 1---4---------------10------] 11--] thread2: 2-------] 6---9---] thread3: 3-----------7---------------] thread4: 5-------8-----------]

and there can tell total time spent working on 11 processes 8 minutes.

the problem arises because dealing thousands of records, , there periods of time no processing happening @ all.

how can result using pl/sql query selecting table?

unless i'm missing something, need difference between lowest start date , highest end date:

select (max(end_date) - min(start_time))*24*60 elapsed_time processes;

this'll homecoming time elapsed in minutes.

based on comment, seems want sum of time elapsed each process? if that's case, it's smallish variation on before answer:

select sum(end_date - start_time)*24*60 elapsed_time processes;

obviously reply doesn't deal identify processes total for, that's not addressed in question @ all, i'm assuming that's not issue.

i see doesn't provide reply you're looking either, because counts time worked multiple processes multiple times. believe next work. based on @rajesh's sample info other answer.

select sum (new_end - new_start) * 24 * 60 * 60 (select distinct min (least (a.start_time, b.start_time)) new_start, max (greatest (a.end_time, b.end_time)) new_end (select rownum rnum, t1.* t1) inner bring together t1 b on a.start_time <= b.end_time , a.end_time >= b.start_time grouping rnum)

basically, we're joining each every other row overlaps , taking earliest start time , latest end time each of pairings. 1 time have that, can utilize distinct set remove duplicates, give distinct time periods.

i believe there still 1 flaw here, cascading time periods: period overlaps period b , period b overlaps period c, period c not overlap period a. think issue solvable, haven't quite figured out yet.

okay, 1 more time: rather joining 1 time between table , itself, version uses recursive bring together follow of cascades end point.

select sum (new_end - new_start) * 24 * 60 * 60 (select distinct min (start_time) new_start, max (end_time) new_end (select start_time, end_time, connect_by_root rnum rnum (select rownum rnum, t1.* t1) connect nocycle a.start_time <= prior end_time , a.end_time >= prior start_time) grouping rnum);

multithreading algorithm oracle plsql