Thanks for the question, Dieter.

Asked: April 30, 2024 - 7:32 am UTC

Last updated: November 04, 2024 - 6:43 pm UTC

Version: Oracle Database 19c

Viewed 1000+ times

You Asked

I understand how to use the online pragma before the invocation of a subprogram that should be inline, but based on the Oracle documentation ( https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-optimization-and-tuning.html#GUID-2E78813E-CF29-409D-9F8B-AA24B294BFA2 ) it should also be possible to use pragma inline to affect every invocation of the specified subprogram:

When the INLINE pragma immediately precedes a declaration, it affects: Every invocation of the specified subprogram in that declaration

Unfortunately when trying to do so it seems as if this would not work as expected when reading the documentation:

ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL';

CREATE OR REPLACE PACKAGE foo IS
PROCEDURE bar;
END foo;
/

CREATE OR REPLACE PACKAGE BODY foo IS
    
PRAGMA INLINE (sub, 'YES');
PROCEDURE sub;

PROCEDURE bar IS
BEGIN
    dbms_output.put_line('bar');
 sub;
END bar;

PROCEDURE sub IS
BEGIN
    dbms_output.put_line('sub');
END sub;

END foo;
/

SELECT * FROM user_errors;

PLW-05011: pragma INLINE for procedure 'SUB' does not apply to any calls

with LiveSQL Test Case:

https://livesql.oracle.com/apex/livesql/s/bnxtewkpw3mxal7nu0v5uvhao

and Chris said...

I think it's useful to include the text just above the one you've quoted

The INLINE pragma affects only the immediately following declaration or statement , and only some kinds of statements.

When the INLINE pragma immediately precedes a declaration, it affects:

* Every invocation of the specified subprogram in that declaration
* Every initialization value in that declaration except the default initialization values of records

(emphasis mine).

So the INLINE pragma only affects the next statement. In the example:

PRAGMA INLINE (sub, 'YES');
PROCEDURE sub;

No subprograms are invoked in the declaration! This matches the requirement Every invocation of the specified subprogram in that declaration. I see no contradiction here.

This is also supported by the cause listed for PLW-05011:

A pragma INLINE was provided, but there was no call to the mentioned procedure in the next statement.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

I still do not really understand

Dieter Oberkofler, June 26, 2024 - 3:36 pm UTC

I still do not really understand and I guess it all boils does to what is the exact meaning of declaration in "The INLINE pragma affects only the immediately following declaration or statement , and only some kinds of statements" is?

I all languages I used you declare a function as inline and the compiler will when "inline" this function whenever used.
In PL/SQL this seems to be differed and I do neither understand why nor how to get to this behaviour?

What does it exactly mean to declare a function as inline as explained in documentation: "When the INLINE pragma immediately precedes a declaration, it affects: Every invocation of the specified subprogram in that declaration"?

Do I have to use the INLINE pragma each time the function is invoked and how would this make sense?

June 27, 2024 - 2:43 am UTC

Here's an easy demo to help explain. You have full control over when you want a routine to be inlined.

SQL> set serveroutput on
SQL> declare
  2    iterations  number := 20000000;
  3    l_start     number;
  4    l_result    number;
  5
  6    function myfunc (p1 number,p2 number) return number as
  7    begin
  8      return p1 + p2;
  9    end;
 10
 11  begin
 12    l_start := dbms_utility.get_time;
 13
 14    for i in 1 .. iterations loop
 15  --    pragma inline (myfunc, 'yes');
 16      l_result := myfunc(1, i);
 17    end loop;
 18
 19    dbms_output.put_line('elapsed: ' || (dbms_utility.get_time - l_start));
 20  end;
 21  /
elapsed: 136

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> set serveroutput on
SQL> declare
  2    iterations  number := 20000000;
  3    l_start     number;
  4    l_result    number;
  5
  6    function myfunc (p1 number,p2 number) return number as
  7    begin
  8      return p1 + p2;
  9    end;
 10
 11  begin
 12    l_start := dbms_utility.get_time;
 13
 14    for i in 1 .. iterations loop
 15      pragma inline (myfunc, 'yes');
 16      l_result := myfunc(1, i);
 17    end loop;
 18
 19    dbms_output.put_line('elapsed: ' || (dbms_utility.get_time - l_start));
 20  end;
 21  /
elapsed: 50

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> set serveroutput on
SQL> declare
  2    iterations  number := 20000000;
  3    l_start     number;
  4    l_result    number;
  5
  6    function myfunc (p1 number,p2 number) return number as
  7    begin
  8      return p1 + p2;
  9    end;
 10
 11  begin
 12    l_start := dbms_utility.get_time;
 13
 14    for i in 1 .. iterations loop
 15      pragma inline (myfunc, 'yes');
 16      l_result := myfunc(1, i);
 17    end loop;
 18
 19    for i in 1 .. iterations loop
 20      pragma inline (myfunc, 'yes');
 21      l_result := myfunc(1, i);
 22    end loop;
 23
 24
 25    dbms_output.put_line('elapsed: ' || (dbms_utility.get_time - l_start));
 26  end;
 27  /
elapsed: 103

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> set serveroutput on
SQL> declare
  2    iterations  number := 20000000;
  3    l_start     number;
  4    l_result    number;
  5
  6    function myfunc (p1 number,p2 number) return number as
  7    begin
  8      return p1 + p2;
  9    end;
 10
 11  begin
 12    l_start := dbms_utility.get_time;
 13
 14    for i in 1 .. iterations loop
 15      --pragma inline (myfunc, 'yes');
 16      l_result := myfunc(1, i);
 17    end loop;
 18
 19    for i in 1 .. iterations loop
 20      pragma inline (myfunc, 'yes');
 21      l_result := myfunc(1, i);
 22    end loop;
 23
 24
 25    dbms_output.put_line('elapsed: ' || (dbms_utility.get_time - l_start));
 26  end;
 27  /
elapsed: 201

PL/SQL procedure successfully completed.

SQL>

I still do not really understand

Dieter Oberkofler, June 27, 2024 - 4:06 am UTC

Thank you for the example.
I understand that using the pragma in each invocation (not declaration) does work but it still does not answer my question:

1) The documentations states "When the INLINE pragma immediately precedes a declaration, it affects: Every invocation of the specified subprogram in that declaration". This is exactly what I would expect and in my understanding means that when using the pragma before the declaration (not invocation) every invocation will be inlined and this does not seem to be the case.

