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;
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:
Implemented a session cleanup at 15:00:
And here is one more from couple of days later when I added 1 hour grace period for all 'nobody' sessions independent of their activity:
When first popular Oracle ApEx versions (like 1.6, 2.0) came out, they had problems with displaying SQL Workshop Object Browser windows on some FF versions, just dont remember which exactly (imho 2.2). Here we are again having the same problem: red screen. This time its new Google browser Chrome. The same problem can be found on ApEx versions 3.0, 3.1 and 3.1.2, havent tested this on others. The common solution back then was to add a few directives to Oracle HTTP_Server's configuration file httpd.conf: