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

[Shai B. <sh...@pl...>]

If you're stuck, humor my suggestion from early in the thread -- try to 
recreate the database with a UTF8 server encoding and see if that changes 
anything.

HTH,
	Shai.

On Wednesday 04 February 2015 23:33:44 Dayton Gomez wrote:
> Sadly, still pretty stuck on this issue. Was looking at the EXPLAIN PLAN
> for the slow query. Used...
> 
> select plan_table_output from
> table(dbms_xplan.display_cursor('id',null,'basic +PEEKED_BINDS'));
> and
> select plan_table_output from table(dbms_xplan.display_cursor('id',null,
> '+ALLSTATS'));
> 
> I get the following (not gonna obfuscate the full query anymore. Too
> stuck). Please correct me if I'm wrong, but I don't see anything
> immediately out of place.
> 
> SELECT COUNT(*) FROM "APPSTORE_DEVICE" INNER JOIN "AUTH_USER" ON (
> "APPSTORE_DEVICE"."USER_ID" = "AUTH_USER"."ID" )
> WHERE("APPSTORE_DEVICE"."IS_ACTIVE" = 1  AND "APPSTORE_DEVICE"."TENANT_ID"
> =
> 9  AND "AUTH_USER"."IS_ACTIVE" = 1  AND "APPSTORE_DEVICE"."USER_ID" IS
> NOT NULL AND "APPSTORE_DEVICE"."IS_CORPORATE_OWNED" = 0  AND :arg0 =
> "APPSTORE_DEVICE"."PLATFORM" )
> 
> 
> PLAN_TABLE_OUTPUT
> ---------------------------------------------------------------------------
> -
> 
> | Id  | Operation		       | Name			  | E-Rows |
> 
> ---------------------------------------------------------------------------
> -
> 
> |   0 | SELECT STATEMENT	       |			  |	   |
> |   1 |  SORT AGGREGATE 	       |			  |	 1 |
> |   2 |   NESTED LOOPS		       |			  |	   |
> |   3 |    NESTED LOOPS 	       |			  |	 1 |
> |
> |*  4 |     TABLE ACCESS BY INDEX ROWID| APPSTORE_DEVICE	  |	 1 |
> |*  5 |      INDEX RANGE SCAN	       | APPSTORE_DEVICE_E3D75FEF |	55 |
> |*  6 |     INDEX RANGE SCAN	       | AUTH_USER_55F56498	  |  44270 |
> |*  7 |    TABLE ACCESS BY INDEX ROWID | AUTH_USER		  |	 1 |
> 
> PLAN_TABLE_OUTPUT
> ---------------------------------------------------------------------------
> -
> 
> Predicate Information (identified by operation id):
> ---------------------------------------------------
> 
>    4 - filter(("APPSTORE_DEVICE"."IS_ACTIVE"=1 AND
> 	      "APPSTORE_DEVICE"."TENANT_ID"=9 AND
> 	      "APPSTORE_DEVICE"."IS_CORPORATE_OWNED"=0 AND
> 	      "APPSTORE_DEVICE"."USER_ID" IS NOT NULL))
>    5 - access("APPSTORE_DEVICE"."PLATFORM"=:ARG0)
>    6 - access("AUTH_USER"."IS_ACTIVE"=1)
> 
> PLAN_TABLE_OUTPUT
> ---------------------------------------------------------------------------
> --
>    7 - filter("APPSTORE_DEVICE"."USER_ID"="AUTH_USER"."ID")
> 
> 
> 
> Peeked Binds (identified by position):
> --------------------------------------
> 
>    1 - :ARG0 (NVARCHAR2(30), CSID=2000): 'ios'
> 
> Again, the device table has...
>  PLATFORM										      NVARCHAR2(20)
> 
> 
> Does the size difference matter?
> 
> Thanks
> 
> >>>> 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 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.
> 
> ---------------------------------------------------------------------------
> --- 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