2) Having a fine grained control over what will be inline might (although I cannot really imagine a use case) be useful but the typical use case (and what all other languages I ever used do) is to decide what function should always be inline and not what invocation of a function should be inlined.

June 27, 2024 - 8:26 am UTC

1 - Refer again to the line above this in the docs and the types of statements inlined below it. Inlining only affects the immediately following statement. In a forward declaration of a function, there are no calls, assignments, etc. to be inlined.

Declaration in this context refers to variables or constants. For example:

CREATE OR REPLACE PACKAGE BODY foo IS
    
FUNCTION sub ( p INT ) RETURN INT;

PROCEDURE bar IS
BEGIN
  FOR i IN 1 .. 10000000 LOOP
  DECLARE
    PRAGMA INLINE ( sub, 'YES' );
    v INT := sub ( i ) ; -- This is a declaration; SUB is inlined here
  BEGIN
    NULL;
  END;
  END LOOP;
END bar;

FUNCTION sub ( p INT ) RETURN INT IS
BEGIN
  RETURN p + 1;
END sub;

END foo;
/
/*
LINE/COL  ERROR
--------- -------------------------------------------------------------
10/5      PLW-06004: inlining of call of procedure 'SUB' requested
10/5      PLW-06005: inlining of call of procedure 'SUB' was done
17/1      PLW-06027: procedure "SUB" is removed after inlining
*/

2 - It's rare you'll want/need to do this. If you raise the PLSQL_OPTIMIZE_LEVEL to 3, the PL/SQL compiler will attempt to inline everything anyway.

If you'd like the ability to inline all instances of a function, wherever it's called, raise an enhancement request.

Inlining all invocations of one specific subprogram

mathguy, November 04, 2024 - 4:12 am UTC

If you'd like the ability to inline all instances of a function, wherever it's called, raise an enhancement request.

For me the confusion is caused by what I read in Charles Wetherell's article, "A PL/SQL Inlining Primer", from 2015. Alas the paper seems to have been withdrawn from various places, but it can still be found - my Google search finds it on the "Oracle Blogs" but for some reason the link comes out weird:

https://www.google.com/url?sa=t&source=web&rct=j&opi=89978449&url=https://blogs.oracle.com/content/published/api/v1.1/assets/CONT25B50B25917945089C63978E5BD99B05/native/plsql-inlining-primer-charles.pdf%3FchannelToken%3D6cfdb5758b544e9d97eea1b8b7eeb273

As I understand it, Charles was at the time responsible for the development of the PL/SQL optimizer at Oracle (or in any case one of the main developers), and from what I read about him, he is a serious professional (likely retired by now), not some lunatic who writes about his own fantasies. Here is a quote from his paper:

Pragma INLINE may also appear before a declaration. There are two cases. If the declaration contains initializers and the pragma names a subprogram invoked in the initializations, then the subprogram will be inlined in the execution of the initializers. If the pragma’s subprogram identifier specifies the subprogram being declared, it controls all calls to that subprogram (and any overloads) throughout the declaration scope of the subprogram. This allows inlining to be turned on or off for all invocations of a particular subprogram.

Doesn't this talk exactly about the enhancement you sugest we should request? Did it exist in some versions of PL/SQL, only to be removed from more recent ones? What Charles describes in that quote is exactly what the original poster (Dieter) was asking about, and he (Charles) includes sample code illustrating the same.

Of course, the simple solution is to compile our code with optimization level set to 3 (and compilation type set to native - a separate consideration) and forget about PRAGMA this and PRAGMA that. But the question still makes sense for optimization level set to 2.

November 04, 2024 - 6:43 pm UTC

You can find this article and various other PL/SQL essays at https://blogs.oracle.com/sql/post/a-collection-of-plsql-essays

Yes - Charles was a senior figure in the PL/SQL development team. He's now retired, so we can't check with him to see the background.

In any case, what's written in the docs is what matters - I don't see any contradiction between what's described there and the observed behaviour.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

I still do not really understand

I still do not really understand

Inlining all invocations of one specific subprogram

More to Explore

PL/SQL demos

PL/SQL docs

Using pragma inline to affect every invocation of the specified subprogram

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

I still do not really understand

I still do not really understand

Inlining all invocations of one specific subprogram

More to Explore

PL/SQL demos

PL/SQL docs