office space: Apex 3.2 idle session cleanup and WWV_FLOW

Abstract

When users login they get a temporary 'nobody' session with a few populated variables (or many if you have public translations or custom values), after login user gets an assigned session id and starts populating WWV_FLOW_DATA with item values.

Problem

If not maintained WWV_FLOW_DATA explodes and performance degrades.
I suspect that session idle setting is not working in Apex 3.2, tested many settings and checked Apex tables - nothing changes no matter what the settings are. Sessions pile up during the day, default cache purge procedures only allow cleaning up old sessions by date, not by idle period. Thus I can only run it at night or not at all (if environment gets international).

Findings

With this query we can find sessions which had no activity for more then 4 hours:

SELECT last, userid, session_id
FROM
(SELECT to_char(max(time_stamp),'DDMMYYYY HH24:MI') last, userid, session_id, max(time_stamp) mx
FROM apex_030200.wwv_flow_activity_log1$ l
--where userid = 'ADAM'
WHERE exists (select 1 from apex_030200.wwv_flow_sessions$ s where id = l.session_id)
GROUP BY userid, session_id
)
WHERE mx < sysdate-4/24;

Was digging Apex 3.2 packages for a couple of hours looking for a procedure to logout or cleanup. Looks like they where only introduced in 4.1. All the existing procedures are relying on Cookie values or session variables which I was not able to set manually.
Eureka moment. Even though Apex packages are wrapped - there was a comment or piece of code visible, that cleanup procedures simply delete from the views. So likely there are some instead triggers running which do the job.

Code

1) grant delete on apex_030200.wwv_flow_sessions$ to ;
2) Create procedure in your schema:

create or replace procedure "S_CLEAR_SESSIONS"
is
n NUMBER := 0;
BEGIN
FOR c IN (SELECT last, userid, session_id
FROM
(SELECT to_char(max(time_stamp),'DDMMYYYY HH24:MI') last, userid, session_id, max(time_stamp) mx
FROM apex_030200.wwv_flow_activity_log1$ l
--where userid = 'ADAM'
WHERE exists (select 1 from apex_030200.wwv_flow_sessions$ s where id = l.session_id)
GROUP BY userid, session_id
)
WHERE mx < sysdate-4/24

UNION

SELECT last, userid, session_id
FROM
(SELECT to_char(max(time_stamp),'DDMMYYYY HH24:MI') last, userid, session_id, max(time_stamp) mx
FROM apex_030200.wwv_flow_activity_log1$ l
WHERE nvl(userid,'nobody') = 'nobody'
AND exists (select 1 from apex_030200.wwv_flow_sessions$ s where id = l.session_id)
GROUP BY userid, session_id
)
WHERE mx < sysdate-1/24
)
LOOP
---htp.p('dead session: '||c.userid || ' sess: '||c.session_id);
DELETE FROM apex_030200.wwv_flow_sessions$
WHERE id = c.session_id;
END LOOP;
END;

3) Run the procedure:
begin
S_CLEAR_SESSIONS();
end;

Testing

select count(*) from apex_030200.wwv_flow_data;

Returns 130364

begin S_CLEAR_SESSIONS(); end;
select count(*) from apex_030200.wwv_flow_data;

Returns 129318

And thats only while writing this article. My first drop was from 1300000 items to just 200000, then added a shorter 'nobody' session killer as you can see in the code.

Charts

Nightly cleanup used to look like this: