Search code examples
pythonpython-3.xdatabaseoracle-databasecx-oracle

Slow speed of cx_Oracle connection

i am using cx_oracle with python 3.7 to connect to oracle database and execute stored procedures stored in oracle database.

right now i am connecting to database as follows 

dbconstr = "username/password@databaseip/sid"
db_connection = cx_Oracle.connect(dbconstr)
cursor = db_connection.cursor()

#calling sp here

cursor.close()
db_connection.close()

but in this code connection time for cx_Oracle.connect(dbconstr) is about 250ms and whole code will run in about 500ms what i want is to reduce conenction time of 250ms.

I am using flask rest-api in python and this code is used for that, 250ms for connection is too long when entire response time is 500ms.

i have also tried maintaining a connection a for a life time of application by declaring global variable for connect object and creating and closing cursors only as shown below will give result in 250ms

dbconstr = "username/password@databaseip/sid"
db_connection = cx_Oracle.connect(dbconstr)

def api_response():
    cursor = db_connection.cursor()
    #calling sp here
    cursor.close()
    return result

by this method response time is reduced but a connection is getting maintained even when no one is using the application. After some time of being idle execution speed will get reduced for first request after some idle time, it is in seconds which is very bad.

so, i want help in creating stable code with good response time.

Solution

  • Creating a connection involves a lot of work on the database server: process startup, memory allocation, authentication etc.

    Your solution - or using a connection pool - are the ways to reduce connection times in Oracle applications. A pool with an acquire & release around the point of use in the app has benefits for planned and unplanned DB maintenance. This is due to the internal implementation of the pool.

    What's the load on your service? You probably want to start a pool and aquire/release connections, see How to use cx_Oracle session pool with Flask gracefuly? and Unresponsive requests- understanding the bottleneck (Flask + Oracle + Gunicorn) and others. Pro tip: keep the pool small, and make the minimum & maximum size the same.

    Is there a problem with having connections open? What is that impacting? There are some solutions such as Shared Servers, or DRCP but generally there shouldn't be any need to use them unless your database server is short of memory.