Great Explaination!

I remember I read somewhere that even when you do simple things like assignments ( somevar := an_sql_function_here(); ) pl/sql has to call SQL in order to make it so ( like, that would be wrapped in select an_sql_function_here() into somevar from dual ). Is it true ?

Hi Tom,
First off, sorry that this has nothing to do with the current thread. I have been visiting your site *daily* for the last more than two weeks and the "Status" on right hand top corner *always* said "Sorry I have a large backlog right now, please ask a question later". Understandably so, considering the number of follow-ups that you are handling. I was just wondering how this two-day old thread crept in. Am I missing something/some link somewhere?
Thanks in advance for your time.

There was a brief window on Saturday (3/25) around noon EST. He was accepting questions for about 1 hour on that day.. by 2pm his status was again "Sorry I have a backlog..". Seems you missed the window like many. May be you should have some sort of program to page you when tom opens his window -(hey tom, can we signup for an email alert on your site?).

"brief window on Saturday (3/25)..." This thread originated on 3/28. Lets wait to hear from the horse's mouth please.

Guys, 1 man answering questions on a page that gets 20,000 hits a day, do the math.....

Beg to kindly differ with reader, above.
Access to Tom's "Ask a Question" should be based on "luck" or else just plain diligence... not automated! :)

Lets face it Tom does a great job with this site and as has been said before he is only one man.

I'm a daily visitor and try to read most of the new threads in an attempt to extend my knowledge (don't always understand them all though!). Most questions I have can be answered by a quick search of the site. As to availability, UK working hours don't coincide with Tom's working hours so in the 3 or 4 years I've been following this site I've seen it open for questions perhaps five times (in fact I have a question I'm waiting to post at the moment that I can't find a suitable existing thread to piggy back it on so I'll have to wait).

Tom, is there any way you could occasionally shedule the 'open for questions' feature to fit in with other timezones around the world. Give those of us not in the US or central Asia more of a chance?

can be scripted via wget and mail (not too hard)

I personally have a job in one of my production databases
running using utl_htp and utl_smtp
they grab asktom.oracle.com every 2 minutes and inform me
whenever I can ask a question immediately

only messing, but not too hard to do !

my dictionary doesn't contain "erradicate" (with two "r" :o) at all ...

Big "context switch" on this thread ...

Does your dictionary also recommend starting a sentence with an uppercase letter?

Hi Tom,
I was wondering how you go about weeding out scripts from humans only on the basis of user-agents, especially when you have site-downloaders with *configurable* user-agents. Just wondering, because checking other parameters like hit counts/ip addresses seems to be a laborious task...

To Reviewer Steve Kiteley above:
<quote>
so in the 3 or 4 years I've been following this site I've seen it open for questions perhaps five times (in fact I have a question I'm waiting to post at the moment that I can't find a suitable existing thread to piggy back it on so I'll have to wait)
</unquote>
I second you on this count, but it's a pretty discouraging scenario. It's roughly once in a year probably because Tom's "window" opens up when potential posers not in his time zone are sleeping. I guess the (in)frequency at which the Tom's "window" opens gives us _enough_ time to to go through the Oracle documentation and get the right answers we want. Roughly a full year. What say? Are you listening Tom?

Virtue is its own punishment. Well, here's a suggestion that you may like to implement (or probably you may have considered already, and in that case, just ignore):
Let an Oracle user post a problem, and let the Oracle users world-wide who access this portal post a solution. You step in only when you feel your expertise would correct or enhance the best solution that you find in the responses. Just my two cents....

Hi,
I've been waiting two years to post a question, but the status always said "Sorry". I find a couple of Qs here a day or two old. How's that? I'm in south asia. Hope you'd resolve this issue soon.

Tom,

As Gabe said there is a big context switch on this thred, hope it will switch back to the original context.

Thank you for providing an excellent answer. Can you please validate if my understanding below is correct

Lets say I have a pl/sql program in which I run a sql statement. The sql statement uses a user defined pl/sql function which in turn uses an sql statement inside it. So in this case does the context switching work like:

1. begins with 'pl/sql context'
2. switches to 'sql context' when it encounteres the sql
3. switches to 'pl/sql context' when it encouters the pl/sql udf
4. switches to 'sql context' when it encouters the sql inside the pl/sql udf
... so on..
??

I searched Oracle doc, but could not find much info about context switches.. Are there any other resources which you can suggest that talk about this topic?

Your suggestion about views was very helpful. I think we will greatly benefit from that.

Once again thanks for your time and efforts. And very glad to know that you are not moving :).

But can you comment if my understandig above is correct?

hi Tom,

Above in this thread you said:

(the "hit" is most evident when
SQL invokes PLSQL - not so much the other way, when SQL is embedded in PLSQL).

It seems to counter a bit what you said in this thread, e.g.,

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:14339283697418 <code>

you said:

Anytime you see someone taking a flat file and reading it row by row (slow by
slow) -- doing some lookup -- and then inserting the data -- ask yourself "why
isn't that a single sql statement with an external table and maybe a pipelined
function"

*****

Can you please elaborate further on this? Is it that in the "row by row/slow by slow", the performance hit comes from fetching a row from the database - meaning data being passed from a server process to a client process ?

Do I understand correctly: The procedrual part in a PL-SQL procedure is executed in the client process and the SQL is executed in the server?

Thanks,
Naresh.

hi Tom,

It is clear that

insert into t select * from s ;

should not be done procedurally.

And it should be OK to embed SQL in PL-SQL.

My question was actually related to - the "hit" is **MOST** evident when
SQL invokes PLSQL.

So now,

instead of a procedure implementing the below pseudo code

procedure p
for x in (select from .....)
do
function x
done

In what situations is it advantageous to do a pipelined function that implements the logic in <function x> to be used in a SQL statement directly?

Some more thoughts/questions:

1) Is the procedural part in a PL-SQL procedure executed in the client process and the SQL is executed in the server?

