0

Seems I have found answer my own question :

First - let me reword my question :

"Under exactly same environment and conditions" - Some users are complaining that same SQL which runs in few seconds takes over an hour, when same SQL is made part of a PL/SQL procedure or package.

The key words here are "same conditions and environment". The Oracle Version is 12.1.0.2.0. The table, row count, operation, statistics etc. are same for both (SQL and PL/SQL).

Here is a quick test.

The statement I choose is straight update - so practically no network or terminal display delay

  1. Create a table with say 200,000 rows. ( No indexes etc. whatsoever ).
  2. Turn on SQL_TRACE = ON at server level

SQL : a. update stats b. set timing on

Update TABLE_1 set Character_Col = 'XXXX';

PL/SQL :

a. update stats

create or replace procedure upd_tab as
begin
   update TABLE_1 set Character_Col = 'XXXX';

   dbms_output ( .. print SQL%ROWCOUNT etc. );
end;

When comparing the TRACE, the significant difference is found in the Execute part of trace

SQL :

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Execute      1      2.19       2.40          0       1902     257114      200000

PL/SQL:

call     count       cpu    elapsed       disk      query    current        rows
Execute      1      2.13       5.94          0       1884     256912      200000

So - what I can understand is that there is CONTEXT-SWITCHING taking place when PL/SQL sends statement to SQL. When statement is directly executed as SQL, then no context switching is involved.

It seems my end-users are using cursors to send one UPDATE statement at a time to database from a stored procedure. And for 100,000+ rows, these few seconds are quickly adding up.

It reminds me of Tom Kyte's famous words ( not exactly; hope somebody can find the link )

if you want to do something, do it in SQL,

if SQL is not suitable, then do it in PL/SQL,

if PL/SQL is not suitable, then do it as Java,

if Java is not suitable, then do it as External C procedure,

if External C is not suitable, then think - why we have to do it in first place.

Community
  • 1
  • 1
oradbanj
  • 551
  • 7
  • 23
  • 1
    Without more details, it's impossible to say. My guess is that it is not actually the same SQL statement and that one is using bind variables/ literals/ a SQL profile/ an outline/ something else that the other is not. – Justin Cave Oct 22 '15 at 18:03
  • How do you expect us to answer a question that says *Here's zero useful information. Can you post some wild speculations on the millions of things that might cause this with no more details?* Sure. Here's an answer that's as clear as the details in your question: Sure. You've got a problem in the PL/SQL procedure. Figure out what it is and fix it. – Ken White Oct 22 '15 at 18:05
  • We are using oracle 11.2.0.3, standard edition. We are not using SQL Profiles or SQL baselines. The PL/SQL is using bind variables and the SQL statement (naturally) using hard-coded literals. So could it be possible that the SQL is using latest EXPLAIN plan and for some reason the PL/SQL is not able to use latest EXPLAIN PLAN. – oradbanj Oct 22 '15 at 18:17
  • @JustinCave, Ken White. Question is "what can be wrong?". Valid answers include "Arguments conversion", "Context switch between SQL and PL/SQL engines". Downvotes are not required. Answers are. – Alain Pannetier Oct 22 '15 at 18:25
  • @AlainPannetier - As it sits, the question is overly broad because there are literally thousands of possible answers and nowhere near enough information to determine which of the answers are most likely. – Justin Cave Oct 22 '15 at 18:28
  • @oradbanj, before your question gets closed by the grumpy oracle stars, let me suggest looking into BULK COLLECT. Saved my life once from 12h to a few minutes. – Alain Pannetier Oct 22 '15 at 18:34
  • @oradbanj, "It seems my end-users are using cursors to send one UPDATE statement at a time to database from a stored procedure... 100,000+ rows". This seems to me as a good case for using BULK COLLECT. Here is a solution outline: 1/ Declare an array of records holding all new values for each target row. 2/ Open cursor of rows to update. 3/ Loop on target rows 4/ Populate array - including PK of target rows. 5/ Close cursor, end loop. 6/ forall i in indices of yourarray update yourtable set targerow.tartgetfield1 = yourarray(i).newvalue1,... where targettable.pkid = yourarray.id; commit; – Alain Pannetier Oct 23 '15 at 06:44

1 Answers1

0

Different hints, session parameters, resurce limit, context, bind variables, bug.. Best is to compare execution plans.

clq
  • 180
  • 9
  • yes. I compared execution plans. And the only reason why it takes more elapsed time in PL/SQL is context switching. Notice that CPU time is almost same, because once context switching is complete, it takes same amount of effort on part of CPU to execute it. – oradbanj Oct 22 '15 at 22:33
  • Commonly I would expect context switching to see between several executions of statement. So, statement inside loop or so. If it is only one execution and different elapsed time probably it is not related to context switching.. – clq Oct 23 '15 at 06:38
  • Yes - my end users are running UPDATE in a loop - hence 100,000+ context switches. Even few milliseconds will add up. – oradbanj Oct 23 '15 at 18:03
  • Thanks for info. If context switches are the issue probably bulk ops can be considered. Cheers – clq Oct 23 '15 at 18:55