Why is PLSQL slower than SQL*Plus

Question

I have several Oracle queries that perform well when run through SQL*PLUS. However when they are executed as a part of a PL/SQL package, they take MUCH longer.

Our DBA has watched these queries take 10 minutes through PLSQL and 10 seconds through SQL*Plus.

Does anybody have any pointers on where to look for the misconfiguration?

Client - Windows 2000 Server - Linux (Oracle Enterprise)

Thanks

--

Resolution:

I wish I could have accepted everyone's answers. Several of them were quite helpful.

• The query was converting data types.
• The execution plans didn't match. (Hints fixed that.)
• The DBA was looking at the time the cursor was open instead of the query time.

Answer 1

Use SQL trace to see what the execution plans are in each case. One possibility that springs to mind (from experience): is the package binding the wrong type of values to the query? It could be that in SQL Plus you are running:

select * from mytable where id = '1234';

but in PL/SQL you are running:

select * from mytable where id = p_id;

with p_id being defined as a number. That will force a TO_NUMBER on the ID column and prevent Oracle using the index.

Answer 2

Most probably, it's not the queries that run longer but the overhead to process them in PL/SQL.

When you process the query results in a PL/SQL script, a context switch occurs. It requires to pass loads of data between Oracle processes and is quite slow.

Like this code:

DECLARE
        cnt INTEGER := 0;
        CURSOR  cr_main IS
        SELECT  1 AS id
        FROM    dual
        CONNECT BY
                level <= 1000000;
BEGIN
        FOR res IN cr_main
        LOOP
                cnt := cnt + res.id;
        END LOOP;
        DBMS_OUTPUT.put_line(cnt);
END;

runs for more than 3 seconds on my machine, while this one:

SELECT  SUM(1)
FROM    dual
CONNECT BY
        level <= 1000000

completes in only 0.5 seconds.

The context switch also occurs when you call PL/SQL from SQL, like this:

SELECT  plsql_function(column)
FROM    mytable

or when a trigger fires.

Answer 3

Our DBA has watched these queries take 10 minutes through PLSQL and 10 seconds through PL/PSQL.

I could understand if DBA wouldn't want to solve this issue for you but if your DBA really has seen both occurences and has not provided you with explain plans for both cases yet, then he really is not a very good DBA.

There probably is no misconfiguration, I've had it happen on myself - all bind variables, no constants, no hints. Run it directly - good performance. Put it inside BEGIN..END - bam, slow as hell. Turned out that sometimes queries just use different execution plans from within PL/SQL (that was Oracle 9.2).

My solution - used hints until PL/SQL version used the same plan as SQL.

Other possible issues:

1. SQL*Plus returns only first 100 or so rows and then waits for you to ask for more, but PL/SQL has to process them all without asking. Trivial issue but sometimes overlooked.
2. You use constants for SQL*Plus and bind variables for PL/SQL. Sometimes using constants allows optimizer to check for skewed data and it could use some other index.