apex_workspace_activity_log purge rate

Scott Wesley

Can we modify settings to hold workspace activity log data for 2 months?

Perhaps I'm misinterpreting what the switch interval is, but if I executed something like this

exec apex_instance_admin.SET_LOG_SWITCH_INTERVAL('ACTIVITY',7*8);

And I saw these results

select * from apex_050000.WWV_FLOW_LOG_NUMBERS;CURRENT_LOG_NUMBER LOG_SWITCHED_DATE     LOG_SWITCH_AFTER_DAYS LOG_NAME   ------------------ -------------------- ---------------------- -----------                 2 01/SEP/2016 00:00:00                     14 ACCESS                      1 18/AUG/2016 00:00:00                     56 ACTIVITY                    1 20/AUG/2015 00:00:00                     14 CLICKTHRU                   1 23/AUG/2016 00:00:00                     14 DEBUG 

Shouldn't I see data from 18th August? Not data capped at 14 days old?

select sysdate,sysdate-14, min(view_date) from apex_workspace_activity_log;SYSDATE              SYSDATE-14           MIN(VIEW_DATE)     -------------------- -------------------- --------------------05/SEP/2016 13:17:01 22/AUG/2016 13:17:01 22/AUG/2016 13:34:54

Edit: Forgot to mention I'm on 5.0.1

(I often forget to practice what I preach, sorry)

I thought once upon a time a dba_job could have it's frequency changed, but now it seems it's all done in here: WWV_FLOW_MAINT.DAILY_MAINTENANCE ?

joelkallman-Oracle

Hi Scott,

You're referring to two different views here, APEX_WORKSPACE_ACTIVITY_LOG and APEX_ACTIVITY_LOG.  APEX_ACTIVITY_LOG is simply on top of the "raw" underlying database views, but APEX_WORKSPACE_ACTIVITY_LOG is the preferred one.  When you see references to columns like FLOW_ID, you'll know that this is not the preferred view (even though the documentation references it!).

1)  As far as APEX_WORKSPACE_ACTIVITY_LOG, you're correct - the WHERE clause of this was modified in 5.1 to remove the restriction to the last 14 days.

2)  The "raw" view APEX_ACTIVITY_LOG never had this limit in the WHERE clause.

3)  In APEX 5.1, the views APEX_ACTIVITY_LOG and APEX_WORKSPACE_ACTIVITY_LOG views will return between N and N*2 days of activity, where N is your log switch interval. 

Joel

joelkallman-Oracle

Hi Scott,

When did you issue the call to apex_instance_admin.SET_LOG_SWITCH_INTERVAL?  Was it on or after 22-AUG?

Joel

Scott Wesley

Sorry for the delay.

I believe it was changed in at least enough time for more than 2 weeks to pass.

As it stands now I have

CURRENT_LOG_NUMBER LOG_SWITCHED_DATE    LOG_SWITCH_AFTER_DAYS LOG_NAME ------------------- -------------------- --------------------- ----------                  1 13/OCT/2016 00:00:00                    14 ACCESS                     2 13/OCT/2016 00:00:00                    56 ACTIVITY                   1 20/AUG/2015 00:00:00                    14 CLICKTHRU                  1 18/OCT/2016 00:00:00                    14 DEBUG   SYSDATE              SYSDATE-14          MIN(VIEW_DATE)   -------------------- -------------------- --------------------26/OCT/2016 09:24:34 12/OCT/2016 09:24:34 12/OCT/2016 09:36:07

So my live activity data remains at a rolling 2 weeks.

Scott

joelkallman-Oracle

Scott,

Let's please wait to check it on 28-OCT, okay?

Joel

Scott Wesley

Hi Joel,

I've finally had a chance to revisit this, now that it's been running for a while and I have a moment to consider it.

I think I've found a/the bug, but it appears to be rectified in 5.1.

Here is some feedback from the Manage Instance regarding the status of my logs

So instead of tables having the default switch of 14 days, this is successfully switching at 56 days, no problem there.

The wwv_flow_activity_log view combines these two physical tables, which by default would have up to 28 days of combined data, now it has between 56 and 112.

The apex_workspace_activity_log view definition in <= 5.0 has this filter, which I think is causing the disruption

l.time_stamp > sysdate - 14

This filter is missing/removed in 5.1

So is it safe to say that based on the documentation here, and the analysis above

Creating Custom Activity Reports Using APEX_ACTIVITY_LOG

That the view will now return (by default) between 14 and 28 days of activity logs?

Scott.

joelkallman-Oracle

Hi Scott,

You're referring to two different views here, APEX_WORKSPACE_ACTIVITY_LOG and APEX_ACTIVITY_LOG.  APEX_ACTIVITY_LOG is simply on top of the "raw" underlying database views, but APEX_WORKSPACE_ACTIVITY_LOG is the preferred one.  When you see references to columns like FLOW_ID, you'll know that this is not the preferred view (even though the documentation references it!).

1)  As far as APEX_WORKSPACE_ACTIVITY_LOG, you're correct - the WHERE clause of this was modified in 5.1 to remove the restriction to the last 14 days.

2)  The "raw" view APEX_ACTIVITY_LOG never had this limit in the WHERE clause.

3)  In APEX 5.1, the views APEX_ACTIVITY_LOG and APEX_WORKSPACE_ACTIVITY_LOG views will return between N and N*2 days of activity, where N is your log switch interval. 

Joel

Scott Wesley

Ahh, that documentation I linked at the end was mostly referring to the final paragraph on the page which I thought was applicable to both views. It didn't occur to me it was about APEX_ACTIVITY_LOG (which I don't use).

Thank you for clarifying point 3. I've been using Martin's technique for archiving logs but it SQL needs tuning for when history is large.

I'm trying to write a neat monitoring application that uses a balance of fresh, live data and historical. Two weeks wasn't long enough for fresher queries. I'm glad JET has arrived so I can use decent charts.

Now I know how to control the balance, and see the correct output ;p

joelkallman-Oracle

Hi Scott,

Just for the sake of completeness in my response, please be aware of view APEX_WORKSPACE_LOG_SUMMARY.  This is a view against the aggregated and archived activity log (computed once daily, via the database job ORACLE_APEX_DAILY_MAINTENANCE).  The data in this view is preserved for all time, it doesn't "rotate" like the operational activity logs.

I hope this helps.

Joel