How to avoid Bad Gateway timeout in Oracle APEX 5.1 when executing long running queries?

Question

I have Oracle APEX 5.1 installed (version 5.1.4.00.08 to be exactly) together with ORDS (Oracle REST Data Services). If I want to run reports where the SQL select query on the database takes longer than one minute, a certain timeout appears to have been reached and the error message "502 Bad Gateway" is returned instead of the report. However, when the page reloads, the query still appears to be in the database cache, so APEX can load the report afterwards.

Unfortunately, I haven't yet found a setting in APEX or anywhere else that allows me to change the amount of time I want to wait to load the report before getting a response with HTTP code 502. I guess the problem might be with the ORDS configuration but I#m not sure about that.

Not an expert in ORDS configuration .. but this sounds like an optimization problem. No way to speed up your SQL query using materialized views, indexing, partitioning or anything else? Even if there is no timeout, I can hardly imagine someone wants to wait more than one minute. — Thomas Tschernich
Probably the queries can be improved, but unfortunately I lack deeper knowledge.. :-) Fortunately I was able to fix the timeout problem (see answer below). — jonashlm

Mark Stewart Mark Stewart · Accepted Answer · 2020-09-02T18:10:10

This answer applies for those that are accessing an APEX application over an internet path that has firewalls. If a query runs over 15 or 20 minutes, generally the firewalls that your connection crossed will "forget" the conversation between your web browser and the server hosting the APEX application. This will cause errors such as the 502 Bad Gateway error. And unfortunately, there is not much that can be done in that case. There are some things that can help reduce the chances of that happening.

I support an APEX application that has some monster report pages that would sometimes run well over 15 minutes, depending on the criteria the user entered. Some things I did for Interactive Reports (IR):

Use the DBMS_SQLTUNE package (requires optional license) on the queries. But be aware if the user modifies the IR, the query could change and tuning would not be helped for those custom runs.
Insert query hints if unable or undesirable to use DBMS_SQLTUNE.
I limited the date range on the query page for my report to limit the date range to 6 months or less
In APEX design mode, the ACTIONS menu can be modified (in the IR definition's Attributes folder) to turn off certain options for the Search Bar and Actions Menu. If you tune the query SQL, if a user via an IR's Actions menu button adds or removes a column, adds options such as a control break, etc. the query will likely not use the optimized settings.
Add JavaScript to remove ability to click on IR headers, and turn off Sort options from the from APEX IR "Actions" button. Be aware this could need updating with upgrades for APEX are applied.

These will help prevent most long running query issues.

Example settings for Actions button:

Example JavaScript to put in page definition's "JavaScript" Property for "Function and Global Variable Declaration" (valid for APEX version 5.1.4) follows. It disables the ability to click on column headings, and removes the Sort option from the IR "Actions" → "Format" menu:

.a-IRR-headerLink {
    pointer-events: none !important;
    cursor: default !important;
}

.a-IRR-sortWidget {
    display: none !important;
}

.a-IRR-sortWidget-actions {
    display: none !important;
}

.a-IRR-sortWidget-searchField {
    display: none !important;
}

.a-IRR-sortWidget-rows {
    display: none !important;
}

.a-IRR-sortWidget-search {
    display: none !important;
}

How to avoid Bad Gateway timeout in Oracle APEX 5.1 when executing long running queries?

2 Answers