Excessive network "wait" time showing up in OEM monitoring tool

[cswanningson]

We are currently in process of migrating our ORDS (Oracle Rest Data Services) modules to Python FastAPI, and utilizing python-oracledb to connect to our Oracle Enterprise Edition Database instead of ORDS. After 6 days of having the connections flow into the database via Python instead of ORDS, two of our most utilized processes (1000's of executions per day) have started to show some excessive network waits in OEM (Oracle Enterprise Monitoring). We have tried to simulate this situation and REST API volume in our test environment but have been unable to reproduce the situation this far. Our Python services are running in a docker container, and performing a recycle of the services (and thus recycling the Python sessions on our database) appears to have "resolved" the problem for now, so it seems that something is building up over time causing excessive network waits on API calls via python-oracledb. This process is not using any DB Links or performing any network calls, the database process which is invoked by python FastAPI just opens a SYS_REFCURSOR of a query, and then in the python layer the REFCURSOR is parsed into JSON and returned to the browser. We are currently running in "thin" mode, but would be open to trying "thick" mode if there was a good reason for it (obviously would need to vet this out for a bit in our test environment first also).
Our current session settings are:
DEFAULT_LOG_LEVEL=INFO
LOG_FORMAT_JSON_ENABLED=False
CONNECTION_POOL_MIN=20
CONNECTION_POOL_MAX=200
DB_CALL_TIMEOUT_MS=120000
DB_TYPE=ORACLE
LOG_FILE_BACKUP_COUNT=20

Any tips/tricks/advice anyone can provide would be greatly appreciated.

Thanks!

[cjbj]

• Use a fixed connection pool when min==max
• Do you need such a large pool
• we're definitely interested whether there is a difference between thin & thick modes
• what version of python-oracledb? If not 1.3.1, then upgrade
• what is the actual DB datatype being used?
• how many rows are being fetched per query?

[chad22]

• We agree that the connection pool seems unnecessarily high given the speed the services are completing. We will reduce it
• We are using version 1.3.1 so we should be good there. We are using an Oracle Database 19c Enterprise Edition and utilize Oracle RAC.
• There are just under 3000 rows being returned from the problem service. (500kb of data)
  

[cjbj]

Can you share some code and the table schema?

Is that 3000 rows per request? What is your Cursor.arraysize set to?