Re: Performance Tuning

If you want to fetch a batch of rows into a PL/SQL cursor, add the following statement at the start of your PL/SQL code - after the initial 'BEGIN' statement (It is a SQL*Plus command)

SET ARRAYSIZE 100 This contols the maximum number of rows fetched at any one time (in a multi-row fetch senario.) i.e. if you set arraysize = 1 this will return the first row fast when executing an on-line query. Set to higher values if you are concerned about executing the whole fetch (or set of fetches) in minimum time with reduced network traffic. According to Oracle, performance is not significantly improved above array sizes of 100, you just eat more memory.

The statement can be used on any on-line SQL session or within PL/SQL blocks of code.

Hope this is of use.

Regards  

• Martyn Cavett Senior ORACLE Analyst LGT Asset Management London, UK. home: cavett_at_globalnet.co.uk work: Martyn.Cavett_at_gtplc.com

These views are my own and not those of my employer. Normal conditions apply.

Stevan R. Makarevich <smakar_at_primenet.com> wrote in article <3362d05f.1446145_at_news.primenet.com>...
> Background:
> We are developing a WEB application using the Oracle WEBSERVER Toolkit
> and PL/SQL. The data resides on a mainframe DB2 database (ver 4.1),
> and is accessed via the Oracle Transparent Gateway for DB2.
>
> Question:
> What is the most efficient way of cursor processing in PL/SQL? We know
> that in DB2 SQL, a block fetch can be forced with the use of "SELECT
> FOR FETCH'; is there a similar statement in PL/SQL? We have not been
> able to find it... is it implied, or is each fetch an I/O over the
> network?
>
> Information would be greatly appreciated.
>
Received on Sun Apr 27 1997 - 00:00:00 CDT