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

[Dayton G. <Day...@sy...>]

>>> 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" <djn...@gm...> 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-...@li...
>https://lists.sourceforge.net/lists/listinfo/cx-oracle-users