Home » SQL & PL/SQL » SQL & PL/SQL » Counting minutes in a date/time range
Counting minutes in a date/time range [message #660044] Tue, 07 February 2017 17:41 Go to next message
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?
Re: Counting minutes in a date/time range [message #660046 is a reply to message #660044] Tue, 07 February 2017 18:22 Go to previous messageGo to next message
BlackSwan
Messages: 25797
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?
Re: Counting minutes in a date/time range [message #660054 is a reply to message #660044] Wed, 08 February 2017 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
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.

Re: Counting minutes in a date/time range [message #660084 is a reply to message #660054] Wed, 08 February 2017 11:09 Go to previous messageGo to next message
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.
Re: Counting minutes in a date/time range [message #660086 is a reply to message #660084] Wed, 08 February 2017 11:26 Go to previous messageGo to next message
BlackSwan
Messages: 25797
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.
Re: Counting minutes in a date/time range [message #660087 is a reply to message #660044] Wed, 08 February 2017 11:41 Go to previous messageGo to next message
John Watson
Messages: 7220
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; 
Re: Counting minutes in a date/time range [message #660096 is a reply to message #660087] Wed, 08 February 2017 16:47 Go to previous messageGo to next message
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.


Re: Counting minutes in a date/time range [message #660097 is a reply to message #660096] Wed, 08 February 2017 18:13 Go to previous messageGo to next message
BlackSwan
Messages: 25797
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!
Re: Counting minutes in a date/time range [message #660141 is a reply to message #660097] Thu, 09 February 2017 11:45 Go to previous messageGo to next message
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
Re: Counting minutes in a date/time range [message #660142 is a reply to message #660141] Thu, 09 February 2017 12:09 Go to previous messageGo to next message
Bill B
Messages: 1709
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 Go to previous messageGo to next message
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.

Re: Counting minutes in a date/time range [message #660257 is a reply to message #660254] Mon, 13 February 2017 09:47 Go to previous messageGo to next message
cookiemonster
Messages: 12992
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.
Re: Counting minutes in a date/time range [message #660260 is a reply to message #660254] Mon, 13 February 2017 10:18 Go to previous message
Michel Cadot
Messages: 65317
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.

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:
  


Current Time: Sun Jan 21 03:43:32 CST 2018

Total time taken to generate the page: 0.06238 seconds