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

From: Jeff Smith <jeff.d.smith_at_oracle.com>
Date: Mon, 19 May 2014 08:04:07 -0700 (PDT)
Message-ID: <8245b207-c5f6-48ac-a451-2076f996764f_at_default>

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:04:07 CEST