2) If (1) is true, then does the pipelined function avoid the IPC data exchange for the fetch operations? In case of bulk collect operations, the IPC calls are anyway going to be reduced, so even this seems to be not much of an advantage for pipelined functions.

What piece of the optimization of using pipelined functions am I missing here?

Thanks,
Naresh.

on
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:8912264456901#28568411897338 <code>
you say, the way to measure the number of context switches is
via
tkprof -- number of execute, fetch, parse calls.

so, does that mean I don't have any context switch at all in the following ?


create or replace package cw is
function to_number(p in varchar2) return number;
end cw;
/


create or replace package body cw is
function to_number(p in varchar2) return number is
begin
begin
return standard.to_number(p);
exception when others then
return null;
end;
end to_number;
end cw;
/


set arraysize 1200

alter session set sql_trace=true;


select cw.to_number(l) l from
(
select to_char(l) l from
(
select level l from dual connect by level < 1000
)
);

select to_number(l) l from
(
select to_char(l) l from
(
select level l from dual connect by level < 1000
)
);

alter session set sql_trace=false;


tkprof shows
...
select cw.to_number(l) l from
(
select to_char(l) l from
(
select level l from dual connect by level < 1000
)
)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.01 0 7 0 999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 7 0 999

....
select to_number(l) l from
(
select to_char(l) l from
(
select level l from dual connect by level < 1000
)
)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 7 0 999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 7 0 999

...

So that means, your answer on
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:8912264456901#28568411897338 <code>
was not correct, doesn't it ?

I suspect there is *no* way to measure number of context switches, correct ?

We have a view which has a pl/sql function call in the where statement, like:

CREATE OR REPLACE FORCE VIEW "V_PO_ATTRIBUTE_W" (...) AS SELECT ....
FROM PO_ATTRIBUTE
WHERE PO_ATTRIBUTE.ORUN_ID = DBA.SESSION_PRIV.GET_ORUN_ID
OR
USER = 'ABCDBA' WITH CHECK OPTION

The underlying table has 2 mio. entries.
When we call that view (with additional where statemnts) then it takes approx. 4 minutes. If we set a fix value in the view where statment, like:
WHERE PO_ATTRIBUTE.ORUN_ID = 7
then the execution time drops to 200 ms!

