7 Replies Latest reply on Jan 17, 2017 2:34 PM by joelkallman-Oracle

    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 ?

        • 1. Re: apex_workspace_activity_log purge rate
          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

          • 2. Re: apex_workspace_activity_log purge rate
            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

            • 3. Re: apex_workspace_activity_log purge rate
              joelkallman-Oracle

              Scott,

               

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

               

              Joel

              • 4. Re: apex_workspace_activity_log purge rate
                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

                 

                switch_logs.png

                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.

                • 5. Re: apex_workspace_activity_log purge rate
                  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

                  • 6. Re: apex_workspace_activity_log purge rate
                    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

                    • 7. Re: apex_workspace_activity_log purge rate
                      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