My terminology could be slightly wrong here, but my understanding of this is... The context switch is removed (or the effect minimized) by the PL/SQL having an SQL call signature, rather than a PL/SQL call signature. A stored procedure/function can have the same "benefit" by using PRAGMA UDF. The numbers certainly seem to stack up. Stored PL/SQL is slower than PL/SQL in WITH clause. PL/SQL in WITH clause is slower than stored PL/SQL defined with PRAGMA UDF. A assume the latter is better as the stored PL/SQL is already compiled... Cheers Tim... On Mon, May 19, 2014 at 3:41 PM, Hans Forbrich <fuzzy.graybeard@xxxxxxxxx> wrote: > On 19/05/2014 8:28 AM, McPeak, Matt wrote: >> >> My question is: if an in-line PL/SQL function calls a stored PL/SQL >> function, does it all still execute without a context switch? > > From Steve Feuerstein's blog at > http://www.oracle.com/technetwork/issue-archive/2013/13-sep/o53plsql-1999801.html > > "This approach offers a way to both “extend” the SQL language with > application-specific functionality and reuse (rather than copy) algorithms. > A downside of user-defined function execution in SQL is that it involves a > context switch between the SQL and PL/SQL execution engines." > > and > > "So why would a developer want to copy logic from a PL/SQL function into a > SQL statement? To improve performance. When I call my own PL/SQL function in > a SQL statement, the SQL engine must perform a performance-affecting context > switch to the PL/SQL engine. Moving the code inside the SQL statement means > that that context switch no longer occurs." > > It appears the context switch still occurs outside of the inline PL/SQL > > My question would be "how did they eliminate the context switch in the first > place?" > > /Hans > -- > //www.freelists.org/webpage/oracle-l > > -- //www.freelists.org/webpage/oracle-l