Re: Sql statement slow in pl/sql but ok outside

"Sri" <zmurali_at_certportal.com> wrote in message news:d178ea15.0307311610.1f4b96cf_at_posting.google.com...
> Hello,
> I have an insert statement in a pl/sql procedure that runs very slow.
> But if I were to stop the procedure just before the insert statement
> and run it (the insert i.e.,) in sqlplus it runs right away. Any ideas
> on what's going on?
> I apologize for not being able to post the complete test case (its has
> lot of tables and other stuff and I don't know if I can duplicate this
> with a simple example).
>
> my procedure is somewhat like this:
>
> procedure p1 as
> begin
> stmt 1 ;
> stmt 2 ;
> slow_sql ; -- takes long if run inside procedure
> stmt 3 ;
> end ;
>
> If I were to run the procedure up to stmt_2, use a 'return' statement
> to get out to sql prompt and run 'slow_sql', it runs immediately
>
> Any possible causes? Any thoughts on how i should try to debug this?
>
> Thanks in advance..

and your Oracle version is?

Define slow. In your procedure you are waiting for all the rows to be returned. In sqlplus
you might start seeing the rows earlier on .. but the total run time is the same.
A less probably reason might be that the explain plans are different.

What does tkprof say (+using explain plan) for the two different runs?

Anurag Received on Thu Jul 31 2003 - 19:23:01 CDT