APEX

Our ApEx application is suddenly taking a long time to load apex_collections. Last week it would take maybe ten to fifteen seconds to load a page that includes an on demand process that loads about 7200 records into an apex_collection. The query itself is a rather complex query that includes six or seven "with" clause sub-queries and then a few subtotal and grand total queries. Suddenly this morning, it is taking several minutes to load.

To test this problem, I have created another very simple query to load a collection. It is also taking several seconds to load. Specifically,

declare

cursor cur_collection_test_query is
  with w_iim as (
          select rownum              row_num
               , iim.item_nbr
               , iim.description
               , iim.cost
               , iim.category
            from rub.inv_item_master     iim
          ) -- end w_iim
  select row_num
       , item_nbr
       , description
       , cost
       , category
    from w_iim   wc
   where wc.row_num < 100;

BEGIN

  apex_collection.create_or_truncate_collection(
                   p_collection_name => 'COLLECTION_LOAD_TEST');

  FOR i in cur_collection_test_query LOOP
    apex_collection.add_member(p_collection_name => 'COLLECTION_LOAD_TEST',
                               p_c001            => i.item_nbr,
                               p_c002            => i.description,
                               p_c003            => i.cost,
                               p_c004            => i.category);
  END LOOP;

END;

takes about 2 seconds to render the page. If I increase the row_num filter to get 1000 records it takes about 30 seconds to render the page. (The page is a test page with nothing but an HTML region that displays "Hi." and an on-load process that runs the above pl/sql.)

As far as we know, no settings have been changed in the database.

We are using Oracle 11g and ApEx 3.1.2 with the Embedded PL/SQL Gateway.

Any ideas why my apex_collections are taking so long to load this morning?

Thanks ever so much,

Gregory