Re: Query 60x slower in PL/SQL

Karsten, thank you for your note. I've added comments below.

Karsten Farrell <kfarrell_at_belgariad.com> wrote in message news:<MPG.18bdaee2915af6269896b8_at_news.la.sbcglobal.net>...
> <SNIP>
> 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;

Yes, that's it. The only difference from what you've shown is that mine is in a package, and there are two params, both dates. So more like this:

    procedure foo (p_start in date, p_end in date) is     begin

        insert xxx (...) select ... from ...
        where the_date between p_start and p_end
    exception
        when ....

The where clause also joins the three tables in the from clause (all remote) on their PK/FK columns. The column I've called "the_date" here is a date, also.

> 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.

That's what has me baffled, hence my posting. ;-)

I first wrote the proc, and when it took 15 minutes, I pulled out the SQL to do an EXPLAIN PLAN. The plan looked reasonable, so I just ran the query and was stunned when it completed in 15 seconds. (When I ran it in SQL*Plus, I put literal strings for the dates, the same ones I'd used in testing the proc.) I then spent the rest of the day re-testing and investigating. I tried trivial re-writes, optimizer hints, changing to dynamic SQL, all to no avail. Finally I thought I'd ask here.

Possibly useful info: I'm using Oracle 9.0.1.4 on Solaris, and the remote side is 8.1.7.3 on Win2K. Although I don't know why that would have a bearing on PL/SQL and not SQL*Plus. As you said, it's all the same underlying SQL engine, isn't it?

Thanks for any suggestions or resources you could offer. --Vahe Sarkissian Received on Thu Feb 20 2003 - 16:24:22 CET