OraFAQ Forum: Spatial » Arraysize over 5000

Home » Server Options » Spatial » Arraysize over 5000 (Oracle Locator 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Arraysize over 5000 [message #655338]

Mon, 29 August 2016 03:02

moehre
Messages: 43
Registered: June 2016

Member

Hi,

I have created a webserver with python and cx_Oracle.
But now I have a problem to fetch the data. Sometimes my tiles have more than 5000 output rows. So in the cx_Oracle interface it is written that the default value for arraysize is 50. If I increase the value of 10.000 (cursor.arraysize = 10000) it works fine. But a value lower than 5000 needs milliseconds and a value bigger than 5000 needs some seconds. You can see the results in the picture
/forum/fa/13242/0/

!

Furthermore it is written in the Oracle Docu:

SET ARRAY[SIZE] {15 | n}

Sets the number of rows, called a batch, that SQL*Plus will fetch from the database at one time.Valid values are 1 to 5000. A large value increases the efficiency of queries and subqueries that fetch many rows, but requires more memory.

So I think the results are regarding to this that over 5000 is not a valid value so the execution time is increased strongly...Is this right?
Is there a way to avoid this ?

Attachment: overview.JPG
(Size: 32.97KB, Downloaded 10808 times)

Report message to a moderator

Re: Arraysize over 5000 [message #655339 is a reply to message #655338]

Mon, 29 August 2016 03:16

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Note we don't know what your chart represents, you should explain it.

Quote:

So I think the results are regarding to this that over 5000 is not a valid value so the execution time is increased strongly...Is this right?

No.
The problem then comes from client program and OS. It is NOT the execution time which increases but the end user one.
Activate a SQL or 10046 trace and you will see where the time is spent.

Report message to a moderator

Re: Arraysize over 5000 [message #655342 is a reply to message #655339]

Mon, 29 August 2016 06:05

moehre
Messages: 43
Registered: June 2016

Member

Ok I never used this before. Can I set this into my webserver befor I execute the sql-query (ALTER SESSION SET sql_trace = true;)?
Or do I have to run it from sql developer?

Report message to a moderator

Re: Arraysize over 5000 [message #655343 is a reply to message #655342]

Mon, 29 August 2016 06:13

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

See SQL Trace and also dbms_session (for your own session) and dbms_monitor (for others session):

Code: [Select all] [Show/ hide]

Owner           Package                Procedure              Argument               Type                 In Out D Opts
--------------- ---------------------- ---------------------- ---------------------- -------------------- ------ - ----
SYS             DBMS_SESSION           SESSION_TRACE_DISABLE                                                       U
                                       SESSION_TRACE_ENABLE   WAITS                  PL/SQL BOOLEAN       IN     Y U
                                                              BINDS                  PL/SQL BOOLEAN       IN     Y
                                                              PLAN_STAT              VARCHAR2             IN     Y


Options: A: Aggregate, D: Deterministic, F: auth. deFiner, P: Pipelined, U: auth. current User

Owner           Package                Procedure              Argument               Type                 In Out D Opt
--------------- ---------------------- ---------------------- ---------------------- -------------------- ------ - ---
SYS             DBMS_MONITOR           SESSION_TRACE_DISABLE  SESSION_ID             BINARY_INTEGER       IN     Y F
                                                              SERIAL_NUM             BINARY_INTEGER       IN     Y
                                       SESSION_TRACE_ENABLE   SESSION_ID             BINARY_INTEGER       IN     Y F
                                                              SERIAL_NUM             BINARY_INTEGER       IN     Y
                                                              WAITS                  PL/SQL BOOLEAN       IN     Y
                                                              BINDS                  PL/SQL BOOLEAN       IN     Y
                                                              PLAN_STAT              VARCHAR2             IN     Y


Options: A: Aggregate, D: Deterministic, F: auth. deFiner, P: Pipelined, U: auth. current User

Report message to a moderator

Previous Topic:	Convert coordinates UTM 32 to Wgs84 format
Next Topic:	Using SDO_UTIL.GETVERTICES() to fetch all points

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 08 03:42:37 CDT 2025