7 Ответы Последний ответ: 17.01.2017 14:34, автор: 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;
      ------------------ -------------------- ---------------------- -----------
                       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

          Hi Scott,


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



          • 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

            ------------------- -------------------- --------------------- ----------
                              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.



            • 3. Re: apex_workspace_activity_log purge rate



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



              • 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



                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?



                • 5. Re: apex_workspace_activity_log purge rate

                  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. 



                  • 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

                      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.