Boosting APEX menu SQL performance

If you're using dynamic SQL to source your menus and you're utilising the apex_application_pages dictionary view to build you list of menu options - I suggest you consider using Materialized Views.

We have a reporting application that does just this; and the query also links to an authorisation table that decides what types of users see which reports.

You can create a snapshot (materialized view) using something as simple as

create materialized view apx_application_pages
as select * from apex_application_pages;

Then you can add an index on application_id and page_id, then you have the perfect substitute for your menu SQL.

In our development instance containing all sorts of experimental applications - apex_application_pages returns about 1500 rows - but it's a relatively complex view. Using the snapshot instead of the supplied view makes the following difference in throughput when running our query 50 times (on dev)

    33.93 secs (.6786 secs per iteration)
     0.17 secs (.0034 secs per iteration)

Just don't forget to refresh it when your application grows.
exec dbms_mview.refresh('apx_application_pages');

A simple concept, but it can make a very positive difference when done right. It's also applicable to a number of other related scenarios.