Concurrent request scheduling explained

转载 2016年08月30日 16:42:45

摘自:https://me-dba.com/2008/09/16/concurrent-request-scheduling-explained/

Concurrent request scheduling explained

It’s quite an interesting way that’s used to implement concurrent request schedules in Oracle Applications. In this post I’ll describe how “Periodic” and “On Specific Day” type of schedules are stored in the Database and if you will be patient enough to read all the story, I’ll give you a query that can be used to report all the request schedules in the environment.

I’ll pay attention to the repeating schedules only as requests submitted for one-time execution are not too interesting, basically, they use the fnd_concurrent_requests.requested_start_date field to store the time the request has to be executed. This field is used in repeating schedules to specify the time of next execution.

There are just 2 normal types of repeating schedules.

Defining a "periodic" schedule

Defining a ‘periodic’ schedule

Periodic – the request is submitted for execution in pre-defined intervals of specified number of months, weeks, days, hours or minutes.

sdsdfsdfsdf

Definig a ‘on Specific Days’ schedule

On Specific Days – user can choose exact dates of month or days of week when the request will be executed. There’s an option to schedule requests for the last day of months too.

There’s also a 3rd option – Advanced – this is a special option that implements possibility to run requests based on schedules, that depend on different financial calendars and financial periods. This kind of scheduling is rarely used as it’s available in few Applications products only, e.g. GL. These schedules are managed by Scheduler/Prereleaser Concurrent Manager.

There are 2 tables invoved in storing the information about schedules: fnd_concurrent_requests that stores all basic information about concurrent requests including the next time the request is scheduled for, andfnd_conc_release_classes that stores information about the schedules. Both tables can be joined byrelease_class_id.

The fields we are interested in are:
fnd_conc_release_classes.CLASS_TYPE
 – contains value ‘P’ for “Periodic” schedules, “S” – for “on Specific Days” schedules and “X” – for advanced schedules.
fnd_conc_release_classes.DATE1 – 
start date of the schedule (“Start at” field in the form)
fnd_conc_release_classes.DATE2 – 
end date of the schedule (“End at” field in the form) – this information is doubled in fnd_concurrent_requests.resubmit_end_date.

fnd_conc_release_classes.CLASS_INFO – this is the most interesting field as it contains all the information needed for reschedulingThe format of the field depends on the type of schedule.

“PERIODIC” schedule

In case of Periodic schedule fnd_conc_release_classes.CLASS_INFO field contains values like “2:D:S” orX:Y:Z where:

  • X – number of months/weeks/days/hours/minutes the request has to be rescheduled from prior run.
  • Y – contains a single letter representing units
    • “M” – months;
    • “D” – days;
    • “H” – hours;
    • “N” – minutes;
    • (there is no representation of “weeks” option. If you specify interval in weeks, it’s automatically calculated and stored in “days”).
  • Z – contains a single letter to represent if the rescheduling has to be done from start or from completion of the prior run
    • S – from the start of the prior run;
    • C – from the completion of the prior run.

Some samples:

  • 30:N:S – Repeat every 30 minutes from the start of the prior run
  • 5:N:C – Repeat every 5 minutes from the completion of the prior run
  • 12:H:S – Repeat every 12 hours from the start of the prior run

It’s interesting that information about intervals of periodic schedules is duplicated in fnd_concurrent_requeststable fields RESUBMIT_INTERVAL, RESUBMIT_INTERVAL_TYPE_CODE andRESUBMIT_INTERVAL_UNIT_CODE. I haven’t yet found why’s that so.

“ON SPECIFIC DAY” schedule

