CODEWITHSUNDEEP
performanceoracle-databaseplsql
Brad Bruce
Brad Bruce

Reputation: 7817

Why is PLSQL slower than SQL*Plus

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.

Upvotes: 6

Views: 7469

Answers (7)

Sebastien
Sebastien

Reputation: 6542

We faced a similar issue. An update query was running very slowly 17 Minutes when used in PL/SQL block and executing very fast (less than 2 seconds) when used outside PL/SQL.

We discovered that the execution plan used in PL/SQL was different.

Using "alter system flush shared_pool" solved the issue for us. It seemed to force PL/SQL to reconsider the execution plan to use.

Upvotes: 1

darreljnz
darreljnz

Reputation: 2380

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.

Upvotes: 0

Brian
Brian

Reputation:

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.

Upvotes: 0

David Aldridge
David Aldridge

Reputation: 52386

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.

Upvotes: 1

jva
jva

Reputation: 2807

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.

Upvotes: 2

Quassnoi
Quassnoi

Reputation: 425451

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.

Upvotes: 3

Tony Andrews
Tony Andrews

Reputation: 132590

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.

Upvotes: 9

Related Questions