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

From: Tim Hall <tim_at_oracle-base.com>
Date: Mon, 19 May 2014 16:14:52 +0100
Message-ID: <CAP=5zEhe2NemkaEM9ckCQBmB5bduGDbORGwKnVorWVw9ir+-Yw_at_mail.gmail.com>

Jeff: I would question whether this is a benefit. :)

From my testing, stored code using PRAGMA UDF gives the best performance, so that would be the preferred option in my opinion. Since this is stored code, privileges apply as normal...

For the odd little one-off functions, maybe having it "inline" in the WITH clause is OK, but this could easily degenerate into a copy/paste frenzy over time, which doesn't bode well for code reuse... :)

Cheers

Tim...

On Mon, May 19, 2014 at 4:04 PM, Jeff Smith <jeff.d.smith_at_oracle.com> wrote:
> Also remember that another benefit is for those that don't have ability to create pl/sql objects in a particular database - they can still use that code in their SQL statements now...
>
> -----Original Message-----
> From: Tim Hall [mailto:tim_at_oracle-base.com]
> Sent: Monday, May 19, 2014 11:00 AM
> To: Hans Forbrich
> Cc: Oracle-L Freelists
> Subject: Re: PL/SQL in SQL (12c feature)
>
> My terminology could be slightly wrong here, but my understanding of this is...
>
> The context switch is removed (or the effect minimized) by the PL/SQL having an SQL call signature, rather than a PL/SQL call signature. A stored procedure/function can have the same "benefit" by using PRAGMA UDF.
>
> The numbers certainly seem to stack up. Stored PL/SQL is slower than PL/SQL in WITH clause. PL/SQL in WITH clause is slower than stored PL/SQL defined with PRAGMA UDF.
>
> A assume the latter is better as the stored PL/SQL is already compiled...
>
> Cheers
>
> Tim...
>
> On Mon, May 19, 2014 at 3:41 PM, Hans Forbrich <fuzzy.graybeard_at_gmail.com> wrote:
>> On 19/05/2014 8:28 AM, McPeak, Matt wrote:
>>>
>>> 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?
>>
>> From Steve Feuerstein's blog at
>> http://www.oracle.com/technetwork/issue-archive/2013/13-sep/o53plsql-1
>> 999801.html
>>
>> "This approach offers a way to both “extend” the SQL language with
>> application-specific functionality and reuse (rather than copy) algorithms.
>> A downside of user-defined function execution in SQL is that it
>> involves a context switch between the SQL and PL/SQL execution engines."
>>
>> and
>>
>> "So why would a developer want to copy logic from a PL/SQL function
>> into a SQL statement? To improve performance. When I call my own
>> PL/SQL function in a SQL statement, the SQL engine must perform a
>> performance-affecting context switch to the PL/SQL engine. Moving the
>> code inside the SQL statement means that that context switch no longer occurs."
>>
>> It appears the context switch still occurs outside of the inline
>> PL/SQL
>>
>> My question would be "how did they eliminate the context switch in the
>> first place?"
>>
>> /Hans
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

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

Received on Mon May 19 2014 - 17:14:52 CEST