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.

Visualising SQL Analytics Rolling Count with OracleJET in APEX

Back in around 2005, before the time of smart phones, I had some data.

I can't remember what the data was, but I was told that for it to be valid, it should roughly form a bell curve.

Sure, I'm sure I could have aggregated it, exported it to Excel, and plotted it to a graph, but this was 2005. There was no SQL Developer where I could copy & paste the results directly into Excel. No panel in Excel with a button that plots that data in one click. Back in my day, thing like that were a little bit more work ;p

This was a time when I enjoyed SQL*Plus, and I ended up writing a query to nest STDDEV within RPAD so it would plot asterisks down the page, in a vertical bell. In was a work of art. I'm sure I've still got the test case labelled in bowels of Gmail.

Fast forward to today, where I have data I know starts as a daily bell curve, but I'm applying a rolling 24 hour total over that daily resonance. How does that affect the wave?

I use today's fancy tools to plot the said data, and it doesn't turn out quite how I pictured.

This is my query below plotting real data using OracleJET charts in Oracle APEX.

The underlying data still formed a bell, more detail in a future post, but smoothing this out to a rolling 24 hour total - what should that look like? Why is there a flat line during no activity, instead of a dip?
No doubt there is a math-geek corner of the internet that explores this pattern, but I still wasn't confident with the query.

Real data is awesome. The trouble is, it can be real noisy. If you're trying to reconcile your results, it's often worth building a simple test case. As a regular forum participant, I'm such an advocate for this. If the person asking the question took the time to write the DDL for the test case, I reckon half the questions wouldn't need asking. The other half will have an executable test case ready for any responders.
How's this - in a response to a question I asked (with a test case, of course), Andrew Sayer responded with a LiveSQL demonstration! AskTom questions that have a livesql.oracle.com example are generally prioritised.

So, what I have here is a real simple table, with some "logs" at specific times, and a little variance. It's quite similar to the setup you might see for SQL exercises in the Oracle Dev Gym.

-- create a simple table of dates
create table range_eg  (rd date);
-- ready for adjustment
truncate table range_eg;
-- insert sample hits at a regular interval, with more frequency around noon.
insert into range_eg values (timestamp '2019-03-26 09:30:00');
insert into range_eg values (timestamp '2019-03-26 10:30:00');
insert into range_eg values (timestamp '2019-03-26 10:45:00');
insert into range_eg values (timestamp '2019-03-26 11:30:00');
insert into range_eg values (timestamp '2019-03-26 11:45:00');
insert into range_eg values (timestamp '2019-03-26 12:30:00');
insert into range_eg values (timestamp '2019-03-26 12:45:00');
insert into range_eg values (timestamp '2019-03-26 13:30:00');
insert into range_eg values (timestamp '2019-03-26 13:45:00');
insert into range_eg values (timestamp '2019-03-26 14:30:00');
insert into range_eg values (timestamp '2019-03-26 15:30:00');
insert into range_eg values (timestamp '2019-03-26 16:30:00');

-- I took a few hits out the middle, so the bell would not be quite the same shape
insert into range_eg values (timestamp '2019-03-27 09:30:00');
insert into range_eg values (timestamp '2019-03-27 10:30:00');
insert into range_eg values (timestamp '2019-03-27 10:45:00');
insert into range_eg values (timestamp '2019-03-27 11:30:00');
--insert into range_eg values (timestamp '2019-03-27 11:45:00');
--insert into range_eg values (timestamp '2019-03-27 12:30:00');
--insert into range_eg values (timestamp '2019-03-27 12:45:00');
insert into range_eg values (timestamp '2019-03-27 13:30:00');
insert into range_eg values (timestamp '2019-03-27 13:45:00');
insert into range_eg values (timestamp '2019-03-27 14:30:00');
insert into range_eg values (timestamp '2019-03-27 15:30:00');
insert into range_eg values (timestamp '2019-03-27 16:30:00');

-- I added a small day at the end, to see how it may affect rolling total drift to zero
insert into range_eg values (timestamp '2019-03-28 11:30:00');
insert into range_eg values (timestamp '2019-03-28 12:30:00');
insert into range_eg values (timestamp '2019-03-28 13:30:00');

Then used an analytical function to cumulate the running count over a 1 day interval.
The second column, with accompanying union, was an attempt to see what happened if I filled out each hour with a record, to see if that influenced the overnight dip in my real data. It also let me see the cumulative count taper off as the data ended.

