Home » Server Options » Spatial » Arraysize over 5000 (Oracle Locator 11g)
Arraysize over 5000 [message #655338] Mon, 29 August 2016 03:02 Go to next message
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 8782 times)
Re: Arraysize over 5000 [message #655339 is a reply to message #655338] Mon, 29 August 2016 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Arraysize over 5000 [message #655342 is a reply to message #655339] Mon, 29 August 2016 06:05 Go to previous messageGo to next message
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?
Re: Arraysize over 5000 [message #655343 is a reply to message #655342] Mon, 29 August 2016 06:13 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

See SQL Trace and also dbms_session (for your own session) and dbms_monitor (for others session):
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
Previous Topic: Convert coordinates UTM 32 to Wgs84 format
Next Topic: Using SDO_UTIL.GETVERTICES() to fetch all points
Goto Forum:
  


Current Time: Thu Mar 28 18:48:02 CDT 2024