Re: Query 60x slower in PL/SQL

See comments below ...

Vahe Sarkissian wrote:
> Hi,
> I'm an old hand with SQL, but fairly new to Oracle PL/SQL. I have a
> query which executes in about 15 seconds in interactive sql (SQL*Plus
> or Toad). Yet the same query in a PL/SQL procedure takes over 15
> minutes.
>
> I'm not using a loop or any such: the query looks like
>
> INSERT (...) SELECT ... FROM ... WHERE
>
> The entire procedure is basically that insert statement with a
> BEGIN/EXCEPTION/END wrapped around it. I'm using parameters passed in
> to the procedure in the where clause; could that make that big of a
> differece?
>
> I'm using tables through a remote database link. I'm not sure whether
> or why that would make a difference in PL/SQL, but not interactive
> SQL.
Querying through a remote DB link could slow things down.

>
> I've looked around my well-thumbed O'Reilly PL/SQL book, around OTN,
> and googled for a while, but I can't find anything. Most of the docs
> suggest the slowdown comes from using cursor loops inappropriately,
> but I'm not using one at all.
>
> Any suggestions from this short description? Any resources I've
> missed? I'm not even sure how I could profile this, since the whole
> procedure is basically just one statement.
What are the results of an explain plan or tkprof output. If these look [Quoted] good, what about setting event 10046 and looking for waits?

>
> Thanks,
> --Vahe Sarkissian

--
Ron Reidy
Oracle DBA