Re: PL/SQL in SQL (12c feature)
- From: Tim Hall <tim@xxxxxxxxxxxxxxx>
- To: Sayan Sergeevich Malakshinov <malakshinovss@xxxxxxxxx>
- Date: Wed, 21 May 2014 13:59:21 +0100
Hi.
RE: Doubts. Don't we all. As I keep saying to people, I have zero
production 12c experience, so all this is theoretical for me. Just
little tests on a desktop. :)
RE: Context switches. I'm not really too hung up on the terminology
here. Let's ignore the fact the phrase "context switch" was even
mentioned. What can be seen from a little example? From fastest to
slowest...
1) Stored PL/SQL using PRAGMA UDF.
2) Inline PL/SQL in the WITH clause.
3) Stored PL/SQL without PRAGMA UDF.
Are these little tests representative of real workloads? I have no
idea. :) I'm sure there are going to be some issues where this order
is not so predictable.
Cheers
Tim...
On Wed, May 21, 2014 at 12:48 PM, Sayan Sergeevich Malakshinov
<malakshinovss@xxxxxxxxx> wrote:
>
> 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...
>>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
