OraFAQ Forum: SQL & PL/SQL » Counting minutes in a date/time range

Home » SQL & PL/SQL » SQL & PL/SQL » Counting minutes in a date/time range

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Counting minutes in a date/time range [message #660044]

Tue, 07 February 2017 17:41

dale2k9
Messages: 5
Registered: February 2017

Junior Member

I have a table of process runs where the structure is basically jobname, startdatetime, enddatetime. It spans the past year and has a few million rows. The time span for a jobrun could be from a few seconds to a few hours. In order to get a view of system utilization for planning purposes, I'd like to get a total of jobs that are running on a per-minute basis. I can get the minute by epoch time/60 but I need a sum of each minute for which there is one or more jobs running across all of the entries.

My first thought, less thinking required but probably more work and processing, is to write a function that looks at each row, loops through the minutes from start time to stop time, and adds a row to a table for each minute, including the job name, and I can group with count on the table later. The advantage to this is that I can drill in to get a few of each jobname as well.

Alternatively, I can add a row for each minute found if there isn't a row and increment the row for each minute across all of the job runs. This is a much smaller result set but doesn't give the drill down opportunity.

What other options are there? How could I select the existing data to get a sum of utilized minutes without making a copy?

Report message to a moderator

Re: Counting minutes in a date/time range [message #660046 is a reply to message #660044]

Tue, 07 February 2017 18:22

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read..

In my opinion, you are wasting your time.
Garbage In, Garbage Out.

Not all jobs are created equal.
Some jobs are CPU bound.
Some jobs are I/O limited.
Some jobs are memory hogs.
Some jobs may latch objects other jobs require & extend run times.

What is OS name & version?
Is there only 1 instance on this system?
Is this system a dedicated Oracle database server system?

Report message to a moderator

Re: Counting minutes in a date/time range [message #660054 is a reply to message #660044]

Wed, 08 February 2017 01:33

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

In order to get a view of system utilization for planning purposes, I'd like to get a total of jobs that are running on a per-minute basis.

If this is the real question then better do it at system level then you have ALL the processes and not part of them.

By the way, ALWAYS post your Oracle version (with 4 decimals) and OS.

Report message to a moderator

Re: Counting minutes in a date/time range [message #660084 is a reply to message #660054]

Wed, 08 February 2017 11:09

dale2k9
Messages: 5
Registered: February 2017

Junior Member

Thanks for the replies. The oracle version is Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

It's not a waste of time. If there are segments of time with no jobs running then those are system availability gaps that can be utilized. I can also combine the data with system logs monitoring logs and narrow down high-resource utilization jobs. In any case, and no disrespect intended, my question was about how to rather than whether to. My boss has already decided on the whether to; I just need to get it done.

Thanks.

Report message to a moderator

Re: Counting minutes in a date/time range [message #660086 is a reply to message #660084]

Wed, 08 February 2017 11:26

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I just need to get it done.

Does any database processing occur outside batch jobs that were logged into RAW_INPUT table?

You were asked TWICE for OS name & version & decided to not provide requested detail. WHY?
If you won't answer our question why should we answer your questions.

Nobody here prevents you from completing this task.

Are you saying the the only input for this task is a 3 column table containing one VARCHAR2 & two DATE columns?

Please post CREATE TABLE statement for the table to collect the results.
Please explain in detail the processing that is required to convert the input rows into output rows.

Report message to a moderator

Re: Counting minutes in a date/time range [message #660087 is a reply to message #660044]

Wed, 08 February 2017 11:41

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

Not having your table definition, I can't test but is this logic something that you could work from:

WITH minutes
     AS (SELECT To_date('2016-01-01', 'yyyy-mm-dd') + ROWNUM / 1440 AS minute
         FROM   dual
         CONNECT BY LEVEL < 365 * 1440)
SELECT minute,
       Count(*)
FROM   minutes
       join process_run_table
         ON( minutes.minute BETWEEN process_run_table.startdatetime AND
                                        process_run_table.enddatetime )
GROUP  BY minute;

Report message to a moderator

Re: Counting minutes in a date/time range [message #660096 is a reply to message #660087]

Wed, 08 February 2017 16:47

dale2k9
Messages: 5
Registered: February 2017

Junior Member

I don't know the OS version other than it is some version of AIX 6. I'm not a DBA or a Unix admin; I am a developer.

I can't give the DDL for creating the complete source table. I am not the owner of that data. The three columns I described clearly show the problem.

I don't have a target table structure though I can easily come up with one. I only need jobname and an integer minutes where there are multiple records for a jobname and minutes with one record for every minute that the job was running. For instance, 10 minutes today is 10 records. 9 minutes yesterday is 9 records, total 19 records for jobA so far.

Report message to a moderator

Re: Counting minutes in a date/time range [message #660097 is a reply to message #660096]

Wed, 08 February 2017 18:13

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

dale2k9 wrote on Wed, 08 February 2017 14:47

I don't have a target table structure though I can easily come up with one. I only need jobname and an integer minutes where there are multiple records for a jobname and minutes with one record for every minute that the job was running. For instance, 10 minutes today is 10 records. 9 minutes yesterday is 9 records, total 19 records for jobA so far.

If I implemented above, then nobody will know exactly when jobA was active.

CREATE TABLE JOBS_DENORMALIZED (
NOW_TIME DATE,
JOB_NAME VARCHAR2(30),
MINUTES_COUNT NUMBER);

read record from summary file
WHILE <MORE_RECORDS> LOOP
NEXT_MINUTE := START_TIME;
WHILE NEXT_MINUTE <= END_TIME LOOP
INSERT INTO JOBS_DENORMALIZED VALUES(NEXT_MINUTE, JOB_NAME, 1);
NEXT_MINUTE := NEXT_MINUTE+(1/24/60);
END LOOP;
READ NEXT RECORD FROM SUMMARY FILE;
END LOOP;

upon completion JOBS_DENORMALIZED contain 1 record for every minute any job was active.

I still contend that the content JOBS_DENORMALIZED will be essentially USELESS!

ENJOY!

Report message to a moderator

Re: Counting minutes in a date/time range [message #660141 is a reply to message #660097]

Thu, 09 February 2017 11:45

dale2k9
Messages: 5
Registered: February 2017

Junior Member

Thanks for the help. That's what I was afraid I'd have to do - create a row in a target table for each job-minute. I was hoping to find some unknown-to-me aggregate logic that would prevent that.

I think the table would be useful if it gets us the timeline view we need, and it would, but there must certainly be some better way or other tool to do this analysis. I'll keep looking before I try doing this.

Thanks again.

Dale

Report message to a moderator

Re: Counting minutes in a date/time range [message #660142 is a reply to message #660141]

Thu, 09 February 2017 12:09

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Just use the code suggested by John Watson above (or a variant). You do not need a static table

[Updated on: Thu, 09 February 2017 12:15]

Report message to a moderator

Re: Counting minutes in a date/time range [message #660254 is a reply to message #660087]

Mon, 13 February 2017 09:00

dale2k9
Messages: 5
Registered: February 2017

Junior Member

John Watson wrote on Wed, 08 February 2017 11:41

Not having your table definition, I can't test but is this logic something that you could work from:

WITH minutes
     AS (SELECT To_date('2016-01-01', 'yyyy-mm-dd') + ROWNUM / 1440 AS minute
         FROM   dual
         CONNECT BY LEVEL < 365 * 1440)
SELECT minute,
       Count(*)
FROM   minutes
       join process_run_table
         ON( minutes.minute BETWEEN process_run_table.startdatetime AND
                                        process_run_table.enddatetime )
GROUP  BY minute;

This is great. It's just the kind of thing I was looking for and it worked perfectly out of the box with just changing the table name. Thanks and to Bill B as well.

I have the existing job report table where I can see when Job A was running. This doesn't give me the drill down capabilities directly but that won't be tough to come up with. Drill down in this case would be to click a minute, or perhaps a range of minutes, in a report and see which jobs were running at that time.

My SQL background has been mostly SQL Server and I wasn't familiar at all with CONNECT BY or LEVEL. I'll spend some time looking at those.

Report message to a moderator

Re: Counting minutes in a date/time range [message #660257 is a reply to message #660254]

Mon, 13 February 2017 09:47

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

That particular usage of connect by and level is how you do a row generator query.
Run that bit of the query by itself and you'll get n-1 rows where n is the value after the less than sign.

Report message to a moderator

Re: Counting minutes in a date/time range [message #660260 is a reply to message #660254]

Mon, 13 February 2017 10:18

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

My SQL background has been mostly SQL Server and I wasn't familiar at all with CONNECT BY or LEVEL. I'll spend some time looking at those.

This is just a very particular usage of CONNECT BY, see row generator.
The original and general purpose of CONNECT BY is hierarchical queries.

Report message to a moderator

Previous Topic:	Full outer join with ZERO rows in Second table
Next Topic:	PL/SQL: numeric or value error: character string buffer too small

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 10:33:04 CDT 2024