Re: Slow Cursor

As usual a bit of guessing as you, like too many people in this ng do, do not provide any clue as to which version and platform you are using. The answer to this is array fetching. PL/SQL in Oracle 7 doesn't support array fetching while sqlplus does. This means all your rows will be fetched one at a time in PL/SQL and multiple (I believe the default is 14) at a time in sqlplus.
If you have Oracle 8 you should be able to do this using dbms_sql and calls to dbms_sql.define_array.

Hth,

--
Sybrand Bakker, Oracle DBA
Jens Mayer <jmayer_at_ratundtat.com> wrote in message news:383abe92.22950921_at_news.space.net...
> Hi there,
>
> we have a problem with the performance of a cursor. The cursor is a
> join of two tables A and B, A has about 500.000 records, B about
> 10.000.000 records. Primary key of A is col1, primary key of B is
> col1,col2. The join is like
>
> select ...
> from a,b
> where a.col1 = b.col1
>
> The execution plan uses a unique index access for table A and a index
> range scan for table B
>
> We're using this cursor in a package, where it needs 4 seconds to
> retrieve about 10 records. In my opinion, this is SLOW! If we test the
> same select-statement directly from the SQL*Plus-Prompt, the DB
> answers in less than 1 seconds.
>
> How can we tune this ? The cursor has to be executed several thousand
> times, this will last for hours....
>
> Can anyone help ?
>
> Jens
>
>
>
>
>
>
>
> -
> Jens Mayer
> Rat & Tat GmbH
> Hamburg, Germany
Received on Tue Nov 23 1999 - 13:23:39 CST