Started evaluating the performance of a production system as a new project we undertook. APEX pages were slow to load, responsiveness was also slow. After some research, we found out that WWV_FLOW_DATA table had 732 million rows and WWV_FLOW_SESSIONS$ had 193 million rows (APEX_040200 schema). The cause: someone had droppped the scheduled job ORACLE_APEX_PURGE_SESSIONS!

SPECS
3 applications, 1 in-house and 2 public on the internet, availabl 24/7, one with login and the other one without
Apex 4.2.5.00.08 served by OHS
Oracle Database 11g Release 11.2.0.2.0
2 Node RAC with ASM Storage
Oracle Linux Server release 6.5

DISCLAIMER: Oracle does not support manual intervention to tables like WWV_FLOW_SESSIONS$. Expired sessions should have their records deleted by the scheduled job. Use the following steps at your own risk.

Attempting to delete rows from WWV_FLOW_SESSIONS$ was very slow, a rough estimate after an 1 day run of the DELETE script, showed that it would take 5,5 months to finish deleting all the records (270000 new records are added to WWV_FLOW_SESSIONS$ every day, 7x that to WWV_FLOW_DATA). So recreating the missing scheduled job would not have an immediate effect on performance

Created a small offline window, stopped OHS instances, cleaned-up WWV_FLOW_DATA (rename-recreate-drop). The result was a 3x speedup of the delete on WWV_FLOW_SESSIONS#. Still not good enough.

We decided to truncate WWV_FLOW_SESSIONS$. In order to do that, the system should be offline, all foreign keys disabled, relative records from the detail tables deleted, truncate the table and then reenable the relative foreign keys. WWV_FLOW_SESSIONS$ is a master table. Detail tables are:

WWV_FLOW_DATA
WWV_FLOW_RT$USER_SESSIONS
WWV_FLOW_COLLECTIONS$
WWV_FLOW_PAGE_SUBMISSIONS
WWV_FLOW_REQUEST_VERIFICATIONS
WWV_FLOW_SC_TRANS
WWV_FLOW_TREE_STATE

Note that there is a DELETE trigger on WWV_FLOW_SESSIONS$ that deletes records in WWV_FLOW_WORKSHEET_RPTS

The truncate procedure went smoothly, all detail records were deleted, system went online without any problem. Recovered the code for the missing ORACLE_APEX_PURGE_SESSIONS from a fresh installation on a VM and re-created the sceduled job

begin
    sys.dbms_scheduler.create_job
     (job_name        => 'APEX_040200.ORACLE_APEX_PURGE_SESSIONS',
      job_type        => 'STORED_PROCEDURE',
      job_action      => 'WWV_FLOW_CACHE.PURGE_SESSIONS',
      start_date      => SYSTIMESTAMP,
      repeat_interval => 'FREQ=HOURLY;BYMINUTE=0',
      enabled         => TRUE,
      auto_drop       => FALSE);
end;

Hope you never need this procedure!