CODEWITHSUNDEEP
oracle-databaseoracle-apexoracle-apex-5.1
jonashlm
jonashlm

Reputation: 23

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

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.

Upvotes: 1

Views: 6576

Answers (2)

Mark Stewart
Mark Stewart

Reputation: 2098

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 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;
}

Upvotes: 0

jonashlm
jonashlm

Reputation: 23

I finally found the mistake! It was neither APEX nor ORDS. I'm using an Apache server that uses mod_proxy to forward requests to the APEX server. Here the timeout occurred and can be fixed by the following settings in the Virtual Host:

Timeout 5400
ProxyTimeout 5400

Upvotes: 1

Related Questions