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

From: Tim Hall <tim_at_oracle-base.com>
Date: Wed, 21 May 2014 09:17:09 +0100
Message-ID: <CAP=5zEje6SbaB8dCR-t-1FJXo6ZAokKwty+W5fwOghOd2N4d3g_at_mail.gmail.com>

I'm paraphrasing Christian Antognini, who himself was paraphrasing Brynn Llewellyn (so lots of scope me writing rubbish here :) ), but...

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...

On Mon, May 19, 2014 at 4:13 PM, McPeak, Matt <vxsmimmcp_at_subaru.com> wrote:
> Very cool. I will have to read up on that pragma.
>
> Thanks!
> Matt
>
>
> -----Original Message-----
> From: timseanhall_at_gmail.com [mailto:timseanhall_at_gmail.com] On Behalf Of Tim Hall
> Sent: Monday, May 19, 2014 10:53 AM
> To: McPeak, Matt
> Cc: oracle-l_at_freelists.org
> Subject: Re: PL/SQL in SQL (12c feature)
>
> Depends if the stored PL/SQL is defined with PRAGMA UDF or not.
>
> http://www.oracle-base.com/articles/12c/with-clause-enhancements-12cr1.php#pragma-udf
>
> Cheers
>
> Tim...
>
> On Mon, May 19, 2014 at 3:28 PM, McPeak, Matt <vxsmimmcp_at_subaru.com> wrote:
>> Hi. I’m a bit lazy on this one – looked for 15 minutes and didn’t
>> find anything and it’s sort of just a passing question in my mind
>> anyway. But I thought I’d throw it out to the group…
>>
>>
>>
>> So, 12c has a new feature that allows you to declare PL/SQL functions
>> in-line in SQL with a “WITH” clause. In addition to not requiring
>> privileges to create packages, these PL/SQL functions are executed
>> without a context switch from SQL to PL/SQL, and so are perform better
>> than calls to stored PL/SQL. (Stop me right there if any of that’s
>> not correct)
>>
>>
>>
>> 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?
>>
>>
>>
>> Thanks in advance for your help!
>>
>>
>>
>> Matt
>>
>>

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

Received on Wed May 21 2014 - 10:17:09 CEST