How to obtain a speedup using Python multiprocessing with cx_Oracle?

Question

From How to use threading in Python? I took this code sample:

from multiprocessing.dummy import Pool as ThreadPool 
pool = ThreadPool(4) 
results = pool.map(my_function, my_array)

And it is perfectly working with such a urllib.request.urlopen function and gives about 2-3 times speed increase

from urllib.request import urlopen
from multiprocessing.dummy import Pool as ThreadPool
from timeit import Timer

urls = [
  'http://www.python.org', 
  'http://www.python.org/about/',
  'http://www.onlamp.com/pub/a/python/2003/04/17/metaclasses.html',
  'http://www.python.org/doc/',
  'http://www.python.org/download/',
  'http://www.python.org/getit/',
  'http://www.python.org/community/',
]

def mult(urls):
    pool = ThreadPool(8) 
    results = pool.map(urlopen, urls)
    pool.close()
    pool.join()

def single(urls):
    [urlopen(url) for url in urls]

print(Timer(lambda: single(urls)).timeit(number=1))
print(Timer(lambda: mult(urls)).timeit(number=1))

But in case of calling DB-procedures I did not notice any speedup provided by multiprocessing

from multiprocessing.dummy import Pool as ThreadPool
import cx_Oracle as ora
import configparser
config = configparser.ConfigParser()
config.read('configuration.ini')
conf = config['sample_config']

dsn = ora.makedsn(conf['ip'], conf['port'], sid=conf['sid'])
connection = ora.Connection(user=conf['user'], password=conf['password'], dsn=dsn, threaded=True)
cursor = ora.Cursor(connection)

def test_function(params):
    cursor = p.connection.cursor()
    # call procedure
    cursor.callproc('Sample_PKG.my_procedure', keywordParameters=params)

dicts = [{'a': 'b'}, {'a': 'c'}] # a lot of dictionaries contains about 30 items each

pool = ThreadPool(4)
pool.map(test_function, dicts)
pool.close()
pool.join()

So, why is it so and what could be the solution to boost the script's work?

UPD Tried to use session pool. This sample code is working

db_pool = ora.SessionPool(user=conf['user'], password=conf['password'], dsn=dsn, min=1, max=4, increment=1, threaded=True)
connection = ora.Connection(dsn=dsn, pool=db_pool)
cursor = connection.cursor()
cursor.execute("select 1 from dual")
result = cursor.fetchall()
cursor.close()
db_pool.release(connection)

But when I replace

cursor.execute("select 1 from dual")

with

cursor.callproc('Sample_PKG.my_procedure', keywordParameters=params)

I get my console hangs on. Am I doing something wrong?

score 0 · Answer 1 · answered Jan 11 '18 at 00:20

0

Each Oracle connection can only execute one statement at a time. Try using a session pool and use a different connection for each statement (and hope your DBA doesn't mind the extra connections).

answered Jan 11 '18 at 00:20

Christopher Jones

9,449
3
24
48

Using a session pool with `cursor.callproc` hangs my console on. Why could it be so? The actual stored procedure just makes an insert of sequence.nextval and all given arguments into a table – Greenev Jan 15 '18 at 16:31
Are there table or row locks blocking it? – Christopher Jones Jan 15 '18 at 21:17
It is correctly working with succesive calls of the procedure without commits between it, so could we conclude there is no table locks blocking multiprocessing inserts? – Greenev Jan 17 '18 at 08:04

How to obtain a speedup using Python multiprocessing with cx_Oracle?

1 Answers1