Re: Explain Plan For Query

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
Received on Fri Apr 12 2002 - 09:54:26 CDT