Query 60x slower in PL/SQL

From: Vahe Sarkissian <vahe.sarkissian_at_latimes.com>
Date: 19 Feb 2003 13:45:41 -0800
Message-ID: <510e7b7e.0302191345.d7e3412_at_posting.google.com>

Hi,
[Quoted] [Quoted] 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. [Quoted] 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.

Thanks,
--Vahe Sarkissian Received on Wed Feb 19 2003 - 22:45:41 CET