select * from (
    select rd 
     ,sum(sum(c)) over ( order by rd  
         range between NUMTODSINTERVAL(1,'day') preceding 
                   and current row)  last_24hr_running
     ,sum(count(*)) over ( order by rd  
         range between NUMTODSINTERVAL(1,'day') preceding 
                   and current row)  last_24hr_cnt
     from 
      (select 1 c,rd from range_eg 
      union all
      -- I even tried to pad out records to see 
      -- if the line would vary during the lulls
      select null c, trunc(sysdate,'hh24')+1-rownum/24
      from dual
      connect by level< 24*3
      )
    group by (rd) 
-- must ignore first day in inline query
-- as cumulative range will be skewed
) --where request_date > sysdate-2 
order by 2;

Data checks out, SQL checks out - does the graph check out?

Test case plotted

I think so, but given the context of the data, I don't think this line graph is appropriate, particularly around the period of no activity. More on that in the next post.

A data mining experience

Hands up who went to Spain in August '08? Well, I did with some friends after my mate's wedding. Last night while talking with the said mate, the topic of earthquakes came up somehow via me mentioning Aaron Rolston.

For a little context, I've visited this site a few times recently to see just how many quakes there are around the world every day - turns out a few dozen. Activity around Japan is just phenomenal.

http://earthquake.usgs.gov/earthquakes/recenteqsww/Quakes/quakes_all.html

So back in Spain - one morning over breakfast, I remember a friend telling us about a news report about a nearby earthquake that occurred the night before while we were playing poker. He mentioned something at the time, not many of us noticed anything - might have been the San Miguel...

So I thought I'd look to see if I could track it down

http://earthquake.usgs.gov/earthquakes/eqarchives/epic/

This is a five day inclusive search around the world in August 2008 - I repeat, only 5 days worth:

So to find those just in Spain, I restricted the region in the search criteria.

The two in highlighted red in the report below are the ones that we were within possibly 3 kms of it, if I remember our exact location correctly. Two evening quakes just over an hour apart, 7km deep.

                    U.  S.  G E O L O G I C A L  S U R V E Y
                     E A R T H Q U A K E  D A T A  B A S E

 FILE CREATED:  Wed Apr 13 15:52:46 2011
 Geographic Grid Search   Earthquakes=         8
 Latitude:   50.000N  -   30.000N
 Longitude:    10.000E  -    20.000W
 Catalog Used: PDE
 Date Range:   2008/08/04   to    2008/08/08
 Data Selection: Historical & Preliminary Data


 CAT    YEAR  MO DA  ORIG TIME   LAT    LONG  DEP  MAGNITUDE  IEM DTSVNWG DIST
                                                              NFO          km
                                                              TF 
 PDE    2008  08 04 060910.21  42.10   -6.82  17  2.8 LgMDD   ... .......      
 PDE    2008  08 05 152121.24  37.00    3.42   0  4.0 UKMDD   ... .......      
 PDE    2008  08 05 224121.93  38.05   -0.78   7  3.5 MLSTR   3F. .......      
 PDE    2008  08 05 234123.44  38.07   -0.78   7  2.7 LgMDD   3F. .......       

 PDE    2008  08 07 090314.60  42.94   -1.33   2  2.5 MLLDG   ... .......      
 PDE    2008  08 07 103215.30  41.98    2.56   5  2.6 MLSTR   ... .......      
 PDE    2008  08 08 094711.30  42.36    3.45  10  2.5 LgMDD   ... .......      
 PDE    2008  08 08 225611.15  35.58   -4.70  65  2.8 UKMDD   ... .......

I had a look on google maps for the location, I think we stayed in Los Montesinos, Torrevieja - very close!

The internet is full of amazing information - sometimes it's just a matter of tapping into it, sometimes it's a matter of coming up with the idea.

ScottWe

The bar has been raised

One day, I'd like to see something like this at an Oracle event.



Sure, it might be a little difficult if it's live, but I'm sure the technology will get there. Perhaps we'll see more of this type of thing once our broadband network gets up to scratch.

On a side note, look out for circles that do wild things - Japan in the 40s (medium red dot), China in the 50s (big red dot), Rwanda in the 90s (little blue dot), Middle East in the late 90s (green dots)... sense a trend?

Apparently Hans Rosling has some good TED talks, I might have to look those up.

Spotted at Pharyngula