Re: PL/SQL in SQL (12c feature)
- From: Tim Hall <tim@xxxxxxxxxxxxxxx>
- To: vxsmimmcp@xxxxxxxxxx
- Date: Wed, 21 May 2014 09:17:09 +0100
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@xxxxxxxxxx> wrote:
> Very cool. I will have to read up on that pragma.
>
> Thanks!
> Matt
>
>
> -----Original Message-----
> From: timseanhall@xxxxxxxxx [mailto:timseanhall@xxxxxxxxx] On Behalf Of Tim
> Hall
> Sent: Monday, May 19, 2014 10:53 AM
> To: McPeak, Matt
> Cc: oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxxx> 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
>>
>>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
