Re: PL/SQL in SQL (12c feature)

I have many doubts about pragma udf and inline functions, because I tested them too little:

1. I'm not sure what we need consider as context switches with inline functions: Pay attention to test #2 and test #3: http://orasql.org/2013/07/13/oracle-12c-behavior-tests-of-the-inline-functions-identities-and-defaults/ We can see that PL/SQL Virtual Machine started and stopped 6 times within just one function call. Is every "PL/SQL Virtual Machine started" a context switch?
2. I have no 12c now, so I can't test: I think, that my test #2, but with function with pragma UDF will have just one "PL/SQL Virtual Machine started" and one "PL/SQL internal call"( or maybe "Procedure call"). And I can imagine, that there are may be some cases, when inline functions could be slower than stored functions (e.g. too many "PL/SQL virtual machine started", "Procedure Call", etc). Also I haven't tested which pl/sql optimizations work with inline functions(like fetch by 100 rows with PLSQL_OPTIMIZE_LEVEL=2, or auto function inlining with PLSQL_OPTIMIZE_LEVEL=3 ).

--
Best regards,
Sayan Malakshinov
http://orasql.org



> The context switch is still present, since ultimately SQL and PL/SQL

> are processed by different engines. A significant factor in the

> performance hit of a context switch is the conversion of parameters

> and return values, since SQL and PL/SQL handle/process parameters in a

> different way. The optimization when using the WITH clause or PRAGMA

> UDF comes from optimizing the way PL/SQL handles the parameters to

> make it the same (or more similar) to the SQL method, thereby reducing

> the impact of the context switch.

> 

> So going back to the original discussion, the whole reason we care

> about the context switch is the impact on performance (elapsed time

> and CPU). If the context switch cost 0 elapsed time and CPU, we

> wouldn't care if they were present or not. So anything that reduces

> the impact of the context switch (like this optimization) allows us to

> care about it less. :)

> 

> Ultimately, if you can remove function calls from SQL, you should, but

> if you can't this definitely helps...

> 

> In addition to the performance aspect, there is the concern that

> functions that reference database, package and context state "break"

> the read consistency of the query, which you need to think about... :)

> 

> Cheers

> 

> Tim...

> 

--
http://www.freelists.org/webpage/oracle-l