The sitestats aggregator job runs the following query for Oracle, which takes minutes to complete:
SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY EVENT_ID ASC) AS rn,
from SAKAI_EVENT e join SAKAI_SESSION s on e.SESSION_ID=s.SESSION_ID
where EVENT_ID >= 264857674)
WHERE rn BETWEEN 0 AND 1000
This query is executed multiple times per job run (by default, 50 times), so it has a significant impact.
The equivalent query used for MySQL works for Oracle as well and takes less than one second. The attached patch changes the code to run the faster query for both MySQL and Oracle.
This has been running in production at Western for over a year now.