Re: PL/SQL in SQL (12c feature)
- From: Sayan Malakshinov <xt.and.r@xxxxxxxxx>
- To: jeff.d.smith@xxxxxxxxxx
- Date: Mon, 19 May 2014 19:38:57 +0400
I've tested context switches some time ago:
http://orasql.org/2013/07/13/oracle-12c-behavior-tests-of-the-inline-functions-identities-and-defaults/
On Mon, May 19, 2014 at 7:04 PM, Jeff Smith <jeff.d.smith@xxxxxxxxxx> 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@xxxxxxxxxxxxxxx]
> 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@xxxxxxxxx>
> 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
> > --
> > //www.freelists.org/webpage/oracle-l
> >
> >
> --
> //www.freelists.org/webpage/oracle-l
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Best regards,
Sayan Malakshinov
Senior performance tuning engineer
PSBank
http://orasql.org
Other related posts:
