Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, R.

Asked: March 25, 2006 - 1:22 pm UTC

Last updated: August 13, 2019 - 1:33 am UTC

Version: 10.2

Viewed 50K+ times! This question is

You Asked

Tom,

Hope you are doing well. Thank you verymuch for your tireless efforts towards the benefit of the larger Oracle community. God belss you!

I have questions about the context switching between sql and pl/sql. Why is it necessary in the first place - I mean, since pl/sql is the proprietary language of Oracle, why can't it naturally execute sql without suffering a context switch? I remember reading that in Oracle 9i, pl/sql and sql share the same sql compiler (which enabled the use of sql features like case in pl/sql)..so why we need a context switch (I am sure I did not understand the 9i change correctly), can you please explain?

What are the other performance penalties in using pl/sql? I know some data types are shared between sql and pl/sql (like varchar, date, number etc, but is there a type conversion penalty when using othe pl/sql types such as say integer? What about collection types, is there any penalty there? Lastly, I wanted to know if there is any performance penalty is wrapping oracle built in functions in pl/sql and use it from sql (say, I want to get last_month_first_date in sql. I can use add_months(last_day(<date>), -2))+1 for that, or I can just wrap the same in a pl/sql function.). I want to know if there will be a context switching suffered if I use a pl/sql function in the above case (there is no procedural logic, it is just wrapping of a core oracle function in pl/sql).

Thanks again for your time. Have a nice day!




and Tom said...

PLSQL is a procedural language, it is very much like Java architecturally (actually, the opposite is true - Java is much like PLSQL given the timelines). You take the source code, compile it into byte code (we call it pcode) and then a virtual machine interprets the code and does "procedural stuff".

SQL is a non-procedural language, it is very different from PLSQL or any procedural language really.

The two environments are just "different", separate and distinct. You can do plsql without SQL, you can do SQL (and many times do) without invoking plsql. There is a call overhead to go from SQL to PLSQL (the "hit" is most evident when SQL invokes PLSQL - not so much the other way, when SQL is embedded in PLSQL). Even if this hit is very very small (say 1/1000th of a second) - if you do it enough, it adds up. So, if it can be avoided - it should be.

The "compiler" you are talking about isn't a compiler but rather a "common SQL parser". In the past (8i and before) the various Oracle tools had their own SQL parsers - so there could be times when there where language features in SQL (such as order by in a subuqery, analytics, group by cube/rollup and the like) that the PLSQL SQL parser did not understand (they did not update that parser). So, in order to prevent that from happening in the future - since it was quite bothersome - the developers standardized a common sql parser so that plsql and sql would not need to diverge in the syntax they supported.


You should always use native SQL functions whenever possible (they are in "C", they are in the kernel. If you can use straight SQL and builtin SQL functions (quite a bit can be accomplished with CASE, DECODE, the others - scalar subqueries and the like) you should.

Hide it in a view if you think the syntax is too "hard" to put into the application (views are good, sort of like stored procedures, great encapsulation mechanism). But use straight SQL and builtin functions.

Resort to calling PLSQL from SQL only when you have to.

But don't be afraid at all to invoke SQL from PLSQL - that is what PLSQL does best.

Rating

  (37 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Rahul, March 28, 2006 - 10:13 am UTC

Great Explaination!

Is it true ?

A reader, March 28, 2006 - 10:42 am UTC

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 ?

Tom Kyte
March 28, 2006 - 4:12 pm UTC

no, that is absolutely false.


now,


l_var := sysdate;

that might, but

l_var := upper( '....' );


no - sysdate and user would be exceptions.

2 days old thread?

A reader, March 28, 2006 - 10:56 am UTC

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.


Tom Kyte
March 28, 2006 - 4:13 pm UTC

it didn't always say that, for I have taken quite a few new questions in the last week......


I took my time answering this one. It was submitted on day and I didn't answer it for a day and a half.

To Reader

A reader, March 28, 2006 - 11:05 am UTC

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?).

To A Reader above

A reader, March 28, 2006 - 11:25 am UTC

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

originally submitted on 25-Mar-2006 13:22 Eastern US time, last updated 28-Mar-2006 9:05

A reader, March 28, 2006 - 11:44 am UTC


Alexander, March 28, 2006 - 4:38 pm UTC

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

No email notification device for when Tom is accepting questions!

Robert, March 28, 2006 - 5:16 pm UTC

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! :)

Steve Kiteley, March 29, 2006 - 4:21 am UTC

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?


Tom Kyte
March 29, 2006 - 7:17 am UTC

I travel a lot in those other timezones :)

we are looking into a modified algorithm to let them "trickle in".

Email notification service when you can ask a question

A reader, March 29, 2006 - 5:51 am UTC

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 !

Tom Kyte
March 29, 2006 - 7:19 am UTC

