Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Explain Plan For Query
On 11 Apr 2002 08:15:09 -0700, yitbsal_at_yahoo.com (Salaam Yitbarek)
wrote:
>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.
>
The default optimizer goal for pl/sql is ALL_ROWS and AFAIK you can't
change that. ALL_ROWS
retrieves the complete resultset in SGA
favors full tablescans.
Hth
>Why is it taking so long when run from a stored procedure? Anyone had
>this happen to them before? Help?!!
>
>Thanks,
>Salaam
Sybrand Bakker, Senior Oracle DBA
To reply remove -verwijderdit from my e-mail address Received on Thu Apr 11 2002 - 12:13:46 CDT