Re: PL/SQL in SQL (12c feature)
- From: Sayan Sergeevich Malakshinov <malakshinovss@xxxxxxxxx>
- To: tim@xxxxxxxxxxxxxxx
- Date: Wed, 21 May 2014 15:48:50 +0400
Tim,
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...
>
Other related posts:
