Re: Python 2: unicode vs str param causing slowness

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-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/