This happens on our new environment with Oracle 10.2.0.1.0 on 64-bit Linux (SLES) with 10.2.0.1.0 JDBC thin driver.
Before, with the same view and the same data, the performance with the original view was much better (something like 1-2 sec). Old env: Oracle 9.2.0.5.0, JDBC thin driver 10.2.0.1.0, HP-UX 11i.

Is there any change between Oracle 9 and 10 in the area of context switching which might result in poorer performance, or is there any other explanation? Might it be some misconfiguration on our Oracle installation?

Thank you in advance for your help.

We tried both ways you recommended, but this didn't solve it, we still have full table scans with 4 min. execution time.

Defining the function as deterministic did not work because we have parameterless functions which depend on session variables, and with that no deterministic function may be used (it even did not compile, furthermore I read that a deterministic function shall not depend on session variables).

The second approach with
where orun_id = (select session_priv.get_orun_id from dual)
did not help neither.

Any other idea?

How does it come that when using the result of a PL/SQL function it makes a full table scan, but when using a fix value (where orun_id = 7) it does not.

There is an index on orun_id, but it seems it's not taken into account when using the function.

We heard that if the return type of the function does not correspond to the real column type then the index is useless, but the type (number) is correct. And even with "where orun_id = '7'" it executes fast.

from reliable source I have come to know that in 10g that PL/SQL engine and SQL engine have become same. If that is the case then there should not be any context switching between PL/SQL engine to SQL engine

your reliable source is confused.

there is a plsql VM
there is a SQL engine.

The 'feature' your reliable source is talking about is probably the "common SQL parser" project that was part of 9i.  Its goal was to fix this:

ops$tkyte@ORA817DEV> select count(*) over () from dual;
 
COUNT(*)OVER()
--------------
             1
 
ops$tkyte@ORA817DEV> begin
  2  for x in ( select count(*) over () cnt from dual )
  3  loop
  4  null;
  5  end loop;
  6  end;
  7  /
for x in ( select count(*) over () cnt from dual )
                                *
ERROR at line 2:
ORA-06550: line 2, column 33:
PLS-00103: Encountered the symbol "(" when expecting one of the following:
, from
 


whereby many new SQL features were not supported in PLSQL (had to hide them in a view or use dynamic SQL to hide them from the PLSQL parser).  

<quote>
Oracle9i New Features in PL/SQL

    * Integration of SQL and PL/SQL parsers

      PL/SQL now supports the complete range of syntax for SQL statements, such as INSERT, UPDATE, DELETE, and so on. If you received errors for valid SQL syntax in PL/SQL programs before, those statements should now work.

      See Also:

      Because of more consistent error-checking, you might find that some invalid code is now found at compile time instead of producing an error at runtime, or vice versa. You might need to change the source code as part of the migration procedure. See Oracle9i Database Migration for details on the complete migration procedure.  
</quote>

The context switch overhead is smaller, and will likely get smaller over time as incremental refinements are made - but - it is very safe to say it'll always be there.

Unless and until SQL becomes PLSQL or PLSQL becomes SQL of course :) 

First off, many thanks on this Thanksgiving eve for your continuous help to everyone.

A few follow-up q's on the extent of context switching...

1. In pl/sql, when context is switched to sql, is the context switched immediate back to pl/sql after the sql is completed or only when plsql is encountered? For example, if I have two insert statements in row, is the context plsql-sql-plsql-sql-plsql or plsql-sql-plsql?

2. Is a lone function call considered sql or does the switch only occur with a select statement? For example:
select case... into x from dual;
vs
x := case... ;

Thanks,
Steve

Tom,
you've said we shouldn't be afraid to call SQL from PL/SQL despite the context switch as that's what PL/SQl does best, and in general I do agree with you. Alas, some people have taken that advice to a bonkers extreme, and instead of doing l_char := TO_CHAR(l_number, l_format), they instead do SELECT TO_CHAR(l_number, l_format) INTO l_char FROM dual and I can't seem to persuade anyone to listen to me that this is a really bad idea, not only for clarity, but also performance. It gets worse when they use the SELECT FROM dual as a reason to wedge in a DECODE rather than just sticking to pure IF/THEN/ELSE and regular assignments.

