Re: Query 60x slower in PL/SQL

vahe.sarkissian_at_latimes.com said...
> 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.
>
> 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
>

[Quoted] Are you saying that your procedure is something like this?

  create or replace procedure xyz(param in varchar2) is   begin
    insert (...) select ... from ... where ...;   exception
    when ... then ...
  end xyz;

[Quoted] If your INSERT is *exactly* the same as the one you execute in SQL*Plus, then your timing should be identical (plus or minus a microsecond or two). After all, PL/SQL simply passes the INSERT off to the SQL engine (just like SQL*Plus does) to be executed. The db link won't add any more to the time than it does from SQL*Plus.

But I suspect you are not doing the above. After all, you wouldn't need any parameters if it was the identical INSERT.

So, tell us the whole story. How are you calling this procedure? What are you doing with your parameter(s) in your WHERE clause? Given an identical INSERT statement, PL/SQL should outperform SQL*Plus because it automatically uses bind variables (so you avoid all those nasty hard parses).

-- 
/Karsten
DBA > retired > DBA