Re: Slow cursor

Michael,

you're right, there ARE additional conditions, but the execution plan tells me that Oracle doesn't make a full table scan on Tab A or B. And, what makes me so sure, is the fact, that calling the same select-statement from the SQL*Plus-Prompt takes less than a second for execution. Only within the package the cursor is slow.

I got an email concerning this problem. The author mentioned, that the difference between PL/SQL and SQL*Plus is, that SQL*Plus supports Array-fetching. In 7.3.4, PL/SQL doesn't support this technique and perhaps that's the cause why the cursor is only slow in the package.

On Tue, 23 Nov 1999 21:33:51 GMT, michael_bialik_at_my-deja.com wrote:

>Hi.
>
> Are you sure you don't have additional conditions in WHERE clause?
> It looks like you supposed to see FULL table access to either tabA or
> tabB.
>
> Michael.
>
>
>
>In article <383ac111.23589830_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.

-
Jens Mayer
Rat & Tat GmbH
Hamburg, Germany Received on Wed Nov 24 1999 - 05:40:53 CST