Attached is some code and test date to illustrate the point. Surely in cases like this (and I really can't believe it's even necessary to ask this, but like I said, some people don't get it) you are always, without question, better sticking to PL/SQL's easy to understand and quicker IF/THEN/ELSE + assignments rather than SELECT DECODE FROM dual?

Function 1 - PL/SQL only:

CREATE OR REPLACE FUNCTION using_plsql (p_user_option_in IN VARCHAR2,
p_number_in IN NUMBER)
RETURN VARCHAR2
IS
l_ret_value VARCHAR2 (50);
BEGIN
IF (p_user_option_in = 'A')
THEN
l_ret_value := TO_CHAR (p_number_in, '999,999,999.99PR');
ELSIF (p_user_option_in = 'B')
THEN
l_ret_value := TO_CHAR (p_number_in, '999.999.999,99S');
ELSE
l_ret_value := TO_CHAR (p_number_in, '999,999,999.99');
END IF;

RETURN (l_ret_value);
END using_plsql;

Function 2 - using SELECT DECODE FROM dual:

CREATE OR REPLACE FUNCTION using_sql (p_user_option_in IN VARCHAR2,
p_number_in IN NUMBER)
RETURN VARCHAR2
IS
l_ret_value VARCHAR2 (50);
BEGIN
SELECT DECODE (p_user_option_in,
'A', TO_CHAR (p_number_in, '999,999,999.99PR'),
'B', TO_CHAR (p_number_in, '999.999.999,99S'),
TO_CHAR (p_number_in, '999,999,999.99'))
INTO l_ret_value
FROM DUAL;

RETURN (l_ret_value);
END using_sql;

3. The test code:

DECLARE
l_start_time PLS_INTEGER;
l_char_output VARCHAR2(50);
l_user_option VARCHAR2(1) := 'A';
l_sample_size NUMBER := 100000;
l_test_number NUMBER:= 92833928;
BEGIN

dbms_output.put_line('SAMPLE USING_SQL() - ' || using_sql(l_user_option, l_test_number));
l_start_time := dbms_utility.get_time;
FOR i IN 1 .. l_sample_size
LOOP
l_char_output := using_sql(l_user_option, l_test_number);
END LOOP;
dbms_output.put_line('ELAPSED FOR USING_SQL() = ' || ((dbms_utility.get_time - l_start_time)/l_sample_size));

dbms_output.put_line('SAMPLE USING_PLSQL() - ' || using_plsql(l_user_option, l_test_number));
l_start_time := dbms_utility.get_time;
FOR i IN 1 .. l_sample_size
LOOP
l_char_output := using_plsql(l_user_option, l_test_number);
END LOOP;
dbms_output.put_line('ELAPSED FOR USING_PLSQL() = ' || ((dbms_utility.get_time - l_start_time)/l_sample_size));

END;

4. The results:

SAMPLE USING_SQL() - 92,833,928.00
ELAPSED FOR USING_SQL() = .00405
SAMPLE USING_PLSQL() - 92,833,928.00
ELAPSED FOR USING_PLSQL() = .00023

As you can see my tests show that avoiding the (utterly unecessary, and less clear) SELECT DECODE FROM dual, we can get this simple function to run 20-times quicker.

Obviously, if there is more complicated stuff going on, such large gains wouldn't be realised, but as a general rule, we should stick to PL/SQL IF/THEN/ELSE rather than SELECT DECODE FROM dual, correct?

Alan.

SAMPLE USING_SQL() -   92,833,928.00
ELAPSED FOR USING_SQL() = .0018
SAMPLE USING_PLSQL() -   92,833,928.00
ELAPSED FOR USING_PLSQL() = .00005

PL/SQL procedure successfully completed.

Thanks so much for this Tom,
but could you explain why get_cpu_time is better than get_time, either in general or in this specific case?

Surely if we are interested in overall real-world timings rather than purely the CPU cycles, then the more inclusive/comprehensive get_time should be used, no? What am I missing?

Tom,

Thanks for taking these questions. They're very helpful.

I was wondering about the context switch from SQL to PL/SQL you talked about. Does making the PL/SQL function DETERMINISTIC reduce the number of context switches since SQL might have an opportunity to cache the result?

dear tom ,
by using what tool i am able to see a context switching from sql to plsql or vise versa? can you please setup a small example to demonstrate?
thanks

Dear Tom,

first of all thank you for your effort explaining context switch.

I noticed in our databases that there is peformance issue between 10g and 11g running the same PL/SQL code. On 11g R2 the code runs 100% slower than on 10g db. I checked carefully the traces, events and everything else to find out, it is "probably" context swich. I am not 100% sure because I do not how I can measure the time spent on the context switch, however, when I checked everything else and I found no problem there, it seems to me like the problem with the context switch.

Here is the code I tested:
create table perftest2 (id number, wert number, flag char(1 byte), constraint perftest2_pk primary key (id));

declare
anz number := 100000;
v_wert perftest2.wert%type;
t0 integer;
begin
t0 := Dbms_Utility.GET_CPU_TIME();
for i in 1 .. anz
loop
insert into perftest2 values (i, i*1000, 'J');
end loop;
Dbms_Output.Put_Line(Dbms_Utility.GET_CPU_TIME()-t0);
--
t0 := Dbms_Utility.GET_CPU_TIME();
for i in 1 .. anz
loop
select wert into v_wert from perftest2 where id = i;
end loop;
Dbms_Output.Put_Line(Dbms_Utility.GET_CPU_TIME()-t0);
--
t0 := Dbms_Utility.GET_CPU_TIME();
for i in 1..anz
loop
update perftest2
set flag = 'N', wert = wert * 2
where id = i;
end loop;
Dbms_Output.Put_Line(Dbms_Utility.GET_CPU_TIME()-t0);
--
t0 := Dbms_Utility.GET_CPU_TIME();
for v_c in (select id, wert, flag
from perftest2
where id <= 10000)
loop
null;
end loop;
Dbms_Output.Put_Line(Dbms_Utility.GET_CPU_TIME()-t0);
--
t0 := Dbms_Utility.GET_CPU_TIME();
begin
update perftest2
set flag = 'N', wert = wert * 2
where id <= 100000;
end;
Dbms_Output.Put_Line(Dbms_Utility.GET_CPU_TIME()-t0);
--
rollback;
end;
/

What is also strange is the following:
1) in 10g environment I see this in the trace file:
INSERT INTO PERFTEST2
VALUES
(:B1 , :B1 *1000, 'J')


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100000 3.24 3.10 0 241 304989 100000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 100001 3.24 3.10 0 241 304989 100000

