Oracle Apex images and ORDS connection pool flawed concept?

Szlanyinka Zoltán

Hi community,

Environment:

Oracle Apex 21.1 Patch 5, Oracle REST DataServices (ORDS) 21.2.0.174.1826, Tomcat

I create a page in which I create a Cards region with a Cards container template. The sql source has a blob column that contains images.

Media:

 Source: BLOB column

 BLOB Column: MED_CONTENT

 Position: First

  Appearence: Auto

 Sizing: Cover

Pagination:

Type: Page

  Cards per Page: 64

At run time, only a few of the 64 images are displayed.

The Tomcat log shows the following:

503 ORDS was unable to make a connection to the database. This can occur if the database is unavailable, the maximum number of sessions has been reached or the pool is not correctly configured.

In line with the recommendations found on the Internet, I increased the number of JDBC connections in the ORDS configuration, which solved the problem. All images are displayed and there is no error in the Tomcat log.

<entry key = "jdbc.InitialLimit"> 16 </entry>

<entry key = "jdbc.MinLimit"> 16 </entry>

<entry key = "jdbc.MaxLimit"> 256 </entry>

However, the solution is not good because the error was not caused by a large number of users, but by a simple query.

It causes several problems:

- All developers need to know about JDBC limitation

- in some cases unnecessarily many database sessions are used (APEX_LISTENER 16 sessions, APEX_PUBLIC_USER 121 sessions, APEX_REST_PUBLIC_USER 105 sessions)

- scaling cannot be specified exactly because it does not depend on the number of competing users, but on the query result / page sizes

For many applications, the problem is also seen in the icon view in Apex Application Builder.

Is there another solution?

Christian Neumueller-Oracle

Hi,

just to clarify, how many concurrent users were hitting ORDS? The browser limits concurrent requests (see e.g. here https://stackoverflow.com/questions/985431/max-parallel-http-connections-in-a-browser) so it can not be a single developer who caused this (unless you ran into a bug, of course).

The ORDS team is working with us on reducing the number of required pool users. Already today, on ATP there is just a single session pool for ORDS_PUBLIC_USER (i.e. no APEX_LISTENER, APEX_PUBLIC_USER, APEX_REST_PUBLIC_USER). With a larger pool size for this single DB user, the same DB can serve more end users.

Regards,Christian

Szlanyinka Zoltán

Hi Christian!

Thanks for the quick response!

how many concurrent users were hitting ORDS? 1 apex session

ords config:

perp01.xml

<entry key="db.username">APEX_PUBLIC_USER</entry>

<entry key="jdbc.InitialLimit">4</entry>

<entry key="jdbc.MinLimit">4</entry>

<entry key="jdbc.MaxLimit">256</entry>

perp01_al.xml

<entry key="db.username">APEX_PUBLIC_USER</entry>

<entry key="jdbc.InitialLimit">4</entry>

<entry key="jdbc.MinLimit">4</entry>

<entry key="jdbc.MaxLimit">256</entry>

perp01_pu.xml

<entry key="db.username">APEX_PUBLIC_USER</entry>

<entry key="jdbc.InitialLimit">4</entry>

<entry key="jdbc.MinLimit">4</entry>

<entry key="jdbc.MaxLimit">256</entry>

perp01_rt.xml

<entry key="db.username">APEX_PUBLIC_USER</entry>

<entry key="jdbc.InitialLimit">4</entry>

<entry key="jdbc.MinLimit">4</entry>

<entry key="jdbc.MaxLimit">256</entry>

Number of current database sessions

APEX_LISTENER 4

APEX_PUBLIC_USER 4

APEX_REST_PUBLIC_USER 4

ORDS_PUBLIC_USER 4

The link you sent read:

https://stackoverflow.com/questions/985431/max-parallel-http-connections-in-a-browser) :

2020 Update Number of parallel connections per browser:

| Browser              | Connections per Domain         | Max Connections                |
| -------------------- | ------------------------------ | ------------------------------ |
| Chrome 81            | 6 [^note1]                     | 256[^note2]                    |
| Edge 18              | *same as Internet Explorer 11* | *same as Internet Explorer 11* |
| Firefox 68           | 9 [^note1] or 6 [^note3]       | 1000+[^note2]                  |
| Internet Explorer 11 | 12 [^note4]                    | 1000+[^note2]                  |
| Safari 13            | 6 [^note1]                     | 1000+[^note2]                  |

--> Chrome 256 parallel connection per browser ...

If is default jdbc setting in ords config xml, you can easily reproduce the problem, you want select returning > 100 records on cards with images.

Christian Neumueller-Oracle

256 parallel connections in Chrome, but to different domains. In your example, all card image links would hit the same ORDS, i.e. Chrome should have tried to fetch 6 images concurrently.

I read the same db.username in your pools above. Is this a copy/paste issue or are you actually using the same DB user for each pool?

Regards, Christian

Szlanyinka Zoltán

copy / paste issue

perp01.xml APEX_PUBLIC_USER

perp01_al.xml APEX_LISTENER

perp01_pu.xml ORDS_PUBLIC_USER

perp_01_rt.xml APEX_REST_PUBLIC_USER