In case of on Specific Day schedule fnd_conc_release_classes.CLASS_INFO field contains values like “000010000000000000000000000000010000000” – a 39 character value consisting of 0 and 1. The idea is that the placement of 1-s represent the options selected through form:

  • 1-s at places 1 to 31 – represent dates, when request has to be run, eg, if the 10th character is “1” – the request is scheduled to run on 10th day of each month;
  • character “1” at the 32nd position – specifies that the request has to be run at the last day of each month;
  • 1-s at places 33 to 39 – specifies days of week (Sunday – Saturday)the request has to be run. if the 33rd character is “1” – the request is scheduled to run each Sunday, if 34th – on Monday and so on.

Some samples:

  • 000000000000000000000000000000000000001 – Days of week: Sa
  • 111111111000000000000000000000000111110 – Dates: 1 2 3 4 5 6 7 8 9. Days of week: Mo Tu We Th Fr
  • 000000000000000000000000000000010000000 – Last day of month

Reporting all schedules

Using the information given above I wrote a “small” query to provide a report like this:

Reporting all schedules

Reporting all schedules

The query was tested in Oracle Applications versions 11.5.9, 11.5.10.1, 12.0.1 and 12.0.4 so there’s a good chance it will work on others as well. The performance of the query was OK.  but I have to say – test/analyze it before running in productio.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
select r.request_id,
       p.user_concurrent_program_name || case
         when p.user_concurrent_program_name = 'Report Set' then
          (select ' - ' || s.user_request_set_name
             from fnd_request_sets_tl s
            where s.application_id = r.argument1
              and s.request_set_id = r.argument2
              and language = 'US')
         when p.user_concurrent_program_name = 'Check Periodic Alert' then
          (select ' - ' || a.alert_name
             from alr_alerts a
            where a.application_id = r.argument1
              and a.alert_id = r.argument2
              and language = 'US')
       end concurrent_program_name,
       case
         when p.user_concurrent_program_name != 'Report Set' and
              p.user_concurrent_program_name != 'Check Periodic Alert' then
          r.argument_text
       end argument_text,
       r.requested_start_date next_run,
       r.hold_flag on_hold,
       decode(c.class_type,
              'P',
              'Periodic',
              'S',
              'On Specific Days',
              'X',
              'Advanced',
              c.class_type) schedule_type,
       case
         when c.class_type = 'P' then
          'Repeat every ' ||
          substr(c.class_info, 1, instr(c.class_info, ':') - 1) ||
          decode(substr(c.class_info, instr(c.class_info, ':', 1, 1) + 1, 1),
                 'N',
                 ' minutes',
                 'M',
                 ' months',
                 'H',
                 ' hours',
                 'D',
                 ' days') ||
          decode(substr(c.class_info, instr(c.class_info, ':', 1, 2) + 1, 1),
                 'S',
                 ' from the start of the prior run',
                 'C',
                 ' from the completion of the prior run')
         when c.class_type = 'S' then
          nvl2(dates.dates, 'Dates: ' || dates.dates || '. ', null) ||
          decode(substr(c.class_info, 32, 1), '1', 'Last day of month ') ||
          decode(sign(to_number(substr(c.class_info, 33))),
                 '1',
                 'Days of week: ' ||
                 decode(substr(c.class_info, 33, 1), '1', 'Su ') ||
                 decode(substr(c.class_info, 34, 1), '1', 'Mo ') ||
                 decode(substr(c.class_info, 35, 1), '1', 'Tu ') ||
                 decode(substr(c.class_info, 36, 1), '1', 'We ') ||
                 decode(substr(c.class_info, 37, 1), '1', 'Th ') ||
                 decode(substr(c.class_info, 38, 1), '1', 'Fr ') ||
                 decode(substr(c.class_info, 39, 1), '1', 'Sa '))
       end schedule,
       c.date1 start_date,
       c.date2 end_date,
       c.class_info
  from fnd_concurrent_requests r,
       fnd_conc_release_classes c,
       fnd_concurrent_programs_tl p,
       (SELECT release_class_id,
               substr(max(SYS_CONNECT_BY_PATH(s, ' ')), 2) dates
          FROM (select release_class_id,
                       rank() over(partition by release_class_id order by s) a,
                       s
                  from (select c.class_info,
                               l,
                               c.release_class_id,
                               decode(substr(c.class_info, l, 1),
                                      '1',
                                      to_char(l)) s
                          from (select level l from dual connect by level <= 31),
                               fnd_conc_release_classes c
                         where c.class_type = 'S')
                 where s is not null)
        CONNECT BY PRIOR
                    (a || release_class_id) = (a - 1) || release_class_id
         START WITH a = 1
         group by release_class_id) dates
 where r.phase_code = 'P'
   and c.application_id = r.release_class_app_id
   and c.release_class_id = r.release_class_id
   and nvl(c.date2, sysdate + 1) > sysdate
   and c.class_type is not null
   and p.concurrent_program_id = r.concurrent_program_id
   and p.application_id = r.program_application_id
   and p.language = 'US'
   and dates.release_class_id(+) = r.release_class_id
 order by on_hold, next_run;

