Re: Explain Plan For Query

On 12 Apr 2002 07:54:26 -0700, afilonov_at_yahoo.com (Alex Filonov) wrote:

>yitbsal_at_yahoo.com (Salaam Yitbarek) wrote in message news:<77439c33.0204110715.12487d1d_at_posting.google.com>...
>> Hi,
>>
>> We're running 8.1.7 on a Solaris 5.7 box.
>>
>> I have the following simple cursor in a stored procedure. It fetches,
>> at most, 2 records. I only want the first.
>>
>> CURSOR cur_Terminated IS
>> SELECT TERMINATED
>> FROM VECTOR
>> WHERE VECTOR_ID = VectorId_in
>> ORDER BY VERSION DESC;
>> ...
>> OPEN cur_Terminated;
>> FETCH cur_Terminated INTO var_Terminated;
>> CLOSE cur_Terminated;
>> ...
>>
>> It takes over 4 minutes to run.
>>
>> The same query, run from the sql prompt, takes milliseconds, and has a
>> good explain plan.
>>
>> Why is it taking so long when run from a stored procedure? Anyone had
>> this happen to them before? Help?!!
>
>PL/SQL uses ALL_ROWS for default. But you can change it with hints. Try to put
>hint first_rows in you select. And run plans for both ALL_ROWS and FIRST_ROWS to
>check the difference. Sometimes you need to switch to rule-based optimization
>(hint RULE). And sometimes you just have to manually define the whole plan
>using combination of hints, inline views and subqueries. I don't think that's the
>case here, select statement is too simple.
>
>>
>> Thanks,
>> Salaam

Hi,
please note, that the ORDER BY clause forces the optimizer to ignore the FIRST_ROWS hint. Oracle got to gather all rows to be able to sort them.

corrections are welcome

regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Sat Apr 13 2002 - 11:55:20 CDT