Taming outbound call rate

Requirements

• There’s a stored procedure which represents the referenced outbound API, we will call it my_api_request();
• There’s a table registering each successfull call to the API by the timestamp of it’s call;
• Querying the table should reveal how many calls per second were made, which should ideally be optimized to 25 times per second.

Solution setup

The solution I present heavily relies on the ordered non-cached sequence feature. The idea is to construct an expression consisting of both a time representation including date, hour, minute and seconds complemented with a value which cycles through 25 different values using the modulo function. Of course, the value of 25 corresponds to the required maximum of 25 calls per second.

Hence, first we create an ordered sequence

create sequence my_ordered_seq order nocache
/

And then we create the API-calls logging table

create table my_api_requests
( time_of_request    timestamp    default systimestamp not null 
, seq_id             number(15)   default my_ordered_seq.nextval
, req_id             varchar2(21) generated always as 
                                  ( substr
                                    ( to_char(time_of_request,'YYYYMMDD-HH24MISS')
                                      ||'.'
                                      ||lpad(mod(seq_id,25),3,'0')
                                    , 1
                                    ,21
                                    )
                                  )
, constraint my_api_requests_limit unique (req_id)
)

As you will see, each time a value is inserted to the table, a subsequent number is derived from the ordered sequence.
If there a more than 25 inserts within the same second, the module function will result in a previously used expression. This will cause a unique key constraint violation, effectively prohibiting the outbound call rate to exceed the required maximum.

Solution test

Contents of timertest.sql, which is parameterized script to allow us to test for the specified N number of seconds:

define p_nrof_sec = &1.
declare
  c_nrof_seconds constant pls_integer := &p_nrof_sec.;
  l_time_ends constant date := sysdate + c_nrof_seconds/(24*3600);

  l_succeeded_count pls_integer := 0;
  l_skipped_count pls_integer := 0;
 
  procedure do_api_request
  is
  pragma autonomous_transaction;
  begin
    insert into my_api_requests (time_of_request) values (default);
    l_succeeded_count:=l_succeeded_count+1;
    -- call my_api_request() here ;
    commit;
  exception
    when others
    then -- too many inserts per unit of time
    l_skipped_count:=l_skipped_count+1;
  end do_api_request;

begin
  while sysdate < l_time_ends
  loop
    do_api_request;
  end loop;
  dbms_output.put_line ( 'succeeded api requests: '||l_succeeded_count);
  dbms_output.put_line ( 'skipped api requests: '||l_skipped_count);
end;
/

select   to_char(time_of_request,'YYYYMMDD-HH24MISS')
,        count(*)
from     my_api_requests
group by to_char(time_of_request,'YYYYMMDD-HH24MISS')
/

Output

SQL> @timertest 10
succeeded api requests: 250
skipped api requests: 11679

PL/SQL procedure successfully completed.

TO_CHAR(TIME_OF_REQUEST,'YYYYMMDD-HH24MISS')  COUNT(*)
--------------------------------------------- --------
20160530-154401                                     25
20160530-154402                                     25
20160530-154403                                     25
20160530-154404                                     25
20160530-154405                                     25
20160530-154406                                     25
20160530-154407                                     25
20160530-154408                                     25
20160530-154409                                     25
20160530-154410                                     25
10 rows selected.

SQL>

Conculsion

We succesfully created a mechanism to both restrict and optimize the number of times the outbound service has been called!