terug naar overzicht




Rob van den Berg

+31 6 22 98 69 27


Taming outbound call rate

Suppose you are the developer of a database service which can be called any number of times. This database service itself just registers the call, and is allowed to immediately make an outbound call to another service, for example to enhance information presented by the initial inbound call. Now suppose the interactions with this service are restricted by a limited outbound call rate, which means you should take care it doesn’t exceed a certain maximum per second. You’d then want to optimize the number of times the API for this service is called to exactly match this allowed maximum. For simplicity, let’s assume the service API should not be called more often than 25 times per second.

  • 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')
                                    , 1
, 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.
  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
  pragma autonomous_transaction;
    insert into my_api_requests (time_of_request) values (default);
    -- call my_api_request() here ;
    when others
    then -- too many inserts per unit of time
  end do_api_request;

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

select   to_char(time_of_request,'YYYYMMDD-HH24MISS')
,        count(*)
from     my_api_requests
group by to_char(time_of_request,'YYYYMMDD-HH24MISS')
SQL> @timertest 10
succeeded api requests: 250
skipped api requests: 11679

PL/SQL procedure successfully completed.

--------------------------------------------- --------
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.

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


Meer weten over dit onderwerp?

Neem contact op met Joyce van Vliet
gang van het kantoor, vx company