but, as those hit me - I find them and erradicate them based on the user agent. Like people running web whackers (offline site readers). They kill me (since those web whackers seems to like to really really hit the site as fast as they can.

ok !

A reader, March 29, 2006 - 7:20 am UTC


A reader, March 29, 2006 - 8:12 am UTC

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

the context of the context switch ...

Gabe, March 29, 2006 - 9:47 am UTC

Big "context switch" on this thread ...

Tom Kyte
March 29, 2006 - 11:12 am UTC

resuming our non-maskable interrupt

Very Funny, Eh?

A reader, March 29, 2006 - 9:49 am UTC

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

Eradicate scripts?

A reader, March 29, 2006 - 10:48 am UTC

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...

Tom Kyte
March 29, 2006 - 11:15 am UTC

reports against my audit trail. it is not perfect but it works well enough.

Frequency of "Ask a Question"

A reader, March 29, 2006 - 11:42 am UTC

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?

Tom Kyte
March 29, 2006 - 11:52 am UTC

I'm listening, but don't know what to say in response.

Try a change

A reader, March 29, 2006 - 12:07 pm UTC

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....

Tom Kyte
March 29, 2006 - 12:41 pm UTC

that is called a "forum" and we have those on OTN. I'm doing just that for the XE (express edition) of Oracle right now.

Waiting two years

A reader, March 31, 2006 - 12:06 pm UTC

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 Kyte
March 31, 2006 - 1:18 pm UTC

perhaps when human cloning is generally available...

I've taken more than a couple of questions from that region of the world. I am just one person however.

Now, this is in the east coast US timezone, but here is a historical sense of "when I've taken questions"



1 select to_char(timestamp,'hh24'), count(*) from
2 WWC_ASK_SUBMITTED_QUESTIONS$
3 group by to_char(timestamp,'hh24')
4* order by 1
ask_tom@ASKUS> /

TO COUNT(*)
-- ----------
00 599
01 420
02 291
03 289
04 306
05 287
06 347
07 677
08 1274
09 1924
10 1965
11 2135
12 1566
13 2879
14 1730
15 1552
16 1470
17 1173
18 802
19 922
20 1317
21 1622
22 1433
23 1014

24 rows selected.


It is just a fact of the timezone I happen to live in (and I'm not moving :)


R, March 31, 2006 - 2:53 pm UTC

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 :).



Tom Kyte
March 31, 2006 - 3:02 pm UTC

Context switching is a fact of life in all "multiple language environment". It is an implementation detail that wouldn't really be documented - it is just a fact of life.



R, March 31, 2006 - 3:18 pm UTC

But can you comment if my understandig above is correct?

Tom Kyte
March 31, 2006 - 4:43 pm UTC

yes, anytime you go from language 1 to language 2, there will be a switch.

so, if you call plsql (switch from whatever language you are using to plsql) that calls sql (switch from plsql to sql) that calls plsql (switch from sql to plsql)....

Some more elaboration please ...

Naresh, April 02, 2006 - 9:26 am UTC

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.


Tom Kyte
April 02, 2006 - 11:30 am UTC

I was already assuming the code was correct and proper.

eg: that the CODE WAS NOT

begin
for x in (select * from t )
loop
insert into another_t values ...
end loop;
end;

but rather:


begin
insert into another_t select * from t;
end;


sorry for the confusion. I would not be afraid to embedd the insert into as select in PLSQL (to have the control over error handling, execute multiple statements and so on)


You don't want to perform slow by slow coding, but don't be afraid to put sql in plsql

When are pipelined functions more performant?

naresh, April 02, 2006 - 3:45 pm UTC

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.


Tom Kyte
April 03, 2006 - 8:02 am UTC

I doubt I would use a pipelined function for that - bulk fetching in 9i (letting 10g do it for me silently as it does) perhaps. But not a pipelined function.

Not unless I was inserting the data - eg: no procedural code would be left.


insert into another_t select * from table( myfunction( ... ) );

maybe - but then I'd of course look at myfunction to make sure I couldn't just do it in sql in the first place.


1) a stored procedure or anonymous block submitted to the server runs in the server (the dedicated or shared server process).





how many context switches ?

&amp;#931;&amp;#969;&amp;#954;&amp;#961;&amp;#940;&amp;#964;&amp;#951;&amp;#962;, April 10, 2006 - 4:47 am UTC

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

...

Tom Kyte
April 10, 2006 - 5:59 am UTC

you are calling a plsql function from sql - you have a context switch for each call you made to cw.to_number (which is a really really bad idea for a name!)

so how can I measure ?

&amp;#931;&amp;#969;&amp;#954;&amp;#961;&amp;#940;&amp;#964;&amp;#951;&amp;#962;, April 10, 2006 - 6:02 am UTC

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 ?



Tom Kyte
April 10, 2006 - 6:03 am UTC

depends - if you had sql in your plsql - something tkprof would track - you would see it.

generally, plsql called from sql does do "some sql", that would be traced and you'd see how often it was executed.

&amp;#931;&amp;#969;&amp;#954;&amp;#961;&amp;#940;&amp;#964;&amp;#951;&amp;#962;, April 10, 2006 - 6:13 am UTC


PL/SQL call from SQL much slower in Oracle 10 than in Oracle 9

Bojan, May 31, 2006 - 5:07 am UTC

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.

Tom Kyte
May 31, 2006 - 10:09 am UTC

totally insufficient data.

I would likely guess "plan changed, function being called more often than previously" before anything else.



I can say you should either

a) define the function as deterministic if it is (looks like it) for in 10gr2 - that "works"
</code> http://dizwell.com/main/index.php?option=com_jd-wiki&Itemid=170&id=deterministicfunctions <code>

b) use

where orun_id = (select session_priv.get_orun_id from dual)

to let scalar subquery caching to the same for you.

PL/SQL call from SQL much slower in Oracle 10 than in Oracle 9

Bojan, June 15, 2006 - 5:11 am UTC

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.





Tom Kyte
June 15, 2006 - 8:43 am UTC

give full test case for me to look at please.

have you COMPARED THE PLANS? yourself?

About context switching in 10g

zafar Iqbal, August 08, 2006 - 8:23 am UTC

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

Tom Kyte
August 09, 2006 - 9:43 am UTC

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).  

http://docs.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89856/whatsnew.htm#967650

<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 :) 

I'm probably splitting hairs here...

Steve, November 22, 2006 - 10:39 am UTC

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 Kyte
November 24, 2006 - 12:40 pm UTC

1) it would be plsql to sql to plsql to sql to plsql

2) a function call is plsql to plsql, no context switch.

you would want to do the latter, not the former, never use SQL to simply call a function unless you have to (like decode)

excellent, thanks.

Steve, November 27, 2006 - 10:23 am UTC


Callin SQL unecessarily with SELECT FROM dual

Alan Byrne, December 07, 2010 - 6:39 am UTC

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.
Tom Kyte
December 07, 2010 - 11:05 am UTC

I have no idea why someone would want to code:


select f(x) into l_x from dual;

over

l_x := f(x);


It is just "stupid" (it must come from T-SQL and Sqlserver/Sybase coders where they had to do it that way)



... better
sticking to PL/SQL's easy to understand and quicker IF/THEN/ELSE + assignments
rather than SELECT DECODE FROM dual? ...

absolutely, it would be "not smart" to call decode to perform something procedural. You could use CASE instead of if/then/else



Instead of get_time, you should use get_cpu_time - that is more relevant in real life.

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.


that's my result with get_cpu_time

get_time vs. get_cpu_time

Alan Byrne, December 09, 2010 - 5:15 am UTC

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 Kyte
December 09, 2010 - 10:57 am UTC

get_time - you are timing the network (and maybe someone was downloading a movie while you did test 1 but not test 2). You are timing your client OS's ability to scroll a screen (and maybe your machine was opening Excel or something during test 2 but not test 1). You are timing the database servers ability to service you - maybe during test 1 - someone else was in the process of running 15 copies of a program with a for i in 1 .. 10000000 loop in it - but not so during test 2 - your elapsed time for test 1 would be through the roof (because you were waiting on CPU) but for test 2 - it would fly.

You are timing, with elapsed time, lots of external things that have no continuous, guaranteed impact on your process over time.

The cpu time used in the database is an unbiased look at how much of that resource a given approach needs. The elapsed times will fluctuate due to external latencies that you cannot control and will not be uniformly present.

What you call real-world, I call totally artificial unless they are repeated thousands of times - in the real world. CPU time doesn't lie. It gives you a consistent measure of how much work that bit of code does.

CPU time is appropriate in this case, elapsed time - not so much.


Context Switch from SQL to PL/SQL

Jon T, April 05, 2012 - 5:05 am UTC

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?

Tom Kyte
April 06, 2012 - 10:09 am UTC

deterministic will also reduce the number of context switches, yes.

where can i see context switch ?

j.h zheng, April 08, 2012 - 4:42 am UTC

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
Tom Kyte
April 08, 2012 - 5:49 pm UTC

timings - you can set up a simulation if you like.

In this particular case, I have it on the word of the PLSQL product manager that the deterministic optimization in SQL is happening at the SQL layer (whereas the result cache optimization takes place at the PLSQL layer)



context switch 10g vs. 11g

Tomas Torkos, November 27, 2012 - 6:14 am UTC

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.
Tom Kyte
November 29, 2012 - 7:53 am UTC

I cannot reproduce:

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




but suggest you look to see if the trace file was truncated - we might have run out of space or you hit max dump size...

Internals of context switching

Alex R, January 16, 2016 - 7:58 pm UTC

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
Connor McDonald
January 18, 2016 - 12:14 am UTC

Thanks for your input.

On "Internals of context switching" by Alex R.

Stew Ashton, August 12, 2019 - 7:00 am UTC

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
Connor McDonald
August 13, 2019 - 1:33 am UTC

That is up to the whim of the engine. You can see some of this by looking at the some explicit dbms_sql calls

For example, if I do:

dbms_sql.parse('begin nonsense end;')

I get a parse error immediately, but if I do:

dbms_sql.parse('begin nonsense(:bind) end;')

it will say it parsed just fine. It didn't *really* but we deferred the parse until the execution phase.

There's other examples like an and 'open cursor' command typically does nothing because we'll defer execution until the first fetch (because otherwise the work might have been a wasted effort).

So bottom line ... the db decides, we don't :-)

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library