cx-oracle-users — Primary discussion list for users (and developers) of cx_Oracle

[cx-oracle-users] Callproc and Oracle.NUMBER problem.

[Brown, S. <Ste...@wo...>]

Hi,

I use Python 2.6.6 with cx_Oracle 5.1.2 and Oracle DB Client 11.2..0.2.0. I cannot upgrade to Python 2.7.

I have the following stored procedure signature:

CREATE OR REPLACE PROCEDURE insert_task_execution(
    vId OUT task_execution.Id%TYPE,
    vJob_id IN task_execution.job_id%TYPE,
    vInitiated_by IN task_execution.initiated_by%TYPE,
.
.
.

The table definition is:

CREATE TABLE task_execution
(
   id                INTEGER                NOT NULL,
   task_summary_id   INTEGER                NOT NULL,
   job_id            INTEGER                NOT NULL,
   initiated_by      VARCHAR2(16 CHAR)      NOT NULL,
.
.
.
   CONSTRAINT        pk_task_exe            PRIMARY KEY (id),
   CONSTRAINT        fk_task_exe_summary    FOREIGN KEY (task_summary_id) REFERENCES task_summary(id)
);

I call cursor.callproc like this:

connection = cx_Oracle.connect(connectionString)
cursor = connection.cursor()
newRecordId = cursor.var(cx_Oracle.NUMBER)
parameters = [newRecordId, 123, initiatedBy, ... ]
cursor.callproc('insert_task_execution', parameters)

I get this exception before any database access.:


cx_Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data type cx_Oracle.NUMBER


Can someone tell me what I'm doing wrong, and how to fix it?

Many thanks,
Steve
This e-mail and any attachments are confidential, intended only for the addressee and may be privileged. If you have received this e-mail in error, please notify the sender immediately and delete it. Any content that does not relate to the business of Worldpay is personal to the sender and not authorised or endorsed by Worldpay. Worldpay does not accept responsibility for viruses or any loss or damage arising from transmission or access.

Worldpay (UK) Limited (Company No: 07316500/ Financial Conduct Authority No: 530923), Worldpay Limited (Company No:03424752 / Financial Conduct Authority No: 504504), Worldpay AP Limited (Company No: 05593466 / Financial Conduct Authority No: 502597). Registered Office: The Walbrook Building, 25 Walbrook, London EC4N 8AF and authorised by the Financial Conduct Authority under the Payment Service Regulations 2009 for the provision of payment services. Worldpay (UK) Limited is authorised and regulated by the Financial Conduct Authority for consumer credit activities. Worldpay B.V. (WPBV) has its registered office in Amsterdam, the Netherlands (Handelsregister KvK no. 60494344). WPBV holds a licence from and is included in the register kept by De Nederlandsche Bank, which registration can be consulted through www.dnb.nl. Worldpay, the logo and any associated brand names are trade marks of the Worldpay group.

Re: [cx-oracle-users] When will Windows Installers for Python 3.4 be available?

[Gary F. <fur...@gm...>]

Anthony's last post (3/2014) suggested that a version for 3.4 will be
available soon. Any news? Can I (we) Help?

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

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

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

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

-- 



On 1/26/15 1:06 PM, "Dayton Gomez" <Day...@sy...> wrote:

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

Re: [cx-oracle-users] Implicit Results interface in cx_Oracle

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

Hi Krishna,

This looks very interesting. Just one comment on the suggested API:

On Tuesday 03 February 2015 13:36:10 Krishna Mohan IV wrote:
> 
> Cursor.getnextimplicitresult()
>
> Fetch cursor for a unique resultset returned by a pl/sql function or
> procedure using dbms_sql.return_result. This function should be used
> when the Cursor. implicitresultcount is greater than 0. This routine
> should be called iteratively to get all the cursors returned by the
> PLSQL procedure.  Fetching of rows for each of these cursor objects can
> be done using fetchall/ fetchmany/ fetchone .
> A Python  none object is returned when no implicit results exist.
> 

I would consider adding, from the get-go, an iterator for such cursors, as the 
primary interface. Something like:

	for imprset in topcur.implicitresults():
		results = imprset.fetchall()
		print results

Note that, according to the docs, the result-sets can be fetched in parallel, 
so something like this should also work:

	result_sets = list(topcur.implicitresults())
	results = zip(*result_sets)

Have fun,
	Shai.

Re: [cx-oracle-users] Implicit Results interface in cx_Oracle

[Amaury F. d'A. <ama...@gm...>]

2015-02-03 12:36 GMT+01:00 Krishna Mohan IV <kri...@or...>:

>  Hello all,
>   There is an interesting and useful feature, Implicit Results, added by
> Oracle in 12c. Planning to provide an interface in cx_Oracle for the same.
> Here is a brief description (links given for complete details) and proposed
> API changes. Let me know how it looks and suggestions you have!
>

That's a good idea!
For the implementation, I suggest that you start with a subclass of
cx_Oracle.Cursor.
I'm sure these new features can be implemented with Python code.

Then when all details are sorted out, it will be easy enough to convert to
C code.


> thanks,
> krishna
>
> *Description*
>     Implicit results are a way of returning cursors from a pl/sql block to
> the client, without specifying the OUT ref cursor parameters. The block or
> the procedure on the server opens the cursors and marks them for returning
> to the client, using dbms_sql.return_result procedure.
> More here.
> http://docs.oracle.com/database/121/LNPLS/dynamic.htm#LNPLS2174
> https://docs.oracle.com/database/121/LNOCI/oci10new.htm#LNOCI73008
>
>
>
>
> *cx_Oracle APIs/Classes **Cursor.implicitresultcount*
> This read-only attribute specifies the number of different resultsets that
> have been returned from a pl/sql function or a procedure or an anonymous
> block. When no implicit results are returned, this value will be 0. This
> value is populated only after a call to  callfunc()/callproc() or execute().
>
>
> *Cursor.getnextimplicitresult()*
> Fetch cursor for a unique resultset returned by a pl/sql function or
> procedure using dbms_sql.return_result. This function should be used when
> the Cursor. implicitresultcount is greater than 0. This routine should be
> called iteratively to get all the cursors returned by the PLSQL procedure.
> Fetching of rows for each of these cursor objects can be done using
> fetchall/ fetchmany/ fetchone .
> A Python  none object is returned when no implicit results exist.
>
> Example
>
> import cx_Oracle
>
> sql = "declare \
> c1 sys_refcursor; \
> c2 sys_refcursor; \
> begin \
> open c1 for select city from locations where rownum < 4; \
> dbms_sql.return_result(c1); \
> open c2 for select first_name, last_name from employees where rownum < 4; \
> dbms_sql.return_result(c2); \
> end;"  #pl-sql block
>
> conn = cx_Oracle.Connection("scott/tiger@inst1")
> topcur = conn.cursor()
> topcur.execute(sql);
> print topcur.*implicitresultcount *-----> Tells us how many such result
> sets are returned
> impRSet = topcur.*getnextimplicitresult()*     -------> To get each of
> those result sets
> while impRSet:
>     results = impRSet.fetchall()
>     print results
>     impRSet = topcur.*getnextimplicitresult()*
> topcur.close()
> conn.close()
>
> Note:
> - The impRSet objects should not be closed by the application. They are
> implicitly closed by topcur.close(), the parent.
> - Since we also have topcur.impicitresultcount, we can also do the above
> using a *for* loop.
>
> *Compatibility*
> 12c Oracle client and server.
>
>
>
> ------------------------------------------------------------------------------
> 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
>
>


-- 
Amaury Forgeot d'Arc

[cx-oracle-users] Implicit Results interface in cx_Oracle

[Krishna M. IV <kri...@or...>]

Hello all,
   There is an interesting and useful feature, Implicit Results, added 
by Oracle in 12c. Planning to provide an interface in cx_Oracle for the 
same. Here is a brief description (links given for complete details) and 
proposed API changes. Let me know how it looks and suggestions you have!

thanks,
krishna

*Description*
     Implicit results are a way of returning cursors from a pl/sql block 
to the client, without specifying the OUT ref cursor parameters. The 
block or the procedure on the server opens the cursors and marks them 
for returning to the client, using dbms_sql.return_result procedure.
More here.
http://docs.oracle.com/database/121/LNPLS/dynamic.htm#LNPLS2174
https://docs.oracle.com/database/121/LNOCI/oci10new.htm#LNOCI73008


*cx_Oracle APIs/Classes

*_Cursor.implicitresultcount_
This read-only attribute specifies the number of different resultsets 
that have been returned from a pl/sql function or a procedure or an 
anonymous block. When no implicit results are returned, this value will 
be 0. This value is populated only after a call to  
callfunc()/callproc() or execute().


_Cursor.getnextimplicitresult()_
Fetch cursor for a unique resultset returned by a pl/sql function or 
procedure using dbms_sql.return_result. This function should be used 
when the Cursor. implicitresultcount is greater than 0. This routine 
should be called iteratively to get all the cursors returned by the 
PLSQL procedure.  Fetching of rows for each of these cursor objects can 
be done using fetchall/ fetchmany/ fetchone .
A Python  none object is returned when no implicit results exist.

Example

import cx_Oracle

sql = "declare \
c1 sys_refcursor; \
c2 sys_refcursor; \
begin \
open c1 for select city from locations where rownum < 4; \
dbms_sql.return_result(c1); \
open c2 for select first_name, last_name from employees where rownum < 4; \
dbms_sql.return_result(c2); \
end;"  #pl-sql block

conn = cx_Oracle.Connection("scott/tiger@inst1")
topcur = conn.cursor()
topcur.execute(sql);
print topcur.*/implicitresultcount/ *-----> Tells us how many such 
result sets are returned
impRSet = topcur./*getnextimplicitresult()*/ -------> To get each of 
those result sets
while impRSet:
     results = impRSet.fetchall()
     print results
     impRSet = topcur./*getnextimplicitresult()*/
topcur.close()
conn.close()

Note:
- The impRSet objects should not be closed by the application. They are 
implicitly closed by topcur.close(), the parent.
- Since we also have topcur.impicitresultcount, we can also do the above 
using a /for/ loop.

*Compatibility*
12c Oracle client and server.

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

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

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

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

I'll provide as much as I can, but some of it is private (full schema or actual data.

 1.  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
    *   client:
       *   oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64
       *   oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64
 2.  cx-oracle version: 5.1.3
 3.  NLS_CHARACTERSET       WE8MSWIN1252
 4.  NLS_NCHAR_CHARACTERSET       AL16UTF16  - is this what you requested?
 5.  NLS_LANG = '.UTF8' (set by Django), the database has… NLS_LANGUAGE       AMERICAN

For the schema, there's actually two tables. Again, I can't share you the whole schema, but here's some of it.

"device":
 ID       NOT NULL NUMBER(11) - pk
 PLATFORM NVARCHAR2(20) - indexed
USER_ID NUMBER(11) - FK to "user"
 IS_ACTIVE        NOT NULL NUMBER(1) - indexed

"user":
 ID       NOT NULL NUMBER(11) - pk
 USERNAME NVARCHAR2(75)
 IS_ACTIVE       NOT NULL NUMBER(1) - indexed


The query does something like…

SLOW:
q = cxcursor.execute('''SELECT COUNT(*) FROM "DEVICE" INNER JOIN "USER" ON ( "DEVICE"."USER_ID" = "USER"."ID" ) WHERE ("DEVICE"."IS_ACTIVE" = 1  AND "USER"."IS_ACTIVE" = 1  AND "DEVICE"."USER_ID" IS NOT NULL AND  "DEVICE"."PLATFORM" = :platform )''', {'platform': u'ios'})
FAST:
q = cxcursor.execute('''SELECT COUNT(*) FROM "DEVICE" INNER JOIN "USER" ON ( "DEVICE"."USER_ID" = "USER"."ID" ) WHERE ("DEVICE"."IS_ACTIVE" = 1  AND "USER"."IS_ACTIVE" = 1  AND "DEVICE"."USER_ID" IS NOT NULL AND  "DEVICE"."PLATFORM" = :platform )''', {'platform': 'ios'})

As for data, the DEVICE table has about 25k entries, with the user table about the same size.

I just tried using one table, no join, and it was extremely fast. So it seems to be the INNER JOIN causing the issues, when coupled with the param.
--
From: avinash nandakumar <avi...@or...<mailto:avi...@or...>>
Organization: Oracle Corporation
Date: Sunday, January 25, 2015 10:40 PM
To: "cx-...@li...<mailto:cx-...@li...>" <cx-...@li...<mailto:cx-...@li...>>
Cc: Dayton Gomez <day...@sy...<mailto:day...@sy...>>
Subject: Re: [cx-oracle-users] Python 2: unicode vs str param causing slowness

Hello Dayton,

I have tried to reproduce the issue on cx-oracle with Oracle 11 with DB charset W8DEC and NCHAR charset AL16UTF16.
However I was not able to reproduce the problem.

Although some of these you have provided before in order to consolidate can you please give details for the following:-
1. Oracle version number
2. cx-oracle version
3. DB characterset
4. DB Ncharset
5. NLS_LANG
6. Table schema
7. Table data

Best Regards,
Avinash

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

[avinash n. <avi...@or...>]

Hello Dayton,

I have tried to reproduce the issue on cx-oracle with Oracle 11 with DB 
charset W8DEC and NCHAR charset AL16UTF16.
However I was not able to reproduce the problem.

Although some of these you have provided before in order to consolidate 
can you please give details for the following:-
1. Oracle version number
2. cx-oracle version
3. DB characterset
4. DB Ncharset
5. NLS_LANG
6. Table schema
7. Table data

Best Regards,
Avinash

On 1/22/2015 7:34 PM, Dayton Gomez wrote:
> Correct. It's set to ".UTF8" on all our systems. I've tried random 
> other encodings with no difference (was shooting in the dark).
> From: Shai Berger <sh...@pl... <mailto:sh...@pl...>>
> Reply-To: "cx-...@li... 
> <mailto:cx-...@li...>" 
> <cx-...@li... 
> <mailto:cx-...@li...>>
> Date: Thursday, January 22, 2015 4:03 AM
> To: "cx-...@li... 
> <mailto:cx-...@li...>" 
> <cx-...@li... 
> <mailto:cx-...@li...>>
> Subject: Re: [cx-oracle-users] Python 2: unicode vs str param causing 
> slowness
>
> Django sets it to ".UTF8".
>
> On 22 ?????? 2015 12:46:07 GMT+02:00, Vladimir Ryabtsev 
> <gre...@gm... <mailto:gre...@gm...>> wrote:
>
>     Dayton,
>     what is the value of NLS_LANG on client machine?
>     What is returned by 'SELECT USERENV ('language') FROM DUAL' when
>     calling from django?
>
>     2015-01-21 21:37 GMT+03:00 Dayton Gomez <Day...@sy...
>     <mailto:Day...@sy...>>:
>
>         I grabbed the ful query, plus the bound variables. I get this
>         for bound vars:
>
>         DATATYPE_STRING   VALUE_STRING
>         ---------------------------  ----------------------
>         NVARCHAR2(32)        ios
>
>         It seems right. The only thing I notice is that the table
>         definition is for NVARCHAR2(20).
>
>         From: Vladimir Ryabtsev <gre...@gm...
>         <mailto:gre...@gm...>>
>         Reply-To: "cx-...@li...
>         <mailto:cx-...@li...>"
>         <cx-...@li...
>         <mailto:cx-...@li...>>
>         Date: Tuesday, January 20, 2015 2:42 PM
>         To: "cx-...@li...
>         <mailto:cx-...@li...>"
>         <cx-...@li...
>         <mailto:cx-...@li...>>
>         Subject: Re: [cx-oracle-users] Python 2: unicode vs str param
>         causing slowness
>
>         Hi,
>         Did you try to look the text of actual query that being
>         executed when you come across the slowness?
>         Something like this:
>         http://stackoverflow.com/questions/55899/how-to-see-the-actual-oracle-sql-statement-that-is-being-executed
>         You can also find out the datatype of your bound variable
>         :platform as mentioned here:
>         http://stackoverflow.com/questions/1707291/how-to-get-the-last-executed-sql-statement-and-bind-variable-values-in-oracle
>         This will you give a clue about implicit datatype conversion.
>         I don't think it's a django thing.
>
>
>         ------------------------------------------------------------------------------
>         New Year. New Location. New Benefits. New Data Center in
>         Ashburn, VA.
>         GigeNET is offering a free month of service with a new server
>         in Ashburn.
>         Choose from 2 high performing configs, both with 100TB of
>         bandwidth.
>         Higher redundancy.Lower latency.Increased capacity.Completely
>         compliant.
>         http://p.sf.net/sfu/gigenet
>         _______________________________________________
>         cx-oracle-users mailing list
>         cx-...@li...
>         <mailto:cx-...@li...>
>         https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
>
>
> ------------------------------------------------------------------------------
> New Year. New Location. New Benefits. New Data Center in Ashburn, VA.
> GigeNET is offering a free month of service with a new server in Ashburn.
> Choose from 2 high performing configs, both with 100TB of bandwidth.
> Higher redundancy.Lower latency.Increased capacity.Completely compliant.
> http://p.sf.net/sfu/gigenet
>
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

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

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

Correct. It's set to ".UTF8" on all our systems. I've tried random other encodings with no difference (was shooting in the dark).
From: Shai Berger <sh...@pl...<mailto:sh...@pl...>>
Reply-To: "cx-...@li...<mailto:cx-...@li...>" <cx-...@li...<mailto:cx-...@li...>>
Date: Thursday, January 22, 2015 4:03 AM
To: "cx-...@li...<mailto:cx-...@li...>" <cx-...@li...<mailto:cx-...@li...>>
Subject: Re: [cx-oracle-users] Python 2: unicode vs str param causing slowness

Django sets it to ".UTF8".

On 22 בינואר 2015 12:46:07 GMT+02:00, Vladimir Ryabtsev <gre...@gm...<mailto:gre...@gm...>> wrote:
Dayton,
what is the value of NLS_LANG on client machine?
What is returned by 'SELECT USERENV ('language') FROM DUAL' when calling from django?

2015-01-21 21:37 GMT+03:00 Dayton Gomez <Day...@sy...<mailto:Day...@sy...>>:
I grabbed the ful query, plus the bound variables. I get this for bound vars:

DATATYPE_STRING   VALUE_STRING
---------------------------    ----------------------
NVARCHAR2(32)        ios

It seems right. The only thing I notice is that the table definition is for NVARCHAR2(20).

From: Vladimir Ryabtsev <gre...@gm...<mailto:gre...@gm...>>
Reply-To: "cx-...@li...<mailto:cx-...@li...>" <cx-...@li...<mailto:cx-...@li...>>
Date: Tuesday, January 20, 2015 2:42 PM
To: "cx-...@li...<mailto:cx-...@li...>" <cx-...@li...<mailto:cx-...@li...>>
Subject: Re: [cx-oracle-users] Python 2: unicode vs str param causing slowness

Hi,
Did you try to look the text of actual query that being executed when you come across the slowness?
Something like this: http://stackoverflow.com/questions/55899/how-to-see-the-actual-oracle-sql-statement-that-is-being-executed
You can also find out the datatype of your bound variable :platform as mentioned here: http://stackoverflow.com/questions/1707291/how-to-get-the-last-executed-sql-statement-and-bind-variable-values-in-oracle
This will you give a clue about implicit datatype conversion.
I don't think it's a django thing.


------------------------------------------------------------------------------
New Year. New Location. New Benefits. New Data Center in Ashburn, VA.
GigeNET is offering a free month of service with a new server in Ashburn.
Choose from 2 high performing configs, both with 100TB of bandwidth.
Higher redundancy.Lower latency.Increased capacity.Completely compliant.
http://p.sf.net/sfu/gigenet
_______________________________________________
cx-oracle-users mailing list
cx-...@li...<mailto:cx-...@li...>
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

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

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

Django sets it to ".UTF8".

On 22 בינואר 2015 12:46:07 GMT+02:00, Vladimir Ryabtsev <gre...@gm...> wrote:
>Dayton,
>what is the value of NLS_LANG on client machine?
>What is returned by 'SELECT USERENV ('language') FROM DUAL' when
>calling
>from django?
>
>2015-01-21 21:37 GMT+03:00 Dayton Gomez <Day...@sy...>:
>
>> I grabbed the ful query, plus the bound variables. I get this for
>bound
>> vars:
>>
>> DATATYPE_STRING   VALUE_STRING
>> ---------------------------    ----------------------
>> NVARCHAR2(32)        ios
>>
>> It seems right. The only thing I notice is that the table definition
>is
>> for NVARCHAR2(20).
>>
>> From: Vladimir Ryabtsev <gre...@gm...>
>> Reply-To: "cx-...@li..." <
>> cx-...@li...>
>> Date: Tuesday, January 20, 2015 2:42 PM
>> To: "cx-...@li..." <
>> cx-...@li...>
>> Subject: Re: [cx-oracle-users] Python 2: unicode vs str param causing
>> slowness
>>
>> Hi,
>> Did you try to look the text of actual query that being executed when
>you
>> come across the slowness?
>> Something like this:
>>
>http://stackoverflow.com/questions/55899/how-to-see-the-actual-oracle-sql-statement-that-is-being-executed
>> You can also find out the datatype of your bound variable :platform
>as
>> mentioned here:
>>
>http://stackoverflow.com/questions/1707291/how-to-get-the-last-executed-sql-statement-and-bind-variable-values-in-oracle
>> This will you give a clue about implicit datatype conversion.
>> I don't think it's a django thing.
>>
>>
>>
>>
>------------------------------------------------------------------------------
>> New Year. New Location. New Benefits. New Data Center in Ashburn, VA.
>> GigeNET is offering a free month of service with a new server in
>Ashburn.
>> Choose from 2 high performing configs, both with 100TB of bandwidth.
>> Higher redundancy.Lower latency.Increased capacity.Completely
>compliant.
>> http://p.sf.net/sfu/gigenet
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
>
>
>------------------------------------------------------------------------
>
>------------------------------------------------------------------------------
>New Year. New Location. New Benefits. New Data Center in Ashburn, VA.
>GigeNET is offering a free month of service with a new server in
>Ashburn.
>Choose from 2 high performing configs, both with 100TB of bandwidth.
>Higher redundancy.Lower latency.Increased capacity.Completely
>compliant.
>http://p.sf.net/sfu/gigenet
>
>------------------------------------------------------------------------
>
>_______________________________________________
>cx-oracle-users mailing list
>cx-...@li...
>https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.

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

[Vladimir R. <gre...@gm...>]

Dayton,
what is the value of NLS_LANG on client machine?
What is returned by 'SELECT USERENV ('language') FROM DUAL' when calling
from django?

2015-01-21 21:37 GMT+03:00 Dayton Gomez <Day...@sy...>:

> I grabbed the ful query, plus the bound variables. I get this for bound
> vars:
>
> DATATYPE_STRING   VALUE_STRING
> ---------------------------    ----------------------
> NVARCHAR2(32)        ios
>
> It seems right. The only thing I notice is that the table definition is
> for NVARCHAR2(20).
>
> From: Vladimir Ryabtsev <gre...@gm...>
> Reply-To: "cx-...@li..." <
> cx-...@li...>
> Date: Tuesday, January 20, 2015 2:42 PM
> To: "cx-...@li..." <
> cx-...@li...>
> Subject: Re: [cx-oracle-users] Python 2: unicode vs str param causing
> slowness
>
> Hi,
> Did you try to look the text of actual query that being executed when you
> come across the slowness?
> Something like this:
> http://stackoverflow.com/questions/55899/how-to-see-the-actual-oracle-sql-statement-that-is-being-executed
> You can also find out the datatype of your bound variable :platform as
> mentioned here:
> http://stackoverflow.com/questions/1707291/how-to-get-the-last-executed-sql-statement-and-bind-variable-values-in-oracle
> This will you give a clue about implicit datatype conversion.
> I don't think it's a django thing.
>
>
>
> ------------------------------------------------------------------------------
> New Year. New Location. New Benefits. New Data Center in Ashburn, VA.
> GigeNET is offering a free month of service with a new server in Ashburn.
> Choose from 2 high performing configs, both with 100TB of bandwidth.
> Higher redundancy.Lower latency.Increased capacity.Completely compliant.
> http://p.sf.net/sfu/gigenet
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

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

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

Hi Dayton,

On Tuesday 20 January 2015 17:55:46 Dayton Gomez wrote:
> Another interesting fact is that the slowness happens on 11g, but not
> oracle 12c. 12c seems to operate just fine with either. Both have:
> NLS_CHARACTERSET        WE8MSWIN1252
> NLS_NCHAR_CHARACTERSET        AL16UTF16
> 
The Django documentation says that Django assumes a server encoding of UTF8; 
your use of UTF16 could create problems with values Django will think are fine 
but your server will think are too long.

Django also uses utf8 as the client-side encoding -- and that may (or may not) 
be related to the performance problem.

I recommend that, if this makes sense businesswise, you try to use a utf8 
encoding such as AL32UTF8.

HTH,
	Shai.

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

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

I grabbed the ful query, plus the bound variables. I get this for bound vars:

DATATYPE_STRING   VALUE_STRING
---------------------------    ----------------------
NVARCHAR2(32)        ios

It seems right. The only thing I notice is that the table definition is for NVARCHAR2(20).

From: Vladimir Ryabtsev <gre...@gm...<mailto:gre...@gm...>>
Reply-To: "cx-...@li...<mailto:cx-...@li...>" <cx-...@li...<mailto:cx-...@li...>>
Date: Tuesday, January 20, 2015 2:42 PM
To: "cx-...@li...<mailto:cx-...@li...>" <cx-...@li...<mailto:cx-...@li...>>
Subject: Re: [cx-oracle-users] Python 2: unicode vs str param causing slowness

Hi,
Did you try to look the text of actual query that being executed when you come across the slowness?
Something like this: http://stackoverflow.com/questions/55899/how-to-see-the-actual-oracle-sql-statement-that-is-being-executed
You can also find out the datatype of your bound variable :platform as mentioned here: http://stackoverflow.com/questions/1707291/how-to-get-the-last-executed-sql-statement-and-bind-variable-values-in-oracle
This will you give a clue about implicit datatype conversion.
I don't think it's a django thing.

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

[Vladimir R. <gre...@gm...>]

Hi,
Did you try to look the text of actual query that being executed when you
come across the slowness?
Something like this:
http://stackoverflow.com/questions/55899/how-to-see-the-actual-oracle-sql-statement-that-is-being-executed
You can also find out the datatype of your bound variable :platform as
mentioned here:
http://stackoverflow.com/questions/1707291/how-to-get-the-last-executed-sql-statement-and-bind-variable-values-in-oracle
This will you give a clue about implicit datatype conversion.
I don't think it's a django thing.

2015-01-20 21:05 GMT+03:00 Dayton Gomez <Day...@sy...>:

> Yea, I think you're right. There's some implicit type conversion.
>
> Sadly, flipping the WHERE clause didn't change the outcome.
> --
>
>
> On 1/20/15 10:39 AM, "Doug Henderson" <djn...@gm...> wrote:
>
> >On 20 January 2015 at 08:55, Dayton Gomez <Day...@sy...>
> >wrote:
> >>
> >> 100 seconds:
> >> cursor.execute('''SELECT COUNT(*) FROM "device" WHERE "PLATFORM" =
> >> :platform''', {'platform': u'ios'})
> >>
> >> .1 seconds:
> >> cursor.execute('''SELECT COUNT(*) FROM "device" WHERE "PLATFORM" =
> >> :platform''', {'platform': 'ios'})
> >
> >This sounds similar to problems where the indexed column is implicitly
> >type converted to match the bind variable's type.
> >
> >To test this, in 11g, if possible, rewrite the query as:
> >
> >cursor.execute('''SELECT COUNT(*) FROM "device" WHERE :platform =
> >"PLATFORM"''', {'platform': u'ios'})
> >
> >Doug
> >
> >--
> >Doug Henderson, Calgary, Alberta, Canada
> >
> >--------------------------------------------------------------------------
> >----
> >New Year. New Location. New Benefits. New Data Center in Ashburn, VA.
> >GigeNET is offering a free month of service with a new server in Ashburn.
> >Choose from 2 high performing configs, both with 100TB of bandwidth.
> >Higher redundancy.Lower latency.Increased capacity.Completely compliant.
> >http://p.sf.net/sfu/gigenet
> >_______________________________________________
> >cx-oracle-users mailing list
> >cx-...@li...
> >https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
>
> ------------------------------------------------------------------------------
> New Year. New Location. New Benefits. New Data Center in Ashburn, VA.
> GigeNET is offering a free month of service with a new server in Ashburn.
> Choose from 2 high performing configs, both with 100TB of bandwidth.
> Higher redundancy.Lower latency.Increased capacity.Completely compliant.
> http://p.sf.net/sfu/gigenet
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

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

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

Yea, I think you're right. There's some implicit type conversion.

Sadly, flipping the WHERE clause didn't change the outcome.
-- 


On 1/20/15 10:39 AM, "Doug Henderson" <djn...@gm...> wrote:

>On 20 January 2015 at 08:55, Dayton Gomez <Day...@sy...>
>wrote:
>>
>> 100 seconds:
>> cursor.execute('''SELECT COUNT(*) FROM "device" WHERE "PLATFORM" =
>> :platform''', {'platform': u'ios'})
>>
>> .1 seconds:
>> cursor.execute('''SELECT COUNT(*) FROM "device" WHERE "PLATFORM" =
>> :platform''', {'platform': 'ios'})
>
>This sounds similar to problems where the indexed column is implicitly
>type converted to match the bind variable's type.
>
>To test this, in 11g, if possible, rewrite the query as:
>
>cursor.execute('''SELECT COUNT(*) FROM "device" WHERE :platform =
>"PLATFORM"''', {'platform': u'ios'})
>
>Doug
>
>-- 
>Doug Henderson, Calgary, Alberta, Canada
>
>--------------------------------------------------------------------------
>----
>New Year. New Location. New Benefits. New Data Center in Ashburn, VA.
>GigeNET is offering a free month of service with a new server in Ashburn.
>Choose from 2 high performing configs, both with 100TB of bandwidth.
>Higher redundancy.Lower latency.Increased capacity.Completely compliant.
>http://p.sf.net/sfu/gigenet
>_______________________________________________
>cx-oracle-users mailing list
>cx-...@li...
>https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

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

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

On 20 January 2015 at 08:55, Dayton Gomez <Day...@sy...> wrote:
>
> 100 seconds:
> cursor.execute('''SELECT COUNT(*) FROM "device" WHERE "PLATFORM" =
> :platform''', {'platform': u'ios'})
>
> .1 seconds:
> cursor.execute('''SELECT COUNT(*) FROM "device" WHERE "PLATFORM" =
> :platform''', {'platform': 'ios'})

This sounds similar to problems where the indexed column is implicitly
type converted to match the bind variable's type.

To test this, in 11g, if possible, rewrite the query as:

cursor.execute('''SELECT COUNT(*) FROM "device" WHERE :platform =
"PLATFORM"''', {'platform': u'ios'})

Doug

-- 
Doug Henderson, Calgary, Alberta, Canada

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

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

This very well could be the oracle server, but I figure I could ask here. Perhaps someone has run into this and could guide me in a better direction.

I have a table, let's call it "device", that has lots of rows. It has a column, "platform", that's NVARCHAR2(20) and has an index on it. In python, when I execute a query on the data using a unicode object as a parameter, the whole query slows down from .1 seconds to over 100 seconds.

100 seconds:
cursor.execute('''SELECT COUNT(*) FROM "device" WHERE "PLATFORM" = :platform''', {'platform': u'ios'})

.1 seconds:
cursor.execute('''SELECT COUNT(*) FROM "device" WHERE "PLATFORM" = :platform''', {'platform': 'ios'})

Another interesting fact is that the slowness happens on 11g, but not oracle 12c. 12c seems to operate just fine with either. Both have:
NLS_CHARACTERSET        WE8MSWIN1252
NLS_NCHAR_CHARACTERSET        AL16UTF16

I'm using django as a layer of abstraction above cx_Oracle, and django seems to make everything unicode before the query is sent. So string conversion beforehand is likely not an option.

Is there some… oracle-side setting I'm missing? Or maybe some encoding/decoding setting in cx_Oracle?

Thanks much!

[cx-oracle-users] Returning VArrays from callfunc

[Dominic G. <dom...@gm...>]

Apologies if this is a repeat…. I checked the archives and couldn’t find it.

I'm trying to determine if it’s possible to return a simple array of numbers from a package using cx_oracle (5.1.2). I believe this is possible. I've not been able to find anything that suggest it isn’t.  

create or replace TYPE NUMARRAY  
-- Simple VArray of numbers  
is VARRAY(3) OF NUMBER;  
/  

create or replace PACKAGE SIMPLEPACKAGE  
AS  
  FUNCTION DoSomethingSimple(  
      cust_id INTEGER)  
    RETURN numarray;  
  FUNCTION DoSomethingSimpler(  
      cust_id INTEGER)  
    RETURN INTEGER;  
END SIMPLEPACKAGE;  
/  

create or replace PACKAGE BODY SIMPLEPACKAGE  
AS  
FUNCTION DOSOMETHINGSIMPLE(  
    cust_id INTEGER)  
  RETURN numarray  
AS  
  simple_array numarray := numarray();  
BEGIN  
  simple_array.extend;  
  simple_array(1) := cust_id;  
  simple_array.extend;  
  simple_array(2) := cust_id;  
  simple_array.extend;  
  simple_array(3) := cust_id;  
  RETURN SIMPLE_ARRAY;  
END DOSOMETHINGSIMPLE;  
FUNCTION DOSOMETHINGSIMPLER(  
    cust_id INTEGER)  
  RETURN INTEGER  
AS  
BEGIN  
  RETURN cust_id;  
END DOSOMETHINGSIMPLER;  
END SIMPLEPACKAGE;  
/  

And the Python test code

import cx_Oracle  

if __name__ == '__main__':  
     with cx_Oracle.connect('soe', 'soe', 'oracle12c2/soe') as connection:  
        try:  
            cursor = connection.cursor();  
            ArrayType = cursor.arrayvar(cx_Oracle.NUMBER,3)  
            NumberType = cursor.var(cx_Oracle.NUMBER)  
            cursor.callfunc("SIMPLEPACKAGE.DOSOMETHINGSIMPLER", NumberType, [99])  
            cursor.callfunc("SIMPLEPACKAGE.DOSOMETHINGSIMPLE", ArrayType, [99])  
        except cx_Oracle.DatabaseError as dberror:  
            print dberror  
        finally:  
            cursor.close()  

The call to return works just fine. The call to return the function gives the error  

ORA-06550: line 1, column 13:  
PLS-00382: expression is of wrong type  
ORA-06550: line 1, column 7:  
PL/SQL: Statement ignored  

Any ideas what I'm doing wrong?  

Thanks

Dom 

Re: [cx-oracle-users] python code just "stops" when attempting and insert

[Amaury F. d'A. <ama...@gm...>]

Hi,

2015-01-13 16:43 GMT+01:00 Jem North <je...@3g...>:

> I have a sequence of code that iterates over a collection of data, writing
> subsections into different oracle tables.
> For some reason, the process is now intermittently locking up when it
> attempts to insert a new record.
>
> pseudo code for the issue is:
>
> try:
>   insert into the table with data;
>   record the returned id;
> except:
>   query the table for the matching data;
>   record the returned id;
>
> The exception would be thrown if the record already exists, due to Oracle
> unique indices.
>

If another session has already inserted a row with same primary unique key,
but did not commit, then the RDBMS waits until the other commits or roll
back.

Something like this: https://community.oracle.com/thread/924623



> Even when running the python code (2.7) via a debug session in eclipse,
> the process will just hang and cannot be interrupted to examine the
> failure, only terminated.
>
> The process has previously worked fine against the original Oracle 12
> database, but this problem seems to have arisen against a cloned VM.
>
>
>
> ------------------------------------------------------------------------------
> New Year. New Location. New Benefits. New Data Center in Ashburn, VA.
> GigeNET is offering a free month of service with a new server in Ashburn.
> Choose from 2 high performing configs, both with 100TB of bandwidth.
> Higher redundancy.Lower latency.Increased capacity.Completely compliant.
> http://p.sf.net/sfu/gigenet
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>



-- 
Amaury Forgeot d'Arc

[cx-oracle-users] python code just "stops" when attempting and insert

[Jem N. <je...@3g...>]

I have a sequence of code that iterates over a collection of data, writing 
subsections into different oracle tables.
For some reason, the process is now intermittently locking up when it 
attempts to insert a new record.

pseudo code for the issue is:

try:
  insert into the table with data;
  record the returned id;
except:
  query the table for the matching data;
  record the returned id;

The exception would be thrown if the record already exists, due to Oracle 
unique indices.

Even when running the python code (2.7) via a debug session in eclipse, 
the process will just hang and cannot be interrupted to examine the 
failure, only terminated.

The process has previously worked fine against the original Oracle 12 
database, but this problem seems to have arisen against a cloned VM.

Re: [cx-oracle-users] cx_Oracle - Django ORM - Reference Count Increase

[Anurag C. <anu...@gm...>]

Thanks for the response Shai. I indeed had DEBUG=True in my settings and I
can confirm that changing it to False resolved this for me.

Please note that I had already tried closing the DB Connection every once
in a while but that did not help.

*django.db.close_connection*

For some reason, the list was not getting cleared with the close of the
connnection.

Regards,
Guddu

On Mon, Dec 29, 2014 at 6:18 PM, Shai Berger <sh...@pl...> wrote:

> Hi,
>
> On Monday 29 December 2014 19:57:55 Amaury Forgeot d'Arc wrote:
> > 2014-12-27 22:23 GMT+01:00 Anurag Chourasia <anu...@gm...
> >:
> > > I have a Long Running Python Process that uses Django ORM against
> Oracle
> > > database.
> > >
> > > The size of the process keeps on increasing steadily.
> > >
> > > I was profiling this process using mem_top and i find that the
> reference
> > > count of one particular data type <list> increases continuously with
> > > iterations.
> > >
> > > Datatype is <type 'list'> [{u'time': u'0.004', u'sql': u'QUERY =
> > > u\'SELECT "RANGE_STATUS"."I
> > >
> > > References increased from 534 to 53295
> >
> > This list looks very much like this one:
> >
> https://docs.djangoproject.com/en/1.7/faq/models/#how-can-i-see-the-raw-sql
> > -queries-django-is-running
> >
> > I don't know why there should be many references to it, but it's
> definitely
> > a Django thing.
>
> I am a Django core team member, and yes, this is definitely a Django thing.
>
> You can prevent this list from being created by changing your DEBUG
> setting to
> False; or, if you do need debug info, close the connection once in every
> while; this will free the list of queries. A new connection will be opened
> automatically when you execute the next database query (you cannot use the
> CONN_MAX_AGE setting for this, because it is tied to the requests cycle,
> and
> you apparently do not have requests).
>
> As none of the above is Oracle-specific, the proper forum for further
> discussion of this is the django-users list[1] or the #django IRC channel
> on
> freenode.net.
>
> [1] http://groups.google.com/group/django-users
>
> HTH,
>         Shai.
>
>
> ------------------------------------------------------------------------------
> 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
>

Re: [cx-oracle-users] cx_Oracle - Django ORM - Reference Count Increase

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

Hi,

On Monday 29 December 2014 19:57:55 Amaury Forgeot d'Arc wrote:
> 2014-12-27 22:23 GMT+01:00 Anurag Chourasia <anu...@gm...>:
> > I have a Long Running Python Process that uses Django ORM against Oracle
> > database.
> > 
> > The size of the process keeps on increasing steadily.
> > 
> > I was profiling this process using mem_top and i find that the reference
> > count of one particular data type <list> increases continuously with
> > iterations.
> > 
> > Datatype is <type 'list'> [{u'time': u'0.004', u'sql': u'QUERY =
> > u\'SELECT "RANGE_STATUS"."I
> > 
> > References increased from 534 to 53295
> 
> This list looks very much like this one:
> https://docs.djangoproject.com/en/1.7/faq/models/#how-can-i-see-the-raw-sql
> -queries-django-is-running
> 
> I don't know why there should be many references to it, but it's definitely
> a Django thing.

I am a Django core team member, and yes, this is definitely a Django thing.

You can prevent this list from being created by changing your DEBUG setting to 
False; or, if you do need debug info, close the connection once in every 
while; this will free the list of queries. A new connection will be opened 
automatically when you execute the next database query (you cannot use the 
CONN_MAX_AGE setting for this, because it is tied to the requests cycle, and 
you apparently do not have requests).

As none of the above is Oracle-specific, the proper forum for further 
discussion of this is the django-users list[1] or the #django IRC channel on 
freenode.net.

[1] http://groups.google.com/group/django-users

HTH,
	Shai.