Re: Slow Cursor

In your example with the information you have supplied, it could be difficult to tell what the problem is. One suggestion I have is in the from clause, if you switch positions of a and b, your execution plan will change. Since table a is only 500,000 rows and table b is 10,000,000, you might be better off with this switch. What happens is that the parser works on the statement backwards and therefore may be seeing table b as the driving table. This could slow performance down using table b as the driving table instead of table a. In reading the other responses, you may have to couple this idea with what the others have suggested.

Eric Peterson
Programmer/Analyst DBA
Maurices Inc.
eric_peterson_at_maurices.inrg.nospammers.com

To email me, remove the '.nospammers'
The views I have expressed are solely my own and not my companies'. In article <383abe92.22950921_at_news.space.net>,   jmayer_at_ratundtat.com wrote:
> 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
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 24 1999 - 10:04:06 CST