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.
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.
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') /
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>
We succesfully created a mechanism to both restrict and optimize the number of times the outbound service has been called!