Re: [cx-oracle-users] Python 2: unicode vs str param causing slowness

[Doug H. <djn...@gm...>]

On 26 January 2015 at 10:34, Dayton Gomez wrote:

>
> NLS_CHARACTERSET       WE8MSWIN1252
> NLS_NCHAR_CHARACTERSET       AL16UTF16  - is this what you requested?
> NLS_LANG = '.UTF8' (set by Django), the database has… NLS_LANGUAGE
> AMERICAN
>
This clearly shows that you will have a character set conversion
between VARCHAR2 and NVARCHAR2 strings.


Try issuing the following queries from django, to try to see this.

cursor.execute("""select platform, dump(platform, 1017) from device""")

cursor.execute("""select :platform a, dump(:platform, 1017) b from
dual""", {'platform: 'ios'})

cursor.execute("""select :platform a, dump(:platform, 1017) b from
dual""", {'platform: u'ios'})

I suspect you will see that the bound variable has a different charset
for the last two queries.

In the slow query, I suspect that there will be an implicit character
set conversion that prevents using the index. If you can get the
execution plan for the fast and slow versions, they will be very
different.



It would be interesting to see if you can declare a cx_oracle variable
to which you bind, rather than binding directly to the python string.
Compare these two variations:

platformVar = cursor.var(cx_Oracle.STRING, 20) # varchar2(20)
platformVar.setvalue(0, 'ios')
cursor.execute("""select :platform a, dump(:platform, 1017) b from
dual""", {'platform: platformVar})

platformVar = cursor.var(cx_Oracle.UNICODE, 20) # nvarchar2(20)
platformVar.setvalue(0, u'ios')
cursor.execute("""select :platform a, dump(:platform, 1017) b from
dual""", {'platform: platformVar})

If that works, you might be able to modify the original query to force
the fast version at all times.



BTW, I am assuming your table statistics are up-to-date, and that you
are using cost based optimization, and that your index does not need
to be rebuilt.


Doug

-- 
Doug Henderson, Calgary, Alberta, Canada