I know there are some interesting lines in this query that might need some explanation, but as that is not directly connected to the topic of this post I’ll provide explanation in comments if you will ask for it.


SA-Concurrent request scheduling explained

Concurrent request scheduling explained 摘自:http://appsdbalife.wordpress.com/2008/09/16/concurrent-r...
  • kikiwhq
  • kikiwhq
  • 2014年08月12日 14:24
  • 326

Scheduling a Concurrent program

EBS中的Program是可以根据需求周期性运行的,比如‘Purge Logs and Closed System Alerts’ Program,我们需要每天运行来清理日渐庞大的日志表,周期性运行P...
  • pan_tian
  • pan_tian
  • 2012年06月28日 16:53
  • 2259

Load Balancing Scheduling Methods Explained

Submitted by davidquaid on Thu, 06/06/2013 - 12:19 There are several load balancing metho...
  • miller_lover
  • miller_lover
  • 2014年11月11日 19:38
  • 521

Jmeter之HTTP Request Defaults

转载地址:http://www.cnblogs.com/puresoul/p/4853276.html 一、HTTP Request Defaults的作用:   该组件可以为我们的htt...
  • wanglha
  • wanglha
  • 2015年10月23日 13:21
  • 584

极限编程 Extreme Programming (中英文对照)图形解释

 极限编程 Extreme Programming 作者 不详 来源 审校 BigMac[AKA]译者 march-bird lucian yjf taopin wl jazz韩伟 nullgate ...
  • SafeF8
  • SafeF8
  • 2005年01月03日 22:07
  • 4391

Concurrent Request Tables

目录(?)[-] Oracle Apps Concurrent Request Table RelationshipFND_CONCURRENT_PROGRAMSFND_CONCURRENT_R...
  • meunsina
  • meunsina
  • 2016年04月19日 14:38
  • 152

Bash One-Liners Explained 译文(四)

这是 Bash One-Liners Explained 系列的第四篇文章。在这一篇里,我会给大家介绍 Bash 命令行历史功相关的内容。我会选择用最合适的 Bash 方法,各种常见的语法和技...
  • vichie2008
  • vichie2008
  • 2015年07月30日 11:47
  • 229

Word2vec Parameter Learning Explained 论文学习笔记

原始论文:http://www-personal.umich.edu/~ronxin/pdf/w2vexp.pdf 之前学习Word2vec时,脱离了神经网络,这周补充了下相关知识,打算之后再...
  • li8630
  • li8630
  • 2015年03月27日 19:02
  • 2571

【Linux】【Kernel】BUG: scheduling while atomic问题分析

scheduling while atomic
  • vickytong1018
  • vickytong1018
  • 2017年06月07日 20:40
  • 1414

How I explained OOD to my wife

Introduction My wife Farhana wants to resume her career as a software developer (she started her c...
  • cszhouwei
  • cszhouwei
  • 2015年04月20日 19:04
  • 895
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Concurrent request scheduling explained
举报原因:
原因补充:

(最多只允许输入30个字)