How to set Readbuffer Parameter (equivalent to SQL*Plus set arraysize)

[zstringer]

Hello all,

I'm copying a 6mm row dataset over a database link, and we've found using the performance using PL/SQL to be very poor.

However, if we run a SQLPlus script with SET ARRAYSIZE = 1500 we get satisfactory performance.

I found some references to the READBUFFER parameter; is there a way I can set this either in Oracle or PL/SQL Developer? That way we could improve the performance of other data copies done using PL/SQL.

Thanks,
Peter

 

[Marco Kalter]

Can you show me an example or concept of the script that performs this copy operation?

 

[MarcusRangel]

Hello,

I'm having the same problem. Have you found a solution?

Best regards,
Marcus

 

[Marco Kalter]

Can you provide an example?

 

[MarcusRangel]

Well, it's a performance issue. Our development databases are in AWS, so it would be more efficient to bring everything in larger chunks. For instance, using SQL*Plus:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SET TIMING ON
SQL> SET AUTOT TRACEONLY STATISTICS
SQL> select * from kiprev.fo_cuentas where rownum < 5000;

4999 rows selected.

Elapsed: 00:00:58.42

Statistics
----------------------------------------------------------
...
800392 bytes sent via SQL*Net to client
4139 bytes received via SQL*Net from client
335 SQL*Net roundtrips to/from client
...

SQL> SET ARRAYSIZE 1000
SQL> select * from kiprev.fo_cuentas where rownum < 5000;

4999 rows selected.

Elapsed: 00:00:08.17

Statistics
----------------------------------------------------------
...
772098 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client...

As you can see, the time spent and the number of roundtrips drop dramatically.

The Command Window accepts setting ARRAYSIZE, and maybe the SQL Window does too, but the problem for me are all the things brought by sessions I cannot control. The main issue are large packages; plsqldev is taking several minutes to open the code in a Program Window.

In both TOAD and SQLDeveloper you can set the arraysize, so I guess it's something you can pass to the OCI libraries when requesting a connection.

 

[Marco Kalter]

The main issue are large packages; plsqldev is taking several minutes to open the code in a Program Window.

The arraysize for reading package sources should be large enough. To obtain some more diagnostic information, can you modify the PL/SQL Developer shortcut and add the DebugSQL parameter? For example:

"C:\Program Files\PLSQL Developer 13\plsqldev.exe" DebugSQL

Reproduce the performance problem and send me the debug.txt file that is generated in the %APPDATA%\PLSQL Developer 13 directory (e.g. C:\Users\\AppData\Roaming\PLSQL Developer 13).

 

[shiv Kumar]

Parameter 2 specifies the address of a buffer and parameter 3 specifies the buffer length. These two parameters are not used for the INIT, TERM, and CLOSE functions.
On input for a WRITE function, the buffer address points to a buffer that contains the record to be written. The buffer length parameter specifies the length of the data to be written from the buffer. The caller must provide the buffer address and length. If you provide your own replaceable I/O routines, your routine must support all of the functions that the system-supplied I/O routine performs. When the I/O routine is called with the TERM function, all buffers are freed.

 

[MarcusRangel]

Here is the debug output:
https://s3.amazonaws.com/kiman-public/Debug.zip

What I did was: run plsqldev, connect to a database hosted in AWS, open the package (view spec and body), wait ~3 minutes until loaded, close plsqldev. The same package opens in 4 seconds from a database inside my network.

 

[MarcusRangel]

So... should I look for the arraysize setting in a future version?