Thinking Out Loud

October 29, 2016

Working With Interval Datatype

Filed under: 10g,oracle — mdinh @ 3:41 pm

Here is the scope:

The job takes 42m to complete and monitoring threshold is 30m.

If 42m is the typical to complete, then we should increase the threshold to avoid noise.

If 42m is not the typical time to complete, then we should investigate to see if this is an anomaly.

From dba_scheduler_job_run_details, RUN_DURATION  data type is INTERVAL DAY(3) TO SECOND(0).

Querying dba_scheduler_job_run_details provides the following result.

This seems to be a lot of work trying to identify jobs running longer that 30m, plus there can be errors as a run could have been missed which I did not highlight.

This test case was for 10.2.0.4 but imagine it would for all versions higher.

-- dba_scheduler_jobs
Enter value for 1: PURGE_JOB

OWNER			        JOB_NAME 		      STATE	      REPEAT_INTERVAL						   NEXT_RUN_DATE
------------------------------ ------------------------------ --------------- ------------------------------------------------------------ --------------------------------------------------
DEMODB01		       PURGE_JOB	      SCHEDULED       Freq=Hourly						   28-OCT-16 02.00.00.000000 PM -05:00

-- dba_scheduler_job_run_details

LOG_DATE			    JOB_NAME			   STATUS     ACTUAL_START_DATE 				 RUN_DURATION
----------------------------------- ------------------------------ ---------- -------------------------------------------------- ---------------
28-OCT-16 01.18.34.766342 PM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 01.00.00.101332 PM -05:00		 +000 00:18:35
28-OCT-16 12.18.50.630375 PM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 12.00.00.128330 PM -05:00		 +000 00:18:50
28-OCT-16 11.19.39.302422 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 11.00.00.096290 AM -05:00		 +000 00:19:39
28-OCT-16 10.19.06.631086 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 10.00.00.098943 AM -05:00		 +000 00:19:07
28-OCT-16 09.21.55.042321 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 09.00.00.142871 AM -05:00		 +000 00:21:55
28-OCT-16 08.22.05.168894 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 08.00.00.114841 AM -05:00		 +000 00:22:05
28-OCT-16 07.20.58.795685 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 07.00.00.615018 AM -05:00		 +000 00:20:58
28-OCT-16 06.19.32.286364 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 06.00.04.355748 AM -05:00		 +000 00:19:28
28-OCT-16 05.22.12.990840 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 05.00.00.108447 AM -05:00		 +000 00:22:13
28-OCT-16 04.22.46.316064 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 04.00.00.822142 AM -05:00		 +000 00:22:45
28-OCT-16 03.23.49.518493 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 03.00.00.767310 AM -05:00		 +000 00:23:49
28-OCT-16 02.33.20.747873 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 02.00.00.689275 AM -05:00		 +000 00:33:20
28-OCT-16 01.42.40.477522 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 01.00.00.687059 AM -05:00		 +000 00:42:40
28-OCT-16 12.24.03.200350 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 12.00.00.687291 AM -05:00		 +000 00:24:03
27-OCT-16 11.21.45.310127 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 11.00.00.174274 PM -05:00		 +000 00:21:45
27-OCT-16 10.22.04.226755 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 10.00.00.100101 PM -05:00		 +000 00:22:04
27-OCT-16 09.20.46.491481 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 09.00.00.095188 PM -05:00		 +000 00:20:46
27-OCT-16 08.20.11.936472 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 08.00.01.162137 PM -05:00		 +000 00:20:11
27-OCT-16 07.16.30.836698 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 07.00.00.122310 PM -05:00		 +000 00:16:31
27-OCT-16 06.17.19.315011 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 06.00.00.683161 PM -05:00		 +000 00:17:19
27-OCT-16 05.17.40.010428 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 05.00.00.093667 PM -05:00		 +000 00:17:40
27-OCT-16 04.18.08.064115 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 04.00.00.227937 PM -05:00		 +000 00:18:08
27-OCT-16 03.19.58.561961 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 03.00.00.136952 PM -05:00		 +000 00:19:58
27-OCT-16 02.17.30.492761 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 02.00.00.149822 PM -05:00		 +000 00:17:30
27-OCT-16 01.17.44.490772 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 01.00.00.084995 PM -05:00		 +000 00:17:44
27-OCT-16 12.19.29.486819 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 12.00.00.665087 PM -05:00		 +000 00:19:29
27-OCT-16 11.19.43.126793 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 11.00.00.095392 AM -05:00		 +000 00:19:43
27-OCT-16 10.20.28.096927 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 10.00.00.098227 AM -05:00		 +000 00:20:28
27-OCT-16 09.20.59.774236 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 09.00.00.087305 AM -05:00		 +000 00:21:00
27-OCT-16 08.20.11.194525 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 08.00.00.095362 AM -05:00		 +000 00:20:11
27-OCT-16 07.21.19.440710 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 07.00.00.093116 AM -05:00		 +000 00:21:19
27-OCT-16 06.21.21.777725 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 06.00.00.729176 AM -05:00		 +000 00:21:21
27-OCT-16 05.22.00.235810 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 05.00.00.093358 AM -05:00		 +000 00:22:00
27-OCT-16 04.22.41.035983 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 04.00.00.109282 AM -05:00		 +000 00:22:41
27-OCT-16 03.23.03.747790 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 03.00.00.740992 AM -05:00		 +000 00:23:03
27-OCT-16 02.28.28.902795 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 02.00.00.098180 AM -05:00		 +000 00:28:29
27-OCT-16 01.38.20.999365 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 01.00.00.618087 AM -05:00		 +000 00:38:20
27-OCT-16 12.22.48.596935 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 12.00.00.134944 AM -05:00		 +000 00:22:48
26-OCT-16 11.22.39.417841 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 11.00.00.601951 PM -05:00		 +000 00:22:39
26-OCT-16 10.21.24.088692 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 10.00.00.700209 PM -05:00		 +000 00:21:23
26-OCT-16 09.21.48.083949 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 09.00.00.108737 PM -05:00		 +000 00:21:48
26-OCT-16 08.20.36.600056 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 08.00.00.599238 PM -05:00		 +000 00:20:36
26-OCT-16 07.21.26.973000 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 07.00.00.170795 PM -05:00		 +000 00:21:27
26-OCT-16 06.21.13.513245 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 06.00.00.661959 PM -05:00		 +000 00:21:13
26-OCT-16 05.20.38.611174 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 05.00.00.095111 PM -05:00		 +000 00:20:39
26-OCT-16 04.19.43.026097 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 04.00.00.647115 PM -05:00		 +000 00:19:42
26-OCT-16 03.22.25.930388 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 03.00.00.099210 PM -05:00		 +000 00:22:26
26-OCT-16 02.22.28.929046 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 02.00.00.084756 PM -05:00		 +000 00:22:29
26-OCT-16 01.22.35.673399 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 01.00.00.092354 PM -05:00		 +000 00:22:36
26-OCT-16 12.22.51.518382 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 12.00.00.098155 PM -05:00		 +000 00:22:51
26-OCT-16 11.25.12.885694 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 11.00.00.097514 AM -05:00		 +000 00:25:13
26-OCT-16 10.25.30.697623 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 10.00.00.109984 AM -05:00		 +000 00:25:31
26-OCT-16 09.25.48.331970 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 09.00.00.650305 AM -05:00		 +000 00:25:48
26-OCT-16 08.23.00.786181 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 08.00.00.092182 AM -05:00		 +000 00:23:01
26-OCT-16 07.23.09.292134 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 07.00.00.706906 AM -05:00		 +000 00:23:09
26-OCT-16 06.23.26.838110 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 06.00.00.737269 AM -05:00		 +000 00:23:26
26-OCT-16 05.23.46.628555 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 05.00.00.779244 AM -05:00		 +000 00:23:46
26-OCT-16 04.26.10.248261 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 04.00.00.118811 AM -05:00		 +000 00:26:10
26-OCT-16 03.26.45.574405 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 03.00.00.868199 AM -05:00		 +000 00:26:45
26-OCT-16 02.29.59.220470 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 02.00.00.716143 AM -05:00		 +000 00:29:59
26-OCT-16 01.41.19.190372 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 01.00.00.671400 AM -05:00		 +000 00:41:19
26-OCT-16 12.40.14.668943 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 12.00.00.686242 AM -05:00		 +000 00:40:14
25-OCT-16 11.25.01.222486 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 11.00.00.094405 PM -05:00		 +000 00:25:01
25-OCT-16 10.25.16.188272 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 10.00.00.093149 PM -05:00		 +000 00:25:16
25-OCT-16 09.22.50.957227 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 09.00.00.685572 PM -05:00		 +000 00:22:50
25-OCT-16 08.23.24.366195 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 08.00.00.681146 PM -05:00		 +000 00:23:24
25-OCT-16 07.21.34.877025 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 07.00.00.679188 PM -05:00		 +000 00:21:34
25-OCT-16 06.22.01.687270 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 06.00.00.705931 PM -05:00		 +000 00:22:01
25-OCT-16 05.22.59.985410 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 05.00.00.058401 PM -05:00		 +000 00:23:00
25-OCT-16 04.23.47.510414 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 04.00.00.117113 PM -05:00		 +000 00:23:47
25-OCT-16 03.23.04.877802 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 03.00.00.099222 PM -05:00		 +000 00:23:05
25-OCT-16 02.26.03.301910 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 02.00.00.116152 PM -05:00		 +000 00:26:03
25-OCT-16 01.24.27.430723 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 01.00.00.117626 PM -05:00		 +000 00:24:27
25-OCT-16 12.24.36.971637 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 12.00.00.664118 PM -05:00		 +000 00:24:36
25-OCT-16 11.23.58.307402 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 11.00.00.096260 AM -05:00		 +000 00:23:58
25-OCT-16 10.24.31.716126 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 10.00.00.092256 AM -05:00		 +000 00:24:32
25-OCT-16 09.24.10.974503 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 09.00.00.100348 AM -05:00		 +000 00:24:11
25-OCT-16 08.22.53.833215 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 08.00.00.082274 AM -05:00		 +000 00:22:54
25-OCT-16 07.24.30.938431 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 07.00.00.692159 AM -05:00		 +000 00:24:30
25-OCT-16 06.23.52.431531 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 06.00.00.093246 AM -05:00		 +000 00:23:52
25-OCT-16 05.24.41.532133 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 05.00.00.729381 AM -05:00		 +000 00:24:41
25-OCT-16 04.23.25.958305 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 04.00.00.208211 AM -05:00		 +000 00:23:26
25-OCT-16 03.25.01.192060 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 03.00.00.106130 AM -05:00		 +000 00:25:01
25-OCT-16 02.28.53.376223 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 02.00.00.662053 AM -05:00		 +000 00:28:53
25-OCT-16 01.40.10.906657 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 01.00.00.690348 AM -05:00		 +000 00:40:10
25-OCT-16 12.26.06.567009 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 12.00.00.101229 AM -05:00		 +000 00:26:06
24-OCT-16 11.23.44.968956 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 11.00.00.112954 PM -05:00		 +000 00:23:45
24-OCT-16 10.25.27.790486 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 10.00.02.818356 PM -05:00		 +000 00:25:25
24-OCT-16 09.21.43.329295 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 09.00.00.143735 PM -05:00		 +000 00:21:43
24-OCT-16 08.23.53.456424 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 08.00.00.699357 PM -05:00		 +000 00:23:53
24-OCT-16 07.22.46.201840 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 07.00.00.108008 PM -05:00		 +000 00:22:46
24-OCT-16 06.24.31.090402 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 06.00.00.712135 PM -05:00		 +000 00:24:30
24-OCT-16 05.22.41.748800 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 05.00.00.092252 PM -05:00		 +000 00:22:42
24-OCT-16 04.24.25.687414 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 04.00.00.093241 PM -05:00		 +000 00:24:26
24-OCT-16 03.24.51.693561 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 03.00.00.734264 PM -05:00		 +000 00:24:51
24-OCT-16 02.23.25.886242 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 02.00.00.689991 PM -05:00		 +000 00:23:25
24-OCT-16 01.26.34.238241 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 01.00.00.097179 PM -05:00		 +000 00:26:34
24-OCT-16 12.26.56.946893 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 12.00.00.097951 PM -05:00		 +000 00:26:57

