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

Stmt 1 and stmt 2 - are these DML, or just select statements? If DML, what might be happening is that the undo data for stmt1 and stmt2 used up all the buffer cache so the undo data for slow_sql has to go to disk.   However, if you return first after stmt2 (thus committing the transaction) and then run slow_sql separately, then the undo data for slow_sql now can be stored in the buffer cache.

Cheers,
Dave

Sri wrote:
> 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..
Received on Thu Jul 31 2003 - 19:44:11 CDT