Re: Slow Cursor

I think there must be some bits of
information that you are not supplying.

In principal the path you claim is extremely unlikely for the fragment of query supplied. Given the complete absence of exclusion clauses Oracle has to examine and join at least 500,000 rows - which is pretty good in 4 seconds.

However, one point to bear in mind - SQL inside PL/SQL operates under the ALL_ROWS optimisation goal, possibly your SQL session is using a different goal.

Suggestion: for optimal response times where the result set is always expected to be very small, put the /*+ first_rows */ hint in the SQL executed by PL/SQL.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Jens Mayer wrote in message <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....
Received on Tue Nov 23 1999 - 16:21:27 CST