Why PLSQL is slower than SQL * Plus

Question

I have several Oracle queries that work well when executing SQL * PLUS. However, when they are executed as part of a PL / SQL package, they take up MUCH more.

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

Does anyone have pointers on where to look for the wrong configuration?

Client - Windows 2000 Server - Linux (Oracle Enterprise)

thanks

-

Resolution:

I would like to accept all the answers. Some of them were very helpful.

• The query converted data types.
• Implementation plans do not match. (Tips fixed.)
• The DBA looked at the fact that the cursor was open instead of the query time.

Answer 1

Use SQL tracing to see which execution plans are in each case. One possibility that comes to mind (from experience): Does the package associate the wrong type of values ​​with the query? Perhaps in SQL Plus you run:

select * from mytable where id = '1234'; 

but in the PL / SQL that you run:

 select * from mytable where id = p_id; 

where p_id is defined as a number. This will force TO_NUMBER in the ID column and prevent Oracle from using the index.

Answer 2

Most likely, these are not queries that work longer, but service data is processed in PL/SQL .

When you process the query results in a PL/SQL script, a context switch occurs. It requires transferring data with data between Oracle processes and is rather 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, and the following:

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

ends in just 0.5 seconds.

A context switch also occurs when you call PL/SQL from SQL , for example:

 SELECT plsql_function(column) FROM mytable 

or when the trigger fires.

Answer 3

Our DBA observed that these queries take 10 minutes through PLSQL and 10 seconds through PL / PSQL.

I could understand if the DBA could solve this problem for you, but if your database administrator really saw both events and has not yet provided you with explanation plans for both cases, then he really is not a very good database administrator.

There is probably no wrong configuration, I had this on myself - all the bind variables, no constants, no hints. Run it directly - good performance. Put it inside a BEGIN..END-bam, slowly, like hell. It turned out that sometimes queries simply use different execution plans from PL / SQL (it was Oracle 9.2).

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

Other possible problems:

• SQL * Plus returns only the first 100 or so rows, and then waits for you to ask for more, but PL / SQL should handle all they did not ask. A trivial problem, but sometimes it is missed.
• You use constants for SQL * Plus and bind variables for PL / SQL. Sometimes using constants allows the optimizer to check for corrupted data and use a different index.

Answer 4

Are you really comparing as if? Do you execute raw SQL queries in PL / SQL (the best option) or do you use explicit or implicit cursors to return values ​​and then process them? There is a big difference.

Answer 5

We faced a similar problem. The update request was very slow for 17 minutes when used in a PL / SQL block and was very fast (less than 2 seconds) when used outside of PL / SQL.

We found that the execution plan used in PL / SQL is different.

Using "alter system flush shared_pool" solved the problem for us. It seemed that PL / SQL was forcing a review of the execution plan.

Answer 6

DML (e.g. SELECT, UPDATE, DELETE) released through SQLPlus is issued directly to the Oracle SQL engine, while DML in the PLSQL procedure is first processed by PL / SQL (for example, to bind variables) and then sent to the SQL engine.

For the most part, the same statement in PL / SQL will work the same as SQL, and in both cases the same execution plan is usually executed. In my experience (usually when variable binding is required), this can lead to very different performance. I have seen times when SELECT released in SQL Plus takes a split second, while SELECT released through PL / SQL takes 1-2 minutes.

I recommend that you customize your statement so that it works as well in PL / SQL as it does in SQL. Focus on variable binding correctly (using FORALL and BULK COLLECT), as well as study execution plans and do unit tests.

Answer 7

To quote and expand Quassnoi:

Most likely, these are not queries that work longer, but the overhead processes them in PL / SQL.

When processing query results in a PL / SQL script, context switching occurs. It requires the transfer of goods between Oracle processes and is fairly 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; 

works on my machine for more than 3 seconds, and this one:

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

ends in just 0.5 seconds.

A context switch also occurs when you call PL / SQL from SQL, for example:

 SELECT plsql_function(column) FROM mytable or when a trigger fires. 

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