Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Explain Plan For Query

Re: Explain Plan For Query

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Thu, 11 Apr 2002 19:13:46 +0200
Message-ID: <3vgbbucc07vml3rnn5fsqejt5am8eds5nl@4ax.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US