This discussion is archived
8 Replies Latest reply: May 10, 2011 12:32 PM by dwdba RSS

Documentation on APEX scheduler jobs?

dwdba Newbie
Currently Being Moderated
Does anyone have any documentation on what happens in the following Apex scheduler processes:


There are two other ones as well, but I suspect one of these contains what I'm looking for.

As background, running Apex 4.0. We are having issues with the collection table statistics. When the tables have statistics (after reports using collections have been run) the reports run in a timely manner. However, over the weekend, when no one is running the reports, statistics are run against the collections tables (wwv_flow_collections$ and wwv_flow_collection_members$) and revert the row counts to 0. Come Monday morning, the queries run abnormally slow since the optimizer thinks the tables are emptly.

I would like to be able to generate statistics on the tables and NOT have them wiped out by some scheduler job. Just trying to figure out which one it is, as I will likely have to put in place a "counter" job to import statistics after the built-in job runs.

  • 1. Re: Documentation on APEX scheduler jobs?
    438346 Journeyer
    Currently Being Moderated
    Hi DownW,
    I think these two packages are Oracle Internal packages. So Oracle do not supply any documentaions for internal stuff..

    **Not 100% sure
  • 2. Re: Documentation on APEX scheduler jobs?
    jkallman Employee ACE
    Currently Being Moderated

    wwv_flow_cache.purge_sessions is most likely the culprit. It's this job which cleans up "old" sessions from the APEX sessions table. Collections and collection members are specific to an APEX session, and have a foreign key constraint to the sessions table with "on delete cascade". So as the sessions are purged, the collection rows are as well.

    I hope this helps.

  • 3. Re: Documentation on APEX scheduler jobs?
    Pollocks01 Newbie
    Currently Being Moderated
    One way to handle this that I'm currently trialling in production is to:

    1) dbms_stats.delete_table_stats
    2) dbms_stats.lock_table_stats

    ....for the 2 tables that lay underneath the APEX_COLLECTIONS view.

    This - unless I'm mistaken - forces Oracle to do dynamic sampling each time the view is hit.

    If you are using collections in a high volume env, I suggest perhaps loading all with lots of representative data, then generating full stats, then locking those good stats.

  • 4. Re: Documentation on APEX scheduler jobs?
    dwdba Newbie
    Currently Being Moderated
    Learn something new every day :-)
    I was thinking I was going to have to export a set of stats and import. This is easier.
    I'm going to give the lock_stats a try and see if that mitigates the issue.

    Thanks for the asisistance.
  • 5. Re: Documentation on APEX scheduler jobs?
    Maheswara Explorer
    Currently Being Moderated
    Hi Dawn,

    *"JOB_NAME" ----"JOB_TYPE" ----"JOB_ACTION"*

    Thanks and Regards
  • 6. Re: Documentation on APEX scheduler jobs?
    Pollocks01 Newbie
    Currently Being Moderated
    Careful with my stats approach.....I've just reverted it from my prod env as follows:

    - Code which ran just fine for 12 months of so suddenly started to perform poorly.
    - So, I fixed the performance problem by deleting and then locking the stats - I essentially promoted the use of dynamic sampling.
    - Since then we've had intermittent failures "ORA-20103: Member sequence xx does not exist in Application collection yyyy". This doesn't always occur, just sometimes.......:
    SELECT TO_NUMBER (ac.c001), 
                      TO_DATE (ac.c003, v_date_format),
          BULK COLLECT INTO v_invoice_id, v_included_flag, v_due_date_override,
                 v_note, v_bill_acct_id
    FROM apex_collections ac, tinvoice_batch_detail tibd
    WHERE ac.collection_name = 'P1200_UPDCOLS'
    AND apex_collection.get_member_md5 ('P1200_UPDCOLS', ac.seq_id) <> ac.md5_original
    AND TO_NUMBER (ac.c001) = tibd.bill_acct_id
    and tibd.invoice_batch_id = :v_invoice_batch_id;
    It appears as though the error message comes from get_member_md5 and is a false message, but may be resolved by unlocking the stats.

    This makes NO sense to me as to why locking the stats would cause functional errors.

    Edited by: user6246156 on Apr 26, 2011 3:03 PM
  • 7. Re: Documentation on APEX scheduler jobs?
    Pollocks01 Newbie
    Currently Being Moderated
    So, I determined what caused the error that I describe above.........

    Essentially, Oracle was - on occasion - rewriting the query such that it evaluated the function call (get_member_md5) before evaluating the collection_name predicate.

    I'd seen Oracle re-writing the apex_collections view incorrectly in the past (i.e. join from all collection members to joined table (tinvoice_batch_detail in my case) and then filter on collection name) but this only causes a performance issue and not an exception.

    We need to ensure that Oracle evaluates APEX_COLLECTIONS as a single program unit and I achieved this as follows:
          BULK COLLECT INTO v_bill_acct_id, v_included_flag, v_due_date_override, v_note
            FROM (
                  select to_number(c001) bill_acct_id, 
                         c002 included_flag, 
                         TO_DATE (c003, v_date_format) due_date_override, 
                         c004 note,
                  from   apex_collections
                  where  collection_name = 'P1200_UPDCOLS'
                    and  rownum > 0
                 ) ac,
                 tinvoice_batch_detail tibd
           WHERE apex_collection.get_member_md5 (ac.collection_name, ac.seq_id) <> ac.md5_original
             and ac.bill_acct_id = tibd.bill_acct_id
             and tibd.invoice_batch_id = v_invoice_batch_id;
    I strongly advise anyone working with APEX_COLLECTIONS to use an approach like this (or a WITH clause or NO_QUERY_TRANSFORMATION hint etc) to ensure that Oracle evaluates the view as it's intended and doesn't do a rewrite for you.

    RE: stats, I learned from a colleague that dynamic sampling only kicks in when the query is parsed and so if the query stays in the shared pool then it could potentially be running based on stale stats.

    As such, I'm about to embark upon my other stats approach for collections i.e. to set stats manually, then lock those as opposed to deleting the stats and relying on dynamic sampling.

    If I remember (!), I'll post my 'set stats' script here.
  • 8. Re: Documentation on APEX scheduler jobs?
    dwdba Newbie
    Currently Being Moderated
    Thanks for the update - good information to keep in mind. So far the locked stats are working for us, so I'll leave them as is. Appreciate the collections tips - they seem to be a bit challenging to work with.


  • Correct Answers - 10 points
  • Helpful Answers - 5 points