in 11g I see the following:
INSERT INTO PERFTEST2
VALUES
(:B1 , :B1 *1000, 'J')


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 63166 3.50 3.36 0 151 192644 63166
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 63167 3.50 3.36 0 151 192644 63166

why only 63166 rows were processed? I see in the table 100000!

2) When I tried to use FORALL clause, it performed equally in both environments.

Thank you for your help.
Regards,
T.

INSERT INTO PERFTEST2
VALUES
 (:B1 , :B1 *1000, 'J')


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 100000      7.62       7.96          0       1531     310518      100000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   100001      7.62       7.96          0       1531     310518      100000

I searched Oracle doc, but could not find much info about context switches.. Are there any other
resources which you can suggest that talk about this topic?

You can find explanation on how it works under the hood in this articles
https://sqlmdx.wordpress.com/2014/09/30/measuring-context-switches/
https://sqlmdx.wordpress.com/2015/12/13/context-switches-may-not-be-an-issue-any-longer/

by using what tool i am able to see a context switching from sql to plsql or vise versa?

As described in above articles you can use dbms_trace (or event 10938) and dbms_hprof to measure context switches

Take care in interpreting the "measures of context switching" given by SQLMDX, as mentioned in the preceding review.

When querying the output, the column CONTEXT_SWITCH measures the number of times the PL/SQL Virtual Machine was started. This is useful for measuring context switches when SQL calls PL/SQL.

When PL/SQL calls SQL, the other column SQL_INVOKE is used. This measures the number of SQL executions. As Tom Kyte said above (search for the first occurence of TKPROF in this thread), you have to measure parse, execute and fetch. TKPROF does that.

I do have a question in all this: can PL/SQL piggyback PARSE, EXECUTE and the first FETCH in one call to the SQL engine, or does it really do a context switch for each bit?

Thanks in advance,
Stew