[cx-oracle-users] issue with Unicode parameters

[Valentin K. <vk...@gm...>]

Hi,
I identified a problem with cx_Oracle driver and would like to understand how
it can be resolved. Basically, I observed a huge latencies with query if
I pass unicode string as binded parameter value. Below a stand-alone
code which reproduce a problem with our Oracle.

We run CERN Scientific Linux distribution (SLC 6.8), the code runs on 
x86_64 platform with gcc493 and cx_Oracle 5.2.1

import cx_Oracle
def test_cx_oracle(uname, pwd, host, sql, params):
    time0 = time.time()
    connection = cx_Oracle.connect(uname, pwd, host)
    cursor = connection.cursor()
    cursor.execute(sql, **params)
    lfn = params['logical_file_name']
    print("lfn type=%s, cursor execute %s sec" % (type(lfn), time.time()-time0))
    for row in cursor:
        print row
    cursor.close()

def main():
    # read password from a file
    ppp = open('ppp').readline().replace('\n','').replace('oracle://', '')
    line = ppp.split('@')
    host = line[-1]
    uname, pwd = line[0].split(':')
    sql = 'SELECT F.LOGICAL_FILE_NAME   FROM cms_dbs3_int_global_owner.FILES F   WHERE F.IS_FILE_VALID <> -1  AND F.LOGICAL_FILE_NAME = :logical_file_name'
    lfn = '/store/generator/Summer11/ZZTo4e_7TeV_mll8_mZZ95-160-powheg15-pythia6/GEN/START311_V2-v2/00000/BA1D37D5-93EE-E211-ADCF-003048F174AC.root'
    params = {'logical_file_name': lfn}
    test_cx_oracle(uname, pwd, host, sql, params)
    params = {'logical_file_name': u'%s' % lfn}
    test_cx_oracle(uname, pwd, host, sql, params)

if __name__ == '__main__':
    main()

The output of this program is the following:

lfn type=<type 'str'>, cursor execute 0.0578980445862 sec
('/store/generator/Summer11/ZZTo4e_7TeV_mll8_mZZ95-160-powheg15-pythia6/GEN/START311_V2-v2/00000/BA1D37D5-93EE-E211-ADCF-003048F174AC.root',)

lfn type=<type 'unicode'>, cursor execute 144.633666992 sec
('/store/generator/Summer11/ZZTo4e_7TeV_mll8_mZZ95-160-powheg15-pythia6/GEN/START311_V2-v2/00000/BA1D37D5-93EE-E211-ADCF-003048F174AC.root',)

As you can see using string parameter we quickly get results from Oracle
in 0.06 sec, while using unicode we see huge latency 144sec.

Any suggestions why unicode parameters such poorly behaves and where the time is spent on?

Thanks,
Valentin.