Re: Python 2: unicode vs str param causing slowness

>>> cursor.execute("""select :platform a, dump(:platform, 1017) b from
>>>dual""", {'platform': 'ios'}).fetchall()
[(u'ios', 'Typ=1 Len=6 CharacterSet=AL16UTF16: ^@,i,^@,o,^@,s')]

>>> cursor.execute("""select :platform a, dump(:platform, 1017) b from
>>>dual""", {'platform': u'ios'}).fetchall()
[(u'ios', 'Typ=1 Len=6 CharacterSet=AL16UTF16: ^@,i,^@,o,^@,s')]

The slow query with a dump (SELECT COUNT(*), dump(:platform, 1017)Š)
outputs the same encoding:
(27195, 'Typ=1 Len=6 CharacterSet=AL16UTF16: ^@,i,^@,o,^@,s')

They're the same. If what you said was correct, shouldn't they be
different?
-- 


On 1/26/15 12:05 PM, "Doug Henderson" <[email protected]> wrote:

>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
>
>--------------------------------------------------------------------------
>----
>Dive into the World of Parallel Programming. The Go Parallel Website,
>sponsored by Intel and developed in partnership with Slashdot Media, is
>your
>hub for all things parallel software development, from weekly thought
>leadership blogs to news, videos, case studies, tutorials and more. Take a
>look and join the conversation now. http://goparallel.sourceforge.net/
>_______________________________________________
>cx-oracle-users mailing list
>cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
>https://lists.sourceforge.net/lists/listinfo/cx-oracle-users


------------------------------------------------------------------------------
Dive into the World of Parallel Programming. The Go Parallel Website,
sponsored by Intel and developed in partnership with Slashdot Media, is your
hub for all things parallel software development, from weekly thought
leadership blogs to news, videos, case studies, tutorials and more. Take a
look and join the conversation now. http://goparallel.sourceforge.net/