APEX Web Service Request Limit

Every now and then there's a new setting worth mentioning.

The New Features page included news regarding the logging of

Requests to external web services from inside the database

Firstly, this only appears to log requests coming from within the APEX environment.
Requests from scheduled jobs will not be logged, as of 18.2.

Secondly, moving to the cloud can be ... a learning experience. Since we're now using Social Sign-in, web requests are up. We also re-configured directly listings and file fetches to use web services. Web services are great tools.

However, there's a new setting that needs guesstimating before you expect too many users to log in, otherwise you might spot the following error in your error logs:
ORA-20001: You have exceeded the maximum number of web service requests per workspace

Search for this message and you'll find Dimitri encountered it during his experiences with AOP. Pierre points also points out the setting in a forum post.

Found in the Internal workspace, under Security settings, Workspace Isolation, there is a Maximum Web Service Requests attribute. There is a workspace level setting under Manage Workspaces, Existing Workspaces, then choose your workspace.

Maximum Web Service Requests attribute

However, the instance level setting has a default of 1000. You may need to review that value if you have a reasonable number of users using web services within Oracle APEX.

Now while the item help states:

Enter the maximum number of web service requests that Application Express supports for each workspace in this instance. You can configure a more specific value at the workspace-level.

This forum response from Carsten states this applies to a rolling 24 hour window.

I immediately envisaged a JET chart in our monitoring application, so we could visualise how this figure changes over time - and to see where it peaks. After a slight hiccup, I now see this graph that tells me out limit should at least 4000, to allow wiggle room.

Plotting apex_webservice_log

The really thin bars were an accident that I ended up thinking better represents the data, as compared to being a line graph.

So as activity occurred during the day, as depicted by the green line, the rolling 24h figure in blue crept up. This was calculated using an analytic function that allows me to create a moving window on the data based on an interval of one day. And I'm summing how many at each moment, hence the sum(count(*)).

select * from (
    select request_date 
       ,sum(count(*)) over 
            (order by request_date  
             range between NUMTODSINTERVAL(1,'day') preceding 
                       and current row)  last_24hr
    from APEX_WEBSERVICE_LOG 
    where request_date > sysdate-3
    group by (request_date) 
-- must ignore first day in inline query
-- as cumulative range will be skewed
) where request_date > sysdate-2 
order by 2

The 'Hits' count in the y2 axis is rounded to 30 minutes, finding inspiration from these forum responses.
trunc(request_date)+round(to_char(request_date,'sssss')/1800)/48
Without this rounding, the chart looked too much like a NY skyline, while the rounding smooths the staccatic nature of the data. Perhaps there's a JET attribute I could use instead?

What took me some convincing was the shape of the rolling average graph at night. I knew daily activity was essentially in the shape of a bell. I saw it in the y2 axis above, and I see it in the APEX activity logs.

Two days of APEX activity. Can you see lunchtime?

So I found myself building a test case with a few dozen rows representing the bell, just to check there wasn't necessarily a dip in the rolling data overnight, instead of flat-lining.

I'm was not entirely convinced, so I detailed a the test case in this post, looking closely at the range analytic function. Get some real "SQL-perts"? on the case. It's the same query as above, just with smaller, static data set.

And now I've seen the rolling activity drift down on Saturday mornings, I'm more confident with the graph outcome. It's almost poetry.

What happens on the weekend

I must say, playing with these time-based graphs has been a much easier experience in 18.x. I'm still getting the hang of which settings to use (particularly with dates), but at least I can just use the SQL I need, not extra bloat just to help the UI.
While I'm complimenting these charts, the database is also thankful we can now define the SQL once, and use the same source to plot multiple series.

Load test your applications, people.