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.

Answer 4

DML (e.g. SELECT, UPDATE, DELETE) issued through SQLPlus is issued directly to Oracle's SQL engine whereas DML in a PLSQL procedure is first processed by PL/SQL (e.g to do variable bindings) and then sent to the SQL engine.

For the most part the same statement in PL/SQL will perform the same as SQL and both ways will usually produce the same execution plan. In my experience (usually when binding of variables is required) it can cause very different performance. I have seen times where a SELECT issued in SQL Plus takes a fraction of a second while a SELECT issued through PL/SQL takes 1-2 minutes.

I recommend you tune your statement so it works just as well in PL/SQL as it does in SQL. Focus on binding variables correctly (using FORALL and BULK COLLECT) but also examine the execution plans and do unit tests.

Answer 5

Are you truly comparing like-for-like here? Are you executing raw SQL statements in PL/SQL (the optimum case) or are you using explicit or implicit cursors to return values and then process them? There's a big difference.

Answer 6

To quote and extend Quassnoi:

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.

One way to solve the context switch problem is to use a BULK COLLECT. If you are collecting a lot of rows, using BULK COLLECT INTO a collection of some type can dramatically speed up SQL in PL/SQL statements.