98 rows selected.

SQL >

Here’s the better way for  doing this using extract function.

Datatype is data type is INTERVAL DAY(3) TO SECOND(0)..

24 hours per day and 60 minutes per hour.

SQL > r
  1  SELECT log_date, job_name, extract(day from 24*60*RUN_DURATION) mins
  2  FROM dba_scheduler_job_run_details
  3  WHERE extract(day from 24*60*RUN_DURATION)>30
  4  AND job_name='PURGE_JOB'
  5  and log_date>sysdate-90
  6  ORDER BY 1 DESC
  7*

LOG_DATE			    JOB_NAME				 MINS
----------------------------------- ------------------------------ ----------
28-OCT-16 02.33.20.747873 AM -05:00 PURGE_JOB			   33
28-OCT-16 01.42.40.477522 AM -05:00 PURGE_JOB			   42
27-OCT-16 01.38.20.999365 AM -05:00 PURGE_JOB			   38
26-OCT-16 01.41.19.190372 AM -05:00 PURGE_JOB			   41
26-OCT-16 12.40.14.668943 AM -05:00 PURGE_JOB			   40
25-OCT-16 01.40.10.906657 AM -05:00 PURGE_JOB			   40
24-OCT-16 03.31.51.372573 AM -05:00 PURGE_JOB			   31
24-OCT-16 02.31.55.268260 AM -05:00 PURGE_JOB			   31
24-OCT-16 01.45.33.525611 AM -05:00 PURGE_JOB			   45
22-OCT-16 05.39.23.980162 AM -05:00 PURGE_JOB			   39
22-OCT-16 04.31.25.202116 AM -05:00 PURGE_JOB			   31
21-OCT-16 01.38.29.977490 AM -05:00 PURGE_JOB			   38
20-OCT-16 01.40.58.167010 AM -05:00 PURGE_JOB			   40
19-OCT-16 01.32.25.295604 AM -05:00 PURGE_JOB			   32
18-OCT-16 01.32.53.733495 AM -05:00 PURGE_JOB			   32
17-OCT-16 01.31.49.296251 AM -05:00 PURGE_JOB			   31
14-OCT-16 03.31.43.274818 AM -05:00 PURGE_JOB			   31
14-OCT-16 02.36.02.084047 AM -05:00 PURGE_JOB			   36
14-OCT-16 01.40.30.270864 AM -05:00 PURGE_JOB			   40
13-OCT-16 01.40.32.792297 AM -05:00 PURGE_JOB			   40
12-OCT-16 03.31.59.942443 AM -05:00 PURGE_JOB			   31
12-OCT-16 02.35.10.422916 AM -05:00 PURGE_JOB			   35
12-OCT-16 01.44.37.543112 AM -05:00 PURGE_JOB			   44
11-OCT-16 01.37.06.018139 AM -05:00 PURGE_JOB			   37
10-OCT-16 03.31.07.278843 AM -05:00 PURGE_JOB			   31
10-OCT-16 02.33.08.974312 AM -05:00 PURGE_JOB			   33
10-OCT-16 01.44.18.952183 AM -05:00 PURGE_JOB			   44
08-OCT-16 05.34.36.615095 AM -05:00 PURGE_JOB			   34
03-OCT-16 02.34.54.308012 AM -05:00 PURGE_JOB			   34
03-OCT-16 01.38.21.388805 AM -05:00 PURGE_JOB			   38
02-OCT-16 04.33.57.111031 PM -05:00 PURGE_JOB			   33
02-OCT-16 03.31.53.305048 AM -05:00 PURGE_JOB			   31
02-OCT-16 01.38.53.896792 AM -05:00 PURGE_JOB			   38
30-SEP-16 04.31.26.036246 AM -05:00 PURGE_JOB			   31
30-SEP-16 01.42.47.725811 AM -05:00 PURGE_JOB			   42
29-SEP-16 01.35.00.663317 AM -05:00 PURGE_JOB			   35

36 rows selected.

SQL > 
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: