Performance impact of function calls in an SQL Query (About Context Switching)

Question

Are there any performance impact in calling functions in an SQL statement due to context switching between SQL and PL/SQL engines? Or is the potential impact dependent on the function called?

1. Calling built-in functions:

select dbms_crypto.hash(utl_raw.cast_to_raw(COLUMN),1) from TABLE_NM;

2. Calling PL/SQL functions:

create or replace package PCK as
  function doSomething(val varchar2) return varchar2;
end PCK;

select PCK.doSomething(COLUMN) from TABLE_NM

Are there performance overhead due to function calls? Thanks.

Answer 1

Calling built-in functions:

It depends. But, most of the native SQL functions and built-in functions are compiled in low-level "C language" as a single module in the Oracle kernel. For example, CASE or UPPER function won't have any context switch between the two engines as I said they are in a single module.

Well, there are some built-in functions which are exceptions, like SYSDATE and USER etc. Thomas Kyte has beautifully explained it here.

Calling PL/SQL functions

A SQL call in PL/SQL, or vice-versa, i.e. a PL/SQL call in SQL needs a context switch. 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).